Tandberg TMS SQL User Manual
Cisco TMS SQL Database
Product Configuration Guide
December 2010
D14216.01
Table of contents
Table of contents
Tip format .................................................................................................................................................... 5
TIP T100 –Different types of SQL Server ................................................................................................... 6
Extra reference links ............................................................................................................................ 6
TIP T101 –SQL Server management tools ......................................................................................... 7
Extra reference links ............................................................................................................................ 7
TIP T103 –SQL Versions bundled with Cisco TMS .................................................................................... 8
TIP T104 –Cisco TMS SQL version compatibility ....................................................................................... 9
TIP T105 –Cisco TMS database and files ................................................................................................ 10
TIP T106 –The SQL Server instance........................................................................................................ 11
TIP T107 – Identifying the SQL Server instance name ............................................................................ 12
TIP T108 –SQL authentication modes...................................................................................................... 13
Extra reference links .......................................................................................................................... 13
Tip T109 –When do I need a full version of SQL? .................................................................................... 14
Extra reference links .......................................................................................................................... 14
TIP T109 –Using osql for Cisco TMS tasks .............................................................................................. 15
Simple examples for Cisco TMS use ................................................................................................ 16
Extra reference links .......................................................................................................................... 16
TIP T200 –Enabling remote access to SQL ............................................................................................. 17
Extra reference links .......................................................................................................................... 17
TIP T201 –SQL Server 2005 and firewalls ............................................................................................... 18
Possible scenarios ............................................................................................................................. 18
Using Windows Firewall for a single instance ................................................................................... 18
Using Windows Firewall for multiple instances ................................................................................. 18
Extra reference links .......................................................................................................................... 19
TIP T202 –SQL Server 2000 and Firewalls .............................................................................................. 20
Possible Scenarios ............................................................................................................................ 20
Using Windows Firewall for single instance ...................................................................................... 20
Using Windows Firewall for multiple instances ................................................................................. 20
To find the port a SQL Server instance is currently using: ................................................................ 20
Extra reference links .......................................................................................................................... 21
TIP T300 –Backing up the Cisco TMS Database using osql .................................................................... 22
Extra reference links .......................................................................................................................... 22
TIP T301 –Restoring the Cisco TMS Database using osql ...................................................................... 23
Restoring a database to the same SQL server ................................................................................. 23
Restoring a database to a different SQL server ................................................................................ 23
Extra reference links .......................................................................................................................... 24
TIP T302 –Automating backups with SQL 2005 Express ......................................................................... 25
Extra reference links .......................................................................................................................... 26
TIP T303 –Detaching and reattaching databases .................................................................................... 27
To detach the Cisco TMS database using osql ................................................................................. 27
To attach the Cisco TMS database using osql. ................................................................................. 27
Extra reference links .......................................................................................................................... 27
TIP T304 –Upgrading MSDE 2000 to SQL 2005 Express ........................................................................ 28
Cisco TMS customers running MSDE 2000 and Cisco TMS versions 9.0 through 10.0 ................... 28
Cisco TMS Customers running MSDE 2000 and Cisco TMS Versions 11.0 through 11.9.1 ............ 29
Alternative 1 – Install SQL Server manually ...................................................................................... 29
Cisco TMS Database Knowledge Tips Page 2 of 35
Table of contents
Pros ................................................................................................................................................... 29
Cons .................................................................................................................................................. 30
Alternative 2 – Use Cisco TMS Installer to install SQL Server .......................................................... 31
Pros ................................................................................................................................................... 31
Cons .................................................................................................................................................. 31
SQL code to update indexes and statistics ....................................................................................... 32
Extra reference links .......................................................................................................................... 33
TIP T305 –Resetting/changing/forgotten sa password ............................................................................. 34
Extra reference links .......................................................................................................................... 34
Cisco TMS Database Knowledge Tips Page 3 of 35
Document revision history
Revision 1.0 Initial Release of Document
Revision 1.1 Applied Cisco techdocs template, product names changed, TANDBERG
references changed to Cisco
Revision 1.2 Applied new Cisco product names
Revision 1.3 2nd stage rebranding
Introduction
Introduction
This document is a collection of various tips for Cisco TelePresence Management Suite (Cisco TMS)
administrators working with SQL backend databases. Because Cisco TMS uses a standard SQL server
for its database, there are many tools and methods available to complete tasks. Many of these tasks can
be completed in several different ways or with different commands. These tips are not written to be
taken as the only way to achieve a goal – simply as a verified way so that Cisco TMS owners who are
not as familiar with SQL or its management tools have easy references.
Tip format
Each Tip will be numbered for each reference. You can click on any tip number to jump to that tip.
Each tip will specify the version of Cisco TMS and SQL Server version it applies to. Due to changes in
Cisco TMS and requirements, not all tips will apply to all installations.
Tip information is limited to Cisco TMS versions 9.0 or newer for clarity purposes
Cisco TMS Database Knowledge Tips Page 5 of 35
Tips category – General
Tips category – General
TIP T100 –Different types of SQL Server
Cisco TMS Versions SQL Server Versions
SQL v7 – All Versions
MSDE – All Versions
All
Microsoft has produced many versions of their SQL Server. The ones most significant in regards to
Cisco TMS have been:
SQL Server v7 – Introduced 1998 – Full featured version. Replaced by SQL Server 2000
MSDE – Microsoft Database Engine – Same Engine as SQL v7 – Freely distributable version
intended for software integrators. Replaced by MSDE 2000
SQL 2000 – All Versions
MSDE 2000 – All Versions
SQL 2005 – All Versions
SQL 2005 Express – All Versions
SQL Server 2000 – Introduced 2000 – Full featured version. Replaced by SQL Server 2005
MSDE 2000 –MSDE product based on the SQL 2000 Engine - Freely distributable version intended
for software integrators. Replaced by SQL Server 2005 Express
SQL Server 2005 – Introduced 2005 – Full featured version.
SQL Server 2005 Express – Freely distributable version based on the SQL 2005 Engine
SQL Server 2008 – Microsoft’s next SQL Server release – slated for 2008 release.
Each fully featured version of SQL was sold in several variants which varied support for amount of
memory, processors, add-on features, etc. Cisco TMS requirements did not require more than the
‘Standard’ edition for fully featured SQL Servers.
The MSDE and Express variants use the same database engine as their fully featured versions but have
specific performance throttles and features removed. The performance limits may be significant to Cisco
TMS depending on the size and type of Cisco TMS installation. See ‘Tip T109 –When do I need a full
version of SQL?’ for additional information.
Extra reference links
Comparison of SQL Server 2005 Editions
SQL Server 2005 Express Download
Cisco TMS Database Knowledge Tips Page 6 of 35
Tips category – General
TIP T101 –SQL Server management tools
All
Microsoft provides both graphical and command line interfaces to manage the SQL Server. In general, a
tools installed by a newer server version can control an older version, but not vice versa. Management
tools are automatically installed on the server where SQL Server is installed. Client-only tools can be
installed on other computers to control SQL Servers Remotely.
osql – command line tool which can be used to interact with nearly all aspects of SQL server.
Installed on any machine where SQL Server or SQL Management Tools have been installed
sqlcmd – Command line tool introduced with SQL 2005 intended to replace osql. Installed on any
machine where SQL 2005, SQL 2005 Express, or SQL 2005 Management Tools are installed. osql
is still available with SQL 2005 but is marked as depreciated
Cisco TMS Versions SQL Server Versions
SQL v7 – All Versions
MSDE – All Versions
SQL 2000 – All Versions
MSDE 2000 – All Versions
SQL 2005 – All Versions
SQL 2005 Express – All Versions
SQL Enterprise Manager – GUI Configuration tool installed with full versions of SQL 7 and SQL
2000. Can be installed on additional client computers but is not available for download
SQL Server Management Studio – GUI Configuration tool installed with full versions of SQL 2005.
Can be installed on additional client computers but is not available for download
SQL Server Management Studio Express – GUI Configuration tool freely available for download
from Microsoft
Extra reference links
SQL Server Management Studio Express Download
Cisco TMS Database Knowledge Tips Page 7 of 35
Tips category – General
TIP T103 –SQL Versions bundled with Cisco TMS
All N/A
To simplify installation for Cisco TMS customers, the Cisco TMS installer as an option would install a
free version of the SQL Server engine. The version has changed as Cisco TMS requirements and
version availability has matured.
Cisco TMS v9.0 through Cisco TMS v11.1 – MSDE 2000 SP3a
Cisco TMS v11.5 to current – SQL Server 2005 Express
Cisco TMS Versions SQL Server Versions
Cisco TMS Database Knowledge Tips Page 8 of 35
Tips category – General
TIP T104 –Cisco TMS SQL version compatibility
All
The SQL Server versions supported for each version of Cisco TMS are listed in the Cisco TMS Release
notes. The major changes in support can be summarized as:
Cisco TMS 9.0 to Cisco TMS 9.21 – MSDE, MSDE 2000, SQL 2000
Cisco TMS 9.6 to Cisco TMS 10.0 – MSDE 2000, SQL 2000
Cisco TMS 11.0 to Cisco TMS 11.9.1 – MSDE 2000, SQL 2000, SQL 2005, SQL 2005 Express
Cisco TMS 12.0 to current – SQL Server 2005, SQL Server 2005 Express
Please see the Installation and Getting Started Manual for your Cisco TMS version for specific version
requirements and service pack levels
Cisco TMS Versions SQL Server Versions
SQL 2000 – All Versions
MSDE 2000 – All Versions
SQL 2005 – All Versions
SQL 2005 Express – All Versions
Cisco TMS Database Knowledge Tips Page 9 of 35
Tips category – General
TIP T105 –Cisco TMS database and files
All
The Cisco TMS database is installed and maintained by the Cisco TMS installer. Since Cisco TMS v9.0
– the Cisco TMS database is named tmsng . The database consists of two files, a data file and log file –
tmsng.mdf and tmsng_log.ldf respectively. These files will be created in the default data directory of the
SQL Server. For default installations this will be:
For MSDE/SQL 2000 Servers - C:\Program Files\Microsoft SQL Server\MSSQL\Data
SQL Server 2005 Express is installed as the named instance \SQLTMS . The data directory is C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\MSSQL.1\MSSQL\DATA
The Cisco TMS data and log files may not be directly manipulated as long as the database is attached to
the SQL Server. All interaction must be through the SQL Server interface. To work with the files directly,
the database should be detached from the SQL Server (removing the database from the server without
deleting it).
Cisco TMS Versions SQL Server Versions
SQL 2000 – All Versions
MSDE 2000 – All Versions
SQL 2005 – All Versions
SQL 2005 Express – All Versions
Cisco TMS Database Knowledge Tips Page 10 of 35
Tips category – General
TIP T106 –The SQL Server instance
All
SQL Servers are installed as the default instance, or a named instance. The default instance is normally
reached via IP or hostname of the server. A named instance is reached by appending \<instancename>
to the IP or hostname. Example: productionsql\sqltms . Named instances allow multiple SQL servers
to be operating on the same physical server. Configuring a server to run as a named instance can only
be configured at time of installation. SQL 2000 by default installs as the default instance. SQL 2005
Express by default will install as the named instance \sqlexpress
Prior to Cisco TMS 11.5, MSDE 2000 installed by Cisco TMS would be installed as the default
instance.
Starting with Cisco TMS 11.5, SQL 2005 Express installed by Cisco TMS would be installed as the
named instance \SQLTMS
Cisco TMS Versions SQL Server Versions
SQL 2000 – All Versions
MSDE 2000 – All Versions
SQL 2005 – All Versions
SQL 2005 Express – All Versions
When using osql, the –S parameter is used to specify which server to connect to.
Example: osql –E –S localhost\sqltms would connect to the named instance SQLTMS on the
local server
Cisco TMS Database Knowledge Tips Page 11 of 35
Loading...