TMS Database Knowledge Tips

(1)

TMS Database

Knowledge Tips

(2)

Tips for the TMS SQL Database

INTRODUCTION

This document is intended to be a collection of various tips for TMS administrators in regards to working with the SQL database backend of the TMS. Because 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 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 TMS and SQL Server version it applies to. Due to changes in TMS and requirements, not all tips will apply to all installations.

(6)

1 TIPS CATEGORY – GENERAL

TIP T100 –Different Types of SQL Server

TMS Versions SQL Server Versions

Applies to: All

SQL v7 – All Versions MSDE – All Versions SQL 2000 – All Versions MSDE 2000 – All Versions

SQL 2005 – All Versions SQL 2005 Express – All Versions

Microsoft has had many versions of their SQL Server product. The ones most significant in regards to 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 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.

(7)

TIP T101 –SQL Server Management Tools

Applies to: 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

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

(8)

TIP T103 –SQL Versions Bundled with TMS

Applies to: All N/A

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

(9)

TIP T104 –TMS SQL Version Compatibility

Applies to: All

SQL 2000 – All Versions MSDE 2000 – All Versions

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

TMS 9.0 to TMS 9.21 – MSDE, MSDE 2000, SQL 2000 TMS 9.6 to TMS 10.0 – MSDE 2000, SQL 2000

TMS 11.0 to TMS 11.9.1 – MSDE 2000, SQL 2000, SQL 2005, SQL 2005 Express TMS 12.0 to current – SQL 2005, SQL 2005 Express

(10)

TIP T105 –TMS Database and files

Applies to: All

The TMS database is installed and maintained by the TMS installer. Since TMS v9.0 – the 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

(11)

TIP T106 –The SQL Server Instance

Applies to: 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 TMS 11.5, MSDE 2000 installed by TMS would be installed as the default instance.

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

When using osql, the –S parameter is used to specify which server to connect to.

(12)

TIP T107 – Identifying the SQL Server Instance Name

Applies to: 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.

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

2. Enter the command osql –L

(13)

TIP T108 –SQL Authentication Modes

Applies to: 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.

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

Extra Reference Links

(14)

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

Applies to: TMS 11 and newer

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 TANDBERG MPS MCU Scheduling conferences larger than 8 participants

Using a external integration product with TMS (Exchange, 3rd Party Booking, etc) Need to use 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

(15)

TIP T109 –Using osql for TMS Tasks

Applies to: 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 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 TMS operations are listed below

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 _{Connect to named instance} sqlexpress at

(16)

Examples:

-U sa _{Connect using username sa}

