In part5 of this series I’ve shown you how to create a windows cluster. In this part of this Series, let’s proceed further installing and configuring Active-Passive configuration of your SQL Cluster. Before proceeding any further, let me tell you, I’ve created 2 domain accounts called “sqldbengine” and “sqlagent” in my Domain controller machine(in my case SANDC) with non-expiring passwords. I’ll be using these accounts as my service accounts for SQL installation. Also, I’ve created a Group for DBAops and created few dummy accounts and added to that group just for simulating a real environment in my lab.
Okay…Being said that, at this point we are all set to go ahead and create SQL Cluster, but we’ve to actually install “APPLICATION ROLE” on all the nodes which are participating in our Cluster. So…Go to your Serve manager in both the nodes and enable Application role as shown below.
Next->Next and choose Distribution transactions as well(this is needed if your SQL has to deal with Distributed Queries(MSDTC) using your Linked Servers) as shown below.
Next-> and Install. Go ahead and do the same on Node2 as well. This might take a while depending on your server capabilities and it may require server reboot. You should be seeing something similar to
Once you are Done with Installing application role. Now go to your Node1 and create MSDTC clustered service prior to SQL Server Installation. So, on NODE1 go to your failover cluster manager and follow the below screenshots sequentially.
Note: MSDTC clustered service has it’s own IP and service name & we already created a dedicated LUN M$ for MSDTC if you remember. It will validate and now it’s time to choose the LUN which we’ve already created. In my case it’s LUN M$. I’ve selected accordingly as shown below
you should see the final confirmation as shown below.
Click on Next and It’ll configure all the required Resources in our windows cluster. Please note, we haven’t yet came to actual SQL Server cluster installation. We are just preparing our Box for SQL Cluster Installation. If every thing went smooth, now you should see
You can add preferred Owners by just right clicking on winclustdtc in your left pane and going to properties. FYI I’ve chosen Node1 and Node2 in the same order as preferred owners.
Okay, now let’s start SQL Installation. I’ve inserted my SQL Server evaluation edition DVD into my DVD drive. You can also provide an ISO path in your VMWare Workstation if you have your installation media as an ISO file. Go to Node1 in your VMWare Workstation and
Once you click Ok, now you should be seeing that DVD being inserted in your Node1. Run Setup.exe and at this point you might get an annoying message saying that you need .Net frame work and latest version of Windows Installer(KB942288) even though you’ve already installed it. Just click Ok and it’ll install and asks for a system reboot. Just click OK. Once rebooted, launch Setup.exe again and now you should be welcomed with the SQL Installation Center. Select New SQL Server Failover Cluster Installation as shown below
It’ll do a very high level basic rule check and let you know if finds any potential issues which might prevent successful installation.
Click OK to proceed further. Choose your Edition and License key(if you’ve any). I’ve chosen Eval Enterprise Edition and accept the License terms and Click on “Install Setup Support Files”. This basically performs some windows installer actions,Normalize and validate Registry permissions and creates the required SQL Setup Bootstrap files under path “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap” by default. Once you click OK, it again does a detailed Rule check. You should not see any errors or Warnings in your real environment(here in my lab I actually got Network Binding Order Issue, we can safely ignore that here in lab). Click Next and choose
It’s upto you what to choose. In this Demo I’m selecting only bare minimum Database Engine and SSMS components considering my Server Configuration. Click Next and you will be asked for Instance Configuration. here I’ve chosen “sreeprod” as my SQL network name and i’ve selected named Instance(prod1) as shown below, which makes my work easy if i’ve any plans to make it an Active-Active cluster in future.
Click Next. It checks for Disk Space requirements. Click Next. Now, It’s time to select our SQL Resource Group name. You can Just click Next for defaults or you can choose your own fancy name if you’ve any standards for your naming conventions.
Click Next and now it’s time to configure our Disk Drives, In the below Screenshot, you can see Disks 1 and 2 are already reserved for Quorum and MSDTC, so you’ll have an option to select from remaining Disks. In this case, I’ve chosen all the available disks for me.
Click Next and now, it’s time to configure our Networks. Uncheck “DHCP” and provide a Static IP address for your SQL Network as shown below. I’ve chosen 192.168.1.118
Next and choose Service SID’s as shown below. In Windows Server 2003, we’ve to provide Domain Groups.
Click Next and Choose your Service Accounts. Remember I told that I’ve created two accounts prior to starting this installation. I’ll use those accounts as my service accounts as shown below.
Click Next and now it’s time to configure our DB engine. I’ve selected Mixed Mode Authentication and added my SQLDBA group as Administrators on this SQL Instance as shown below.
Now Don’t click Next, Instead go to Data directories tab for configuring your Data, Log and Backup Drives as shown below as per our LUNS created.
That’s pretty much it…..Just clik Next ->Next -> and Install as shown Below.
That’s it Guys!! This will take decent amount of time depending on your server configuration. Go and grab a cup of hot coffee(btw, don’t forget to keep your fingers crossed ;p). After 17 minutes, i’m welcomed with below screenshot:)
Hurrayyyyyyyyyyyyy..We did it!! See how simple it is…. Okay let’s go to our failover manager and also open SSMS and see how it looks like. See below Sreenshots!
Note: I’ve manually added Node2 to our Preferred owners List(Optional). Once, you add Node2 to our cluster, you will be seeing Node2 and Node1 as possible owners for your SQL-Sreeprod.
Let’s see how to add a node to our SQL Server Failover cluster and also I’ll show you how to patch your Clustered SQL Instance by applying SQL Server 2008 SP1. Stay tuned!