Basic SQL Server operations

(1)

Voxtron Knowledge Base

The Voxtron Factory NV – Hoogkamerstraat 233 – 9140 Temse – Belgium +32 3 7604020 - http://www.voxtron.com

Page 1 of 13

Basic SQL Server operations

KB number KB0042

History 22/05/2008 V1.0 By Thomas De Smet

C

ONTENTS

CONTENTS ... 1 DESCRIPTION ... 1 SOLUTION ... 1 REQUIREMENTS ...13 REFERENCES ...13 APPLIES TO ...13 KEYWORDS ...13

D

ESCRIPTION

This document describes some basics on how to manage and use SQL Server in conjunction with Voxtron software. Focus is on the SQL server versions used by agenTel 6 and Axxium 6.

S

OLUTION

Concepts

Database server: A pc running the SQL Server database engine. Example: MyAgentelServer

Instance: An isolated SQL Server database engine, a database server can be running several named instances, possible running different SQL Server versions. Instances are referenced in the following format: ServerName\InstanceName. Example: MyAgentelServer\AGENTEL or MyAgentelServer\AXXIUM

Database: A database engine contains at least one database. For example: ContactCenter60

Table: Combination of columns that contains data in organized in rows

SQL Command: Order for the SQL server to execute, like Select * From Tuser. This would return all records and all columns from a table called Tuser.

Script: Combination of SQL commands often saved as .sql file. A script can be run manually or automatically. For example AGT60CCDbServicePackInstall.sql runs as part of the service pack installation.

Stored Procedure: Specific construct of SQL commands that accepts input parameters Extended Stored Procedure: Stored procedure that is linked to a DLL file

SQL Agent: Service that performs additional tasks like time based routines and sending of emails. This is required by agenTel.

Authentication: There are two ways of logging in to a SQL server. By using your windows user and password, called windows authentication or by supplying a user and password combination that is defined within SQL server itself, this is called SQL

authentication. To view and manipulate elements of a database the specified user should have enough rights within SQL server itself.

(2)

Page 2 of 13

Versions & editions

A quick rundown of common SQL Server versions and their editions SQL Server 2000 (SQL 8.0)

o MSDE:

 light weight database server based on the full SQL server engine  can be delivered free as part of a software solution

 Database files have a fixed upper size limit of 2GB  Does not include any graphical management tools  Used by agenTel up to version 6

o SQL Server 2000:

 Full featured database server

 Includes the management tool Enterprise Manger  No limitations

SQL Server 2005 (SQL 9.0) o SQL Server 2005 Express

 light weight database server based on the full SQL server engine  Can be freely obtained from the Microsoft website

 Database has a fixed upper size limit of 2GB on the database itself  Additional, but free, management tool available called SQL Server

Management Studio Express  Used by Axxium 6

o SQL Server 2005 Standard Edition:  Full featured database server

 Includes the SQL Server Management Studio tool

Connecting

Start SQL Server Management Studio (Express) by clicking its shortcut in the start menu under Microsoft SQL Server 2005.

Figure 1 Connecting with SQL Server Management Studio

Enter the server\instance, select it from the drop down or select browse to get a list of available SQL servers.

Note that even when connecting to a local server the server\instance combination will be used. SQL Server Management Studio makes no deference between a local or remote server. Select the authentication type. When selecting SQL Server Authentication you have to supply the login and password. Windows Authentication passes the current logged on user

(3)

Page 3 of 13 See the table below for some users and passwords used by Voxtron software.

Instance name Version Username Password

AGENTEL Up to 5 sa (blank)

AGENTEL 6 sa C@llC3nt3r

AGENTEL Up to 6 agenTelAdmin agenTel

AXXIUM 6 release version sa voxtron

AXXIUM 6 SP1 and up sa V@xtr@n01

AXXIUM 6 and up Runtime voxtron

Basic actions

When authentication succeeds the SQL Server Management Studio opens. The left part or object explorer contains all server items in a tree structure. The panes in the right contain the open table, scripts or results. In the Object Explorer to open the Databases container, all elements below the Database container are part of one specific database, all other containers like Security and Server Objects are system wide for this instance.

In the databases Container all databases on the instance. Open up a database to show the components. Open the Tables container to get a list of all tables inside this database. Right click a table and choose Open Table. The table is opened in the right pane and all records are shown. Note that the right pane allows to edit or enter records.

