Tandberg TMS SQL User Manual

Download

Cisco TMS SQL Database

Product Configuration Guide

December 2010

D14216.01

Table of contents

Tip format .................................................................................................................................................... 5

TIP T100 –Different types of SQL Server ................................................................................................... 6

Extra reference links ............................................................................................................................ 6

TIP T101 –SQL Server management tools ......................................................................................... 7

Extra reference links ............................................................................................................................ 7

TIP T103 –SQL Versions bundled with Cisco TMS .................................................................................... 8

TIP T104 –Cisco TMS SQL version compatibility ....................................................................................... 9

TIP T105 –Cisco TMS database and files ................................................................................................ 10

TIP T106 –The SQL Server instance........................................................................................................ 11

TIP T107 – Identifying the SQL Server instance name ............................................................................ 12

TIP T108 –SQL authentication modes...................................................................................................... 13

Extra reference links .......................................................................................................................... 13

Tip T109 –When do I need a full version of SQL? .................................................................................... 14

Extra reference links .......................................................................................................................... 14

TIP T109 –Using osql for Cisco TMS tasks .............................................................................................. 15

Simple examples for Cisco TMS use ................................................................................................ 16

Extra reference links .......................................................................................................................... 16

TIP T200 –Enabling remote access to SQL ............................................................................................. 17

Extra reference links .......................................................................................................................... 17

TIP T201 –SQL Server 2005 and firewalls ............................................................................................... 18

Possible scenarios ............................................................................................................................. 18

Using Windows Firewall for a single instance ................................................................................... 18

Using Windows Firewall for multiple instances ................................................................................. 18

Extra reference links .......................................................................................................................... 19

TIP T202 –SQL Server 2000 and Firewalls .............................................................................................. 20

Possible Scenarios ............................................................................................................................ 20

Using Windows Firewall for single instance ...................................................................................... 20

Using Windows Firewall for multiple instances ................................................................................. 20

To find the port a SQL Server instance is currently using: ................................................................ 20

Extra reference links .......................................................................................................................... 21

TIP T300 –Backing up the Cisco TMS Database using osql .................................................................... 22

Extra reference links .......................................................................................................................... 22

TIP T301 –Restoring the Cisco TMS Database using osql ...................................................................... 23

Restoring a database to the same SQL server ................................................................................. 23

Restoring a database to a different SQL server ................................................................................ 23

Extra reference links .......................................................................................................................... 24

TIP T302 –Automating backups with SQL 2005 Express ......................................................................... 25

Extra reference links .......................................................................................................................... 26

TIP T303 –Detaching and reattaching databases .................................................................................... 27

To detach the Cisco TMS database using osql ................................................................................. 27

To attach the Cisco TMS database using osql. ................................................................................. 27

Extra reference links .......................................................................................................................... 27

TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express ........................................................................ 28

Cisco TMS customers running MSDE 2000 and Cisco TMS versions 9.0 through 10.0 ................... 28

Cisco TMS Customers running MSDE 2000 and Cisco TMS Versions 11.0 through 11.9.1 ............ 29

Alternative 1 – Install SQL Server manually ...................................................................................... 29

Cisco TMS Database Knowledge Tips Page 2 of 35

Table of contents

Pros ................................................................................................................................................... 29

Cons .................................................................................................................................................. 30

Alternative 2 – Use Cisco TMS Installer to install SQL Server .......................................................... 31

Pros ................................................................................................................................................... 31

Cons .................................................................................................................................................. 31

SQL code to update indexes and statistics ....................................................................................... 32

Extra reference links .......................................................................................................................... 33

TIP T305 –Resetting/changing/forgotten sa password ............................................................................. 34

Extra reference links .......................................................................................................................... 34

Cisco TMS Database Knowledge Tips Page 3 of 35

Document revision history

Revision 1.0 Initial Release of Document Revision 1.1 Applied Cisco techdocs template, product names changed, TANDBERG

references changed to Cisco Revision 1.2 Applied new Cisco product names Revision 1.3 2nd stage rebranding

Introduction

This document is a collection of various tips for Cisco TelePresence Management Suite (Cisco TMS) administrators working with SQL backend databases. Because Cisco TMS uses a standard SQL server for its database, there are many tools and methods available to complete tasks. Many of these tasks can be completed in several different ways or with different commands. These tips are not written to be taken as the only way to achieve a goal – simply as a verified way so that Cisco TMS owners who are not as familiar with SQL or its management tools have easy references.

Tip format

Each Tip will be numbered for each reference. You can click on any tip number to jump to that tip. Each tip will specify the version of Cisco TMS and SQL Server version it applies to. Due to changes in

Cisco TMS and requirements, not all tips will apply to all installations. Tip information is limited to Cisco TMS versions 9.0 or newer for clarity purposes

Cisco TMS Database Knowledge Tips Page 5 of 35

Tips category – General

TIP T100 –Different types of SQL Server

Cisco TMS Versions SQL Server Versions

SQL v7 – All Versions MSDE – All Versions

All

Microsoft has produced many versions of their SQL Server. The ones most significant in regards to Cisco TMS have been:



SQL Server v7 – Introduced 1998 – Full featured version. Replaced by SQL Server 2000



MSDE – Microsoft Database Engine – Same Engine as SQL v7 – Freely distributable version intended for software integrators. Replaced by MSDE 2000

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions



SQL Server 2000 – Introduced 2000 – Full featured version. Replaced by SQL Server 2005



MSDE 2000 –MSDE product based on the SQL 2000 Engine - Freely distributable version intended for software integrators. Replaced by SQL Server 2005 Express



SQL Server 2005 – Introduced 2005 – Full featured version.



SQL Server 2005 Express – Freely distributable version based on the SQL 2005 Engine



SQL Server 2008 – Microsoft’s next SQL Server release – slated for 2008 release.

Each fully featured version of SQL was sold in several variants which varied support for amount of memory, processors, add-on features, etc. Cisco TMS requirements did not require more than the ‘Standard’ edition for fully featured SQL Servers.

The MSDE and Express variants use the same database engine as their fully featured versions but have specific performance throttles and features removed. The performance limits may be significant to Cisco TMS depending on the size and type of Cisco TMS installation. See ‘Tip T109 –When do I need a full version of SQL?’ for additional information.

Extra reference links

Comparison of SQL Server 2005 Editions SQL Server 2005 Express Download

Cisco TMS Database Knowledge Tips Page 6 of 35

Tips category – General

TIP T101 –SQL Server management tools

All

Microsoft provides both graphical and command line interfaces to manage the SQL Server. In general, a tools installed by a newer server version can control an older version, but not vice versa. Management tools are automatically installed on the server where SQL Server is installed. Client-only tools can be installed on other computers to control SQL Servers Remotely.



osql – command line tool which can be used to interact with nearly all aspects of SQL server. Installed on any machine where SQL Server or SQL Management Tools have been installed



sqlcmd – Command line tool introduced with SQL 2005 intended to replace osql. Installed on any machine where SQL 2005, SQL 2005 Express, or SQL 2005 Management Tools are installed. osql is still available with SQL 2005 but is marked as depreciated

Cisco TMS Versions SQL Server Versions

SQL v7 – All Versions MSDE – All Versions SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions



SQL Enterprise Manager – GUI Configuration tool installed with full versions of SQL 7 and SQL

2000. Can be installed on additional client computers but is not available for download



SQL Server Management Studio – GUI Configuration tool installed with full versions of SQL 2005. Can be installed on additional client computers but is not available for download



SQL Server Management Studio Express – GUI Configuration tool freely available for download from Microsoft

Extra reference links

SQL Server Management Studio Express Download

Cisco TMS Database Knowledge Tips Page 7 of 35

Tips category – General

TIP T103 –SQL Versions bundled with Cisco TMS

All N/A

To simplify installation for Cisco TMS customers, the Cisco TMS installer as an option would install a free version of the SQL Server engine. The version has changed as Cisco TMS requirements and version availability has matured.



Cisco TMS v9.0 through Cisco TMS v11.1 – MSDE 2000 SP3a



Cisco TMS v11.5 to current – SQL Server 2005 Express

Cisco TMS Versions SQL Server Versions

Cisco TMS Database Knowledge Tips Page 8 of 35

Tips category – General

TIP T104 –Cisco TMS SQL version compatibility

All

The SQL Server versions supported for each version of Cisco TMS are listed in the Cisco TMS Release notes. The major changes in support can be summarized as:



Cisco TMS 9.0 to Cisco TMS 9.21 – MSDE, MSDE 2000, SQL 2000



Cisco TMS 9.6 to Cisco TMS 10.0 – MSDE 2000, SQL 2000



Cisco TMS 11.0 to Cisco TMS 11.9.1 – MSDE 2000, SQL 2000, SQL 2005, SQL 2005 Express



Cisco TMS 12.0 to current – SQL Server 2005, SQL Server 2005 Express

Please see the Installation and Getting Started Manual for your Cisco TMS version for specific version requirements and service pack levels

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Cisco TMS Database Knowledge Tips Page 9 of 35

Tips category – General

TIP T105 –Cisco TMS database and files

All

The Cisco TMS database is installed and maintained by the Cisco TMS installer. Since Cisco TMS v9.0 – the Cisco TMS database is named tmsng . The database consists of two files, a data file and log file – tmsng.mdf and tmsng_log.ldf respectively. These files will be created in the default data directory of the SQL Server. For default installations this will be:



For MSDE/SQL 2000 Servers - C:\Program Files\Microsoft SQL Server\MSSQL\Data



SQL Server 2005 Express is installed as the named instance \SQLTMS . The data directory is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\MSSQL.1\MSSQL\DATA

The Cisco TMS data and log files may not be directly manipulated as long as the database is attached to the SQL Server. All interaction must be through the SQL Server interface. To work with the files directly, the database should be detached from the SQL Server (removing the database from the server without deleting it).

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Cisco TMS Database Knowledge Tips Page 10 of 35

Tips category – General

TIP T106 –The SQL Server instance

All

SQL Servers are installed as the default instance, or a named instance. The default instance is normally reached via IP or hostname of the server. A named instance is reached by appending \<instancename> to the IP or hostname. Example: productionsql\sqltms . Named instances allow multiple SQL servers to be operating on the same physical server. Configuring a server to run as a named instance can only be configured at time of installation. SQL 2000 by default installs as the default instance. SQL 2005 Express by default will install as the named instance \sqlexpress



Prior to Cisco TMS 11.5, MSDE 2000 installed by Cisco TMS would be installed as the default instance.



Starting with Cisco TMS 11.5, SQL 2005 Express installed by Cisco TMS would be installed as the named instance \SQLTMS

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

When using osql, the –S parameter is used to specify which server to connect to. Example: osql –E –S localhost\sqltms would connect to the named instance SQLTMS on the

local server

Cisco TMS Database Knowledge Tips Page 11 of 35

Tips category – General

TIP T107 – Identifying the SQL Server instance name

All

If you do not know the SQL Server’s instance name, you cannot connect to it. The easiest way to find the name of an installed named instance is to look in the Services Control Panel of the Windows Server running the SQL Server. Start Menu->Control Panel->Administrative Tools->Services



MSDE and SQL 2000 services will be named MSSQL$INSTANCENAME – The default instance is named MSSQLSERVER



SQL 2005 and 2005 Express services will be named SQL Server(INSTANCENAME) – If there is no name in parentheses, it is installed as the default instance

You may also use the osql utility to find local SQL instances and any remote instances that are broadcasting their presence on the local network.

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions



Open a command prompt on a computer with SQL Server installed or one with the SQL Management Tools installed



Enter the command osql –L



The command will list any servers it was able to discover

Cisco TMS Database Knowledge Tips Page 12 of 35

Tips category – General

TIP T108 –SQL authentication modes

All

When a client connects to a SQL server, it must authenticate to the server with a SQL Login. The server can be configured in one of two modes for how clients authenticate. Windows Authentication or Mixed Mode Authentication. Windows Authentication is using accounts from the Windows Server system. Accounts can be either local or domain users. Mixed Mode Authentication allows Windows Authentication but additionally SQL Server Authentication where logins can be defined in the SQL server itself.

When using Windows Authentication, the credentials of the user running the SQL client are automatically used to authenticate against the SQL server. When using SQL Server Authentication, the SQL client provides a specified username and password which may differ from those of the user themselves.

The authentication mode is configured for an entire SQL instance. When installed, an administrator account named ‘sa’ is created as the Administrator when using SQL Server Authentication. If installing with Windows Authentication, local administrators of the Windows Server are automatically granted administration rights within SQL Server.

Cisco TMS requires the SQL Server be in Mixed Mode Authentication as it uses SQL Server Authentication to connect to the SQL server.

Cisco TMS Versions SQL Server Versions

SQL v7 – All Versions MSDE – All Versions SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Extra reference links

Authentication Modes in SQL Server 2000

Cisco TMS Database Knowledge Tips Page 13 of 35

Tips category – General

Tip T109 –When do I need a full version of SQL?

Cisco TMS 11 and newer

Cisco TMS ships with a free version of SQL Server to accommodate smaller installations. This edition of SQL Server is not suitable for all installations, depending on usage and size due to the restrictions on the free versions of SQL Server.

If running MSDE 2000 upgrading to SQL Server 2005 Express or a full edition of SQL is recommended if any of the below criteria are met:



Installations larger than 50 systems



Scheduling/Monitoring a Cisco TelePresence MPS MCU



