The following commands, executed in sequence, will provide you the list of services running one a node.
regedit /e %temp%servicelst.txt HKEY_LOCAL_MACHINESYSTEMControlSet001Services
type %temp%servicelst.txt | find “DisplayName”
An easy way of starting or stopping the services in bulk is to prepare a batch file which contains all services names with following command
To stop the service
net stop “Service Name”
To start the service
net start “Service Name”
Network Protocols to be Used
During SQL Server Installation, the network protocol Named Pipes is used. SQL Server 2000 must be set to listen to the default pipe, \.pipesqlquery. Named Pipes is required even for a tape backup upgrade. TCP/IP is also required for SQL Server to run in a cluster.
Memory Configuration
Memory & Clusters
There are certain considerations to be taken care of before we configure the cluster’s memory.
Let’s look at example of a four node clustering environment. In the scenario below, we have the X and Y nodes configured as Active / Active, where both node can take over each other’s resources in case of a failover event. Nodes XX and Node YY are also configured as Active / Active, and they also can take over each other’s resources in case of a failover event. Node X is running up with SQL Server A, Node Y is running with SQL Server B, Node XX is running with SQL Server AA and Node YY is running with SQL Server BB.
All 4 nodes X, Y, XX and YY each have 8 Gigs of RAM. As we can see in picture below, every node has 1 GB of RAM occupied by the operating system resources, and the rest of the memory is either free or occupied by SQL server running on the box.
Let’s simulate a failover event with this configuration and see how memory components behave.
Node X has failed and Node Y takes over:
Node X has SQL Server A running on it, and is using 5 Gigs of RAM. On other side, Node Y has SQL Server B running on it, which is using 5 Gigs of RAM, and 1 Gig of RAM is occupied by operating system resources. So Y Node has only 2 Gigs of free memory left. In this case the failover will not be successful. There is not a sufficient amount of memory resources available on the failover Node.
Node XX has failed and Node YY takes over:
Node XX has SQL Server AA running on it and is using 3 Gigs of RAM. On other side, Node YY has SQL Server BB running on it, which is using 3 Gigs of RAM, and 1 Gig of RAM is occupied by operating system resources. So Node YY has 4 Gigs of free memory left. In this case, this failover will work out smoothly. There are sufficient amount of memory resources available on the failover Node.
Memory Capping
There can be certain situations where you want to limit how much RAM SQL server uses. This can be achieved by Memory Capping. We can cap the maximum memory usage threshold by using the sp_configure “max server memory” option.
In above failover scenario, let’s discuss that how Memory Capping would help where Node X has failed and Y is takes over. If we cap the memory on SQL Server A and SQL Server B to 3 Gigs, then the failover would be successful.
Memory capping helps where you have many small instances running on one box and you want to control the memory usage on each of them according to their criticality.
Important Factors When Configuring Memory
With a clustered SQL Server configuration, the DBA should consider using additional memory and high speed CPU resources.
-
Use Task Manager, or Performance Monitor counters, to analyze that how much memory your operating system needs in a typical day-to-day cycle.
-
SQL Server 2000 and standard services together use over 100 MB of memory as a baseline.
-
User connections consume about 24 KB each.
-
While the minimum memory for query execution is one MB of RAM, the average query may require two to four MB of RAM.
Memory Switches
Memory switches play an important role in memory configuration between the operating system and SQL Server. Let’s look at an example of a server with 4 Gigs of memory. If you do not specify any memory switches, SQL Server will be able to access only 2 Gigs by default out of 4 GB memory on the server. We can specify a /3GB switch in Boot.ini to tell the SQL Server to make use of 3 Gigs of RAM out of the base 4 Gigs. If you don’t specify this option, it will be waste of 1 Gig of RAM.
If your total RAM exceeds 16 Gigs of RAM and the AWE option is enabled, then your operating system itself needs 2 Gigs of memory to manage the 16 Gigs of AWE memory. Configuring the /3GB switch in Boot.ini would not help in this scenario. Following is an example of enabling the /3GB switch in boot.ini file.
multi(0)disk(0)rdisk(0)partition(2)WINNT=”Windows 2000 Advanced Server” /3GB