Figure 2 Retrieving data in a table

(4)

Page 4 of 13 Figure 3 Modifying a table

Again right click on a table, choose Script Table as then choose Select to and finally New Query Editor Window. A new tab opens in the right pane containing a SQL script already filled with a basic SELECT statement FROM the current table.

Figure 4 Creating a select script

(5)

Page 5 of 13 Figure 5 Executing the script

Commands that work can be adapted to the FreeSQL rules and copied to an Axxium ODBC state to be used inside the project.

Figure 6 Usage of FreeSQL inside Axxium or Telebutler

Executing scripts

Sometimes it might be required to run a special script against the database. For some specific cases Voxtron might supply you with such a script in the form of a *.sql file. Double click this file on any system with SQL Server Management Studio installed to open the script. You will be asked to enter the connection information. Supply the right information to connect to the intended target database. The script opens in a readable format.

Some scripts require a manual adaptation before running. If this is the case, this will be indicated in the comment at the beginning of the script. Make the necessary adaptations. Check that the database selector on the left side of the execute button points to the right database and then press the execute button.

When you have trouble executing a Voxtron supplied script send the error message to Voxtron Support and if possible also add database backup.

It is highly advisable to backup the database before running any scripts or possible destructive actions against the database.

(6)

Page 6 of 13 during service pack or hot fix installation or when such a script is run manually. You can

always use this table to check for the consistency and version of the agenTel database.

Figure 7 TDBVersion inside ContactCenter60

Backup & Restoring

(7)

Page 7 of 13 Figure 8 Backup options

Remove the current backup destination and add a new one. Browse to the directory where you wish to store the backup and enter a file name + extensions. Choose Ok until the backup starts.

Figure 9 Selecting the destination

(8)

Page 8 of 13

To restore a database from a backup:

Right click on a database or on the databases container and select tasks, restore.

Figure 11 Restoring

Select from device to restore from a file. Then choose Add and browse to select the file containing the backup. Specify the database where it should be restored to. Note that you can change this name to create a new database or duplicate. Click OK.

Figure 12 Selecting the restore source

When restoring databases between servers all users with access to the database are not automatically restored! This should be done manually. See the section on authentication below.

Under options some more settings can be specified. One set of options that is noteworthy is the database file paths. When restoring to a new database instance these point to the default SQL Server Data path. In agenTel 60 this should be \program

(9)

Page 9 of 13 Figure 13 Restore options

Authentication

Connect to your database server and inside the object explorer you will find a logins entry as part of the security container. Here all users with specific rights on the server level are defined. If you wish to add a user, click right on the logins container and choose new login. To modify an existing user, click right on the user name and choose properties.

Figure 14 The logins container

(10)

Page 10 of 13

Figure 15 SQL User properties

Under the server roles option you can specify a general security level, for example administrator.

Figure 16 Server role

(11)

Page 11 of 13 generate scheduled statistics.

Figure 17 User Mapping

Notice that in the object explorer under the database itself you also have a security and logins container. Here all users that have specific rights on this database are listed. This excludes the inherited from the general user role because of their overall server access like administrators. One common issue is that after a database restore the users listed here are not correct. Although the usernames can be identical the SQL users listed on the database level might be deferent then those configured on the server level. In these cases no access is granted. To resolve this it suffices to remove all user entries on the database level to repopulate the database with the right user references.

Figure 18 Security on the database level

Jobs

SQL Server Jobs are scripts that are scheduled to run at a specific date and time. For this to work the SQL Server Agent service should be running.

This feature is used by agenTel to schedule the automatic creation of statistic reports.

(12)

Page 12 of 13 More interesting information can be found by double clicking on the Job Activity Monitor.

Here you can view job status, job settings and manually start jobs.

Figure 19 Job Activity Monitor

(13)

Page 13 of 13 Figure 20 SQL Agent logging

R

EQUIREMENTS

SQL Server Enterprise Manger SQL Server Management Studio

SQL Server Management Studio Express

R

EFERENCES

See MS SQL Book Online, the SQL online help reference for in depth syntax and feature discussion

http://msdn.microsoft.com/nl-nl/library/bb545450%28en-us%29.aspx Download SQL Server Express