SQL Server Clustering

Chapter1 INTRODUCTION

 

 

A poorly performing database application can cost each user time, and have an impact on other applications running on the same computer or the same network. If your mission-critical SQL Server should experience a failure, how long will it be down? One hour, four hours, a day, or longer? How much will this cost your business in lost sales or productivity?

The purpose of this document is to help our user improve the availability and performance of SQL Server database,along with its pros and cons.

As you may already be aware, there is a way to help boost your SQL Server’s uptime, and that is by clustering SQL Servers. First,let’s take a brief look at what the cluster is. More specifically, clustering refers to a group of two or more servers (generally called nodes) that work together and represent themselves as a single virtual server to a network. In other words, when a client connects to clustered SQL Servers, it thinks there is only a single SQL Server, not more than one. When one of the nodes fails, its responsibilities are taken over by another server in the cluster, and the end-user notices little, if any differences before, during, and after the failover.

 

 

 

 

Implementing SQL Server clustering is a big decision,so let’s look at the pros and cons,software and hardware needed of SQL Server Clustering.

Pros of SQL Server Clustering

• Reduces downtime to a bare minimum.

• Permits an automatic response to a failed server or software. No human intervention is required.

• It allows you to perform upgrades without forcing users off the system for extended periods of time.

• It allows you to reduce downtime due to routine server, network, or database maintenance.

• Clustering doesn't require any servers to be renamed. So when failover occurs, it is relatively transparent to end-users.

• Failing back is quick, and can be done whenever the primary server if fixed and put back on-line.

• In some cases, clustering can be used to increase the scalability of an application. For example, if a current cluster is getting too busy, another server could be added to the cluster to expand the resources and help boost the performance of the application.

Cons of Clustering

• More expensive than other failover alternatives, such as log shipping or stand-by servers.

• Requires more set up time than other alternatives.

• Requires more on-going maintenance than other alternatives.

• Requires more experienced DBAs and network administrators.

Software Needed for Clustering

The software you need for SQL Server clustering depends on whether you want to cluster two nodes, or more than two nodes.

To cluster two nodes, you will need the following:

• Two Microsoft Windows 2000 Advanced Server Licenses

• One SQL Server 7.0 Enterprise or SQL Server 2000 Enterprise Licenses for Active/Passive, or two licenses for Active/Active

• The latest Windows 2000 and SQL Server Service Packs

To cluster more than two nodes, you will need the following:

• Two or More Microsoft Windows 2000 Datacenter Server Licenses

• Two or More SQL Server 7.0 Enterprise or SQL Server 2000 Enterprise Licenses

• The latest Windows 2000 and SQL Server Service Packs

I want to emphasize that you always want to go with the latest service packs, as many irritating cluster-related bugs have been fixed by them.

Hardware Needed for Clustering

Assuming you are clustering two SQL Servers, you will need at the very minimum the following:

• Two servers with a minimum of 256MB RAM and a single Pentium III CPU.

• One shared disk array that supports RAID 5 or 10, either SCSI or fibre channel.

• Each server must have at least one local SCSI hard disk on its own SCSI controller.

• Each server must have a SCSI or fiber channel adapter to talk to the shared disk array. The shared disk array cannot use the SCSI controller used by the local hard disk or CD-ROM.

• Each server must have two PCI network cards (one for the private connection and one for the public connection).

• selected clustering hardware is that is must be on Microsoft’s Hardware Compatibility List (HCL).

 

 

Chapter2 Logical standby

2.1 Server mode

SQL Server Enterprise Edition run in normal mode.

2.2 Standby time

The appropriate standby time should be chosen by users’ self such as 12 every night. The principle of standby is no business or little business on this server.

2.3 Standby location

The bad practice is leaving the standby data in the same disk with the original data. Users should use a individual computer for standby.

2.4 Standby commands

For SQL Server 2000 Enterprise Edition:

backup database dbname to backupdevice1

restore database dbname from backupdevice1 with standby = 'undo.ldf'

backup log dbname to backupdevice2

restore log dbname from backupdevice2

restore database dbname with recovery

etc.

2.5 Standby keeping time

For some reason, keeping the last twice backup data is okay.

2.6 Standby tool with GDI

For increasing the manageability, we should provide a standby tool with GDI implementing the function of logical backup and logical restore.

2.7 Pros and cons

	High manageability for users such as standby tool with GDI or scripts for automation of backup, restore and other operation etc.
	Low costs for users. The performance of standby server can be ignored. And they do not need a professional DBA.
	Little option for backup such as timestamp or table.
	The time for backup is unacceptability for instance 3.255MB/sec in our test.
	It is unreal time system. The restore only aim at the last backup and will cause data out of sync.

Chapter3 Hot standby with replication

3.1 Summary

User can replicate data one from data source to other data sources. Replication technology provided by SQL Server 2000 Enterprise Edition itself. It can ensure the sync of data in different SQL Server.

3.2 Server mode

SQL Server run in issue mode.

3.3 Server requirement

We need 2 or more same servers for SQL Server.

3.4 Pros and cons

	Automated manageability for backup and high manageability for restore.
	More costs for users. The performance of standby server must be considered. But they do not need a professional DBA still.
	It is unreal time system. The delay for replication will reach 10 minutes or above 1 hour and will cause data out of sync.
	Replication need more source of server and will affect performance of server.
	Replication only aim at table, index, data, view and store procedure but not trigger, constraint etc.

Chapter4 Hot standby with log shipping

4.1 Summary

Current database copy its log to standby server with “Standby mechanism” provided by SQL Server 2000 Enterprise Edition itself. Standby server restore the log automatically to keep the sync of the 2 servers. If current database failed, standby server will instead of current server immediately. The details can get from BOL of SQL7 named “Using standby server”.

4.2 Server mode

SQL Server run in normal mode.

4.3 Server requirement

We need 2 same server for SQL Server.

4.4 Pros and cons

	Automated manageability for backup and high manageability for restore.
	Compare to replication, log shipping need less source of server.
	More costs for users. The performance of standby server must be considered. And they  need a professional DBA.
	It also is unreal time system. The restore only aim at the last backup and will cause data out of sync.

Chapter5 Hot standby with SQL Server failover clustering

5.1 Summery

SQL Server failover clustering is a example of Server clustering of Windows series. More detail will found in another document named XXX.

Server clusters provide a highly available platform for mission critical, line of business applications. Server clusters are used to ensure that stateful applications can be kept highly available by failing over the application from one server to another in the event of a failure. SQL Server 2000 setup is aware of Server clusters. In the case of Microsoft SQL Server 2000 setup, the appropriate files are copied to all servers and registry settings and other SQL Server configuration is done just once.

5.2 1 + 1 Mode(Failover Pairs)

In a large cluster, failover policies can be defined such that each application is set to failover between two nodes. The simple example below shows two applications App1 and App2 in a 4-node cluster.

Figure 1: Failover pairs

 

 

Configuration has pros and cons:

	Good for clusters that are supporting heavy-weight applications. This configuration ensures that in the event of failure, two applications will not be hosted on the same node.
	Very easy to plan capacity. Each node is sized based on the application that it will need to host (just like a 2-node cluster hosting one application).
	Effect of a node failure on availability and performance of the system is very easy to determine.
	Get the flexibility of a larger cluster. In the event that a node is taken out for maintenance, the buddy for a given application can be changed dynamically (may end up with standby policy below).
	In simple configurations such as the one above, only 50% of the capacity of the cluster is in use.
	Administrator intervention may be required in the event of multiple failures.

Failover pairs are supported by server clusters on all versions of Windows by limiting the possible owner list for each resource to a given pair of nodes.

5.3 N + 1 Mode(Hot-Standby Server)

To reduce the overhead of failover pairs, the spare node for each pair may be consolidated into a single node, providing a hot standby server that is capable of picking up the work in the event of a failure.

Figure 2: Standby Server

 

 

Configuration has pros and cons:

	Good for clusters that are supporting heavy-weight applications. This configuration ensures that in the event of a single failure, two applications will not be hosted on the same node.
	Very easy to plan capacity. Each node is sized based on the application that it will need to host, the spare is sized to be the maximum of the other nodes.
	Effect of a node failure on availability and performance of the system is very easy to determine.
	Configuration is targeted towards a single point of failure.
	Does not really handle multiple failures well. This may be an issue during scheduled maintenance where the spare may be in use.

Server clusters support standby servers today using a combination of the possible owners list and the preferred owners list. The preferred node should be set to the node that the application will run on by default and the possible owners for a given resource should be set to the preferred node and the spare node.

5.4 N + I Mode

Standby server works well for 4-node clusters in some configurations, however, its ability to handle multiple failures is limited. N+I configurations are an extension of the standby server concept where there are N nodes hosting applications and I nodes spare.

Figure 3: N+I Spare node configuration

 

 

Configuration has pros and cons:

	Good for clusters that are supporting heavy-weight applications. This configuration ensures that in the event of a failure, an application instance will failover to a spare node, not one that is already in use.
	Very easy to plan capacity. Each node is sized based on the application that it will need to host.
	Effect of a node failure on availability and performance of the system is very easy to determine.
	Configuration works well for multiple failures.
	Does not really handle multiple applications running in the same cluster well. This policy is best suited to applications running on a dedicated cluster.

Server cluster supports N+I scenarios in the Windows Server 2003 release using a cluster group public property AntiAffinityClassNames. This property can contain an arbitrary string of characters. In the event of a failover, if a group being failed over has a non-empty string in the AntiAffinityClassNames property, the failover manager will check all other nodes. If there are any nodes in the possible owners list for the resource that are NOT hosting a group with the same value in AntiAffinityClassNames, then those nodes are considered a good target for failover. If all nodes in the cluster are hosting groups that contain the same value in the AntiAffinityClassNames property, then the preferred node list is used to select a failover target.

5.5 Multi site Mode(Geographically dispersed cluster)

Geographically dispersed cluster.

The server cluster has the following attributes:

1. Has multiple storage arrays, at least one deployed at each site. This ensures that in the event of failure of any one site, the other site(s) will have local copies of the data that they can use to continue to provide the services and applications.

2. Nodes are connected to storage in such a way that in the event of a failure of either a site or the communication links between sites, the nodes on a given site can access the storage on that site. In other words, in a two-site configuration, the nodes in site A are connected to the storage in site A directly and the nodes in site B are connected to the storage in site B directly. The nodes in site A con continue without accessing the storage on site B and vice-versa.

3. The storage fabric or host-based software provides a way to mirror or replicate data between the sites so that each site has a copy of the data. Different levels of consistency are available.

The following diagram shows a simple two-site cluster configuration.

 

 

Configuration has pros and cons:

	Good for clusters that are supporting heavy-weight applications. This configuration ensures that in the event of a failure, an application instance will failover to a spare node, not one that is already in use.
	Very easy to plan capacity. Each node is sized based on the application that it will need to host.
	Effect of a node failure on availability and performance of the system is very easy to determine.
	Ensure that loss of one site in the solution does not cause a loss of the complete application. Sites have completely diff power, diff communications infrastructure providers, and are placed so that natural disasters (e.g. earthquakes) are extremely unlikely to take out more than one site.
	Configuration works well for multiple failures.
	Does not really handle multiple applications running in the same cluster well. This policy is best suited to applications running on a dedicated cluster.