The information listed below has been tested using Microsoft SQL Server 2005 and Microsoft SQL Server 2008.

Upon completion of the install remember to reboot the Office Communications Server 2007 R2 Enterprise Edition Pool server to make sure that all the services start without an issue.

Author: Mike Adkins

Publication date: April 2009

Product version: Office Communications Server 2007 R2

Office Communications Server 2007 Enterprise Edition R2 supports the use of non default TCP\IP port configurations for network access to its pool configuration databases that are located on the backend SQL server instance. Microsoft SQL Server supports three types of configurations for the Instances that it can host. They are:

Default Instance

The Default Instance is the instance that is installed on the SQL Server that will inherit the host name of the server that SQL Server is installed on. There can only be one Default Instance installation per SQL Server. So any instances that are installed before or after the single Default Instance will have to be a SQL Server Named Instance.

Named Instance

A Named Instance can be installed onto a SQL Server at any time. Microsoft SQL Server can support many Named Instances at one time. Named Instances will use whatever meaningful name that they were given during their installation. For specifics on the number of Named Instances the your version of SQL Server can  support please query the SQL Server Books Online help tool or visit the Microsoft SQL Server website and search its technical listings.

Default Instance\Named Instance

This SQL Server installation includes an installed Default Instance along with one or more Named Instances that are installed on the Microsoft SQL Server. The Default Instance will inherit the host name of the server that Microsoft SQL Server is installed on, and any Named Instance will use whatever meaningful name that they were given during their installation. For specifics on the number of Named Instances the your version of SQL Server can  support please query the SQL Server Books Online help tool or visit the Microsoft SQL Server website and search its technical listings.

The Microsoft SQL Server Default Instance will always use the TCP Port 1433 to listen on with its initial installation. This is the default listening port for the Microsoft SQL Server service when it is installed as a Default Instance. The Default Instance can be configured to listen on a non default TCP\IP port. When configured to listen on a non default TCP\IP port the SQL Server client application will have to specify the non default TCP\IP port in their configuration string e. g. Default Instance,1501. This will allow the SQL Server client application connect to the server that is hosting Microsoft SQL Server by using its host name and specifying the non default TCP\IP port 1501.

The SQL Server Browser service is used by the Microsoft SQL Server to manage the non default port connectivity information for each of the Named Instances that are installed on the Microsoft SQL Server. The Named Instances use a dynamic TCP\IP port configuration by default, but they can have a static non default TCP\IP port configuration manually applied to them. The SQL Server Browser service will manage the TCP\IP port connectivity information for either. Please remember the SQL Server Default Instance does not ever use the SQL Browser service to manage its non default TCP\IP port configuration. The SQL Server Browser service should be enabled to start automatically when a Named Instance is installed on the Microsoft SQL Server. Use the Microsoft SQL Server services.msc to locate the SQL Server Browser service. Make sure that this service is set to start automatically and is started. The SQL Server Bowser service will automatically assign its self to UDP port 1434. Please remember to make sure that UDP port 1434 in unrestricted for bi-directional traffic between the Office Communications Server 2007 Enterprise Edition R2 Pool server and the Microsoft SQL Server back end.

SQL Server Books Online (November 2008)
Using SQL Server Browser
http://msdn.microsoft.com/en-us/library/ms165724(SQL.90).aspx

Listed below are the configuration steps that will allow the Office Communications Server 2007 R2 Enterprise Edition Pool server to access the Microsoft SQL Server Named Instance, Default Instance \ Named Instance configurations. Since the Microsoft SQL Server Default Instance configuration requires the use of a specific configuration string e. g. Default Instance,1501 it is currently not fully applicable with the Office Communications Server 2007 R2 Enterprise Edition Create Pool wizard, so at this time I will not bother to include this non default TCP\IP port configuration for the Microsoft SQL Server Default Instance configuration. For this article I will specify the non default TCP\IP port configuration for the Default Instance\Named Instance Microsoft SQL Server Instance configuration.

The Microsoft SQL Server named instance is installed with the default instance e. g. Default Instance\Named Instance. The steps listed below will also work with the implementation of the Microsoft SQL Server Named Instance configuration.

To begin with you will need to use the SQL Server Configuration Manager on the Microsoft SQL Server that will host the Office Communications Server 2007 R2 Enterprise Edition database collection to make sure that the protocols listed in the Microsoft SQL Server network configuration match each other.

SQL Server Configuration Manager

Protocols for SQL Server

Protocols for Named Instance

SQL Native Client Configuration - Client Protocols


 

Next the TCP/IP static listening port needs to be set on the Named Instance using the SQL Server Configuration Manager on the Microsoft SQL Server.

SQL Server Network Configuration

