SQL Server 2000 & 2005 Clustering

Named Instance Alias

If your operating system is Windows 2003 and you are going to create a named SQL Server instance, you will have to create an entry for that Named Instance using the SQL Server Client Network Utility. For more details on this visit “Additional steps for Windows 2003 clustering” section later in this article.

Required Services to be Up and Running

Verify that only the services necessary for the operating system to function are running. Any other services should be stopped because they may interfere with the installation process.

Following are the services on Windows 2000 Advanced Server and Windows 2000 Datacenter Server that should be left running.

Alerter
Cluster Service
Computer Browser
Distributed File System
Distributed Link Tracking Client
Distributed Link Tracking Server
DNS Client
Driver Extensions
Event Log
License Logging Service
Logical Disk Manager
Messenger
Net Logon
Network Connectors
Plug and Play
Process Control
Remote Procedure Call (RPC) Locator
Remote Procedure Call (RPC) Service
Remote Registry Service
Removable Storage
Security Accounts Manager
Server
Spooler
TCP/IP NetBIOS Helper
Windows Management Instrumentation
Windows NT LM Security Support Provider
Windows Time Service
Workstation

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.

Continues…



Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |