TMS Database Knowledge Tips Tips for the TMS SQL by coronanlime

VIEWS: 0 PAGES: 37

									       TMS Database
      Knowledge Tips



Tips for the TMS SQL Database



                         June 2008


                     D14216 Rev 1.0
                                                                                                                            Tips for the TMS SQL Database



TABLE OF CONTENTS

TABLE OF CONTENTS ....................................................................................................................... 2

DOCUMENT REVISION HISTORY ...................................................................................................... 4

INTRODUCTION .............................................................................................................................. 5

Tip Format .................................................................................................................................................. 5

1        TIPS CATEGORY – GENERAL ................................................................................................... 6

TIP T100 –Different Types of SQL Server ................................................................................................... 6
TIP T101 –SQL Server Management Tools ................................................................................................. 7
TIP T103 –SQL Versions Bundled with TMS ............................................................................................... 8
TIP T104 –TMS SQL Version Compatibility ................................................................................................. 9
TIP T105 –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
Tip T109 –When do I need a full version of SQL? .................................................................................... 14
TIP T109 –Using osql for TMS Tasks ......................................................................................................... 15

2        TIPS CATEGORY – NETWORKING .......................................................................................... 18

TIP T200 –Enabling Remote Access to SQL .............................................................................................. 18
TIP T201 –SQL Server 2005 and Firewalls ................................................................................................ 19
         Using Windows Firewall for a single instance ................................................................................ 19

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

TIP T202 –SQL Server 2000 and Firewalls ................................................................................................ 21
      Using Windows Firewall for single instance................................................................................... 21

         Using Windows Firewall for multiple instances ............................................................................. 21

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

3        TIPS CATEGORY – MAINTAINING/UPKEEP ............................................................................ 23

TIP T300 –Backing up the TMS Database using osql ................................................................................ 23
TIP T301 –Restoring the TMS Database using osql .................................................................................. 24
      Restoring a database to the same SQL server ............................................................................... 24

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

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



D14216 Rev 1.0                                                                                                                                   Page 2 of 37
                                                                                                            Tips for the TMS SQL Database


TIP T303 –Detaching and Reattaching Databases .................................................................................... 28
TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express ........................................................................... 29
        TMS Customers running MSDE 2000 and TMS Versions 9.0 through 10.0 .................................... 29

        TMS Customers running MSDE 2000 and TMS Versions 11.0 through 11.9.1............................... 31

        SQL Code to update indexes and statistics ..................................................................................... 36

TIP T305 –Resetting/Changing/Forgotten sa password........................................................................... 37




D14216 Rev 1.0                                                                                                               Page 3 of 37
                                               Tips for the TMS SQL Database




DOCUMENT REVISION HISTORY
Revision 1.0     Initial Release of Document




D14216 Rev 1.0                                                  Page 4 of 37
                                                                                 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.
Tip information is limited to TMS versions 9.0 or newer for clarity purposes




D14216 Rev 1.0                                                                                    Page 5 of 37
                                                                                 Tips for the TMS SQL Database




1 TIPS CATEGORY – GENERAL

TIP T100 –Different Types of SQL Server
                       TMS Versions                  SQL Server Versions
                                                    SQL v7 – All Versions
                                                     MSDE – All Versions
                                                   SQL 2000 – All Versions
   Applies to:              All
                                                  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.
Each fully featured version of SQL was sold in several variants which varied support for amount of
memory, processors, add-on features, etc. TMS requirements did not require beyond 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
TMS depending on the size and type of 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




D14216 Rev 1.0                                                                                    Page 6 of 37
                                                                                  Tips for the TMS SQL Database




TIP T101 –SQL Server Management Tools
                       TMS Versions                  SQL Server Versions
                                                     SQL v7 – All Versions
                                                     MSDE – All Versions
                                                   SQL 2000 – All Versions
   Applies to:               All
                                                  MSDE 2000 – All Versions
                                                   SQL 2005 – All Versions
                                               SQL 2005 Express – All Versions


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
         SQL Server Management Studio Express Download




D14216 Rev 1.0                                                                                     Page 7 of 37
                                                                                 Tips for the TMS SQL Database




TIP T103 –SQL Versions Bundled with TMS
                      TMS Versions                  SQL Server Versions

   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.
         TMS v9.0 through TMS v11.1 – MSDE 2000 SP3a
         TMS v11.5 to current – SQL Server 2005 Express




D14216 Rev 1.0                                                                                    Page 8 of 37
                                                                                 Tips for the TMS SQL Database




TIP T104 –TMS SQL Version Compatibility
                      TMS Versions                  SQL Server Versions
                                                  SQL 2000 – All Versions
                                                 MSDE 2000 – All Versions
   Applies to:              All
                                                  SQL 2005 – All Versions
                                              SQL 2005 Express – 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
Please see the Installation and Getting Started Manual for your TMS version for specific version
requirements and service pack levels