-U john.smith _{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 _{Login using account sa with} password coolPass

-U sa -P _{Login using account sa but prompt}

for password

-d database _{Specify the database to connect to. Equivalent to saying ‘use databasename’.} For TMS operations, you will almost always specify the tmsng database

Examples:

-d tmsng _{Connect to database named tmsng}

-Q “query” _{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

-i filename _{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

(17)

Simple examples for TMS use

Execute a script c:\update.sql against a local 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 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

(18)

2 TIPS CATEGORY – NETWORKING

TIP T200 –Enabling Remote Access to SQL

TMS Versions SQL Server Versions Applies to: 11.0 or newer SQL 2005 – All Versions

SQL 2005 Express – All Versions

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 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

(19)

TIP T201 –SQL Server 2005 and Firewalls

TMS Versions SQL Server Versions Applies to: 11.0 or newer SQL 2005 – All Versions

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.

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

(20)

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

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

8. 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.

6. Click OK to close the Windows firewall program.

Extra Reference Links

How to: Configure a Firewall for SQL Server Access

(21)

TIP T202 –SQL Server 2000 and Firewalls

TMS Versions SQL Server Versions Applies to: TMS 9.0 through 11.9.1 SQL 2000 – All Versions

MSDE 2000 – All Versions

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.

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

(22)

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

Extra Reference Links

(23)

3 TIPS CATEGORY – MAINTAINING/UPKEEP

TIP T300 –Backing up the TMS Database using osql

Applies to: 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 TMS Windows Services and web servers pointed at the 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 TIP T109 –Using osql for TMS Tasks

Extra Reference Links

(24)

TIP T301 –Restoring the TMS Database using osql

Applies to: 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 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 TMS database, stop all TMS Windows Services and IIS web servers pointed at the TMS database.

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

2. Find the Services whose names begin with TMS. Right-click on each and click Stop. Repeat for all services whose name starts with TMS

3. Right-Click the World Wide Web Publishing Service and select Stop 4. Repeat the above steps for all TMS servers pointed at the 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 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‟”

(25)

3. Right-Click the World Wide Web Publishing Service and select Stop 4. Repeat the above steps for all TMS servers pointed at the 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

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 TIP T109 –Using osql for 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

(26)

TIP T302 –Automating backups with SQL 2005 Express

TMS Versions SQL Server Versions Applies to: TMS 11.0 or newer SQL 2005 – All Versions

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

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, 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 backup 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 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.

(27)

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 13. Click OK. If prompted, supply the password for the account again

Extra Reference Links

(28)

TIP T303 –Detaching and Reattaching Databases

Applies to: 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 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 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'”

Extra Reference Links

(29)

TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express

Applies to: TMS v9.0 and newer

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 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 TMS. For 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 TMS customers, this tip will outline possible upgrade scenarios for TMS customers who have had SQL installed by TMS. Please read the introduction text in each to see which applies to your situation and is best for you.

TMS Customers running MSDE 2000 and TMS Versions 9.0 through 10.0

If you are currently running a TMS version older then TMS v11.0, you must also upgrade your TMS version to support SQL 2005. Customers currently running newer versions of TMS, jump to TMS Customers running MSDE 2000 and 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 TMS defaults. These steps will upgrade the SQL Server and the TMS version.

1. Read the current TMS release notes and Installation manual and ensure your server meets the current OS and hardware requirements.

(30)

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

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'"

(31)

indexes and statistics section near the end of this tip to create a SQL script to perform the

task. Return here when completed.

23. You may now continue with the installation of any TMS version TMS v11.0 or higher. NOTE: When performing the 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.

24. Downloading and installing SQL Server Management Studio Express is recommended for future management of the SQL Server.

TMS Customers running MSDE 2000 and TMS Versions 11.0 through 11.9.1

If you are currently running a 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 TMS installer to install the SQL Server and restore your data. Upgrades to newer versions of 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 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 TMS is up to date as the TMS requirements overlap the SQL requirements. If your current TMS version is earlier then TMS 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

(32)

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

1. Stop all connections to the database from TMS. To stop all connections to the TMS database, stop all TMS Windows Services and IIS web servers pointed at the TMS database.

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

3. Right-Click the World Wide Web Publishing Service and select Stop 4. Repeat the above steps for all TMS servers pointed at the 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

(33)

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 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 TMS server to allow all services the chance to reconnect to the SQL database. After the restart, verify the TMS page loads properly by logging into TMS. It is recommended to download and install SQL Server Management Studio Express for future management of the SQL Server.

Alternative 2 – Use TMS Installer to Install SQL Server

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

Pros

Easier steps to perform

Installer handles pre-requisites

Harmonizes install to current TMS defaults Cons

(34)

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

3. Run the 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 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 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.

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.

(35)

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 TMS installer will finish some tasks at the start of your login – this is normal.

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

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

II. Find the Services whose names begin with TMS. Right-click on each and click Stop. Repeat for all services whose name starts with TMS

III. 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 TMS server is configured to point at the new 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 instructions in the SQL Code to update

indexes and statistics section near the end of this tip to create a SQL script to perform the

task. Return here when completed.

(36)

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 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

(37)

SQL Server Management Studio Express Download

TIP T305 –Resetting/Changing/Forgotten sa password

Applies to: All

If the sa password for the 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 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'”

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

3. Use the TMSTools app on the 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 TMS Server to ensure all services restart using the new password

TMS Database Knowledge Tips

TMS Database

Knowledge Tips

TABLE OF CONTENTS

INTRODUCTION

Tip Format

1

TIPS CATEGORY – GENERAL

TIP T100 –Different Types of SQL Server

TIP T101 –SQL Server Management Tools

Extra Reference Links

TIP T103 –SQL Versions Bundled with TMS

TIP T104 –TMS SQL Version Compatibility

TIP T105 –TMS Database and files

TIP T106 –The SQL Server Instance

TIP T107 – Identifying the SQL Server Instance Name

TIP T108 –SQL Authentication Modes

Extra Reference Links

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

Extra Reference Links

TIP T109 –Using osql for TMS Tasks

Simple examples for TMS use

Extra Reference Links

2

TIPS CATEGORY – NETWORKING

TIP T200 –Enabling Remote Access to SQL

TIP T201 –SQL Server 2005 and Firewalls

Using Windows Firewall for a single instance

Using Windows Firewall for multiple instances

Extra Reference Links

TIP T202 –SQL Server 2000 and Firewalls

Using Windows Firewall for single instance

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

Extra Reference Links

3

TIPS CATEGORY – MAINTAINING/UPKEEP

TIP T300 –Backing up the TMS Database using osql

Extra Reference Links

TIP T301 –Restoring the TMS Database using osql

Restoring a database to the same SQL server

Extra Reference Links

TIP T302 –Automating backups with SQL 2005 Express

Extra Reference Links

TIP T303 –Detaching and Reattaching Databases

Extra Reference Links

TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express

TMS Customers running MSDE 2000 and TMS Versions 9.0 through 10.0

TMS Customers running MSDE 2000 and TMS Versions 11.0 through 11.9.1

SQL Code to update indexes and statistics

TIP T305 –Resetting/Changing/Forgotten sa password

Extra Reference Links