How to create and setup a SQL clustering on your Desktop or Laptop
I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get a chance to do or to learn SQL clustering. Also it needs bit knowledge from out of SQL (Windows AD, Network and Storage (SAN)).
In this post, I will give you an idea how to configure SQL failover clustering to yourself. We are doing everything in virtual environment (Single machine) the same you can do physically but you need all in physically in production environment (Machine, Domain, Storage & network). It’s not possible to show all the steps and screen shot here because it has more than 40 screenshots. I have prepared a document and will share with you if needed.
What is SQL clustering?
In general cluster means more than one.
SQL clustering is a collection of two or more servers access the data from same single shared storage (SAN), if one server failed another one server will take the data from the storage and the application will be running without any problem.
I suggest readers to read Kendra Little’s article. It’s good article and she explains with diagram.
The followings are the software for setup clustering.
|VMware workstation||30 day trial||VMware workstation|
|Starwind||30 day trial||Starwind iscsi SAN|
|Windows server 2008||60 day trial||Windows server 2008|
|SQL server 2008||180 day trial||SQL server 2008|
Planning the IP address
|Component Name||IP address|
|Node1 / Machine1||xxx.xxx.1.7|
|Node2 / Machine2||xxx.xxx.1.8|
|Virtual SQL server||xxx.xxx.1.11|
Installing a VMware workstation is an easy one. Just click the typical and next.
Creating three virtual machines using the windows trail edition and assigning the right amount of RAM, CPU & disk Space for each guest host. Configure the network adapter for public and private network for internet connection.
- Domain controller (DC)
- Machine1 (Node1)
- Machine2 (Node2)
Creating Domain DC
The domain controller is also known as active directory.
- Install and create a domain.
Creating Shared disk
We know SQL clustering needs a shared disk typically SAN. The use of StarWind’s ISCSI we can make our local disk as shared disk. Because we don’t have a SAN storage server physically so i am creating the shared storage in the DC machine itself using StarWind’s iSCSI.
Format the disk on each guest and use it.
Creating and installing windows Failover cluster
- Installing the windows fail over cluster.
- Validate and add the nodes.
- Assign the IP and Cluster the Nodes
- Add the MSDTC
Installing SQL Failover cluster
Install the SQL server failover cluster on both the nodes. Its easy one just you can give the network IP address and choose the shared disk.
Second node will automatically display the SQL cluster name and we can just add it.
To view the Cluster admin go to run –> type CLUADMIN.MSC
Node1 (Muthu1) is the primary host. Which will be taking care of the SQL server and the database applications.
Use this command to check which node the cluster SQL server is currently running.
SELECT SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’)
How do I know, Is the cluster automatically fail over the server, if anything goes wrong on the running node?
Just test it. Power off the Node1 (Muthu1) and you can see the SQL server automatically fail over to the node2 (muthu2).
When the SQL cluster will be down? Or Is clustering is disaster recovery?
SQL fail over clustering is not a disaster recovery. In case if the storage (SAN) is not available then clustering won’t start.
Testing: Power off the DC. In our case DC machine has storage shared disk. Hence the shared disk (SAN) goes off, then SQL clustering will not work.
My special thanks go to my VM ware team.
Nice work muthu.
I am not a programmer but I have this SQL subject this session and have to prepare for it. What all topics should be covered in it?
And has anyone studied from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance…
would really appreciate help
Great muthu. very good explanation. thanks
I am glad i could help you
Can you send the doc that has all screenshots?
Good work,Can you please share the document which you prepared?
Nice sharing. Java training in chennai
Hi Muthukkumaran ,
Nice post,can you please share the document which you have prepared.
hi can you please send me the full document of screenshots you mention at the top?
can you please share the whole document on how to create the cluster. I want to use Hyper V instead of Vmware
Selenium Training in Chennai
Very great article keep sharing
Best Selenium Training in Chennai | Android Training in Chennai | Java Training in chennai | Webdesigning Training in Chennai
great article thanks for sharing
Best Selenium Training Center in chennai | Best Automation Testing Training in Chennai
Hey, very nice site. I came across this on Google, and I am stoked that I did. I will definitely be coming back here more often. Wish I could add to the conversation and bring a bit more to the table, but am just taking in as much info as I can at the moment. Thanks .
Superb. I really enjoyed very much with this article here. Really it is an amazing article I had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.thank you for sharing such a great blog with us. expecting for your.
Soft Skills Training in Chennai
Really superb..! I have many techniques about this topic and I am anxiously waiting for your new updates…