Highlight the Protocols for Named Instance node and then locate TCP\IP in the Details pane. Open the TCP\IP properties dialog and click on the IP Addresses tab. Three categories will be listed as IP1, IP2 and IPAll. Replace the 0 in the TCP Dynamic Ports entry with a blank space. Then add your chosen TCP port number to the TCP port entry for all 3 categories. For instance, TCP Port 1501 (as per this example).

Click on the OK button and you will be prompted to restart the Default Instance\Named Instance for the changes to take effect. The SQL Server service for each should restart without an error.

From a command prompt on the Microsoft SQL Server box use c:\>netstat -ano followed by c:\>tasklist /svc. This will list the active and listening ports / process IDs on the server along with the process ID / service name. The c:\>netstat -ano output should have an entry similar to the one listed below. The SQL Server process for the Named Instance has a process ID of 2401 and is listening on all interfaces on TCP 1501.

 Proto  Local Address         Foreign Address        State              PID

 TCP    0.0.0.0:1501          0.0.0.0:0                 LISTENING       2401
 

The c:\>tasklist /svc entry will back end similar to the one listed above

Image Name                   PID Services

========================= ==============================================

sqlservr.exe                2401 MSSQL$NamedInstance

Now we can see that the SQL Server service for the Named Instance is listening on the TCP port that we specified earlier. 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. Please remember that if the Named Instance is hosting other applications besides the Office Communications Server 2007 R2 databases that this update could cause a break in connectivity for the legacy client applications. This operation should be performed on a Microsoft SQL Server Named Instance that is dedicated to just the Office Communications Server 2007 R2 databases. The benefit of using the static TCP port for the SQL Server Named Instance is that it will allow network administrators the flexibility to choose the TCP ports that they would want their SQL Server Instances to use on their network which hosts the Office Communications Server 2007 Enterprise Edition R2 databases. Please make sure that routing or firewall rules on devices that will help route the IP traffic between the Office Communications Server 2007 R2 Enterprise Edition Pool and the Microsoft SQL Server that will host the Office Communications Server 2007 R2 Enterprise Edition back end databases.

How it works

The SQL Server native client library will query the SQL Server Browser service on the Microsoft SQL Server using an ephemeral source UDP port that has a destination of the listening UDP port 1434 on the Microsoft SQL Server box. The SQL Server browser will respond to the query for the Named Instance SQL Server service listening TCP ports with the requested 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.

Testing non default port connectivity

You can install the Microsoft SQL Server workstation tools on the Office Communications Server 2007 R2 Enterprise Edition consolidated server. Though the SQL Server native library is available with the R2 installation of Office Communications Server 2007, the SQL Server workstation tools allow you to use the GUI SQL Server Configuration Manager along with the SQL Server Management Studio. This combination allows you the availability to test the connectivity between the server that will host Office Communications Server 2007 R2 Server and the SQL Server database installations. When testing is complete you can remove the Microsoft SQL Server workstation tools from the Office Communications Server 2007 R2 Pool server.

Now to test your connectivity prior to installing Office Communications Server 2007 R2 Enterprise Edition you will need to install a Network capture tool on to the Office Communications Server 2007 R2 server to back end and open the SQL Server Management Studio. Using the SQL Server 2007 Management Studio you will connect to your Default Instance\Named Instance while taking a network capture to confirm that you are connecting to the Microsoft SQL Server using port TCP 1501 (as per the example).

  1. Start the network capture
  2. In SQL Server Management Studio choose Connect \ Database Engine from the pull down menu
  3. Enter your Default Instance\Named Instance
  4. Click on Connect. You will back end able to browse the SQL server system databases in the Object Explorer. You will not back end able to view the contents of the system tables though - they are read only.
  5. Stop your network capture and view the TCP traffic to the IP address of the SQL Server. Notice that your designated TCP\IP port is back ending used.

Now you are ready to:

  • Create the Office Communications Server 2007 Enterprise Edition R2 pool from the Office Communications Server 2007 R2 server and configure the Pool
  • Add the Server to the Pool
  • Create and apply the certificates to the Office Communications Server 2007 R2 pool server
  • Start the Office Communications Server 2007 Enterprise Edition R2 services

While performing the steps listed above you can take a network capture at each step so you can view the TCP traffic between the non default port on the Microsoft SQL Server backend server and Office Communications Server 2007 R2 Enterprise Edition pool server. This will allow you to confirm that the non default port configuration for the Default Instance\Named Instance is working as expected. Also, if you want you can filter the network capture for the UDP port 1434 traffic. Inspecting these packets will let you see the SQL Server instance TCP port configuration that is passed back to the Office Communications Server 2007 R2 Enterprise Edition server.

Lync Server Resources

We Want to Hear from You