D14216 Rev 1.0                                                                                    Page 9 of 37
                                                                                  Tips for the TMS SQL Database




TIP T105 –TMS Database and files
                      TMS Versions                   SQL Server Versions
                                                   SQL 2000 – All Versions
                                                  MSDE 2000 – All Versions
   Applies to:              All
                                                   SQL 2005 – All Versions
                                               SQL 2005 Express – All Versions


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
The 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).




D14216 Rev 1.0                                                                                    Page 10 of 37
                                                                                Tips for the TMS SQL Database




TIP T106 –The SQL Server Instance
                      TMS Versions                 SQL Server Versions
                                                  SQL 2000 – All Versions
                                                 MSDE 2000 – All Versions
   Applies to:             All
                                                  SQL 2005 – All Versions
                                              SQL 2005 Express – All Versions


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.
Example osql –E –S localhost\sqltms would connect to the named instance SQLTMS on the local
server




D14216 Rev 1.0                                                                                  Page 11 of 37
                                                                                 Tips for the TMS SQL Database




TIP T107 – Identifying the SQL Server Instance Name
                      TMS Versions                  SQL Server Versions
                                                   SQL 2000 – All Versions
                                                  MSDE 2000 – All Versions
   Applies to:              All
                                                   SQL 2005 – All Versions
                                               SQL 2005 Express – All Versions


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
    3. The command will list any servers it was able to discover




D14216 Rev 1.0                                                                                   Page 12 of 37
                                                                                Tips for the TMS SQL Database




TIP T108 –SQL Authentication Modes
                      TMS Versions                  SQL Server Versions
                                                    SQL v7 – All Versions
                                                    MSDE – All Versions
                                                   SQL 2000 – All Versions
   Applies to:             All
                                                 MSDE 2000 – All Versions
                                                   SQL 2005 – All Versions
                                              SQL 2005 Express – All Versions


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
         Authentication Modes in SQL Server 2000




D14216 Rev 1.0                                                                                  Page 13 of 37
                                                                                    Tips for the TMS SQL Database



