SQL Server Virtual Lab Section 3

Back to Main page
Back to Section 2

K. Install Fail-Over Clustering and Remote Admin Tools

1. Make sure both your Domain Controller and iSCSI Server are up.
2. Log into your VM with Windows 2012 Full Installation (SQL2012NodeA). The Server Manager should automatically start, if it doesn’t just click on its icon on the bottom left of the task bar.
3. On the Dashboard click “Add Roles and Features.” On the first two screens just click next.
4. On the Server selection make sure you have your server selected (at this point it should be the only one on the list) and click next. Do not select anything under Server Roles, just click next.
5. Under features select the following:
Fail-over Clustering

A box should appear teling you that other tools are required, press ok.
Check under the section Remote Server Administration Tools and make sure the following features are selected under the subcategories of this section:
>Failover Clustering Tools
>Role Administration Tools
> AD DS and AD LDS Tools
>AD DS Tools
>DNS Server Tools

Many of these will automatically be selected when you try to install these features, but just double check. You may be wondering why you should add the AD tools and DNS Server Tools. The reason is, you may want to manage your Domain Computers and Users via the GUI which is much easier.

L. Install iSCSI Initiator

1.  We will be adding iSCSI Initiator and configuring drives from this GUI not from PowerShell over on the Windows Core iSCSI Server. On the Dashboard of SQL2012NodeA select “Add other servers to manage.”
2. Make sure SQLLAB is selected as the location and click Find Now. Select your two remote servers (Domain Controller and iSCSI Server) and click OK.

Add Remote Servers to Manage

3. You should be back on at the Dashboard, click All Servers on the left. You should see your other two servers now available for you to manage. You may have to wait a few moments until “Online – Perf…” is listed under Manageability. Once that is ready, go back to the Dashboard by clicking the icon on the left side and click Add Roles and Features again.
4. Click Next twice and under Server Selection pick your iSCSI Server.
5. Under Server Roles, expand File and Storage Services, then expand File and iSCSI Services and select “iSCSI Target Server.” Do not let the grey half filled box trick you, there are features that need to be installed.
6. A Pop-up should appear saying that other features are required. Uncheck the box labeled “Include Management Tools” and click Add Features.
7. Click Next until you reach the last screen and click Install.

M. Installing iSCSI Virtual Drive

1. On your SQL2012NodeA Server, under Server Manager click Tools iSCSI Initiator. A Pop-up should appear asking you if you want to start the iSCSI services, click yes.
2. The iSCSI Initiator Properties should show up now. Click on the Discovery tab. Under Target Portals, your iSCSI Server should be listed, if not click on 
the button labeled “Discover Portal” and input the IP Address of your iSCSI Server. Click OK when done.
3. Go back to Server Manager, click on File and Storage Services (should be on the left side)
4. Click on the iSCSI option (last one on the left hand side of the new menu) then click on the link in the middle section “To Create an iSCSI Virtual disk…”
5. A Pop-up should appear, make sure you iSCSI Windows Core server is selected and that Drive C is also selected. Click Next.

iSCSI Setup1

6. On the next Screen Give your Drive a creative name (iSCIDrive1 or something). Click Next.
7. Give the Drive a Size (5GB should be sufficient for our test environment) and then click next again.
8. On the next screen you should only have one option which is to Create a new iSCSI Target. Click Next, give the Target a name, and click next again.
9. For Access Servers click Add. A pop-up should appear and it should have a window in the middle under the section labled “Select from the initiator cache on the target server. ” You will see some strange looking address that looks something like this
**Note your server name should be part of that address.**
Select it and click OK.
10. Click Next, leave the defaults under the next section (Authentication) and click Next. Finally click Create and wait for it to complete.
11. Under Server Manager click Tools iSCSI Initiator. On the first tab type the IP Address of your iSCSI Server and click Quick Connect. A Pop-up should appear with your target server, click Connect then Done.
12. Under Server Manager click the Menu Tools > Computer Manager.
13. Click on the Disk Management section on the left (under storage). You should see a new drive that is Offline, right click and select Online.

iSCSI Setup2

14. Right click again and select Initialize. When the pop-up appears, select GPT as the partition style and click OK.
15. Right click on the Black section of the disk that says Unallocated and click New Simple Volume.
16. Assign the Drive the maximum size possible, keep all the other defaults and give the volume a memorable name. Optionally, on the last step when naming your volume you may wish to change your Allocation Unit Size to 64K. This is the recommended size for drives with SQL Data since Extents are 64K in size and
SQL Server groups pages logically into extents (Uniform/Non-Uniform). It is not essential for this Lab environment but something to consider for real implementations.
17. You may repeat steps 3-10 and steps 12-16 again if you would like to add another drive. I added a second drive that was 1GB for my Cluster Quorum Drive. This time around you will go to the same location to create the virtual drive but the text in the middle of the box that says “To create an iSCSI Virtual disk…” will not be there. Just click on Tasks on the upper right hand side and select “New Virtual iSCSI Disk.” Also, do not create a new iSCSI target, use the existing one.