Scheduling conferences larger than 8 participants



Using a external integration product with Cisco TMS (Exchange, 3rd Party Booking, etc)

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions



Need to use Cisco TMS versions newer then version 11.9.1

SQL Server 2005 is less restrictive then MSDE 2000 and can accommodate most installations but a full version of SQL Server 2005 is required if any of the below criteria are met



Database is greater than 4gigs in Size



Wish to utilize more than 1 CPU and/or 1gig of RAM for SQL Server



Any clustering/failover solutions are to be used for the SQL Server

Extra reference links

SQL Server 2005 Features Comparison

Cisco TMS Database Knowledge Tips Page 14 of 35

Tips category – General

TIP T109 –Using osql for Cisco TMS tasks

All

osql is the command line utility installed with SQL server. For SQL Server 2005, osql has been superseded by the new sqlcmd utility, but is still available for use. osql may be intimidating to users for server configuration tasks, but is well suited for quick changes or simple queries. Many instructions or changes from Tech Support may be listed using osql for its precision and general availability. osql is best used by opening a dedicated command prompt on the Windows Server. It will be added to the path for Windows, so it can be executed without having to navigate to a specific directory.

For Cisco TMS related tasks, the osql syntax needed is simple. Syntax help can be seen by using osql

-? Note that command line switches are case sensitive! The most common switches used with Cisco TMS operations are listed below

Cisco TMS Versions SQL Server Versions

SQL v7 – All Versions MSDE – All Versions SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Switch Description

-S server\instance

Specifies the SQL Server Address to connect to. Can be a hostname, IP or WINS name. Connect to a named instance by appending \instancename

Examples:

-S prodsql.company.com\sqlexpress

-S localhost\sqltms

-E

Use Windows Authentication to authenticate to the SQL Server. Will try connecting to the server using the username and password you logged into the computer with

-U username

Use SQL Server Authentication with the specified username. If –P parameter is not specified, login is attempted with a NULL password.

Examples:

-U sa

-U john.smith

Connect to named instance sqlexpress at prodsql.company.com

Connect to named instance sqltms on local server

Connect using username sa

Connect using username john.smith

-P password

Use SQL Server Authentication with the specified password. Used in conjunction with the –U option. If –P is specified but no password supplied, the system will prompt for the password when the command is executed

Examples:

-U sa –P coolPass

-U sa -P

Cisco TMS Database Knowledge Tips Page 15 of 35

Tips category – General

-d database

-Q “query”

-i filename

Specify the database to connect to. Equivalent to saying ‘use databasename’. For Cisco TMS operations, you will almost always specify the tmsng database

Examples:

-d tmsng

Connect to database named tmsng

Executes a query and immediately exits. Use double quotation marks around the query and single quotation marks around anything embedded in the query. Used to run a single command against the server.

Examples:

-Q “select * from acluser”

Runs the t-sql query select * from acluser

Executes a SQL script from the specified filename. Useful when running a batch of commands. Filenames that include spaces should be enclosed in double quotation marks. To ease specifying filenames, files can be dragged and dropped to the command window and the full path will automatically be typed out

Examples:

-I “c:\update scripts\test.sql”

Will execute the SQL code in the file test.sql

-o filename

Outputs result of command to a file rather than to the screen. Useful when running large scripts or queries with lots of output.

Simple examples for Cisco TMS use



Execute a script c:\update.sql against a local Cisco TMS SQL server install while logged in as a Windows administrator

osql –E –S localhost\sqltms –d tmsng –i “c:\update.sql”



Run a query ‘select username from acluser’ against a local Cisco TMS SQL server install while logged in as a Windows administrator and output results to c:\output.txt

osql –E –S localhost\sqltms –d tmsng –o c:\output.txt –Q “select username from acluser”

Extra reference links

How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility

Cisco TMS Database Knowledge Tips Page 16 of 35

Tips category – Networking

TIP T200 –Enabling remote access to SQL

Cisco TMS Versions SQL Server Versions

Tips category – Networking

11.0 or newer

SQL Server 2005 installs in a secure fashion that only allows local connections to the database by default. This includes SQL Server 2005 Express installed by the Cisco TMS installer. To connect to the database from a remote computer, you must enable remote access.

To enable remote access

1. Open the SQL Server Surface Area Configuration tool from the Microsoft SQL Server Program

Group in the Start Menu

2. Expand under the SQL Server to Database Engine and click on Remote Connections

3. By Default, Local Connections Only is selected. Select Using TCP/IP Only and Click Apply

4. The Database Engine must be restarted to make the change take effect. Click on Service under the

database engine in the left panel. In the details pane, click Stop, and once the service stops, click Start to restart it.

If using named instances, you should enable the SQL Server Browser as well to allow all instances to be found from a single connection point. Some restricted security scenarios require connecting to the explicit port of each instance rather than using the SQL Server Browser.

5. To enable the browser, click on SQL Server Browser in the left panel.

6. In the details pane, change startup type to Automatic, and click Start to start the service immediately

If there are firewalls enabled or between you and the server, you must open ports for the SQL Connection – See TIP T201 –SQL Server 2005 and firewalls for more details

SQL 2005 – All Versions SQL 2005 Express – All Versions

Extra reference links

How to configure SQL Server 2005 to allow remote connections

Cisco TMS Database Knowledge Tips Page 17 of 35

TIP T201 –SQL Server 2005 and firewalls

Cisco TMS Versions SQL Server Versions

Tips category – Networking

11.0 or newer

SQL Server 2005 requires one TCP port per named instance. Named instances by default will run on an unassigned dynamic port. Default instances will run on TCP Port 1433. The SQL Server Browser service is used to tell clients what port a named instance is currently using. The SQL Server Browser service uses UDP Port 1434 . Restrictive networks can force an instance to run on a fixed port allowing specific firewall rules to be made and remove the need for SQL Server Browser. The Windows Firewall on the SQL Server can be setup to allow the dynamic ports for a single instance of SQL Server.

SQL 2005 – All Versions SQL 2005 Express – All Versions

Possible scenarios



Only using the default instance? Open TCP Port 1433 in your firewall



Only using one instance and only Windows Firewall - no external firewalls between SQL and client? Use Windows Firewall steps below



Using multiple instances on same server with local firewall? Must configure fixed ports for each instance and configure firewall rules for those ports. SQL Server Browser is optional



Using external firewalls between client and server? Must configure fixed ports for each instance and configure firewall rules for those ports. SQL Server Browser is optional

Using Windows Firewall for a single instance

These steps allow a single instance to run on dynamic ports with Windows Firewall Enabled on the SQL Server

1. Open the Control Panel, open Network Connections, right-click the active connection, and then click

Properties

2. Click the Advanced tab, and then click Windows Firewall Settings

3. Click the Exceptions tab

