Cisco TMS SQL DATABASE User Manual

Tips category – Maintaining/Upkeep
Cisco TMS Database Knowledge Tips Page 29 of 35
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 SQL DATABASE Specifications

General

Brand	Cisco
Model	TMS SQL DATABASE
Category	Software
Language	English

Summary

Introduction

Tip format

Explains the numbering, structure, and applicability of tips within the document.

Tips category – General SQL Server Topics

TIP T100 - Different types of SQL Server

Details the various SQL Server versions relevant to Cisco TMS, including MSDE and Express editions.

TIP T101 - SQL Server management tools

Discusses command-line and GUI tools for managing SQL Server, such as osql and SSMS.

TIP T103 - SQL Versions bundled with Cisco TMS

Explains which SQL Server versions were included with Cisco TMS installers historically.

TIP T104 - Cisco TMS SQL version compatibility

Outlines the SQL Server versions compatible with different Cisco TMS releases.

TIP T105 - Cisco TMS database and files

Details the default naming and location of Cisco TMS database files (mdf, ldf).

TIP T106 - The SQL Server instance

Explains the concept of default and named SQL Server instances and how they are used.

TIP T107 – Identifying the SQL Server instance name

Provides methods to find the name of a SQL Server instance using Windows Services or osql.

TIP T108 - SQL authentication modes

Describes SQL Server authentication modes (Windows vs. Mixed Mode) and TMS requirements.

TIP T109 - When do I need a full version of SQL?

Helps determine when to use a full SQL Server edition instead of free Express versions based on usage.

TIP T109 - Using osql for Cisco TMS tasks

Details the syntax and usage of the osql command-line tool for Cisco TMS operations.

Simple examples for Cisco TMS use

Provides practical examples of using osql for common Cisco TMS database tasks.

Tips category - Networking SQL Connectivity

TIP T200 - Enabling remote access to SQL

Guides on how to configure SQL Server to allow remote connections from other computers.

TIP T201 - SQL Server 2005 and firewalls

Explains how to configure Windows Firewall for SQL Server 2005, considering single and multiple instances.

TIP T202 - SQL Server 2000 and Firewalls

Details firewall configuration for SQL Server 2000, including port usage and scenarios.

Tips category - Maintaining and Upkeep

TIP T300 - Backing up the Cisco TMS Database using osql

Provides instructions on how to perform full backups of the Cisco TMS database using the osql utility.

TIP T301 - Restoring the Cisco TMS Database using osql

Explains how to restore the Cisco TMS database from a backup using the osql utility.

TIP T302 - Automating backups with SQL 2005 Express

Guides on automating database backups using SQL 2005 Express and the expressmaint utility.

TIP T303 - Detaching and reattaching databases

Describes how to detach and reattach Cisco TMS database files using osql for moving or copying.

TIP T304 - Upgrading MSDE 2000 to SQL 2005 Express

Outlines the steps for upgrading from MSDE 2000 to SQL Server 2005 Express for Cisco TMS.

Alternative 1 – Install SQL Server manually

Details the manual installation of SQL Server 2005 Express as an upgrade path for Cisco TMS.

Quicker

Describes the advantages of performing a quicker manual SQL Server installation.

Cons

Lists the disadvantages of performing a manual SQL Server installation for Cisco TMS.

Alternative 2 - Use Cisco TMS Installer to install SQL Server

Explains how to use the Cisco TMS installer to perform the SQL Server upgrade.

SQL code to update indexes and statistics

Provides SQL scripts to update indexes and statistics after database upgrades for performance.

TIP T305 - Resetting/changing/forgotten sa password

Details how to reset or change the 'sa' user password for the Cisco TMS SQL Server.