N. Setting up a SQL Cluster – Setting up the Windows Cluster and Shared Storage

1. Go to the Start menu and type “Cluster” and then select Failover Cluster Manager.
2. When the application starts, click on Create Cluster which is on the right hand side.
3. Click next on the welcome screen of the Wizard. Enter the name of your first node (SQL2012NodeA in my case) and click add. If you have another Node at this point that you created, you can add it as well. Click Next.


4. On the next screens, keep all the defaults and keep clicking Next. Your Cluster should validate everything successfully with a few warnings.
5. Once that is done, Click Finish and a new pop-up will emerge (Access Point Administration). Give your Cluster a Name and a unique IP Address then Click Next then Next. Some more validation will happen and then you will be prompted with a screen that says Finished. You’re done!


6. Just to make sure your Cluster is working, you can start a Command Prompt and Ping the Virtual name it should come back with the IP.
7. Now it is time to configure the shared storage. In the Cluster Manager, expand the nodes under your Cluster until you find the one that says disks (Screen shot should help). Then click on the Add Disk button on the right hand side. Make sure your disks are listed and then click OK.


8. Make sure you take a minute to inspect the disk. Check to see that the proper volume drives are on the bottom pane.
**The Following Instructions are for if you created a secondary stored disk for Quorum and would like to add Disk voting to your Quorum Model**
9. Click on your cluster name in Cluster Manager (SQLCluster) and on the right hand side in the Actions section click on More Actions. Select Configure Cluster Quorum Settings and press next on the first page of the Wizard.
10. On the next screen select Add or Change Quorum the Witness and click next. Make sure Configure a Witness Disk is selected and click next.
11. Select the Drive you want to setup as your Quorum Witness disk then click next until you are finished.

O. Setting up a SQL Cluster – Installing SQL Server as a Clustered Instance

1. Insert your Windows Server Installation DVD do this by selecting Media > DVD Drive > Insert Disk on your Hyper-V machine menu. You can select an .ISO
file or you can capture the Physical DVD drive if you have the media there.
2. When that is complete, start up a command prompt by going to the start menu and typing CMD then hit enter. Enter the following commands and hit enter.
Dism /online /enable-feature /featurename:NetFX3 /All /Source:D:\sources\SxS /LimitAccess
***NetFX3 is a requirement for the SQL Server installation. Another method to install this is by changing your Virtual Switch to internal and running a Windows Update to download and install this feature***
3. Insert your SQL Server Installation Media – do this by selecting Media > DVD Drive > Insert Disk on your Hyper-V machine menu. You can select an .ISO file or you can capture the Physical DVD drive if you have the media there.
4. Once the install starts up, you should be greeted by SQL Server Installation Center. Click on Installation on the left hand side.
5. Select New SQL Server Failover Cluster Installation. Click Ok when the Setup Support files pop-up appears.
6. On the next pop-up enter your product key or select evaluation. Accept the License terms, Skip the updates on the next screen, and once the Setup Files section has completed click Install. This may take some time.
7. Once all the support files have been installed select SQL Server Feature Installation and click Next.
8. Under Feature Selection pick at minimum Database Engine Services and the Management Tools. Feel free to select other services you would like to have such as SSAS, SSRS, SSIS .  Click next when done to proceed.
9. After the feature rules have been verified click next.
10. Give your SQL Server a network name (I chose SQL01) and leave Default Instance selected. Click Next.
11. Click Next until you get to Cluster Disk Selection. You should see your shared disk from your Windows Cluster listed. Make sure it is selected and click next.
12. Give your Cluster an IP Address. It has to be different than your Windows Cluster IP (you’ll notice at the bottom of my screen shot an error when I tried to give it the same IP Address). Check the box next to IPv4 then type the new IP under Address. Click Next.

SQL Cluster1

13. Now it is time to setup your service accounts. Since we have not added any AD Accounts, let’s use our Domain Admin account to start the SQL Services. This is the WORST thing you could do in your Production environment. I repeat, it’s not bad, it is the WORST thing you could do in Production – you could at this point use the Active Directory tools that you installed to add new Domain accounts and use them here. If you are installing Reporting Services, just leave the default value for Account.
14. Make sure all your services are set to Manual for Start Up type, except for SQL Browser.
15. On the next screen select Mixed Mode Authentication and give a password to your SA Account. Additionally, click Add Current user to make sure that your current user is an Admin on the SQL Server Instance.
16. Click on the Data Directories tab. By Default, the installation should have selected your Clustered Disk for the paths of all your files. In a production environment, you would have at minimum one shared drive for Transaction Log Files, one for Data, One for TempDB, and one for Backups. You may choose to make whatever arrangements you want on this screen since this is a Demo environment. Click Next.