Tip T109 –When do I need a full version of SQL?
                       TMS Versions                    SQL Server Versions
                                                     SQL 2000 – All Versions
                                                    MSDE 2000 – All Versions
   Applies to:       TMS 11 and newer
                                                     SQL 2005 – All Versions
                                                  SQL 2005 Express – All Versions


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
                                                                      rd
         Using a external integration product with TMS (Exchange, 3 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
         SQL Server 2005 Features Comparison




D14216 Rev 1.0                                                                                      Page 14 of 37
                                                                                 Tips for the TMS SQL Database




TIP T109 –Using osql for TMS Tasks
                      TMS Versions                  SQL Server Versions
                                                    SQL v7 – All Versions
                                                    MSDE – All Versions
                                                  SQL 2000 – All Versions
     Applies to:           All
                                                 MSDE 2000 – All Versions
                                                  SQL 2005 – All Versions
                                               SQL 2005 Express – All Versions


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
                                                                  prodsql.company.com
                         -S localhost\sqltms
                                                                  Connect to named instance sqltms
                                                                  on local server
-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.




D14216 Rev 1.0                                                                                   Page 15 of 37
                                                                         Tips for the TMS SQL Database


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




D14216 Rev 1.0                                                                           Page 16 of 37
                                                                                    Tips for the TMS SQL Database

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
     How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition
     by using the osql utility




D14216 Rev 1.0                                                                                      Page 17 of 37
                                                                                  Tips for the TMS SQL Database




2 TIPS CATEGORY – NETWORKING

TIP T200 –Enabling Remote Access to SQL
                      TMS Versions                   SQL Server Versions
                                                   SQL 2005 – All Versions
   Applies to:        11.0 or newer
                                               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
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

Extra Reference Links
         How to configure SQL Server 2005 to allow remote connections




D14216 Rev 1.0                                                                                    Page 18 of 37
                                                                                 Tips for the TMS SQL Database




TIP T201 –SQL Server 2005 and Firewalls
                       TMS Versions                 SQL Server Versions
                                                   SQL 2005 – All Versions
   Applies to:         11.0 or newer
                                               SQL 2005 Express – 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
    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 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.



D14216 Rev 1.0                                                                                   Page 19 of 37
                                                                                Tips for the TMS SQL Database

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
    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
         How to: Configure a Server to Listen on a Specific TCP Port




D14216 Rev 1.0                                                                                  Page 20 of 37
                                                                                Tips for the TMS SQL Database




TIP T202 –SQL Server 2000 and Firewalls
                       TMS Versions                 SQL Server Versions
                                                   SQL 2000 – All Versions
   Applies to:    TMS 9.0 through 11.9.1
                                                  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
    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

    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



D14216 Rev 1.0                                                                                  Page 21 of 37
                                                                                  Tips for the TMS SQL Database

    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

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




D14216 Rev 1.0                                                                                    Page 22 of 37
                                                                                Tips for the TMS SQL Database




3 TIPS CATEGORY – MAINTAINING/UPKEEP

TIP T300 –Backing up the TMS Database using osql
                      TMS Versions                 SQL Server Versions
                                                  SQL 2000 – All Versions
                                                 MSDE 2000 – All Versions
   Applies to:             All
                                                  SQL 2005 – All Versions
                                              SQL 2005 Express – All Versions


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
         How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express
         Edition by using the osql utility




D14216 Rev 1.0                                                                                  Page 23 of 37
                                                                                   Tips for the TMS SQL Database




TIP T301 –Restoring the TMS Database using osql
                      TMS Versions                   SQL Server Versions
                                                    SQL 2000 – All Versions
                                                   MSDE 2000 – All Versions
   Applies to:              All
                                                    SQL 2005 – All Versions
                                                SQL 2005 Express – All Versions


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

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


Restoring a database to a different 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




D14216 Rev 1.0                                                                                     Page 24 of 37
                                                                                 Tips for the TMS SQL Database

    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
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
    Backing Up and Restoring Databases in SQL Server
    Copying Databases with Backup and Restore
    RESTORE Arguments (Transact-SQL)




D14216 Rev 1.0                                                                                   Page 25 of 37
                                                                                  Tips for the TMS SQL Database




TIP T302 –Automating backups with SQL 2005 Express
                       TMS Versions                  SQL Server Versions
                                                   SQL 2005 – All Versions
   Applies to:       TMS 11.0 or newer
                                               SQL 2005 Express – 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.
Perform the following on the 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 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:



D14216 Rev 1.0                                                                                    Page 26 of 37
                                                                                 Tips for the TMS SQL Database

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




D14216 Rev 1.0                                                                                   Page 27 of 37
                                                                                 Tips for the TMS SQL Database




TIP T303 –Detaching and Reattaching Databases
                      TMS Versions                  SQL Server Versions
                                                   SQL 2000 – All Versions
                                                  MSDE 2000 – All Versions
   Applies to:              All
                                                   SQL 2005 – All Versions
                                               SQL 2005 Express – All Versions


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
         How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express
         Edition by using the osql utility




D14216 Rev 1.0                                                                                   Page 28 of 37
                                                                                 Tips for the TMS SQL Database




TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express
                      TMS Versions                  SQL Server Versions
                                                   SQL 2000 – All Versions
                                                  MSDE 2000 – All Versions
   Applies to:     TMS v9.0 and newer
                                                   SQL 2005 – All Versions
                                               SQL 2005 Express – All Versions


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.
    2. 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.
    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 servers 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



D14216 Rev 1.0                                                                                   Page 29 of 37
                                                                                   Tips for the TMS SQL Database

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

    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 instructions in the SQL Code to update




D14216 Rev 1.0                                                                                     Page 30 of 37
                                                                                   Tips for the TMS SQL Database

         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

                 No TMS Reconfiguration
                 Less Steps
                 Quicker
         Cons
                 Requires you complete the installation of SQL Server steps manually instead of
                 automated
                 Keeps older Instance Name – will not be inline with TMS defaults going forward
                 Less chance for reconfiguration if desired




D14216 Rev 1.0                                                                                     Page 31 of 37
                                                                                        Tips for the TMS SQL Database

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




D14216 Rev 1.0                                                                                          Page 32 of 37
                                                                                        Tips for the TMS SQL Database

                 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

                     Requires having TMS Installer available
                     May take longer to complete
         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. SQL Server 2005 Express is not installed by the TMS installer in versions before TMS 11.5. If
       you are running a TMS version between 11.0 or 11.1 – you must upgrade to 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 TMS 11.5 through TMS 11.9.1, continue on.




D14216 Rev 1.0                                                                                          Page 33 of 37
                                                                                   Tips for the TMS SQL Database

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




D14216 Rev 1.0                                                                                     Page 34 of 37
                                                                                   Tips for the TMS SQL Database

    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.
    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 TMS webpage to verify
        it is operating properly.
Your 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.




D14216 Rev 1.0                                                                                     Page 35 of 37
                                                                                 Tips for the TMS SQL Database

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
   2. Execute the script against your 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)




D14216 Rev 1.0                                                                                   Page 36 of 37
                                                                                Tips for the TMS SQL Database

         SQL Server Management Studio Express Download


TIP T305 –Resetting/Changing/Forgotten sa password
                      TMS Versions                 SQL Server Versions
                                                  SQL 2000 – All Versions
                                                 MSDE 2000 – All Versions
   Applies to:              All
                                                  SQL 2005 – All Versions
                                              SQL 2005 Express – All Versions


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

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




D14216 Rev 1.0                                                                                  Page 37 of 37

								
To top