Microsoft SQL Server 2008 High Availability
Minimize downtime, speed up recovery, and achieve the highest level of availability and reliability for SQL server applications by mastering the concepts of database mirroring,log shipping,clustering, and replication
- Install various SQL Server High Availability options in a step-by-step manner
- A guide to SQL Server High Availability for DBA aspirants, proficient developers and system administrators
- Learn the pre and post installation concepts and common issues you come across while working on SQL Server High Availability
- Tips to enhance performance with SQL Server High Availability
- External references for further study
Q: What is Clustering?
A: Clustering is usually deployed when there is a critical business application running that needs to be available 24 X 7 or in terminology—High Availability. These clusters are known as Failover clusters because the primary goal to set up the cluster is to make services or business processes that are critical for business and should be available 24 X 7 with 99.99% up time.
Q: How does MS Windows server Enterprise and Datacenter edition support failover clustering?
A: MS Windows server Enterprise and Datacenter edition supports failover clustering. This is achieved by having two or more identical nodes connected to each other by means of private network and commonly used resources. In case of failure of any common resource or services, the first node (Active) passes the ownership to another node (Passive).
Q: What is MSDTC?
A: Microsoft Distributed Transaction Coordinator (MSDTC) is a service used by the SQL Server when it is required to have distributed transactions between more than one machine. In a clustered environment, SQL Server service can be hosted on any of the available nodes if the active node fails, and in this case MSDTC comes into the picture in case we have distributed queries and for replication, and hence the MSDTC service should be running. Following are a couple of questions with regard to MSDTC.
Q: What will happen to the data that is being accessed?
A: The data is taken care of, by shared disk arrays as it is shared and every node that is part of the cluster can access it; however, one node at a time can access and own it.
Q: What about clients that were connected previously? Does the failover mean that developers will have to modify the connection string?
A: Nothing like this happens. SQL Server is installed as a virtual server and it has a virtual IP address and that too is shared by every cluster node. So, the client actually knows only one SQL Server or its IP address. Here are the steps that explain how Failover will work:
- Node 1 owns the resources as of now, and is active node.
- The network adapter driver gets corrupted or suffers a physical damage.
- Heartbeat between Node1 and Node 2 is broken.
- Node 2 initiates the process to take ownership of the resources owned by the Node 1.
- It would approximately take two to five minutes to complete the process.
Q: What is Hyper-V? What are their uses?
A: Let’s see what the Hyper-V is:
- It is a hypervisor-based technology that allows multiple operating systems to run on a host operating system at the same time. It has advantages of using SQL Server 2008 R2 on Windows Server 2008 R2 with Hyper-V. One such example could be the ability to migrate a live server, thereby increasing high availability without incurring downtime, among others.
- Hyper-V now supports up to 64 logical processors.
- It can host up to four VMs on a single licensed host server. SQL Server 2008 R2 allows an unrestricted number of virtual servers, thus making consolidation easy.
- It has the ability to manage multiple SQL Servers centrally using Utility Control Point (UCP).
- Sysprep utility can be used to create preconfigured VMs so that SQL Server deployment becomes easier.
Q: What are the Hardware, Software and Operating system requirements for installing SQL Server 2008 R2?
A: The following are the hardware requirements:
- Processor: Intel Pentium 3 or Higher
- Processor Speed: 1 GHZ or Higher
- RAM: 512 MB of RAM but 2 GB is recommended
- Display : VGA or Higher
The following are the software requirements:
- Operating system: Windows 7 Ultimate, Windows Server 2003 (x86 or x64), Windows Server 2008 (x86 or x64)
- Disk space: Minimum 1 GB
- .Net Framework 3.5
- Windows Installer 4.5 or later
- MDAC 2.8 SP1 or later
The following are the operating system requirements for clustering:
To install SQL Server 2008 clustering, it’s essential to have Windows Server 2008 Enterprise or Data Center Edition installed on our host system with Full Installation, so that we don’t have to go back and forth and install the required components and restart the system.
Q: What is to be done when we see the network binding warning coming up?
A: In this scenario, we will have to go to Network and Sharing Center | Change Adapter Settings. Once there, pressing Alt + F, we will select Advanced Settings. Select Public Network and move it up if it is not and repeat this process on the second node.
Q: What is the difference between Active/Passive and Active/Active Failover Cluster?
A: In reality, there is only one difference between Single-instance (Active/Passive Failover Cluster) and Multi-instance (Active/Active Failover Cluster). As its name suggests, in a Multi-instance cluster, there will be two or more SQL Server active instances running in a cluster, compared to one instance running in Single-instance. Also, to configure a multi-instance Cluster, we may need to procure additional disks, IP addresses, and network names for the SQL Server.
Q: What is the benefit of having Multi-instance, that is, Active/Active configuration?
A: Depending on the business requirement and the capability of our hardware, we may have one or more instances running in our cluster environment.
The main goal is to have a better uptime and better High Availability by having multiple SQL Server instances running in an environment. Should anything go wrong with the one SQL Server instance, another instance can easily take over the control and keep the business-critical application up and running!
Q: What will be the difference in the prerequisites for the Multi-instance Failover Cluster as compared to the Single-instance Failover Cluster?
A: There will be no difference compared to a Single-instance Failover Cluster, except that we need to procure additional disk(s), network name, and IP addresses. We need to make sure that our hardware is capable of handling requests that come from client machines for both the instances.
Installing a Multi-instance cluster is almost similar to adding a Single-instance cluster, except for the need to add a few resources along with a couple of steps here and there.