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
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:
Q: What are the Hardware, Software and Operating system requirements for installing SQL Server 2008 R2?
A: The following are the hardware requirements:
The following are the software requirements:
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.
I remember deciding to pursue my first IT certification, the CompTIA A+. I had signed…
Key takeaways The transformer architecture has proved to be revolutionary in outperforming the classical RNN…
Once we learn how to deploy an Ubuntu server, how to manage users, and how…
Key-takeaways: Clean code isn’t just a nice thing to have or a luxury in software projects; it's a necessity. If we…
While developing a web application, or setting dynamic pages and meta tags we need to deal with…
Software architecture is one of the most discussed topics in the software industry today, and…