SQL Server

How to Build a Local SQL Server Availability Group


The following procedures are meant to be used for creating a Windows Server Failover Cluster (WSFC) hosting a SQL Server Availability Group in a LAB environment.  More specifically, this lab should not be connected to any kind of corporate domain.  The lab should be on an isolated laptop or possibly a home network.  The focus is on being able to use SQL Server for learning.  Best practices for security settings, server O/S settings, network settings, etc. are NOT included in these instructions.

The audience is intended to be database professionals who are already familiar with basic SQL Server installations.

The following environment was used to create these instructions:

  • Windows 10 Host (Lenovo ThinkPad T560)
  • VMWare Workstation 10
  • Windows Server 2012 R2 Data Center Edition
  • SQL Server 2016 Enterprise Edition

It is probably worth reviewing the table near the end of this document in the Environment Settings section before embarking on an actual setup.  There are a handful of names that will require some forethought if the cluster is to provide some decent long-term use.

Build the Base Windows server

The first task is to build a new virtual machine.  Install Windows with the goal of using this server as a base for cloning additional servers.  If the creation of the base is successful, the Windows install should only have to be run once.

  1. Install Windows Server 2012 R2 on a new Virtual Machine.
  2. Install Windows Updates to prevent the need to run that first potential logjam of updates on every server.  Subsequent Windows Updates can be applied to each server as they are released by Microsoft.
  3. Under Windows\System32\Sysprep\, run sysprep.exe with “Generalize” selected.  These details may help you get it right; because, it’s easy not to, and then you have to add the license key and go through all kinds of trouble on each Windows machine.
  • Open a command prompt window with Admin privileges
  • Run “cd c:\windows\system32\sysprep” to get to the right directory
  • Run  “sysprep.exe /oobe /generalize /shutdown /mode:vm

Build the Domain Controller

Prepare the Clone

  1. Create a “Linked Clone” of the new VM to use as a Domain Controller.
    1. In VMWare Workstation, right-click the base server and choose Manage –> Clone….
    2. Click Next and choose to use the current state of the VM
    3. Leave the default of “Linked Clone”, and give the VM a meaningful name
  2. Change the server (computer) name to something meaningful (e.g. LESCHVM-DC-001)
    1. Open Windows Explorer, right-click the computer (“This PC”) and choose properties
    2. On the far right, choose Change Settings and click the “Change …” button
    3. Restart Windows
  3. Change the server to use a static IP address.
    1. Choose a static IP address from within the range of 192.168.net.3 – 192.168.net.127
    2. Set the Subnet Mask = (default)
    3. Set the Default Gateway = 192.168.net.2 (or whatever it defaults to)
    1. Open the VMWare “Virtual Network Editor” and note the NAT Gateway IP settings, especially the “net” setting which is the 3rd node of the IP Address (e.g. the net setting for = 34)
    2. Right-click the connection icon in the lower right of the VM (Windows System Tray), and choose “Network and Sharing Center”
    3. Choose “Change adapter settings”, and right-click on Ethernet0 and go to Properties
    4. Go to Properties of IPv4, and manually edit the settings.
    5. Set the Preferred DNS Server = 192.168.net.2 (or whatever it defaults to)
    6. Restart

Install AD DS and GPM

  1. Follow these steps to install Active Directory Domain Services (AD DS) and Group Policy Management (installed automatically with features).  Note that the computer rename and IP change should have already been completed.
    1. In the Server Manager dashboard, choose “Add Roles and Features”
    2. On the “Before you begin” page, hit Next
    3. On “Select installation type” keep the default of “Role-based…”, hit Next
    4. On “Select destination server”, keep the default of “Select a server from the server pool”, hit Next
    5. On “Select server roles”, check “Active Directory Domain Services”
    6. Click the “Add Features” button that pops up
    7. Back on the “Select server roles” page hit Next
    8. The next page of the “Add Roles and Features” wizard will show that “Group Policy Management” has been automatically checked, hit Next
    9. On the “Active Directory Domain Services” page, hit Next
    10. On the “Confirm installation selections” page, there is an option to have an automatic restart and to have a script created, hit Install
    11. After the installation is done the message “Configuration required.  Installation succeeded on [my server].” Should be displayed below the blue status bar.  Click Close, or alternatively click on “Promote this server to a domain controller”.

Promote to a DC

  1. Once AD DS is installed, promote the server to a Domain Controller (via the alert notice in the upper right corner of the Server Manager Dashboard if the server wasn’t already promoted in step 10 above).
    1. On the “Deployment Configuration” page, choose “Add new AD forest” and enter a root domain name (e.g. mydomain.com, mydomain.local, etc.), hit Next
    2. On the “Domain Controller Options” page, make sure Domain Name Server (DNS) is checked, enter a password, hit Next
    3. Skip past DNS Delegation warning, hit Next
    4. On the “Additional Options” page, verify the NETBIOS name (choose wisely…NETBIOS\User is how all of the domain Windows accounts will be named), hit Next
    5. On the “Paths” page, the defaults are probably fine, hit Next
    6. On the “Review Options” page, generate PowerShell script to save if another DC is needed, hit Next
    7. If the “Prerequisites Check” passes, click install.  DNS (Domain Name Service) and GPMC (Group Policy Management Console) are installed automatically.
  2. Open ADDS from Server Manager and create a new domain account to be used as the SQL Server service account on the SQL Server nodes.  Several accounts could be created for the SQL Engine, Agent, and future uses of SSIS, SSRS and SSAS.
    1. Follow these steps for generic user account setup:
      1. In the Server Manager Dashboard, go to the Tools menu at the top and choose Active Directory Users and Computers
      2. Right-click the domain and choose New –> Organizational Unit (e.g. SQLServices)
      3.  Right-click the OU just created and choose New –> User
      4. For a lab environment, checking only the “Password never expires” box might make the most sense
    2. For more SQL Server specific notes on Windows Service Accounts, click here
  3. While in Server Manager, make a note of the DNS Server’s IP address.
  4. Add the main administrative user account to the “Domain Admins” group.

Create the SQL Servers

Create and Prepare the Windows Clones

  1. Create linked clones of the base Windows 2012 machine again for as many replica SQL Servers as needed.  These will be used for the primary and up to 8 (SQL2014) secondary servers for Availability Groups.  Repeat for each SQL Server node.
    1. Sysprep might need to be run again on each clone if it didn’t go well the first time, which then requires entering the Windows license key – a failure message when trying to join the domain will indicate if sysprep is needed again
    2. Change the IPv4 to use another static IP address, and use the new DNS IP Address for the Default DNS
    3. An example:  if the Domain Controller has a static IP address of:
      1. Set the clone’s IP Address =
      2. Set the clone’s Subnet Mask = (default).
      3. Set the Default Gateway = (VMWare will use x.x.x.2 for internet access by default – NAT connection under Windows button –> All apps –> VMWare –> Virtual Network Editor)
      4. Set the Preferred DNS Server = (Domain Controller)
      5. Set the Alternate DNS Server = leave blank
    4. Change each computer name, and join to the domain (on same screen as name change)
    5. Restart
  2. For each SQL node, .NET Framework 3.5 – applies to SQL Server 2012 as well (needed for DB Mail even on SQL2016 reportedly)
    1. Make sure the Win2012 install media is copied to a local drive in the VM and then mounted (should be able to find it on the host by expanding the Network node in Windows Explorer to get to the host machine) – VMWare Workstation –> VM –> Settings –> Options tab –> Shared Folders –> Add
    2. Use “Add roles and features” in Server Manager
    3. Skip past “Server Roles”
    4. In “Features” check the “.NET Framework 3.5 Features” box, hit Next
    5. Due to the warning message, click “Specify an alternate source path” at the bottom of the page
    6. Enter the following “F:\Sources\SxS”, where F: is the mounted drive for the Win2012 media file
    7. Click install

Install SQL Server

  1. Install SQL Server on all of the new clones.
    1. Login with a domain account that has administrator rights.
    2. Choose a stand-alone installation.
    3. Ignore the update failure check if internet access is blocked.
    4. Recommended features are:
      1. Database Engine
      2. Replication Services
      3. Client Tools Connectivity
      4. Integration Services (may want it for sync’ing server-level SQL objects)
      5. Client Tools Backward Compatibility (just to be safe)
      6. Documentation Components
      7. Management Tools – Complete
    5. Set services to run with the domain accounts created earlier, and set the SQL Agent to run automatically.  Don’t forget to type the passwords.
    6. Be sure to choose Mixed Mode for the Authentication Mode, and then set a strong password for the “sa” login.
    7. Click the “Add Current User” button to make sure the Windows domain admin user account gets added.
    8. Make sure the default file paths for data and log files are the same for all instances.  Using the defaults for all installs is an easy way to make sure the file paths are the same.
    9. Install SQL Server, which could take a good 20 minutes or more.
    10. Grant the SQL Server service account the “Perform volume maintenance tasks” permission in the local security policy (under Local Policies –> User Rights Assignments).  There is a nice check box for this in the SQL2016 install.  Don’t miss it.
    11. If SSIS packages will be developed on a server, install SQL Server Data Tools.  SSDT is a separate download at the time of this document’s creation.

Create the Windows Server Failover Cluster

  1. Using Server Manager, install “Failover Clustering” by checking the box on the Features list.  This must be installed on each of the nodes (not the domain controller).
  2. Kill the Windows Firewall, or change the settings to allow Availability Groups to function – by default the SQL Servers won’t be able to see each other even with the cluster (WSFC) working.
    1. To turn off the firewall, go to Control Panel –> System and Security –> Windows Firewall
    2. On the left menu, choose “Turn Windows Firewall on or off”
  3. Back on the primary node, use Failover Cluster Manager to build a failover cluster.
    1. A short-cut to the manager is under Administrative Tools.
    2. First use the “Validate Configuration” link to start the wizard.  There will probably be lots of warnings given the likely less-than-stellar environment being used.
    3. Create the cluster.
      1. This can be done by leaving the check box selected at the end of a successful validation wizard.
      2. Give the cluster a name and an IP address.

Configure Availability Groups

  1. Use SQL Server Configuration Manager on each of the nodes to enable AlwaysOn Availability Groups.  Right-click the engine service to turn the feature on, which also requires a restart of SQL Server.
  2. While in Configuration Manager, set the SQL Server service account to the domain account created in step 9 (if not done during install setup).  Also, confirm that TCP/IP is enabled, which can be done by expanding the other items in Configuration Manager and looking at the protocols.
  3. Create and prepare a new user database.
    1. A common sample database like AdventureWorks is good enough.
    2. Make sure the database is in full recovery model.
    3. Backup the database.
  4. Run the Availability Group wizard by right-clicking AlwaysOn Availability Groups in SQL Server Management Studio (SSMS).  You may need to create a share for the backup path.
    1. Check each tab if you wish to change the defaults (e.g. backup preferences)
    2. If you want automatic failover, be sure to make the AG replicas synchronous
    3. You want to configure a Listener to take full advantage of the AG.  Using port 1433 is easiest.

Configure a File Share Witness

Note that this step could be completed before Availability Groups are configured, and a File Share Witness (FSW) is not really necessary.  However, a FSW is very common in real deployments.

  1. In Failover Cluster Manager, click on the cluster name.  Then on the far right or by using the Action menu at the top, choose “More Actions”.
  2. Select “Configure Cluster Quorum Settings…”, and click Next on the “Before You Begin” page.
  3. Choose the “Advanced quorum configuration” radio button, and click Next.
  4. Use the “Select Nodes” button to deselect a voter, particularly if simulating a non-voting DR node.
  5. On the next page, click the button for “Configure a file share witness”.
  6. Enter a share location from the Domain Controller on the next page, but first make sure the cluster has full access to it.  In a real-world scenario, the share would be locked down.  In this case it may be easier to open the share to read/write for “everyone”.
  7. Finally, click Next to change the voters and create the FSW.

Document the Environment Settings

Keep track of what was built to make maintenance and future builds easier.  For example:


Object Type Windows Name IP Address VM Name
Root Domain Name gotham.com
Domain Controller LESCHVM-DC-001 192.xxx.xx.02 Win2012R2_DC
SQL Primary LESCHVM-SQL-001 192.xxx.xx.03 Win2012R2_SQL2014_Primary
SQL Hot Standby LESCHVM-SQL-002 192.xxx.xx.04 Win2012R2_SQL2014_Secondary1
SQL Hot Read-Only LESCHVM-SQL-003 192.xxx.xx.05 Win2012R2_SQL2014_Secondary2
SQL DR LESCHVM-SQL-004 192.xxx.xx.06 Win2012R2_SQL2014_Secondary3
WSFC Name SQLCL001 192.xxx.xx.23
SQL Instance Name BatCave2016
AG Name BatAG1
AG Listener Name BatEars 192.xxx.xx.20:1433
Network Share \\LESCHVM-DC-001\SQLAG
Organizational Unit SQLServices
SQL Engine Service Account GOTHAM\SQLEngine
SQL Agent Service Account GOTHAM\SQLAgent
SSIS Service Account GOTHAM\SQLIntegration
SSRS Service Account GOTHAM\SQLReporting
SSAS Service Account GOTHAM\SQLAnalysis



Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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