4. Click Add Port. Enter SQL Server Browser in the Name text field, type 1434 in the Port Number text

field, select UDP, and then click OK.

5. Click Add Program

6. Click Browse, and navigate to the instance of SQL Server. The Cisco TMS default installed SQL

Server will be at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\Sqlservr.exe

7. Click OK twice to close the Windows firewall program.

Using Windows Firewall for multiple instances

Each SQL instance must be configured to run on a fixed port. See the Microsoft article at the end of this Tip for instructions on configuring fixed ports. Use the steps below to configure the Windows Firewall

1. Open the Control Panel, open Network Connections, right-click the active connection, and then click

Properties

2. Click the Advanced tab, and then click Windows Firewall Settings

3. Click the Exceptions tab

4. Click Add Port. Enter the name of the instance in the Name text field, type the port number assigned

to the instance in the Port Number text field, select TCP, and then click OK.

5. Repeat Step 4 for each instance the server will use

6. If SQL Server Browser is going to be enabled (optional to allow connecting by name only), Click Add

Port. Enter SQL Server Browser in the Name text field, type 1434 in the Port Number text field, select UDP, and then click OK.

7. Click OK to close the Windows firewall program.

Cisco TMS Database Knowledge Tips Page 18 of 35

Extra reference links

How to: Configure a Firewall for SQL Server Access How to: Configure a Server to Listen on a Specific TCP Port

Tips category – Networking

Cisco TMS Database Knowledge Tips Page 19 of 35

TIP T202 –SQL Server 2000 and Firewalls

Cisco TMS Versions SQL Server Versions

Tips category – Networking

SQL Server 2000 requires one TCP port per named instance. The first instance will run on TCP Port

1433. Secondary named instances will run on a dynamically assigned port. SQL Server will listen on UDP 1434 to assist clients trying to connect to a secondary instance. Fixed ports can be assigned to each instance to simplify firewall connectivity. Dynamic ports in SQL 2000 requires additional client configuration that is beyond the scope of this Tip.

Cisco TMS 9.0 through

11.9.1

SQL 2000 – All Versions MSDE 2000 – All Versions

Possible Scenarios



Only using one instance? Open TCP Port 1433 in your firewall



Only using one instance and only Windows Firewall - no external firewalls between SQL and client? Use Windows Firewall steps below



Using multiple instances on same server with local firewall? Must configure fixed ports for each instance and configure firewall rules for those ports.



Using external firewalls between client and server? Must configure fixed ports for each instance and configure firewall rules for those ports. SQL Server Browser is optional

Using Windows Firewall for single instance

These steps allow a single instance to run on dynamic ports with Windows Firewall Enabled on the SQL Server

1. Open the Control Panel, open Network Connections, right-click the active connection, and then click

Properties

2. Click the Advanced tab, and then click Windows Firewall Settings

3. Click the Exceptions tab

4. Click Add Port. Enter SQL Server in the Name text field, type 1433 in the Port Number text field,

select UDP, and then click OK.

5. Click OK twice to close the Windows firewall program.

Using Windows Firewall for multiple instances

1. Open the Control Panel, open Network Connections, right-click the active connection, and then click

Properties

2. Click the Advanced tab, and then click Windows Firewall Settings

3. Click the Exceptions tab

4. Click Add Port. Enter the name of the instance in the Name text field, type the port number assigned

to the instance in the Port Number text field, select TCP, and then click OK.

5. Repeat Step 4 for each instance the server will use

6. If connecting by name is to be used, UDP Port 1434 must also be enabled. Click Add Port. Enter

SQL Server Browser in the Name text field, type 1434 in the Port Number text field, select UDP, and then click OK.

7. Click OK to close the Windows firewall program.

To find the port a SQL Server instance is currently using:

1. On the server that is running SQL Server 2000, start the Server Network Utility

2. Click the General tab, and then select the instance that you want from the Instances list

3. Click TCP/IP, and then click Properties. The TCP/IP port number for this instance is shown

Cisco TMS Database Knowledge Tips Page 20 of 35

Tips category – Networking

Extra reference links

How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port

Cisco TMS Database Knowledge Tips Page 21 of 35

Tips category – Maintaining/Upkeep

TIP T300 –Backing up the Cisco TMS Database using osql

All

A SQL database must be backed up from the SQL Server to generate a .bak file and then the resulting file can be backed up. Backups can be performed using several tools.

Backups can be taken without taking the database offline, but because backups can take a significant amount of time to complete, the database should be idle to ensure the backup is consistent. Performing backups during times of low usage minimizes this risk.

To fully ensure the database is idle, stop all Cisco TMS Windows Services and web servers pointed at the Cisco TMS database.

SQL provides several types of backups, but to perform a full backup issue the following command in a command prompt

osql –S servername –E –Q “backup database tmsng TO DISK = ‘c:\filename.bak’”

filename.bak is now a full backup of the database tmsng and is free to be moved or stored For additional help on osql command line options, see ‘TIP T109 –Using osql for Cisco TMS tasks’

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Extra reference links

How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility

Cisco TMS Database Knowledge Tips Page 22 of 35

Tips category – Maintaining/Upkeep

TIP T301 –Restoring the Cisco TMS Database using osql

All

A database can be restored to an existing database, or have it create a database if it does not exist. If restoring a database to a different server or newly installed server, additional steps must be taken. The below examples assume a default Cisco TMS installation and you are a SQL administrator.

Restoring a database to the same SQL server

To restore a database, the database must be idle and have no active connection. To stop all connections to the Cisco TMS database, stop all Cisco TMS Windows Services and IIS web servers pointed at the Cisco TMS database.

Open the Services Control Panel. Start Menu -> Control Panel -> Administrative Tools -> Services Find the Services whose names begin with TMS. Right-click on each and click Stop. Repeat for all

services whose name starts with TMS Right-Click the World Wide Web Publishing Service and select Stop Repeat the above steps for all Cisco TMS servers pointed at the Cisco TMS database SQL provides several types of backups, but to perform a full backup issue the following command in a

command prompt. Example to restore the Cisco TMS database from a backup set at c:\tms\backup.bak

osql –S servername –E –Q “restore database tmsng FROM DISK = ‘c:\tms\backup.bak’”

Once the restore is complete, restart the services that were stopped using the Services Control panel by right-clicking on each and selecting Start.

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Restoring a database to a different SQL server

Open the Services Control Panel. Start Menu -> Control Panel -> Administrative Tools -> Services Find the Services whose names begin with TMS. Right-click on each and click Stop. Repeat for all

services whose name starts with TMS Right-Click the World Wide Web Publishing Service and select Stop Repeat the above steps for all Cisco TMS servers pointed at the Cisco TMS database The backup set contains the physical file path of the database files. When switching servers, the

