Cisco TMS SQL DATABASE User Manual

Tips category – Maintaining/Upkeep
Cisco TMS Database Knowledge Tips Page 30 of 35
 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 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.