page contents

About the Post

Author Information

Configuring SQL Server 2012 Always On Lab- Part 1

This is a long overdue topic that I have wanted to cover for a couple of years and have finally got a chance to setup for a lab environment. I wanted to expand upon Brent Ozar’s article: How to Set Up SQL Server 2012 AlwaysOn Availability Groups. I also used Brent’s SQL Server 2012 Availability Groups Checklist.

This will be the first article of a 4 part series that covers the entire process.

  1. Initial Setup
  2. Domain Setup
  3. Cluster (Shared nothing) Setup
  4. SQL Install

I am going to start with setting up a lab environment that covers all the features you would add to prep for a SQL Server 2012 AlwaysOn Availability group with a share nothing storage cluster configuration. I will share a few tips and scripts with you as well.

I begin this process by installing the following VM’s (all with the GUI and updated with latest patches via Windows Update) on my laptop:

  • 1 x Domain Controller with Windows Server 2012R2
  • 3 x SQL 2012 Servers (Primary, Secondary and Readable Replica) with Windows Server 2012R2
  • 1 x Windows 8.1 Client with SMS (Central Management Server)

To prepare for this cluster lab, I created 2 Network Switches for my Hyper-V networks:

  • Public
  • Cluster

To create these networks, you will need to open up Hyper V Manager and then click on the Virtual Switch Manager:


For the Domain and Cluster, I used internal network:


For the Public I created external network:


Next I am going to assign IP addresses for my network by using a series of netsh commands with PowerShell to run them all at once. I can run all of the commands at once, by listing each command then placing a semi colon (;) between each command.

Here is my plan for my Public network:

  • SMLW12R2DC01 172.16.0.10 255.255.0.0
  • SMLW12SQLAG1 172.16.0.20 255.255.0.0
  • SMLW12SQLAG2 172.16.0.21 255.255.0.0
  • SMLW12SQLAG3 172.16.0.22 255.255.0.0
  • SMLSQLCLUS 172.16.0.25 255.255.0.0 (Used Later)
  • SMLW81VM04 172.16.0.30 255.255.0.0

Here is my plan for my Cluster network:

  • SMLW12SQLAG1 192.168.0.20 255.255.0.0
  • SMLW12SQLAG2 192.168.0.21 255.255.0.0
  • SMLW12SQLAG3 192.168.0.22 255.255.0.0

Before I run the script I will need to verify each NIC is connected to the correct network and then I will need to rename them from the default Ethernet name in order for my script to work.

NICs

 

 

 

 

 

—————- Script to set LAN (Domain) NIC —————-

netsh interface ipv4 set address name=LAN source=static address=172.16.0.10 mask=255.255.0.0 gateway=172.16.0.1;netsh interface ip set dnsserver “LAN” static 172.16.0.10 primary;netsh interface ip add dnsserver name=LAN 172.16.0.1 index=2

—————- Script to set Cluster NIC —————-

netsh interface ipv4 set address name=Cluster source=static address=192.168.0.20 mask=255.255.0.0

—————- Script to clear NetBIOS/DNS cache on NIC (Plus enable Quick Edit on cmd.exe) —————-

ipconfig /flushdns; nbtstat -R;nbtstat -RR; ipconfig /registerdns; Set-ItemProperty -path “HKCU:\Console” -name QuickEdit -value 1

Update PowerShell help

Next I update my PowerShell help files, since I use PowerShell a good bit by typing Update-Help.


Now I run all of the commands listed above and my NICs are configured for each network:


Please see my earlier article on how you can create some cool short cuts like seen below:


Last but not least, I created 4 additional hard drives (vhd) to separate my database files:

  • E- bin (10 GB)
  • H- Data (10 GB)
  • O- Logs (5 GB)
  • T- Temp (5 GB)

Note: The reason we separate these files is that data files are written randomly, whereas logs are written sequentially. Having these files on the same drive will cause performance issues. TempDB is created every time SQL server is started and for security/sanity reasons, you should place backup files on another drive(s). For more information, please see this TechNet article.

I use the bin drive for configuration files, backups, scripts, etc. The data drive is for my SQL data files, logs is stored on my “O” drive and a tempdb drive. Now I am ready to setup the extra disks for my SQL drives.


I attempted to do it at first manually, but it got old fast and it was annoying to get all the pop ups:


So I decided to script it and for each drive you could do it like this. (It will check for all raw disks, initialize them and then create/formats (as NTFS with GPT) new partitions to use the entire disk space, assigns a drive letter and a label for the drives without prompting you for input) For me even that gets old and I was not happy with the code:

 

---------------- Script to setup a single disk ----------------

Initialize-Disk -Number 1

New-Partition -DiskNumber 1 -AssignDriveLetter -UseMaximumSize

Format-Volume -DriveLetter E -NewFileSystemLabel "bin" -Confirm:$falseÂ

Here is a cleaned up version that checks for all raw disks, initializes them, then creates/formats (as NTFS with GPT) new partitions to use the entire disk space, assigns a drive letter and a label for the drives without prompting you for input:

 

---------------- Script to setup all disks ----------------

Get-Disk | Where {$_.PartitionStyle -eq 'raw'} | % { 

Initialize-Disk -Number $_.number 

if ($_.number -like '1') 

{

New-Partition -DiskNumber 1 -DriveLetter E -UseMaximumSize 

Format-Volume -DriveLetter E -NewFileSystemLabel "bin" -Force -Confirm:$false

}

elseif ($_.number -like '2')

{

New-Partition -DiskNumber 2 -DriveLetter H -UseMaximumSize 

Format-Volume -DriveLetter H -NewFileSystemLabel "Data" -Force -Confirm:$false 

}

elseif ($_.number -like '3')

{

New-Partition -DiskNumber 3 -DriveLetter O -UseMaximumSize 

Format-Volume -DriveLetter O -NewFileSystemLabel "Logs" -Force -Confirm:$false

}

elseif ($_.number -like '4')

{

New-Partition -DiskNumber 4 -DriveLetter T -UseMaximumSize

Format-Volume -DriveLetter T -NewFileSystemLabel "Temp" -Force -Confirm:$false

}

}

---------------- End of Script ----------------

After running the script you can see here is the drives all configured:


At this point, I have all systems configured with machines names, IP addresses and the latest windows patches. In the next article, I will show you the steps on how to create the domain.

Tags: , , , , , , , ,

8 Responses to “Configuring SQL Server 2012 Always On Lab- Part 1”

  1. Hi! Glad I could help inspire a post series. Dumb question though – why the three networks, especially one for heartbeat?

    May 5, 2014 at 3:38 AM
    • Scott Mattie #

      Hey Brent, long time 🙂

      I guess you are right, I could have just built this “lab” with one “”public” network, but I wanted to kind of mimic a production environment. As for the heartbeat, you are right, I could have gone with a single NIC, but I built into this solution out of habit when building many clusters over the year. Keep mind, the TechNet article for cluster requirements, does mention this comment: “If you connect cluster nodes with a single network, the network will pass the redundancy requirement in the Validate a Configuration Wizard. However, the report from the wizard will include a warning that the network should not have single points of failure.” My intention here was to create a lab that was closer to the steps people would take in a live environment than a lab where people are more concerned with the bare minimum. I guess I should have been a little bit clear in the network build out, like I was in the hard drive setup when I creating 5 drives.

      Additionally, I had my reasons for creating a LAN and Public, since I wanted this environment to be isolated form other lab environments that I have and I wanted to ensure that I had the latest patches. I plan to only have the DC on a public network and run WSUS to patch the other SQL servers, but during setup for ease of use, I allowed all SQL servers access to the public network.

      May 5, 2014 at 11:16 AM
    • Hmm, I’m actually zooming out even farther – you don’t really want to be using heartbeat networks with AGs, or even any clusters on Win 2008R2 or newer.

      http://blogs.technet.com/b/askcore/archive/2010/02/12/windows-server-2008-failover-clusters-networking-part-1.aspx

      http://blogs.technet.com/b/askcore/archive/2010/04/15/windows-server-2008-failover-clusters-networking-part-4.aspx

      May 5, 2014 at 11:18 AM
      • Scott Mattie #

        Thanks for the tip, let me take a look at these articles and do a follow up article to this series 🙂

        May 5, 2014 at 11:37 AM

Trackbacks/Pingbacks

  1. Configuring SQL Server 2012 Always On Lab- Part 1 - SQL Server - SQL Server - Toad World - May 5, 2014

    […] Initial Setup […]

  2. Configuring SQL Server 2012 Always On Lab- Part 2 - SQL Server - SQL Server - Toad World - May 12, 2014

    […] Initial Setup […]

  3. Configuring SQL Server 2012 Always On Lab- Part 4 - SQL Server - SQL Server - Toad World - May 26, 2014

    […] Initial Setup […]

  4. Configuring SQL Server 2012 Always On Lab- Part 3 - SQL Server - SQL Server - Toad World - May 28, 2014

    […] Initial Setup […]

Copy Protected by Chetan's WP-Copyprotect.