manualshive.com logo in svg

Cisco TMS SQL DATABASE, Configuration Manual, Page: 2 / 35

Share
Download
Cisco TMS SQL DATABASE Configuration Manual Download Page 2

Table of contents 

Cisco TMS Database Knowledge Tips 

Page 2 of 35 

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

 

Summary of Contents for TMS SQL DATABASE

Page 1: ...Cisco TMS SQL Database Product Configuration Guide December 2010 D14216 01 ...

Page 2: ...ndows 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 T30...

Page 3: ...age 3 of 35 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 ...

Page 4: ...y 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 2 nd stage rebranding ...

Page 5: ...t 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 Se...

Page 6: ...ely 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 amo...

Page 7: ...eract 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 C...

Page 8: ...ersions 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 ...

Page 9: ...sion 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 Getti...

Page 10: ...espectively 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 ...

Page 11: ...ed 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 ins...

Page 12: ...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 re...

Page 13: ...L 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 con...

Page 14: ...ress or a full edition of SQL is recommended if any of the below criteria are met Installations larger than 50 systems Scheduling Monitoring a Cisco TelePresence MPS MCU Scheduling conferences larger than 8 participants Using a external integration product with Cisco TMS Exchange 3 rd Party Booking etc Need to use Cisco TMS versions newer then version 11 9 1 SQL Server 2005 is less restrictive the...

Page 15: ...mmand line switches are case sensitive The most common switches used with Cisco 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 sq...

Page 16: ...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 Simple examples for Cisco TMS use Execute a scr...

Page 17: ...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 connecti...

Page 18: ... 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 sel...

Page 19: ...ips category Networking Cisco TMS Database Knowledge Tips Page 19 of 35 Extra reference links How to Configure a Firewall for SQL Server Access How to Configure a Server to Listen on a Specific TCP Port ...

Page 20: ...ons 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 instanc...

Page 21: ...Tips category Networking Cisco TMS Database Knowledge Tips Page 21 of 35 Extra reference links How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port ...

Page 22: ...ount 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 Cisco TMS Windows Services and web servers pointed at the Cisco TMS database SQL provides several types of backups but to perform a full backup issue the following command in a command prompt osql S se...

Page 23: ...p 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 Cisco TMS database stop all Cisco TMS Windows Services and IIS web servers pointed at the C...

Page 24: ... 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 T109 Using osql for Cisco TMS tasks Extra reference links How to manage the SQL Server Desktop Engine MSDE 2000 or SQL S...

Page 25: ... example that would be used with a default Cisco 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 Cisco TMS server while logged in as a Windows Administrator These steps are to backup the database daily and...

Page 26: ...Tips category Maintaining Upkeep Cisco TMS Database Knowledge Tips Page 26 of 35 13 Click OK If prompted supply the password for the account again Extra reference links Expressmaint website ...

Page 27: ...active connections to the database to detach it from a SQL server To detach the Cisco 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 Cisco 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 MSSQ...

Page 28: ... Installation manual and ensure your server meets the current OS and hardware requirements 2 Run the Cisco 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 tmsn...

Page 29: ...he 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...

Page 30: ...he 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 ins...

Page 31: ...xact version of Cisco TMS you are currently running to complete this process If you do not have the installer for the version you are currently running contact Cisco Support to obtain a copy of the Cisco TMS installer 3 Run the Cisco 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...

Page 32: ...er 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...

Page 33: ...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 Cisco TMS database by entering the following command in a command prompt Replace server with localhost or localhost sqltms dependi...

Page 34: ... into the Windows server hosting Cisco 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 3 Use the TMSTools app on the Cisco 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...

Page 35: ...CISCO AND THE ABOVE NAMED SUPPLIERS DISCLAIM ALL WARRANTIES EXPRESSED OR IMPLIED INCLUDING WITHOUT LIMITATION THOSE OF MERCHANTABILITY FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT OR ARISING FROM A COURSE OF DEALING USAGE OR TRADE PRACTICE IN NO EVENT SHALL CISCO OR ITS SUPPLIERS BE LIABLE FOR ANY INDIRECT SPECIAL CONSEQUENTIAL OR INCIDENTAL DAMAGES INCLUDING WITHOUT LIMITATION LOST PROFIT...

Reviews:

No comments