physical location for database files is often different. This requires adding addition MOVE parameters to the restore command for each file in the backup set to tell the server where to restore the database files on the new server. Typically the new location for the files should be the DATA folder of the new SQL server.

The files that make up the tmsng database may vary based on when your database was created. To verify the filenames and paths of files in your backup set c:\tms\backup.bak, use the following command in a command prompt. This will output each file, their path, and additional information.

osql –E –S servername –Q “RESTORE filelistonly FROM DISK = ‘c:\tms\backup.bak’”

If the file paths are different on the new server, you must add the MOVE parameter for each file. The syntax is:

Cisco TMS Database Knowledge Tips Page 23 of 35

Tips category – Maintaining/Upkeep

RESTORE databasename FROM DISK = ‘filename’ WITH MOVE ‘file’ TO ‘path\filename’, MOVE ‘file’ to ‘path\filename’

If the new server’s DATA directory is d:\databases the command syntax to restore the backup to the new folder would be:

osql –E –S servername –Q “RESTORE database FROM disk = ‘c:\tms\backup.bak’ WITH MOVE ‘tmsng’ TO ‘d:\databases\tmsng.mdf’, MOVE ‘tmsng_log’ TO ‘d:\databases\tmsng_log.ldf’”

Once the restore is complete, restart the services that were stopped using the Services Control panel by right-clicking on each and selecting Start.

For additional help on osql command line options, see ‘TIP T109 –Using osql for Cisco TMS tasks’

Extra reference links

How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility

Backing Up and Restoring Databases in SQL Server Copying Databases with Backup and Restore RESTORE Arguments (Transact-SQL)

Cisco TMS Database Knowledge Tips Page 24 of 35

Tips category – Maintaining/Upkeep

TIP T302 –Automating backups with SQL 2005 Express

Cisco TMS Versions SQL Server Versions

Cisco TMS 11.0 or newer

SQL 2005 Express can be managed with the SQL Management Studio Express (SMSE) which makes it easy to perform tasks - including backups.

Cisco TMS ships with a DB Management Utility that was originally created because MSDE did not have any GUI tools making it difficult for customers to maintain their databases. With the availability of SMSE, Cisco TMS DB Management Tool is of limited value. Customers looking to add maintenance for their databases should be looking at SMSE as a way to manage their databases.

One omission from SMSE is the lack of a scheduler agent. This means while you can back up the database easily, you cannot automate this process from within SMSE.

There are several ways to accomplish this depending on your needs. The basic premise is simply to create a SQL script or BAT file to execute the SQL commands to do the backup, and have the Windows Scheduler task run the script. The problem with this method is it does not provide for functionality such as rotating backup files.

An open source method to achieve automation in backups and file rotation is described here

http://www.sqldbatips.com/showarticle.asp?ID=27 . This includes a stored procedure method and a

stand-alone utility to perform the task. This site is referenced by Microsoft for alternatives to automating backups.

The code for the procedure and application are maintained in the open source site Codeplex at

http://www.codeplex.com/ExpressMaint

Below is an example that would be used with a default Cisco TMS installation with steps to keep things as simple as possible. Administrators should read the documentation and follow the supplied examples if they wish to customize this or are using a non-default installation.

Perform the following on the Cisco TMS server while logged in as a Windows Administrator. These steps are to backup the database daily, and keep the last three backups.

1. Create a directory on the Cisco TMS server to use for backups - c:\sqlbackups

2. Create a reports directory under the backup directory - c:\sqlbackups\reports

3. Get the expressmaint console app from the Codeplex website . Save the exe file to the backup

directory c:\sqlbackups

4. Open a command prompt using the Start Menu, and navigate to the backups directory - cd

c:\sqlbackups

5. Test backup utility by entering: expressmaint -S localhost\sqltms -D tmsng -T DB -R c:\sqlbackups\reports -RU

DAYS -RV 3 -B c:\sqlbackups -BU DAYS -BV 3 –V

6. After the backup successfully runs, we need to automate it using the Scheduler Task. Under Control

Panel -> Scheduled Tasks, Double-click Add Scheduled Task to start the Scheduled Task Wizard. Click Next in the first dialog box

7. Click Browse, browse to c:\sqlbackups\Expressmaint.exe and then click Open

8. Type a name for the task e.g. DAILY FULL BACKUP and then choose Daily from the scheduling

options and click Next

9. Specify the time and frequency to run the backup. Example: 04:00, every day, starting with today's

date. Click Next

10. Type the name and password of the account that will execute this task. Enter administrator and the

password to the Windows Administrator account.

11. Click Next, select the checkbox to Open the Advanced Properties for this task and then click Finish

12. In the Run text box, enter

c:\sqlbackups\expressmaint.exe -S localhost\sqltms -D tmsng -T DB -R c:\sqlbackups\reports -RU DAYS -RV 3 -B c:\sqlbackups -BU DAYS -BV 3 –V

SQL 2005 – All Versions SQL 2005 Express – All Versions

Cisco TMS Database Knowledge Tips Page 25 of 35

13. Click OK. If prompted, supply the password for the account again

Extra reference links

Expressmaint website

Tips category – Maintaining/Upkeep

Cisco TMS Database Knowledge Tips Page 26 of 35

Tips category – Maintaining/Upkeep

TIP T303 –Detaching and reattaching databases

All

A set of database files can be detached from a SQL server, which frees up the files and later attached to the same SQL server or another. Detaching and reattaching is another way to move a database between servers. There cannot be any active connections to the database to detach it from a SQL server

To detach the Cisco TMS database using osql

osql –E –S servername –Q “sp_detach_db ‘tmsng’”

Once detached, the database files, tmsng.mdf and tmsng_log.ldf are now free to be copied, moved, etc.

To attach the Cisco TMS database using osql.

Put the tmsng.mdf file in the data directory of the SQL server, e.g. C:\Program Files\Microsoft SQL Server\MSSQL\Data\MSSQL.1\MSSQL\Data\tmsng.mdf

osql –E –S servername –Q “sp_attach_db database ‘tmsng’, ‘C:\Program Files\Microsoft SQL Server\MSSQL\Data\MSSQL.1\MSSQL\Data\tmsng.mdf'”

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Extra reference links

How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility

Cisco TMS Database Knowledge Tips Page 27 of 35

Tips category – Maintaining/Upkeep

TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express

Microsoft provides several alternatives for upgrading your SQL Server version. Microsoft documentation on the topic is available at Upgrading MSDE 2000 to SQL Server 2005 Express . The MSDE 2000 instance installed by Cisco TMS was installed with a MSI installer, so it can be upgraded in-place, or removed and install SQL 2005 separately. There is not one upgrade path that is decidedly better than others for customers who have had SQL installed by Cisco TMS. For Cisco TMS customers who are using a dedicated SQL server, you should consult with your Database Administrator for the best upgrade path based on what installation and other services are being used by the SQL Server.

To simplify the task for Cisco TMS customers, this tip will outline possible upgrade scenarios for Cisco TMS customers who have had SQL installed by Cisco TMS. Please read the introduction text in each to see which applies to your situation and is best for you.

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions

Cisco TMS v9.0 and newer

MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Cisco TMS customers running MSDE 2000 and Cisco TMS versions 9.0 through 10.0

If you are currently running a Cisco TMS version older then Cisco TMS v11.0, you must also upgrade your Cisco TMS version to support SQL 2005. Customers currently running newer versions of Cisco TMS, refer to Cisco TMS Customers running MSDE 2000 and Cisco TMS Versions 11.0 through

11.9.1

This upgrade can be achieved with several different methods, the below is a suggestion method aimed at harmonizing the installation to be as close as possible to the current Cisco TMS defaults. These steps will upgrade the SQL Server and the Cisco TMS version.

1. Read the current Cisco TMS release notes and Installation manual and ensure your server meets

the current OS and hardware requirements.

2. Run the Cisco TMS Uninstaller Program from the Add/Remove Programs Control Panel. No

customer data will be lost. Reboot the server at the end of the installation as prompted.

3. Detach the tmsng database from the SQL Server. Open a command prompt and enter

osql –E –S localhost –Q “sp_detach_db ‘tmsng’”

4. Copy the tmsng.mdf file from the SQL server data directory to a safe location and make a backup.

The default SQL data directory is C:\Program Files\Microsoft SQL Server\MSSQL\Data

5. Uninstall Microsoft SQL Server Desktop Engine from the Add/Remove Programs Control panel

6. Delete the SQL Server's install folder. Default folder is C:\Program Files\Microsoft SQL Server

7. Check if your computer has the Microsoft .NET 2 Framework installed by looking for it in the

Add/Remove Program Control Panel. If it is not installed, download the Microsoft .NET v2.0 framework from the Microsoft’s download site and complete the installation. If your server is not up to date with latest Windows features, take note of the Windows Installer and IE requirements on the download page and follow the links on the website to upgrade those components.

8. Download SQL Server 2005 Express from Microsoft’s download site and start the installation. The

installation will perform checks on your computer for compatibility. Resolve any requirements and continue the installation until you get to the Registration Information step.

9. On the Registration Information step, Uncheck the Hide Advanced configuration options checkbox

so all options are shown and click Next

10. Accept the defaults for the Feature Selection page and click Next

11. For Named Instance, ensure Named Instance is selected and enter the name SQLTMS

12. For Service account, leave the default values and click Next

13. For Authentication Mode, select Mixed Mode Authentication and enter a password for the sa

account. Click Next

14. For Collation, leave the default values and click Next

15. Uncheck the User Instance checkbox, check the Add user to SQL Admin… checkbox and click Next

Cisco TMS Database Knowledge Tips Page 28 of 35

Tips category – Maintaining/Upkeep

16. For Error Reporting, leave the defaults and click Next

17. Click Install on the summary page to start the installation. Wait for the installation to complete and

click Finish

18. Move the tmsng.mdf file you backed up earlier to the data directory of the new SQL Server. Move

the tmsng.mdf file to the C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data directory

19. Open a command prompt and enter the following command as a single command

osql -E -S localhost\sqltms -Q "sp_attach_db 'tmsng', 'c:\program files\microsoft sql server\mssql.1\mssql\data\tmsng.mdf'"

20. The server will report a new log file was created and will show several steps upgrading the database.

If you get an error that the osql command is unknown, log out and log back into the server to refresh your PATH environment variable and try again.

21. Your original tmsng database is operational and running on SQL Server 2005 Express. You can

verify this by running the following command in your command prompt. If the command completes with no errors – you are ready.

osql -E -S localhost\sqltms -d tmsng -Q "select * from information_schema.tables"

22. Microsoft recommends updating the indexes and statistics on databases that have been upgraded

from SQL 2000 to SQL 2005. Follow the SQL code to update indexes and statistics instructions to create a SQL script to perform the task. Return here when completed. You may now continue with the installation of any Cisco TMS version v11.0 or higher.

NOTE: When performing the Cisco TMS installation, the registry key containing the location of the tmsng database is out of date and therefore the default settings for which SQL Server to use will be incorrect. When running the installer, you will be forced to use the Custom installation. On the screen where you specify which SQL Server to use enter localhost\sqltms for the SQL Server address, and enter the sa password setup created during the SQL Server installation. Complete the remainder of the installation as normal. Future upgrades will be able to use the Complete installation option if desired.

23. Downloading and installing SQL Server Management Studio Express is recommended for future

management of the SQL Server.

Cisco TMS Customers running MSDE 2000 and Cisco TMS Versions 11.0 through 11.9.1

If you are currently running a Cisco TMS version of the 11.x series, your current version is already compatible with SQL 2005, you only need to upgrade the SQL Server. There are two possible scenarios to perform this upgrade a) upgrade the SQL server yourself or b) use the Cisco TMS installer to install the SQL Server and restore your data. Upgrades to newer versions of Cisco TMS should be addressed after upgrading your SQL Server.

This upgrade can be achieved with several different methods, the below is a suggestion aimed at harmonizing the installation to be as close as possible to the current Cisco TMS defaults. Each method has pros/cons – please read the introduction texts before choosing a path.

Your server must meet the pre-requisites for SQL Server 2005 Express. These should not be a problem if your Cisco TMS is up to date as the Cisco TMS requirements overlap the SQL requirements. If your current Cisco TMS version is earlier than 11.5, you may need to install the Microsoft .NET v2.0 framework prior to installing SQL Server.

Alternative 1 – Install SQL Server manually

This method has the administrator run the SQL Server 2005 Express installer manually.

Pros



No Cisco TMS Reconfiguration



Less Steps

Cisco TMS Database Knowledge Tips Page 29 of 35

Tips category – Maintaining/Upkeep



Quicker

Cons



Requires you complete the installation of SQL Server steps manually instead of automated



Keeps older Instance Name – will not be in line with Cisco TMS defaults going forward



Less chance for reconfiguration if desired

The following instructions assume you have MSDE 2000 and Cisco TMS on the same server in the default Cisco TMS configuration. The steps below should be completed by a user logged into the server running Cisco TMS as a Windows Administrator

1. Stop all connections to the database from Cisco TMS. To stop all connections to the Cisco TMS

database, stop all Cisco TMS Windows Services and IIS web servers pointed at the Cisco TMS database.

a. Open the Services Control Panel. Start Menu -> Control Panel -> Administrative Tools ->

Services

b. Find the Services whose names begin with Cisco TMS. Right-click on each and click Stop.

