FAQs on Microsoft SQL Server 2008 High Availability


Microsoft SQL Server 2008 High Availability

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


        Read more about this book      

(For more resources on Microsoft, see here.)

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:

  1. Node 1 owns the resources as of now, and is active node.
  2. The network adapter driver gets corrupted or suffers a physical damage.
  3. Heartbeat between Node1 and Node 2 is broken.
  4. Node 2 initiates the process to take ownership of the resources owned by the Node 1.
  5. 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.

        Read more about this book      

(For more resources on Microsoft, see here.)

Q: What is the main purpose of Multi-instance, that is, Active/Active Failover Cluster—load balancing or performance boosting?

A: SQL Server failover clustering is for High Availability only. But with multiple instances, we can have two separate applications pointing to separate instances and can redirect the traffic and hence can increase performance. This is how we can balance the network traffic.

Q: What is Peer-to-Peer Replication?

A: Peer-to-Peer Replication type was introduced with the launch of SQL Server 2005, and is the most promising solution when the ultimate goal is to achieve data redundancy and load balancing. Peer-to-Peer Replication is built on top of the Transactional Replication type, where every participant node has a copy of the data, which results in the redundancy of data and ensures that we have high availability.

Q: What is Log Shipping?

A: Log Shipping is one of the solutions that are available with SQL Server to cater to the needs of highly available business applications. It was first introduced with the release of SQL Server 7.0.

As its name suggests, it ships the transaction logs (T-Log) of the one server (primary) to another server (secondary) over the network, to keep the Secondary Server synchronized with the Primary Server. In case of the failure of the Primary Server, we can quickly move over to Secondary Server with the help of just a few steps.

FAQs on Microsoft SQL Server 2008 High Availability

Q: Why we should have Log Shipping as a High Availability option or as a Disaster Recovery option in our environment?

A: Following are the few points we can provide for justification:

  • Easier to set up: It's really an easy job to create Log Shipping. The only thing we need to identify is that we must have two or three servers. The witness server( first is primary/source server and second is secondary/target server) is optional to configure, and it is acceptable if this witness server is not identical to other machines.
  • Manageability: Managing Log Shipping is the simplest among the available HA options that we have with SQL Server. There are only a few errors that can show up with the Log Shipping and they are quite simple to solve (we will see this in the appendix on troubleshooting in detail).
  • Can act as a Reporting server: The Secondary or standby server can act as a Reporting server. We may use this server to read only queries, or to generate reports from. Although, it is really easy to get it working as a reporting server, we need to consider the latency. This means if the T-Log backup copy and restoration would take 10 minutes, and we have scheduled T-Log backup every hour, we will observe 10 minutes delay in data / per hour.
  • Multiple database(s)/servers: We may use multiple servers as a standby/Secondary Server, where one server can be used for reporting purposes, whereas the other can be utilized for HA only.
  • Cheaper than cluster: If we go for clustering, we have to have a special hardware that is compatible with clustering and should be identical in all the participating nodes. On the contrary, Log Shipping doesn't require the same set of hardware.

Q: What is Database Mirroring?

A: Database Mirroring is an option that can be used to cater to the business need, in order to increase the availability of SQL Server database as standby, for it to be used as an alternate production server in the case of any emergency. As its name suggests, Mirroring stands for making an exact copy of the data. Mirroring can be done onto a disk, website, or somewhere else.

Q: What is Disk Mirroring?

A: Disk Mirroring is a technology wherein data is stored on physically separate but identical hard disks at the same time called hardware array disk 1 or RAID 1.

Q: What are the different components of Database Mirroring?

A: To install Database Mirroring, there are three components that are required. They are as follows:

  • Principal Server: This is the database server that will send out the data to the participant server (we'll call it secondary/standby server) in the form of transactions. To have the automatic failover feature, the Principal Server should be in the synchronous mode.
  • Secondary Server: This is the database server that receives all the transactions that are sent by the Principal Server in order to keep the database identical.
  • Witness Server (optional): This server will continuously monitor the Principal and Secondary Server and will enable automatic failover. This is an optional server.

Q: How Database Mirroring works?

A: In Database Mirroring, every server is a known partner and they complement each other as Principal and Mirror. There will be only one Principal and only one Mirror at any given time.

In reality, DML operations that are performed on the Principal Server, are all re-performed at the Mirror server. As we all know, the data is written into the Log Buffer before it is written into data pages. Database Mirroring sends data that is written into Principal Server's Log Buffer simultaneously to the Mirror database. All these transactions are sent in a sequential manner and as quickly as possible.

There are two different operating modes at which Database Mirroring operates—asynchronous and synchronous.

Q: How does Merge Replication work?

A: The following step-by-step process explains working of Merge Replication:

  1. Data gets added from the Publisher's or the Subscriber's end.
  2. The tracking table tracks the data that has been changed.
  3. Merge Agent sends this data to the distribution database.
  4. The distribution database sends this data back to the Merge Agent.
  5. Merge Agent then sends this data to the Publisher and Subscriber(s).

Q: If I am getting Error: 1418; how do I resolve this error?

A: Error 1418 is related to general network error. We have to make sure that the ports we will be using to establish database mirroring are available; also make sure that the firewall are not blocking them.


In this article we have answered some of the frequently asked questions on Microsoft SQL Server 2008 High Availability.

Further resources on this subject:

You've been reading an excerpt of:

Microsoft SQL Server 2008 High Availability

Explore Title