Microsoft Office Communications Server 2007 R2 Enterprise Edition takes advantage of Microsoft SQL Server to host the back-end database for the Enterprise pool. Communications Server 2007 R2 creates the following databases: ACDDyn, RTC, RTCAb, RTCConfig, and RTCDyn. The Enterprise Edition now supports hosting these databases on a SQL Server named instance that is configured to listen on either a static or non-default port. This configuration provides SQL Server administrators the flexibility to scale their SQL Server named instance installation in a secured network environment.
Author: Mike Adkins
Publication date: October 2010
Product version: Microsoft Office Communications Server 2007 R2, Microsoft SQL Server 2005 with SP2, Microsoft SQL Server 2008
The information in this article has been tested with Office Communications Server 2007 R2 Enterprise Edition using Microsoft SQL Server 2005 database software with SP2 and Microsoft SQL Server 2008 database software.
SQL Server supports two types of database instances. These are the following:
- Default instance
The default instance inherits the host name of the server on which SQL Server is installed. There can be only one default instance installation per SQL Server. Any instances that are installed before or after the single default instance must be a SQL Server named instance.
A named instance can be installed on a server running SQL Server at any time. SQL Server can support multiple named instances at once. Named instances will use whatever meaningful name that they are given during installation. For details about the number of named instances your version of SQL Server can support, see the SQL Server Books Online Help or visit the Microsoft SQL Server Tech Center at http://technet.microsoft.com/en-us/sqlserver/default.aspx.
TCP Ports Used by SQL Server
The SQL Server default instance always listens for incoming connection requests on TCP port 1433. This is the default listening port for the SQL Server service when it is installed with a default instance. The default instance can also be configured to listen on a TCP/IP port that is not the default. When configured to listen on a non-default TCP/IP port, the SQL Server client tools application must specify the non-default TCP/IP port in the connection string. The SQL Server client tools application connects to the server that is running SQL Server by using the host name and the specific non-default TCP/IP port.
SQL Server Browser Service
The SQL Server Browser service is installed by SQL Server to manage the non-default port connectivity information for each of the named instances that are configured on the server. The named instances use a dynamic TCP/IP port configuration by default, but they can use a static TCP/IP port that has been manually configured by the SQL Server administrator. The SQL Server Browser service manages the TCP/IP port connectivity information for either configuration. The SQL Server Browser service should be enabled to start automatically when a named instance is installed on the server that is running SQL Server. Use services.msc on the local server that is running SQL Server to locate the SQL Server Browser service. Make sure that this service is set to start automatically and is started. The SQL Server Browser service automatically listens on UDP port 1434. Please ensure that UDP port 1434 is not restricted from bi-directional network traffic by any firewall or router on the network between the Communications Server 2007 R2 front-end servers and the SQL Server back end.
Note. By design, the SQL Server Browser service does not return the name of the SQL Server default instance or its listening TCP port information.
Configuring a Non-Default SQL Server TCP/IP Port
This section covers the configuration steps to allow Communications Server 2007 R2 front-end servers to connect to a SQL Server named instance using a non-default SQL Server TCP/IP port.
The following tasks must be performed:
- Connect to the SQL Server that is hosting the Communications Server 2007 R2 databases: ACDDyn, RTC, RTCAb, RTCConfig, and RTCDyn and locate the protocol configuration for the server that is running SQL Server (if a default instance is installed).
- Ensure that the TCP/IP protocol is enabled.
- Ensure the TCP/IP protocol is enabled for the named instance configuration.
- Edit the TCP/IP properties of the named instance.
The following sections give detailed instructions for performing the previous tasks.
Connect to the SQL Server that Is Hosting the Communications Server Databases, Locate the Protocol Configuration, and Ensure that TCP/IP Is Enabled
- Click Start, click Programs, and then click Microsoft SQL Server 2008.
- Expand the Configuration Tools menu.
- Open the SQL Server Configuration Manager, and do the following:
- Expand the SQL Server Network Configuration node.
- Select the default instance for your configuration (in this example, Protocols for MSSQLSERVER) as shown in Figure 1.
Note. Other applications that are hosted on the server that is running SQL Server may require the use of Named Pipes for remote access. VIA should be disabled unless required, and the Shared Memory protocol, which is used for client applications that are run locally on the SQL Server, can be disabled if not needed.
For details, see Choosing a Network Protocol.
Figure 1. SQL Server default instance and named instance
Ensure the TCP/IP Protocol Is Enabled for the Named Instance Configuration
1. Select the SQL Server named instance in your topology that is hosting the Communications Server 2007 R2 back-end databases (Protocols for OCS2007R2 in this example).
a. Ensure that this named Instance has the TCP/IP protocol enabled (see Figure 2).
b. Ensure that the Communications Server 2007 R2 back-end databases ACDDyn, RTC, RTCAb, RTCConfig, and RTCDyn are the only databases hosted on this named instance.
Figure 2. TCP/IP is enabled
Edit the TCP/IP Properties of the Named Instance
1. With the named instance selected, highlight TCP/IP in the details pane on the right.
2. Right-click TCP/IP, click Properties, and then click the IP Addresses tab.
3. Specify the TCP/IP port that you want to use. In this example, TCP/IP port 1501 is configured as the static port for the named instance (Figure 3).
4. Remove the value 0 in the TCP Dynamic Ports setting (Figure 3). The value 0 in the TCP Dynamic Ports setting represents the use of any ephemeral TCP port that is not already in use by the SQL Server named instance.
a. If IP version 6 (IPv6) is enabled on the NIC of the hosting server that is running SQL Server, you will see entries that reflect that protocol in the TCP/IP Properties dialog box. Ensure that all IPv6 entries have the same settings as IP version 4 (IPv4).
5. When all the changes are complete, click OK and remember to restart the recently updated named instance of the server that is running SQL Server for the changes to take effect.
Note. Make sure that the TCP port that you use for your SQL Server named instance is not being used by other applications on the local server and is routable on your network.
Figure 3. Named instance TCP/IP Properties
Verifying the Configuration
The following steps verify the TCP/IP port configuration for the SQL Server named instance it is listening on.
1. Open a command prompt window on the server that is running SQL Server.
2. Enter the following command: netstat -ano. This command lists the active ports used by the associated process IDs of applications on the local computer. For our example, the relevant entry in the output shows that the process ID of the application listening on port 1501 is PID 2410.
3. Enter the following command: tasklist /svc. This command provides a mapping of the process ID to the application name so you can easily identify which application is using a particular port. In our example, the output shows that PID 2401 maps to the SQL Server process for the named instance.
Image Name PID Services
sqlservr.exe 2410 MSSQL$NamedInstance
We’ve confirmed that the SQL Server service for the named instance is listening on the specified TCP port. At this point, the SQL Server service for the named instance is no longer using a dynamic TCP port to listen for SQL Server requests from client applications.
Note. If the named instance is hosting other applications besides the Communications Server 2007 R2 databases, this change is likely to break database connectivity for those applications. For this reason and performance consideration, Communications Server 2007 R2 is supported only on dedicated database instances.
The benefit of using a static TCP port for a SQL Server named instance is that it will allow network administrators to do the following:
- Provide compliance for network security policies.
- Organize addressing conventions for servers running SQL Server.
Help clarify SQL Server TCP traffic for network monitoring.
How Client Applications Discover the SQL Server Named Instance
The SQL Server native client library will query the SQL Server Browser service that is running on the remote computer that is running SQL Server by sending a request for named instance address information using a UDP packet that has a destination IP address of the computer running SQL Server and a destination port of UDP 1434. The SQL Server Browser Service will respond to this request for named instance connectivity information from the remote SQL Server client application with a UDP packet that contains the named instance TCP port information. The remote SQL Server client application knows the IP address and TCP port to which it needs to send its request for SQL Server information. The information in the UDP packet is in clear text and can be read using a network capture tool such as Wireshark or Network Monitor.
Troubleshooting Connectivity Issues
To troubleshoot connectivity problems between the front-end servers and the back-end SQL Server databases in the pool, a useful tool to use is the Microsoft SQL Server workstation tools. Install this tool on the Communications Server 2007 R2 front-end server or another computer on the same network as the computer running SQL Server. The SQL Server workstation tools provide a graphical interface, SQL Server Management Studio, which makes it easier to test connectivity to the SQL Server database. When testing is complete, you can remove the Microsoft SQL Server workstation tools.
After you verify that network connectivity to the SQL Server named instance is functional, you can complete the installation of your Enterprise pool.
Communications Server 2007 R2 offers more flexibility by making it possible to configure the Enterprise Edition to use a named instance of SQL Server. This allows organizations to leverage an existing SQL Server installation to use as an Enterprise pool back-end server instead of commissioning new hardware to install another instance of SQL Server. The ability to use SQL Server named instances allows the installation of several servers that are running SQL Server on a single server hardware. The ability to specify the listening port for the SQL Server service on a SQL Server named instance provides even more adaptability to the installation and maintenance of Communications Server 2007 R2 on an enterprise network. Communications Server 2007 R2 support for connectivity alternatives for use with SQL Server named instances provides Microsoft customers with an easier and more secure deployment process.
Note. Because the configuration of the SQL Server default instance requires the use of a specific connection string, for example default instance 1501, it is currently not fully compatible with the Communications Server 2007 R2 Enterprise Edition Create Pool wizard. If you are experiencing this type of issue, see the workaround suggested in Microsoft Knowledge Base article 975030, “Error message and Create Pool wizard does not complete if you use a default instance of SQL Server that listens on a nonstandard port: ‘Database installation path must be valid'”.
For more information, see the following:
- How to uninstall SQL Server Management Studio
- Introducing SQL Server Management Studio
- Using SQL Server Browser
- Microsoft Network Monitor 3.4
- Download the latest version of Wireshark
Lync Server Resources
- Lync Server 2010 documentation in the TechNet Library
- DrRez blog
- Lync Server and Communications Server resources