Repeat for all services whose name starts with Cisco TMS c. Right-Click the World Wide Web Publishing Service and select Stop d. Repeat the above steps for all Cisco TMS servers pointed at the Cisco TMS database

2. Check if your computer has the Microsoft .NET 2 Framework installed by looking for it in the Add/Remove Program Control Panel. If it is not installed, download the Microsoft .NET v2.0 framework from the Microsoft’s download site and complete the installation. If your server is not up to date with latest Windows features, take note of the Windows Installer and IE requirements on the download page and follow the links on the website to upgrade those components.

3. Download SQL Server 2005 Express from Microsoft’s download site and start the installation. The installation will perform checks on your computer for compatibility. Resolve any requirements and continue the installation until you get to the Registration Information step.

4. On the Registration Information step, uncheck the Hide Advanced configuration options checkbox so all options are shown and click Next

5. Accept the defaults for the Feature Selection page and click Next

6. For Named Instance, select Default Instance – This is to upgrade the MSDE 2000 installation rather than install a new separate instance. Ensure Default Instance is selected and click Next

7. For Existing Components, mark the checkbox next to SQL Server Database Services 8.xx.xx and click Next

8. The next screen specifies how to connect to the database during the upgrade. Your current administrator should have access to the database, so select Windows Authentication and click next. If Windows Authentication will not work, you can select SQL Server Authentication and specify your sa user and password instead

9. The installer will perform an analysis and outline any issues preventing an upgrade. There should be no errors reported, if so, follow the instructions provided to resolve each problem.

10. The next screen is for the Service account, leave the default values of using Local System. SQL Browser is not necessary as we are installing as the default instance. Uncheck SQL Browser and click Next

11. Uncheck the User Instance checkbox, and Check the Add user to SQL Admin… checkbox

12. For Error Reporting, leave the defaults and click Next

13. Click Install on the summary page to start the installation. Wait for the installation to complete and click Finish

14. Your original tmsng database is operational and running on SQL Server 2005 Express. You can verify this by running the following command in your command prompt. If the command completes with no errors – you are ready.

osql -E -S localhost\sqltms -d tmsng -Q "select * from information_schema.tables"

15. Microsoft recommends updating the indexes and statistics on databases that have been upgraded from SQL 2000 to SQL 2005. Follow the instructions in the SQL Code to update indexes and

Cisco TMS Database Knowledge Tips Page 30 of 35

Tips category – Maintaining/Upkeep

statistics section near the end of this tip to create a SQL script to perform the task. Return here when completed.

16. You should restart the Cisco TMS server to allow all services the chance to reconnect to the SQL database. After the restart, verify the Cisco TMS page loads properly by logging into Cisco TMS. It is recommended to download and install SQL Server Management Studio Express for future management of the SQL Server.

Alternative 2 – Use Cisco TMS Installer to install SQL Server

This method uses the functionality of the Cisco TMS Installer to perform the installation of the SQL Server.

Pros



Easier steps to perform



Installer handles pre-requisites



Harmonizes install to current Cisco TMS defaults

Cons



Requires having Cisco TMS Installer available



May take longer to complete

1. SQL Server 2005 Express is not installed by the Cisco TMS installer in versions before Cisco TMS

11.5. If you are running a Cisco TMS version between 11.0 or 11.1 – you must upgrade to Cisco TMS 11.5 or newer before starting this process or use Alternative 1 – Install SQL Server manually to upgrade your SQL Server. If you are running Cisco TMS 11.5 through Cisco TMS 11.9.1, continue on.

2. You must have the Cisco TMS installer for the exact version of Cisco TMS you are currently running to complete this process. If you do not have the installer for the version you are currently running, contact Cisco Support to obtain a copy of the Cisco TMS installer.

3. Run the Cisco TMS Uninstaller Program from the Add/Remove Programs Control Panel. No customer data will be lost. Reboot the server at the end of the installation as prompted.

4. Detach the tmsng database from the SQL Server. Open a command prompt and enter

osql –E –S localhost –Q “sp_detach_db ‘tmsng’”

5. Copy the tmsng.mdf file from the SQL Server’s data directory to a safe location and make a backup. The default SQL data directory where the file is located is C:\Program Files\Microsoft SQL Server\MSSQL\Data

6. Uninstall Microsoft SQL Server Desktop Engine from the Add/Remove Programs Control panel

7. Delete the SQL Server's install folder. Default folder is C:\Program Files\Microsoft SQL Server

8. Start the Cisco TMS Installer for the same version you were running previously. The old database connectionstring is in the registry and the installer detects this as out of date. You are forced to use the Custom installation.

9. Proceed through the Installer until you get to the screen where you specify the SQL Server properties. Select the option to Install the database on this machine/server and click Next

10. On the next screen, enter a password to use for the sa account. This is the SQL administrator account.

The next screens allow you to configure your Cisco TMS installation. However, we will be restoring your original database, so the values here are not important as they will be overwritten when we restore the database.

11. The next screen allows you to specify the Collation for the database. Leave it as Default and click Next.

Cisco TMS Database Knowledge Tips Page 31 of 35

Tips category – Maintaining/Upkeep

12. The next screen prompts you for your release and option keys. Do not enter any keys and click Next. You will be warned this will leave the server in Trial mode. Acknowledge the warning and continue.

13. The next screen has the Server settings, leave all the values at default and click Next.

14. The next screen is for the default Zone configuration. You cannot leave these fields blank, so enter any information for the fields and click Next. Remember the values will be overwritten later so the values entered are not important.

15. The next screen allows you to specify the installation folders. You should reuse the same folders for the Cisco TMS installation that you had previously. The SQL Server folders can be customized if desired. These values are significant and will not be overwritten.

16. The last screen is the summary; click Next to complete the installation, including the installation of the SQL Server. SQL Server 2005 Express will be installed as the named instance \SQLTMS

17. When the installation is complete, you will be prompted to restart the server now or later. Choose to restart the server now and log in again once the server restarts. The Cisco TMS installer will finish some tasks at the start of your login – this is normal.

18. Stop all Cisco TMS Windows Services and IIS web servers pointed at the Cisco TMS database.

a. Open the Services Control Panel. Start Menu -> Control Panel -> Administrative Tools ->

Services

b. Find the Services whose names begin with TMS. Right-click on each and click Stop. Repeat for

all services whose name starts with TMS

c. Right-Click the World Wide Web Publishing Service and select Stop

19. Delete the current tmsng database from the SQL Server. Open a command prompt and enter

osql –E –S localhost\sqltms –Q “drop database tmsng”

20. Copy the tmsng.mdf file copied earlier in this process to the DATA directory of the SQL Server. Default location is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\MSSQL.1\MSSQL\Data

21. Attach the older database to the new SQL Server. Open a command prompt and enter the following command as a single command

osql -E -S localhost\sqltms -Q "sp_attach_db 'tmsng', C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\MSSQL.1\MSSQL\Data tmsng.mdf'"

22. The server will report a new log file was created and will show several steps upgrading the database.

23. Your original tmsng database is now updated to 2005 format and the Cisco TMS server is configured to point at the new Cisco TMS Server. You can verify the database by running the following command in your command prompt. If the command completes with no errors – you are ready.

osql -E -S localhost\sqltms -d tmsng -Q "select * from information_schema.tables"

24. Microsoft recommends updating the indexes and statistics on databases that have been upgraded from SQL 2000 to SQL 2005. Follow the SQL code to update indexes and statistics instructions to create a SQL script to perform the task. Return here when completed.

25. Restart the TMS services that were stopped earlier using the Services Control Panel or just restart the Server. Once all services have been restarted, log into the Cisco TMS webpage to verify it is operating properly.

Your Cisco TMS Server is now running SQL Server 2005 Express and has all the original data you had prior to the upgrade. It is now ready for upgrades to future versions if required. Downloading and installing SQL Server Management Studio Express is recommended for future management.

SQL code to update indexes and statistics

1. Create a new text file using Notepad and copy the below text into the text file. Save the file as update.txt

DECLARE @table_name varchar(1000) declare c1 cursor for SELECT name

Cisco TMS Database Knowledge Tips Page 32 of 35

Tips category – Maintaining/Upkeep

FROM sysobjects WHERE xtype = 'U' order by name open c1 fetch next from c1 into @table_name while @@Fetch_Status = 0 begin print(@table_name) DBCC DBREINDEX (@table_name, '') fetch next from c1 into @table_name end print('finished') close c1 deallocate c1 GO DECLARE @table_name varchar(1000),@sql nvarchar(4000) declare c1 cursor for SELECT name FROM sysobjects WHERE xtype = 'U' order by name open c1 fetch next from c1 into @table_name while @@Fetch_Status = 0 begin print(@table_name) Select @sql = 'UPDATE STATISTICS '+ @table_name +' WITH FULLSCAN' exec sp_executesql @sql fetch next from c1 into @table_name end print('finished') close c1 deallocate c1 GO

2. Execute the script against your Cisco TMS database by entering the following command in a command prompt. Replace server with localhost or localhost\sqltms depending on your installation

osql –E –S server –d tmsng –i update.txt

Extra reference links

Upgrading MSDE 2000 to SQL Server 2005 Express SQL Server 2005 Express Download Microsoft .NET Framework 2.0 Service Pack 1 (x86) SQL Server Management Studio Express Download

Cisco TMS Database Knowledge Tips Page 33 of 35

Tips category – Maintaining/Upkeep

TIP T305 –Resetting/changing/forgotten sa password

All

If the sa password for the Cisco TMS SQL Server needs to be changed or has been lost/forgotten, the account’s password may be reset by another administrator account. The Windows administrator is an administrator of the SQL Server by default, so logging in as that user allows the sa password to be reset.

1. Log into the Windows server hosting Cisco TMS open a command prompt

2. Enter the command below. Change complexpass with the password of your choice.

osql –E –S localhost\sqltms –Q “sp_password @old = null, @new = 'complexpwd', @loginame ='sa'”

3. Use the TMSTools app on the Cisco TMS Server

4. Under the Configuration Menu, Open Change DB Connect Settings

5. Update the password field to the new sa password and click OK to save the changes

6. Restart the Cisco TMS Server to ensure all services restart using the new password

Note: If using an external SQL Server, perform the command on the SQL server instead of the Cisco TMS Server and update the –S parameter to the instance’s name.

Cisco TMS Versions SQL Server Versions

SQL 2000 – All Versions MSDE 2000 – All Versions SQL 2005 – All Versions SQL 2005 Express – All Versions

Extra reference links

How to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition

Cisco TMS Database Knowledge Tips Page 34 of 35

Tips category – Maintaining/Upkeep

THE SPECIFICATIONS AND INFORMATION REGARDING THE PRODUCTS IN THIS MANUAL ARE SUBJECT TO CHANGE WITHOUT NOTICE. ALL STATEMENTS, INFORMATION, AND RECOMMENDATIONS IN THIS MANUAL ARE BELIEVED TO BE ACCURATE BUT ARE PRESENTED WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED. USERS MUST TAKE FULL RESPONSIBILITY FOR THEIR APPLICATION OF ANY PRODUCTS.

THE SOFTWARE LICENSE AND LIMITED WARRANTY FOR THE ACCOMPANYING PRODUCT ARE SET FORTH IN THE INFORMATION PACKET THAT SHIPPED WITH THE PRODUCT AND ARE INCORPORATED HEREIN BY THIS REFERENCE. IF YOU ARE UNABLE TO LOCATE THE SOFTWARE LICENSE OR LIMITED WARRANTY, CONTACT YOUR CISCO REPRESENTATIVE FOR A COPY.

The Cisco implementation of TCP header compression is an adaptation of a program developed by the University of California, Berkeley (UCB) as part of UCB’s public domain version of the UNIX operating system. All rights reserved. Copyright © 1981, Regents of the University of California.

NOTWITHSTANDING ANY OTHER WARRANTY HEREIN, ALL DOCUMENT FILES AND SOFTWARE OF THESE SUPPLIERS ARE PROVIDED “AS IS” WITH ALL FAULTS. CISCO AND THE ABOVE-NAMED SUPPLIERS DISCLAIM ALL WARRANTIES, EXPRESSED OR IMPLIED, INCLUDING, WITHOUT LIMITATION, THOSE OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OR ARISING FROM A COURSE OF DEALING, USAGE, OR TRADE PRACTICE.

IN NO EVENT SHALL CISCO OR ITS SUPPLIERS BE LIABLE FOR ANY INDIRECT, SPECIAL, CONSEQUENTIAL, OR INCIDENTAL DAMAGES, INCLUDING, WITHOUT LIMITATION, LOST PROFITS OR LOSS OR DAMAGE TO DATA ARISING OUT OF THE USE OR INABILITY TO USE THIS MANUAL, EVEN IF CISCO OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Cisco and the Cisco Logo are trademarks of Cisco Systems, Inc. and/or its affiliates in the U.S. and other countries. A listing of Cisco's trademarks can be found at www.cisco.com/go/trademarks. Third party trademarks mentioned are the property of their respective owners. The use of the word partner does not imply a partnership relationship between Cisco and any other company. (1005R)

Any Internet Protocol (IP) addresses and phone numbers used in this document are not intended to be actual addresses and phone numbers. Any examples, command display output, network topology diagrams, and other figures included in the document are shown for illustrative purposes only. Any use of actual IP addresses or phone numbers in illustrative content is unintentional and coincidental.

Cisco TMS Database Knowledge Tips Page 35 of 35

Tandberg TMS SQL User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual