While teaching last week at our Tampa Immersion Event, I mentioned the support for SMB as a storage in SQL Server 2012, which is documented in the following Books Online topics.

Install SQL Server with SMB fileshare as a storage option
Hardware and Software Requirements for Installing SQL Server 2012 : Storage Types for Data Files

Someone asked the question about whether SMB could be used for the shared storage for a failover cluster in SQL Server 2012, and I had to go do a little research, but I found that it was a supported configuration.

AlwaysOn Failover Cluster Instances (SQL Server)
Before Installing Failover Clustering

Last year, Kevin Farlee from the Storage Engine Team blogged about the changes in the SMB stack that make hosting SQL Server databases over SMB 2.2 a feasible prospect in his blog post SQL Databases on File Shares - It's time to reconsider the scenario.  Additionally the SQLCAT team, actually Kevin Cox specifically, blogged about the use of SMB for hosting database files in the Top 10 Hidden Gems in SQL Server 2008R2 post.  Now that this is a viable alternative to expensive SAN’s it is not surprising to see that it is also a viable storage option for SQL Server Failover Clustering in SQL Server 2012. 

(Note: SAN infrastructures provide a lot of additional benefits that a single server hosting a file share doesn’t, so before you run out and place a mission critical SQL Server database on a SMB file share, make sure that you understand the implications of doing so, and the trade offs that you are making in the process.)

Building the Failover Cluster in Windows

Building the Failover Cluster in Windows is essentially the same as building a standard Failover Cluster for SQL Server, with the exception that you have to skip the Disk checks in the Cluster Validation Tool, and then override the failed checks to actually form the cluster.  To do this, select the option to Run only tests I select on the Validate a Configuration Wizard.

Then collapse all of the parent nodes in the treeview and uncheck the Storage checks and run the cluster validation.

When the validation tests complete, review the report and make sure that you have a supported configuration for failover clustering.

Then click the No option on the Validation Warning page and create the cluster by providing a Cluster Name and IP Address.

Configuring the File Share for Failover Clustering Support

The first step in setting up a Failover Cluster instance for SQL Server that uses SMB for its shared storage is to configure the File Share server to support the clustered instance being able to connect to it with the appropriate permissions to manage the SQL Server databases.  Since there is no shared storage in the environment, there is an additional requirement, if you are using a 2 node Failover Cluster, that the File Share will need to be the witness in the quorum configuration using Node and File Share Majority.  The requirements for this are documented in the Exchange Books Online (I tried to find a SQL Server Books Online or Windows Server Failover Clustering Books Online entry that provided the correct details and couldn’t) http://technet.microsoft.com/en-us/library/bb676490(EXCHG.80).aspx.  Essentially, you need to have a separate File Share for the quorum, and a separate File Share for the SQL Server instance to use for data storage. 

Note: I didn’t initially set my environment up this way, and due to the time it took to create the screenshots for this blog post before filling in the text, I chose to not go back and correct this error, but as a best practice, you should have separate shares for the quorum and SQL Server instance installation if you choose to use SMB for a failover cluster.  This is documented in the Windows Server Failover Clustering Books Online. http://technet.microsoft.com/en-us/library/cc770620(WS.10).aspx

The first thing you want to do is Provision a new Share using the Provision Share context menu item from the Share and Storage Management node on the File Server.

Then you need to provide a path for the storage of the share.  If you are actually setting this up, perform all these steps twice to create two separate shares with the necessary permissions.

Then you want to manually edit the NTFS Permissions for the File Share being created.

For the Quorum Share, you need to provide the Failover Cluster Virtual Computer Object (VCO) account Full Control of the Share in NTFS.  The VCO is the ClusterName followed by a $ in Active Directory, and you will need to click the Objects button to add in the Computers object for the search to find the account.  For the installation share, the SQL Server Service Account will require Full Control permissions in NTFS.

The next step is to provide a name for the File Share(s) that you are creating which will be the path to the share for configuring your quorum and then your Failover Cluster installation.

I am skipping over the SMB Settings window and going to the SMB Permissions where you will need to customize the share permissions for the specific Users that need to access the share to minimize security issues in the environment.

Just like with NTFS, for the Quorum Share, you need to provide the Failover Cluster Virtual Computer Object (VCO) account Full Control of the Share.  For the installation share, the SQL Server Service Account will require Full Control permissions over the Share.

Then create the share and you are ready to configure the quorum settings for the failover cluster.

Configuring Quorum Settings

To configure the Node and File Share Majority quorum for the cluster, required only if you have an even number of voters in the configuration which is typical for 2 node clusters, click the Configure Cluster Quorum Settings menu item from the failover clusters context menu.

Pick the Node and File Share Majority radio button.

Then Browse for the shared folder and type in the name of the File Server that is hosting the SMB share for the quorum.

If all the permissions are configured correctly, you will have a successful configuration of the quorum in the environment.

Installing the Failover Cluster Nodes

For the interests of brevity, I am going to skip showing all of the standard Failover Cluster installation setup screens and only show the ones that matter for the configuration using SMB as the shared storage.  For the instance features in this blog post, the Database Engine, Client Connectivity, Client Connectivity Backwards Compatibility, and Management Tools have been selected for a minimum install in the environment.   Everything is exactly the same as it would be in a standard SQL Server Failover Clustered instance installation until you get to the Cluster Disk Selection page of the installation.  Here there will be nothing shown in the environment, and what is interesting in comparison to SQL Server 2008R2 and previous is that you still have the option to click Next.

The next page of interest is the the Database Engine Configuration page, where you will provide the UNC path to the SMB share for the Data root directory, and if you have multiple shares that target different physical disk arrays in the File Server for the instance you could also specify those UNC paths to achieve physical isolation of the I/O for logs, data files, tempdb and backups following best practices.

Notice the warnings about the file server being specified as the data directory.  If you double click on one of the warnings it will produce a dialog box similar to the following box.

If you don’t get the warnings, don’t worry, you will still get the above warning dialog box as soon as you click Next on the page.

After this last warning Setup will proceed as normal only it will install into the File Share everything that would have been on the shared disk in a standard configuration with a SAN.  Once the first node is setup, you can then proceed to run setup on the additional nodes and they will work just like a standard failover cluster configuration would against a SAN.

Summary

Once all of the setup completes, you can validate the location of the database files using the sys.master_files DMV in SQL Server.

The SMB storage option lets you get past the 25 instance limitation for SQL Server when using shared disks with drive letters assigned, since each instance requires a separate drive letter.  Using SMB up to 50 instances can be installed in a failover cluster, which provides a significant increase in the number of instances you can get over using a SAN.  The improvements in the SMB stack make this a much more viable solution to expensive shared storage implementations using SAN hardware, but as I pointed out earlier, there are still considerations that should be made about the level of redundancy and other benefits that are provided by SAN implementations before you determine that a SMB solution for shared storage in a failover cluster is appropriate for your specific environment.

I recently did some drive upgrades in my personal laptops and as a result I had a couple of SSD drives sitting around that I wanted to make use of.  A few weeks ago I purchased a new OCZ Agility 3 240GB SATA III SSD from Newegg when it was on sale and at the same time I also purchased two MassCool USB 3 external enclosures to make use of the two 120GB OCZ Vertex 2 SSD’s that I would have sitting around.  The cost for the MassCool enclosures was only $14.99 so I figured it would be a good way to reuse the SSD’s at the time.  When the package from Newegg arrived, I immediately installed the SSD into one of my personal machines and then later I went about testing the performance of the USB 3 external enclosures with the older SSD’s.  Initially the performance was impressive, and I posted a few tweets that attracted some attention and a few people requested that I blog my results.

To make this blog post something worth looking at, I ran a number of additional tests over the last few days using SQLIO and a varying configuration of drives with external enclosures.  Specifically I tested two different USB 3 2.5” SATA enclosures, a separate eSATA 2.5” SATA external enclosure, as well as two USB 3 HDD’s that I already owned to get some performance information from each of the setups.

The external USB 3.0 HDD’s being tested are as follows:

• Toshiba CANVIO 3.0 500 GB,External,5400 RPM (E05A050CAU3XW)
• Western Digital My Passport Essential 500GB USB 3

The external enclosures being tested in these tests are as follows:

• MassCool (UHB-2233) USB 3
• Hornettek (Panther) USB 3
• Vantec NexStar CX (NST-200SU-BK) eSATA

The SSD’s used with the external enclosures listed above are:

• OCZ Vertex 2 120GB SATA II
• OCZ Agility 3 240GB SATA III

At the time that I bought the 120GB drive I paid close to $2.42 per megabyte for it, and at the time this was a good deal.  When I purchased the 240GB SSD I paid right around $1.08 per megabyte, showing how much the prices have decreased nearly 3 years later.  I happen to own a number of OCZ drives at home and they are one of my favorite brands personally because of the overall reliability I’ve had with them the last three years as well as the performance to cost ratio I’ve experienced. Initially I was just testing the MassCool USB 3 enclosures that I purchased, and I was incredibly happy with the performance that I had from them.  However, after tweeting about the results, my friend Jose Chinchilla (Blog | Twitter) mentioned that I should also try out eSATA because it performed significantly better in his own tests.  Based on this tweet, I set out to my local MicroCenter computer store to purchase a eSATA enclosure and while I was buying it, one of the sales representatives, a guy named Chris, approached me and asked what I was planning to do with the enclosure because USB 3.0 should be faster for SSD’s. The numbers he quoted didn’t match my previous testing, so he made a recommendation for a specific USB 3.0 enclosure, and I figured, for $20 it was worth testing, so I bought an extra Hornettek Panther USB 3.0 device for comparison testing along with the Vantec NexStar CX eSATA enclosure I had already selected.

For the tests, I ran a short set of tests using SQLIO that I previously blogged about on my blog post about the Powershell parser for SQLIO output.  The results from the tests are below:

The two external USB 3.0 HDD drives had very similar performance characteristics, and they beat their USB 2.0 counterparts performance wise significantly.  I’ve been incredibly happy with my USB 3.0 HDDs overall for the last few years and I only purchase USB 3.0 HDD drives based on my performance tests a few years ago.  However, when compared with the USB 3.0 external enclosures with the SSD’s, the performance difference is quite significant.  One of the problems I’ve had lately is being able to fit all of the virtual machine hard drives, VHD’s for Hyper-V when using my dual boot Hyper-V host VHD, as well as my original VMware Workstation VMDK’s for classes, and even the VirtualBox virtual disk images (VDI) from my blog series on building a completely free playground.  Using the SSD’s with USB 3.0 definitely makes storing my virtual hard disk files on an external array much more feasible performance wise, and my only real limitation from testing is the size of the external device. 

However, the performance of the eSATA external enclosure is incredibly better, with the side trade off that I can only have 1 of them attached to my laptops at a time, and for my personal laptop, the fact that the eSATA enclosure requires not only the eSATA port for throughput, but also the additional USB 2.0 port for power really limits what I can do as far as having multiple disks connected to the laptop.  To be honest, this is something that I can live with given that I also have swapped out my CD/DVD ROM bay for a replacement New Mode US second HDD conversion. This means that I can have two 240GB OCZ SSD’s in my E5420, which is a similar configuration to the dual 256GB Dell SSD configuration in my Precision M6500 for work, while being able to move VM images between the two machines using my older 120GB SSD’s with fantastic performance.

If you are looking at really high performance external hard disk configurations for scalability, I would highly recommend looking at either eSATA with an extra SSD drive or if you need multiple devices and you have USB 3.0, consider going that route.  Either will outperform your existing options significantly.  For the time being, I am going to stick with one eSATA device, which is compatible with both of my laptops, and one USB 3.0 device, which only works with my M6500 at USB 3.0 speeds.  My E5420 only has USB 2.0 onboard, so it makes more sense for me to stick with eSATA for the main shared drive.

Over time, I’ve had a number of reasons to need to run a random workload against SQL Server to be able to demonstrate troubleshooting, how SQLOS works, and most recently how to capture a Replay Trace for my series on the Distributed Replay Utility in SQL Server 2012.  For a while I’ve maintained a large workload script that I would run using multiple sqlcmd command line windows to fire off the workload, but one of the problems with this has been that it was incredibly predictable, and it didn’t scale the way I really wanted it to.

When I was working with Distributed Replay, this became somewhat problematic with generating a randomized workload to capture a Replay Trace off of, so I took a few hours and went back to the drawing board with my idea.  What I came up with was a large script file that contains all of the SELECT statement examples from the SQL Server Books Online (http://msdn.microsoft.com/en-us/library/ms187731.aspx).  This script is divided into separate sections using a delimiter, and then I wrote a PowerShell script that reads the file and breaks it down into individual scripts that are randomly executed against the configured SQL Server using SMO.

The two files required to make use of this are attached to this blog post and can be used with minimal modifications against any SQL Server 2008+ system that has the AdventureWorks2008R2 database attached to it.  To make use of the PowerShell script, you will either have to sign it, or if you work like I do in my VMs, allow unsigned script execution with Set-ExecutionPolicy Unrestricted.

The PowerShell script is incredibly simple code wise.  It loads the SMO assembly, splits the file contents on the delimiter, then inside a infinite loop, it picks a random query and executes it against the SQL Server.

# Load the SMO assembly
[void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");

# Set the server to run the workload against
$ServerName = "SQL2012-DB1";

# Split the input on the delimeter
$Queries = Get-Content -Delimiter "------" -Path "AdventureWorks BOL Workload.sql"

WHILE(1 -eq 1)
{
    # Pick a Random Query from the input object
    $Query = Get-Random -InputObject $Queries;

    #Get a server object which corresponds to the default instance
    $srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server $ServerName

    # Use the AdventureWorks2008R2 database
    $srv.ConnectionContext.set_DatabaseName("AdventureWorks2008R2")

    # Execute the query with ExecuteNonQuery
    $srv.ConnectionContext.ExecuteNonQuery($Query);

    # Disconnect from the server
    $srv.ConnectionContext.Disconnect();
   
    # Sleep for 100 miliseconds between loops
    Start-Sleep -Milliseconds 100
}

To generate random workloads, I generally fire up 3-5 copies of this script on a client concurrently and leave it running in the background to generate the load. 

AdventureWorks BOL Workload.zip (6.35 kb)

Run the Cluster Validation Report

At this point, I have to admit that as a part of trying to streamline the configuration of this environment, I made a mistake in the first post when I added the Failover Clustering feature to the template VM that was cloned to create the two cluster node VMs.  To proceed with setting up a supported cluster configuration, the Failover Clustering feature will have to be removed from each of the cluster nodes, the nodes will have to be restarted, and then the feature will have to have the Failover Clustering feature added back to them in order for the Cluster Validation Report to succeed.  If you don’t perform this step, the Network configuration and Active Directory configuration of the servers will fail in the Cluster Validation Report.

UPDATED 09/20/2011 

NOTE FROM EMAILS: You may have to disconnect the NAT adapters, or "unplug the cable from them" in VirtualBox to pass the Cluster Validation Report.  For some reason it seems like some people are hitting duplicate IP Address issues with the DHCP applied IP Address from VirtualBox for the NAT connection.  If you get a failed CVR, disconnect the NAT adapters, run the CVR Tests again and it should pass.

After removing the Failover Clustering feature, rebooting, and then adding the Failover Clustering feature back to both of the cluster nodes, the cluster nodes will be ready to build the failover cluster.  The first step is to run the Cluster Validation Report to ensure that the current configuration is supported for building a Windows Server Failover Cluster.  To do this, open the Failover Cluster Manager from one of the two VMs from Start | Administrative Tools | Failover Cluster Manager. When the Failover Cluster Manager opens click on the Validate a Configuration link in the right hand corner of the Actions pane, or under the Management pane.

When the Validate Configuration Wizard opens, click Next and then add both of the cluster nodes to the wizard on the Select Servers or a Cluster screen.

Click Next to and then leave the option to Run all tests (Recommended) selected and click Next, and Next again to begin running the tests.

When the validation tests complete, as long as the Summary screen doesn’t show a Failure, the cluster can be created based on the configuration of the nodes.  The validation report summary should show that a warning icon, and to see the specific tests that resulted in the validation warnings, you can click on the View Report button on the Summary screen.  Otherwise, click on the Finish button to close the Cluster Validation Wizard.

Creating the Cluster

To build the cluster, click on the Create a Cluster link in the Failover Cluster Manager, and then click Next to get to the Select Servers or a Cluster screen, then add both of the cluster nodes to the wizard and click Next.  On the Access Point for Administering the Cluster screen, type SQL2K8R2CLSTR in the Cluster Name box, and then uncheck the iSCSI networks, 192.168.31.0/24 and 192.168.32.0/24, from the Network list, and then add the IP address 192.168.81.5 as the cluster network IP address to the 192.168.81.0/24 network, then click Next.

On the Confirmation screen click Next, and the Windows Server Failover Cluster will be built.

On the Summary screen click Finish to close the Create Cluster Wizard.

Validating the Quorum Configuration

When the cluster is formed, the default configuration should be to use Node and Disk Majority based on the number of cluster nodes being used, in this case 2 cluster nodes, and the disk witness configuration should be for the smallest shared disk, in this case Drive Q to be configured as the disk witness.  However, this configuration should be validated before the cluster is used to install SQL Server Failover Clustering in the environment.  To validate the Quorum settings, in Failover Cluster Manager, right-click on the cluster and expand More Actions and click on the Configure Cluster Quorum Settings menu option, or click on More Actions in the Actions pane and select the Configure Cluster Quorum Settings option to setup the Quorum Configuration for the Windows Server Failover Cluster.

In the Configure Cluster Quorum Wizard, click Next, and then validate that the option for Node and Disk Majority (recommended for your current number of nodes), and then click Next.

On the Configure Storage Witness screen, ensure that the Witness Disk is set to the 1GB shared disk on Drive Q.

If this matches the environment, click Cancel to close the dialog.  If it doesn’t change the Witness Disk to the 1GB disk on Drive Q and then click Next, then click Next again, and finally Finish to complete the reconfiguration of the Quorum settings.

Installing SQL Server Failover Clustering

Unfortunately as a part of building this blog series, I have also been using VirtualBox for the first time because it was free, and I wanted to focus this blog series on using things that are free.  However, when I went to build the failover cluster for SQL Server I quickly learned that not everything is equal in VirtualBox that I have always assumed would be based on years of working with SQL Server Failover Cluster configurations in VMware Workstation.  There happens to be a bug associated with the installation of the VirtualBox Guest Tools that prevents you from setting up a failover cluster when the tools are installed. If you attempt to install SQL as a failover cluster a series of errors like the following will occur with the primary failure being the following error:

The given network name is unusable because there was a failure trying to determine if the network name is valid for use by the clustered SQL instance due to the following error: 'The network address is invalid.'

To work around this, the VirtualBox tools have to be removed from the cluster node VMs, but before we do this, we need to copy the installation media into the VMs using a Shared Folder from the host.  If you recall from the first post in this series, I recommended that you download SQL Server 2008 R2 Evaluation Edition, and once you have downloaded the SQLFULL_x64_ENU.exe file, it will need to be extracted to a folder on the host operating system which we will then enable as a shared folder in the two guests VMs that we are setting up as our cluster nodes.  In this case I chose to create a folder named SQL2K8R2_Setup under my Downloads folder where the file was downloaded.

Once the files have been extracted, logon to the SQL2K8R2CLSTR1 VM and then open the VM settings from Machine | Settings menu item. 

Click on the Shared Folders item on the left hand side and then click on the Add button on the far right to add a shared folder to the VM.  Then browse to the path of the local folder or type in the path in the Folder Path box and provide a Folder Name, then click the Read-only checkbox and then click OK and OK again to return the VM.

Inside the VM click Start | Computer to open the Explorer window for the local computer, then click on the Network icon on the left sidebar and then double-click on the VBOXSVR computer object to access the SQL2K8R2_Setup shared folder.  Now create a new folder name SQL2L8R2_Setup on the desktop of both of the cluster nodes and copy the contents of the shared folder into the Desktop folder.  Repeat these steps for both of the Cluster nodes.  Once this is complete, uninstall the VirtualBox tools from Start | Control Panel | Programs | Uninstall a Program and then reboot both of the nodes to continue.

For the purposes of this blog series, which was originally only planned to be three posts long but has been extended into infinity based on the number of tasks that could be accomplished in the configuration of an environment for SQL Server and learning inside of that environment, we are going to install a non-slipstream RTM install of SQL Server 2008 R2 into the test environment as a failover cluster.  The reason for doing this is that it will allow a future post to talk about rolling updates of the cluster nodes and how to best accomplish patching in a clustered environment with minimal downtime.  Open the SQL2K8R2_Setup folder on the Desktop that we previously created, then double-click on the setup application in the folder to begin SQL Server Setup on the cluster node.

When the Open File – Security Warning dialog opens click on Run to begin Setup for SQL Server.

When the SQL Server Installation Center opens up, click on the Installation link on the left hand side and then click on the New SQL Server failover cluster installation link on the right hand side to launch the failover cluster setup.  If another Open File – Security Warning dialog opens click Run again.

On the Setup Support Rules screen click OK.  Then on the Product Key screen, leave the option set to Specify a free edition, and the dropdown set to Evaluation and then click Next.

On the License Terms screen click the checkbox for I accept the license terms. and then click Next.  Then click Install on the Setup Support Files screen.

When the Setup Support Rules checks complete, review the list of items checked and as long as there are only Warnings, click Next.  We will handle most of these warnings in later blog posts in this series, for example configuring MSDTC as a cluster resource, and reviewing the Network Binding order on the servers.

On the Features Selection screen click the Select All button and then uncheck the checkboxes for Analysis Services and Reporting Services, then click Next.

On the Instance Configuration screen type SQL2K8R2FC in the SQL Server Network Name text box, and then click Next.

Click Next on the Disk Space Requirements screen, then click Next on the Cluster Resource Group screen.  On the Cluster Disk Selection screen ensure the the checkbox next to Cluster Disk 1 is checked and then click Next.

On the Cluster Network Configuration screen, uncheck the DHCP checkbox and then type 192.168.81.6 in the Address box and then click Next.

For the Cluster Security Policy screen leave the option to Use service SIDs (recommended) selected and then click Next.

Switch to the Domain Controller VM and then logon and open the Active Directory Users and Computers snapin from Start | Administrative Tools | Active Directory Users and Computers.  Expand SQLskills.demos and then right-click on Users and create a new user from New | User.  Type SQL Server Services into the Full name textbox, and then type SQLServiceAcct in the User logon name textbox. Name the user SQLServiceAcct

Type pass@word1 in both the Password and Confirm password textboxes on the New Object – User screen, and then uncheck the User must change password at next logon checkbox, and check the User cannot change password and Password never expires checkboxes.  Click Finish on the subsequent screen to complete creating the new user.

Switch back to the SQL2K8R2CLSTR1 VM and type the SQLskills\SQLServiceAcct in the Account Name for the SQL Server Agent and SQL Server Database Engine services.  Then type in pass@word1 for the Password for both of the services and click next.

On the Database Engine Configuration screen, click the radio button for Mixed Mode (SQL Server authentication and Windows authentication) and then type pass@word1 into the Enter password and Confirm password textboxes.  Then click on the Add Current User button, and click Next.

Check the checkbox on the Error Reporting screen and click Next.  Click Next on the Cluster Installation Rules screen, and then click Install on the Ready to Install screen to begin the failover cluster installation.

Once the failover cluster setup on SQL2K8R2CLSTR1 completes reinstall the VirtualBox tools on the VM and let it reboot.  Then logon to the SQL2K8R2CLSTR2 VM and then open the SQL2K8R2_Setup folder on the Desktop that we previously created, then double-click on the setup application in the folder to begin SQL Server Setup on the cluster node.  When the Open File – Security Warning dialog opens click on Run to begin Setup for SQL Server.

When the SQL Server Installation Center opens up, click on the Installation link on the left hand side and then click on the Add node to a SQL Server failover cluster link on the right hand side to launch the failover cluster setup. Complete the initial setup steps by selecting Evaluation edition, accepting the License Agreement and installing the SQL Support Files.  Click Next on the Setup Support Rules screen and then on the Cluster Node Configuration screen ensure the the MSSQLSERVER instance is selected in the SQL Server instance name dropdown box, then click Next.

Type the password for the Service Accounts in the Password box of the Service Accounts screen and then click Next.

Check the checkbox on the Error Reporting screen and click Next.  Click Next on the Cluster Installation Rules screen, and then click Install on the Ready to Install screen to begin the failover cluster installation.

When the installation completes, you will have a fully functional SQL Server 2008 R2 failover cluster setup in your free test environment.

Continuing our weekly series on building a free playground for SQL Server, this week we’ll take a look at configuring our iSCSI Target to create the LUNs necessary to build a SQL Server Failover Cluster, and then build out a two node SQL Server Failover Cluster in the environment.  A number of really good comments have been made by readers on the last two blog posts in this series and I want to call out of of them at the beginning of this blog post because it is fairly important to the environment and a step I skipped covering as a part of the blog posts. 

The VM Tools or Guest Additions should be added to each of the VMs that are configured in the playground environment.  This can be accomplished in VirtualBox from the Devices menu with the Install Guess Additions… menu item.

The Guest Additions provide seamless integration between the host environment and the VM guest for things like the clipboard, USB device support in the VM, and mouse integration.

The second item that I wanted to cover is that the iSCSI Target being used in these blog posts requires hardware virtualization to be able to create the 64-bit VM for Windows Server 2008 R2 that is required to use the Microsoft iSCSI Target.  Most modern laptops support hardware virtualization in the CPU, but if you are using an older laptop or desktop that doesn’t have hardware virtualization extensions in the CPU you won’t be able to use the same iSCSI Target that I am using in this blog series.  There is another free alternative available from Starwind Software, a third party software vendor that offers the Starwind Free Edition - iSCSI SAN with Deduplication, for download.  You can use this iSCSI target in lieu of the Microsoft iSCSI Target if you don’t have hardware virtualization support on the computer that you are using to build your playground environment. 

A few people have asked me why I chose to use the Microsoft iSCSI Target over Openfiler, Starwind’s free iSCSI SAN, or other third party products that can be used to build an iSCSI environment, and the reason for the decision to go with the Microsoft iSCSI Target is that it supports all of the features required for building a Windows Server 2008/2008R2 failover cluster for free, and it doesn’t require any kind of registration to download.  I’ve used the Starwind iSCSI SAN software in the past when I was first learning how to use failover clustering in Windows Server 2008 and SQL Server 2008, and it provides all of the functionality required, but you have to register on their site to download it.  I have also used Openfiler in the past to build a Windows Server 2003 failover cluster for SQL Server 2005, but OpenFiler does not support SCSI-3 Persistent Reservations, at least as recently as the last time that I looked at it, which are required for building a Windows Server 2008/2008R2 failover cluster, so it wasn’t a viable option for building out a playground environment for SQL Server clustering.

Creating the cloned VMs for the Failover Cluster

The first step in the process of creating a SQL Server Failover Cluster in our playground is to create two new VMs by cloning our template VM.  To do this, use the same steps described in part 2 of this series, making sure that the you check the box to Reinitialize the MAC address of all network cards as a part of the cloning process.  I am going to create two linked clones named SQL2K8R2CLSTR1 and SQL2K8R2CLSTR2 in my environment.  Once the clones have been created, power them on, and follow the same steps used to configure the Domain network of the iSCSI Virtual SAN and join the VMs into the AD Domain.  For this configuration, SQL2K8R2CLSTR1 will be assigned IP address 192.168.81.3 and SQL2K8R2CLSTR2 will be assigned 192.168.81.4 on the Domain network.

Once the VM clones are created, renamed, and joined into the domain using the same steps used to join the iSCSI Virtual SAN to the domain in part 2 of the series the iSCSI network adapters can be setup to begin the process of provisioning shared storage for building out the SQL Server failover cluster.

Configuring the iSCSI Network Adapters

To simulate an environment with segregated traffic and multiple paths to the iSCSI SAN, we can change the configuration of the vNICs on the iSCSI Virtual SAN, and both cluster nodes to mimic separate hardware networks using the VLANs.  In VirtualBox, this is really easy to do, but simply changing the name of the internal network that Adapter 3 and 4 are connected to.  In this case I am going to change the internal network name from iSCSI Network to iSCSI Network 1 for Adapter 3 and iSCSI Network 2 for Adapter 4 as shown in the below screenshot. 

This change will be made to all three servers, and then we can configure the network settings for each so that iSCSI Network 1 uses IP subnet 192.168.31.x and iSCSI Network 2 uses IP subnet 192.168.32.x.

iSCSI Virtual SAN

SQL2K8R2CLSTR1

SQL2K8R2CLSTR2

Even though we have laid the foundation for having multiple paths to the iSCSI Target from both of our cluster nodes, for the purposes of the this weeks blog post, we are only going to make use of a single path to the iSCSI SAN, reserving the concept and discussion of multiple paths for next weeks blog post.

Configuring the iSCSI Target

Configuring the iSCSI Target in Windows Server 2008 R2 is incredibly simple and can be accomplished in a few minutes.  In fact, you will probably spend more time reading the steps to configure the Target and create the necessary Virtual Disks to support a two node failover cluster, than you will spend actually performing the steps.  To begin configuring the iSCSI Target open the Microsoft iSCSI Software Target management console from Administrative Tools on the iSCSI Virtual SAN VM.  Then create a new iSCSI Target by right clicking on the iSCSI Targets node and clicking on the Create iSCSI Target menu item.

When the Create iSCSI Target Wizard opens click next and then provide a iSCSI target name and Description for the new target.  In this example, I am naming the iSCSI Target SQL2K8R2CLSTR and using a description of SQL Server 2008 R2 Failover Cluster Shared Disks

On the iSCSI Initiators Identifiers screen, we are going to click the Advanced button and specify the IP addresses of the iSCSI network adapters we previously configured for the iSCSI Network 1 adapters.

After clicking the Add button on the Advanced Identifiers screen, change the Identifier Type dropdown to IP Address and then type the IP address for the SQL2K8R2CLSTR1 iSCSI Network 1 connection, 192.168.31.11 in the Value box.

Repeat these steps to configure the IP Address of the SQL2K8R2CLSTR2 iSCSI Network 1 connection, by typing 192.168.31.21 in the Value box.  When the Microsoft iSCSI Software Target warning dialog pops up, click Yes to accept the configuration of multiple initiators for the target.

Then click OK to close the Advanced Initiators dialog, then click Next on the iSCSI Initiator Identifiers window, and then click Finish to configure the target.

Next, create a new virtual disk by right clicking on the Devices node and clicking on the Create Virtual Disk menu item. 

When the Create Virtual Disk Wizard pops up click Next and then specify a file location and name in the File box to store the VHD file for the new virtual disk that will be used as the iSCSI LUN for the failover cluster.

On the Size screen, specify a size for the new virtual hard disk, in megabytes, in this example I am creating a 5GB virtual hard disk which is sufficient enough for building a failover cluster for learning.  Click Next to move to the Description screen where you can provide a detailed description for the Virtual Hard Disk, and then click Next.

On the Access screen click the Add button to open the Add Target dialog, and assign the Virtual Hard Disk to the SQL2K8R2CLSTR iSCSI Target that we previously created, and then click OK.

Click Next on the Add Target dialog and then click Finish to complete adding the Virtual Hard Disk to the iSCSI Target.  Now repeat the steps to create another Virtual Hard Disk that is 1GB in size using the same iSCSI Target for the Failover Cluster Quorum Disk.

Configuring the iSCSI Initiators

Now that we have out iSCSI Virtual SAN configured with a clustered shared disk and quorum disk, we need to setup the iSCSI Initiator on both of the failover cluster node VMs so that they can access the disks being presented by the iSCSI Target.  The first step in configuring the iSCSI Initiator was to login to the SQL2K8R2CLSTR1 VM and then open the iSCSI Initiator from Start | Administrative Tools | iSCSI Initiator.  The first time that you open the iSCSI Initiator a popup dialog will open stating that the iSCSI Initiator Service is not running on the server.  Click Yes on the dialog to start the service and configure it to start automatically each time the server starts.

Once the iSCSI Initiator Properties dialog opens, type the network address to the iSCSI Network 1 connection for the iSCSI Virtual SAN, 192.168.31.1,  in the Target box, and then click Quick Connect.  The Quick Connect dialog will open up and you can click Done to connect to the iSCSI Target. 

Repeat these same steps on the SQL2K8R2CLSTR1 VM to connect the iSCSI Initiator to the iSCSI Target on the second cluster node.  Then open the Computer Management MMC from Start | Administrative Tools | Computer Management, and then expand the Storage node and click on Disk Management.  Right click on each of the iSCSI disks presented to the server and click on Online Disk to bring the disks online on the server.

Then right-click on one of the disks and click on the Initialize Disk menu option to initialize the disks.

Then right-click each of the disks and select the option to create a New Simple Volume, and format the disks.  Assign drive letter S to the Shared Disk that is 5GB in size and format the disk using a 64K allocation unit size, naming the disk SQLData.  Assign drive letter Q to the Quorum Disk that is 1GB in size, and format the disk using the default allocation unit size and name the disk Quorum Disk.

At this point the VMs are ready to begin the failover clustering build out in Windows which will be covered in the next post in this series.

To continue the build out of our Completely FREE Playground for SQL Server, we will begin by setting up an Active Directory Domain Controller and a separate iSCSI Virtual SAN, which are both requirements for setting up a SQL Server Failover Cluster.  To make things easier to follow, this post will break these two tasks up into separate sections, with each section focused on the specifics of the setup involved with each of the servers.

Active Directory Domain Controller

To begin the setup of the Active Directory Domain Controller, the first step is to create a Clone of the “base” VM that was created in the previous blog post.  To do this, right click on the VM and select the Clone option from the context menu.

To create the Clone, provide a common name to the VM and specify the option to “Reinitialize the MAC address of all network cards” on the first screen of the “Clone a virtual machine” wizard in VirtualBox.

When creating a clone of an existing VM, there are two options available; “Full Clone” which copies the existing virtual hard disk file entirely, and “Linked Clone” which creates a snapshot of the existing virtual machine and creates a differential virtual hard disk for the clone.  If you are cloning a VM on a laptop, or on a system with limited space, a “Linked Clone” can save space by reusing the base VMs virtual hard disk for the Windows OS, rather than having duplicated copies of the base OS installation.  However, when a “Linked Clone” is created, the base VM is required for the clone to be able to start up.  If the base VM is moved or lost, all of the “Linked Clones” will become unusable instantaneously.  This is the trade off between the two options; reduced storage vs. viability.

Once I create a “Linked Clone” for the Domain Controller, I open the Settings for the VM and remove Adapter 3 and 4 from the Network settings of the VM.  The Active Directory Domain Controller will not have iSCSI targets configured on it, and does not need the iSCSI network adapters configured for it, so it is best to remove those adapters from the VM.  It would also be possible to remove the previously configured Features from Windows Server 2008 R2 from the Active Directory VM if you chose to do so, those features being .NET Framework 3.5.1, Failover Clustering, and Multipath I/O, but it does not matter for this particular setup.  The next step in setting up the Active Directory Domain Controller is to accept the License Terms and login/set a password for the local Administrator account.  Since this is going to be a Domain Controller, we will need to setup a static IP Address for the Domain network adapter.  To do this, click on the Configure networking link on the Initial Configuration Tasks window that pops up immediately after you login to Windows the first time.  Then open the properties dialog for the connection that is not using NAT and setup on the Domain Internal Network.  To determine which network connection is the Domain network in the VM, you can open the VM settings (Machine>Settings) and simulate unplugging the network cable from the vNIC by toggling the Cable connected checkbox.

For this playground setup, I am going to use 192.168.81.x for the Domain network, and 192.168.31.x for the iSCSI network.  In IPv4 TCP/IP Properties, set the IP Address to 192.168.81.1, the Subnet mask to 255.255.255.0, and the Preferred DNS server to 127.0.0.1.

Now the VM needs to be renamed so that it has the appropriate naming convention to match your locally setup environment.  For the purposes of this blog series, the AD DC will be named SQLskills-DC in the environment.  Once the servers name is changed the VM will require a reboot.

Once the reboot for the rename operation completes, the VM is ready to be configured as an Active Directory Domain Controller.  For the purposes of having a playground environment, a very basic configuration of Active Directory can be done, to minimize the steps required and keep things simple. The following steps will provide you with a fully function Active Directory Domain Controller in your playground, but there is no consideration for high availability of the Domain Controller, backups, best practices for configuration, or anything else that would qualify you to be an Active Directory Domain Administrator beyond the confines of the playground environment that is being built.  To begin the configuration, the Active Directory Domain Services role will need to be added to the server.

After the ADDS role has been installed, run dcpromo to begin the configuration of the AD Domain on this server.

Click Next and then Next again to get to the Deployment Configuration screen.  Choose the option to create a new domain in a new forest and click Next.

Provide a Fully Qualified Domain Name (FQDN) for the new domain and then click Next.  You don’t have to provide an actual domain for the FQDN.  In this series I am using SQLskills.Demos as the FQDN for the domain.

The Domain Functional Level and Forest Functional Level defaults of Windows Server 2003 can be used within the sandbox environment.  Click Next on both of these screens.  After the DNS Configuration check completes, the wizard will recommend additional options for the Domain Controller, including the option to configure it as a DNS server. 

Leave the boxes checked on the screen and click Next.  If you’ve followed this guide up to this point, there should be a Static IP Assignment warning that pops up due to the dynamically assigned IP address for the NAT connection.  Click Yes on this box to continue, and then click Yes on the pop up that tells you the delegation could not be created for the parent zone in DNS.

For the playground environment, there is no need to change the database and log file locations for AD.  Click Next on this Wizard window, provide a password for the Directory Services Restore Mode Administrator account and click Next again.

At this point everything is set to complete the configuration of Active Directory and the information is displayed in a final summary screen.  Clicking Next will begin the configuration of the Active Directory Domain on the server.

Once the server reboots, it will be a fully functional Domain Controller for your playground environment.

iSCSI Virtual SAN

To facilitate building failover cluster configurations in our playground we are going to need a virtual SAN to provide the shared storage between the failover cluster nodes.  One of the requirements for the virtual SAN is that it must support SCSI-3 Persistent Reservations, which are required for failover clustering using Windows Server 2008/2008R2.  There are a number of free virtual SAN products available online, but for the purposes of this setup I chose to use the Microsoft Windows Server 2008 R2 iSCSI Target 3.3 which is available as a free download.  This iSCSI target meets all of the requirements for failover clustering support, and can be configured in a few minutes.

The steps to clone the template VM for the iSCSI Virtual SAN are exactly the same as they were for creating the Domain Controller, with the exception that all of the vNICs will be left configured for the VM.  Once Windows starts up, the first step is to configure the networking for the Domain network so that the VM can be joined into the playground domain.  To do this, first identify the Domain network connection in Windows by disconnecting the cable in the vNIC settings as previously shown.  Then change the IPv4 TCP/IP properties for the connection and set the IP address to 192.168.81.2, the subnet mask to 255.255.255.0 and then the Preferred DNS server to 192.168.81.1 (the IP address of the Domain Controller).

Once this has been done, the server name can be changed and the server can be joined to the domain at the same time.  Once the domain information has been set, the server will attempt to connect to the domain and will request credentials for an account with permissions to join the server to the domain.

After the server joins the domain, it will require a reboot to complete.  When the server restarts, you have to use the Switch User option to change the user that you login with from being the local Administrator account to login with a domain based account.

Note: At this point I would recommend creating a separate user account in the Domain that you use to work on the servers in the playground.  It is never a good practice to use the domain administrator account to work with SQL Server.

Once you’ve logged into the server using a domain account, download the Microsoft iSCSI Software Target 3.3 for Windows Server 2008 R2 and extract the contents to a location on the server.

Installing the iSCSI Target onto the server is a very straightforward process that is basically a series of check boxes for options like accepting the licensing agreement, whether to join the Customer Experience Improvement Program, and whether to use Microsoft Update or not.

Once the installation completes, an entry for the iSCSI Target will be added to the Start Menu and the target MMC can be opened up to begin configuring the iSCSI Targets for the environment.

The next post in this series will go through the configuration options for the iSCSI Target and how to create a new LUN to support configuration of a SQL Server Failover Cluster inside of the playground environment.

For the last few years, I have always had a personal virtual playground for SQL Server setup on my laptop that allowed me to not only learn about new features in SQL Server, but also demonstrate complex topics hands on during presentations.  I recently had to rebuild my playground from the ground up and as a part of doing this; I decided that I would blog about all the steps I took to build a completely FREE environment to play with SQL Server, including a virtual iSCSI “SAN” to support failover clustering.  I have been using VMware Workstation personally for the last few years as the platform for my playground, but only because I received a free license a few years ago when I spoke about virtualizing SQL Server using ESX at the VMware Open Forum in Orlando, FL.  One of the requirements for building this playground is that the virtual machines have to support 64 bit guests, which is not supported by Microsoft Virtual PC but is supported by VMware Workstation.  However, as much as I love my copy of VMware Workstation, it isn’t free and you may not have the budget to purchase it as a base platform, so for the purposes of this series I decided to go with VirtualBox, which is a completely free alternative that also supports 64 bit guest VMs.

To get started you are going to have to download and install VirtualBox from their site:

http://www.virtualbox.org/

While we are covering downloads, the following additional items will be needed to setup the environment:

Windows Server 2008 R2 with Service Pack 1 Evaluation Edition
SQL Server 2008 R2 Evaluation Edition
SQL Server Denali CTP3
Microsoft iSCSI Software Target 3.3 for Windows Server 2008 R2
VMware ESXi VSphere 4.1 Evaluation 

With VirtualBox installed, I start out by building a “base” VM installation that I clone to create all of the other VMs that I need in my playground.  By creating a standardized “base” or template VM I save a ton of time in the long term by not having to install an patch Windows Server 2008 R2 a half dozen times, I can do it once and then by using the sysprep functionality in Windows, I can reuse the image for each of my servers.  I can also add the basic set of features to the template, for example .NET Framework 3.5.1, Failover Clustering, and Multipath I/O to keep from having to manually add them to my VMs that specifically need them.  Some of the VMs that get created from the clone will not need these features specifically, for example the Active Directory Domain Controller we are going to create, but these features don’t impact the ability of the machine to perform as a Domain Controller for the environment.

To get started building the base VM template, create a new VM in VirtualBox (Machine > New) and configure the VM options.  For my template, I chose to allocate 1024MB RAM and create a dynamically expanding virtual hard disk that was 30GB in size, using the Windows Server 2008 x64 template for the VM.  Once the VM is created, you will need to edit the VMs settings to configure the additional networking, and if appropriate additional vCPUs.   For the Networking configuration, I leave NAT enabled for Adapter 1, and then configure internal networking for Adapter 2, 3 and 4 with Adapter 2 as a Internal Network named “Domain Network” and Adapter 3 and 4 on another Internal Network named “iSCSI Network”. 

Once the virtual networking has been configured for the VM, the last thing to do before powering the VM on is to set the CD/DVD device to the Windows Server 2008 R2 Evaluation Edition ISO file that was downloaded from Microsoft.

Once the VM boots it will immediately begin running the Windows Server 2008 Setup off the ISO and you will be on your way to setting up the template VM to base the rest of the servers needed in the environment off of.  Once Windows Server 2008 R2 is installed, I add the three features I mentioned previously in this post to the VM; .NET Framework 3.5.1, Failover Clustering, and Multipath I/O.  Once these have been installed, I run Windows Update to download and apply the most recent updates for Windows to the Server and then allow it to reboot.  Once the VM reboots, depending on how anal retentive I intend to be with the environment, I may or may not disable the Windows Firewall, but as a best practice it should be left turned on.  After I complete the configuration of the “base” template I want for the OS, it is time to run sysprep and reset the VM image for cloning to create multiple VMs based on its image.  Sysprep can be executed from Start > Run.  The options for sysprep should be set to “Enter System Out-of-Box Experience (OOBE)” and the checkbox for Generalize should be checked.

Once the VM shuts down it can be used to create clone VMs to build out the necessary infrastructure for the playground.  In the next post in this series, we’ll build the VM clones for the environments Active Directory Domain Controller and iSCSI “virtual” SAN using the Windows Server iSCSI Target.