SQL Cluster2

17. If you are installing SSAS or SSRS you should see additional setup screens. Make sure for SSAS you add the current user as an Admin and verify the data directories. For SSRS you can chose to install or install and configure. Again, these are additional features that you may wish to explore beyond this blog post.
18. Click Next until you are ready to install then set off the Installation and just wait patiently.
Congratulations you now have a full working SQL Server Cluster!

P. Add a Node to your SQL Server Cluster
1. Create a secondary machine using the steps from part E. Once your new VM loads start a command prompt and issue the following command to create a new SID.
2. After sysprep is done change your network settings (verify that it is on the same subnet as your other server) and add it to the domain. You may need to restart. Additionally, make sure you install the Failover Clustering Tools otherwise you will not be able to add this node to the cluster.
3. Once that is complete and your server has restarted go back to NodeA. Start up Cluster Manager, right click nodes and select Add Node. Click next and then enter the name of node (SQL2012NodeB in my case). Select your shared drives and just click next until all validation is done and the node is added.
4.On Node B, insert your windows disk and then run this in command line to install NetFX3:
Dism /online /enable-feature /featurename:NetFX3 /All /Source:D:\sources\SxS /LimitAccess
5. Once complete, insert your SQL Server Installation media and start SQL Server Installation Center by double clicking on the DVD Drive in My Computer.
6. Select Installation from the left hand side and then Select the option Add Node to a SQL Server Failover Cluster.
7. Once again, patiently wait through all the validation steps and make sure you skip updates.
8. When you get to the Cluster Node Configuration section make sure your setup looks similar to mine. Click next when you have verified.

Add SQL Node1

9. For the next section (Cluster Network Configuration) you should see the IP you setup your first Node with. Keep the default and click next.
10. You will need to provide the password for your Domain accounts on the next screen and then click next.
11. Just keep clicking next until you get the Summary screen called Ready to Add Node and click Install. Assuming everything runs smoothly, you’ve got yourself a secondary SQL Node in your cluster.
12. Next let’s attach those iSCSI disks to our new node. Under Server Manager, click Add Other Servers to Manage. In the pop-up do a search and add your AD and iSCSI Machines. When done and verified you should see new options on the left hand side.
13. Under Server Manager click Tools iSCSI Initiator (click Yes on the pop-up). Go to the Discovery Tab and make sure your server is there, if not then add it.
14. Back on the Server Manager Dashboard, select File and Storage Services then go to iSCSI. You should see your drive(s) listed from your WinCoreiSCSI Machine. If you scroll down a little bit you should see a section for iSCSI Targets. Your connection between NodeA and the iSCSI Server should be displayed. Right click on it and press Properties.
15. Click on Initiators on the left, and then click Add. Find the initiator for NodeB and click OK twice to commit the changes.

iSCSI on NodeB

16. Again back to the iSCSI Initiator (Server Manager > Tools > iSCSI Initiator). Under Discovered Targets you should see your iSCSI Target, click Connect. If you do not see it, then type in the IP and press Quick Connect. It should appear on that new Window, click Done.
17. Now back on Server Manager click Tools > Computer Management. Click on Disk Managment on the left side and now you should see your new disk(s). You cannot access them because they are reserved by NodeA at this point.
18. Now we should test failover. Open Failover Cluster Manager on either Node. Select the name of your Cluster (SQLCluster.SQLLAB.LOCAL). Right Click on it, select More Actions, then Move Core Cluster Resources, Select Node… Select NodeB and click OK.

Add SQL Node2

19. Select Roles, then Right Click on SQL Server (in the Middle pane). Then Select Move > Select Node… and Select Node B. Now all of your resources and services are on Node B. Optionally, you can chose to only move over SQL Server Roles if you have other Clustering roles running on the server.
**The first move between the Nodes will have moved only the Quroum Disk, the second move would have moved the SQL Data Disk and the SQL Service.**
20. You can open SQL Server Management Studio to verify that the instance is up by connecting to it’s virtual name. You can also ping it from a Command Prompt. It should return the IP of Node B when you do that. FYI, you can only connect using SSMS from the node that has SQL Services running on it.
21. Now if you want to have more fun you can download SQL Server 2012 Service Pack 1 and install it on your passive Node. Then Failover from Active –> Passive. Then you install Service Pack on the other node. This is good practice for some of the DBA work you may be required to do on the job.


One response to “SQL Server Virtual Lab Section 3

  1. dgn2200 fastweb mail italy

    August 15, 2017 at 5:15 PM

    Hmm is anyone else experiencing problems with the images on this blog loading?

    I’m trying to determine if its a problem on my end or if it’s
    the blog. Any feed-back would be greatly appreciated.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: