Configuring TCP/IP properties

Select Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager, as shown in Figure 38. The SQL Server Configuration Manager opens as shown in Figure 39.

In the SQL Server Configuration Manager, click SQL Server 2005 Network Configuration.

If the database has only one instance, only one protocol item exists in the red frame in Figure 39. In this case, go to step 6.

If the database has multiple instances, perform the following steps to check the listening port used by each instance to avoid conflicts:

1. Click Protocols for MSSQLSERVER on the left navigation tree.

2. Double-click TCP/IP in the right pane (or click TCP/IP and then click the Properties button on the toolbar).

   Figure 40: Launching the TCP/IP Properties page

   

3. Click the IP Addresses tab to view the listening port used by the instance. The instance uses TCP port 1433, as shown in Figure 41.

   Figure 41: Viewing the TCP port used by the database instance

   

Repeat step 4 to view the listening ports used by existing instances.

Configure the TCP/IP attributes for the newly added instance. Click Protocols for IMCMILP on the left navigation tree.

Double-click TCP/IP in the right pane (or click TCP/IP and then click the Properties button on the toolbar). The TCP/IP Properties page opens, as shown in Figure 42.

Select Yes for Listen All on the Protocol tab.

Select Yes for the Enabled option of each IP address.

Make sure the TCP Dynamic Ports item is null for IPAll and each IPn (such as IP1, IP2, IP3, etc). Set TCP Port to 8433 on the IP Address tab, as shown in Figure 43. The TCP Port should be specified as needed. Make sure the specified TCP Port is not used by any other instances and application.

Click OK to save the configuration.

Click OK when the following message is displayed: Any changes made will be saved; however, they will not take effect until the service is stopped and restarted.

Restart the SQL server to make the configuration effective.

For more information about the configuration procedure, see "Restarting the SQL Server service."

	NOTE: If you did not select Yes for Listen All on the TCP/IP Properties page, enter the page to update the previously-configured IP addresses if the server IP address changes after installing SQL Server 2008 R2. Otherwise, you cannot connect to the database. When you set the TCP/IP properties, for a named instance, all the IP addresses on the IP Address configuration page use the dynamic port (the value for TCP Dynamic Ports is set to 0) by default. You must first delete the value of the TCP Dynamic Ports item for IPALL and each IPn, and then specify the TCP port. In distributed deployment, if multiple database servers are used, these servers must have the same listening port.