Sql Server Faq

 

 

In sql server the default instance has a listener which listens on the fixed port which is TCP port 1433. However for the named instance the port on which the sql server listens is random and is dynamically selected when the named instance of the sql server starts.
So when we connect to the default instance of the sql server the connections goes to the TCP port 1433 while when we connect to the named instance of the sql server the connections goes to the sql browser service which listens on the UDP port 1434. The sql browser service replies to the client with port no. on which the named instance of the sql server listens at that point of time and then the client is redirected and connects to the port no. provided by sql server service.
You may require to change the dynamic port of the named instance to the static port no. so to implement Firewall in your organization so that clients connect to the sql server through the firewall.
For Standalone instance of the sql server we can change the dynamic port of the named instance to the static port by using sql server configuration manager in sql 2005. In order to change the port we need to the sql server configuration manager.
Start->Program Files-> Microsoft sql server 2005->Configuration Tools->sql server configuration manager.

In configuration manager console, on the left hand panel, expand the sql server 2005 Network Configuration.

Click on the”Protocols for <Named-Instance-Name>”. On the right hand panel we need to right click and go to properties on the TCP/IP.

Click on the Ip Addresses tab on the top and keep the “Dynamic ports” row BLANK and write the desired port no. on which you want the named instance to listen on “TCP ports” row in the “IP ALL “section.

After performing these activities we need to restart the sql server services for the changes to take affects.

After restarting the service you can confirm that whether changes has taken affect by checking the following registry key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name> \MSSQLServer\SuperSocketNetLib\Tcp
This key should contain the port no. defined by you.

However in case of the cluster, many a times (In particular when sql server 2005 32 bit is installed on the 64 bit system) it is observed that when we change the port no. of the named instance to the static port using the method described above the port no. again changes back to the dynamic port after you restart the services.

This is behavior can be explained as follows

When we changed the dynamic port of the sql server to the static port and stopped the sql server service the local copy of the registry which contains the dynamic port no. gets checkpointed to the quorum.

Now when the service is started the Checkpointed value from quorum are copied back to the registry and hence in spite of changing the value to static port. The value was replaced by the dynamic port from quorum.

So in order to change the dynamic port of the server to the static port in the clustered named instance of the sql server 2005 we need to follow the following steps

1) Take the sqlserver service offline from cluster administrator

2)  Disable the checkpointing to the quorum using the following command

Cluster res “SQL Server (TEST2005)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”

3) Change the Dynamic port of the sql server to static port  on all the nodes using the method described above.

4) Enable the checkpointing to the quorum using the following command

cluster res ” SQL Server (TEST2005)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”

5) Bring the sql server service online

In the above example “TEST2005” is the name of my sql server instance
                                          MSSQL.2  is since it is second sql server 2005 instance installed on the system

You will observe that the dynamic port is changed to the static port.

 

 

FOR SQL SERVER 2000:

 

Case 1: If the default instance of  sql server is to be upgraded to default clustered instance of sql server 2000

 

Note:

1)       Only Default instance can be upgraded by this method

2)        This Functionality is not vailable in sql server 2005 so if you want to convert a standalone instance of sql server 2005 into clustered we need to follow the steps mentioned in case 2 or case 3.

 

Pre-requisites:

1)       The Standalone instance of the sql server which is to be upgraded to cluster must have all it’s database with data and log files on the shared disk so that these files can be accessed by node 2 when the cluster fails over to node 2

2)       The Sql server service must be running under the domain user account which has local administrator priviledges on both the nodes. This is a pre-requisite for a clustered instance of sql server.

 

Only after fulfilling the above pre-requisites we can go ahead with the steps mentioned below or else the “upgrade the existing instance to cluster” option in the wizard will be grayed and will not be activated.

 

 1)       On the Welcome screen of the SQL Server Installation Wizard, click Next.

  Welcome Wizard 

2)       On the Computer Name screen, click Local Computer. The computer you want to change from a default to a clustered instance should be displayed. You must be on the local computer to upgrade from a default to a clustered instance. Click Next.

 

3)       On the Installation Selection screen, click Upgrade, remove, or add components to an existing instance of SQL Server. Click Next.

 

4)       On the Existing Installation screen, click Upgrade your existing installation to a clustered installation. Click Next.

 

5)       On the Virtual Server Name screen, enter a name for your virtual server. Click Next.

6)       On the Failover Clustering screen, enter one IP address for each network configured for client access. That is, enter one IP address for each network on which the virtual server will be available to clients on a public (or mixed) network. Select the network for which you want to enter an IP address, and then enter the IP address. Click Add.

The IP address and the subnet are displayed. The subnet is supplied by Microsoft Cluster  Service (MSCS). Continue to enter IP addresses for each installed network until you have populated all desired networks with an IP address. Click Next.

 

 

 

 

 

 

7         On the Cluster Management screen, review the failover cluster definition provided by SQL Server 2000. By default, all available nodes are selected. Remove any nodes that will not be part of the failover cluster definition for the virtual server you are creating. Click Next.

 

 

 8         On the Remote Information screen, enter login credentials for the remote cluster node. The login credentials must have administrator privileges on the remote node(s) of the cluster. Click Next.

 

 

 

9           On the Services Accounts screen, select the service account(s) for the SQL Server services under which you want the failover cluster to run. Click Next. 

 

  

10     On the Setup Complete screen, click Finish. If you need to restart the remote nodes in the failover cluster, you will be instructed to do so in the Setup Complete screen.

 

 

  

 

Case 2: If the default instance of sql server is to be upgraded to named clustered instance of sql server 2000

 

1.       Install a clustered  named instance of Microsoft® SQL Server™ 2000.

2.       Run the Copy Database Wizard (CDW.exe) to move all databases and related information into the clustered, named instance of SQL Server 2000.

Optionally we can,

a.       Take backup of all the system databases and user databases

b.       Uninstall the default instance of sql server

c.        Install a new clustered named instance

d.       Restore the backup on this clustered instance

 

 

 

 

Case 3: If the Named instance of sql server is to be upgraded to Named clustered instance of sql server 2000

 

1)       Take backup of all the system databases and user databases

2)       Uninstall the default instance of sql server

3)       Install a new clustered named instance with the same name

4)       Restore the backup on this clustered instance