Want to know how hard it is to create a SQL Server 2012 AlwaysOn database Availability Group? It’s easy!
First, make sure you have your MSFC cluster created. See this post for more details.
Note: This demonstration is being performed on a VMware vSphere 5.0 cluster. It works great as both technologies are very complementary.
Next, on each of your two SQL Server nodes (in this case, servers db3a and db3b), edit your SQL Server service properties and check the box next to ‘Enable AlwaysOn Availability Groups’.
Next, on your primary server (in this case db3a) open SSMS, expand the tree, right click on ‘AlwaysOn High Availability’ and hit the wizard.
Specify the name of your availability group. I’m using the Dell DVDStore workload stressor tool as an example.
Next, the wizard will run a check on the databases on the primary server. If they pass the test, select one or more databases that you want to add to this Availability Group.
Remember to take a backup first, or else you will get the error that you see above.
Select your databases and continue.
Next, sign into your second database server by clicking ‘Add Replica’, specifying your next server and logging in.
Add as many nodes as you wish, up to four.
In this example, I am going to offload the backup functionality onto the second node, as well as use it for reporting, so I want to make the secondary node readable.
Next, click the Endpoints tab and double check your endpoints and its name. Double check your SQL Server Service Accounts and ensure that you have a shared file share set up somewhere that these accounts have access to. It will kick off a backup, restore and log backup automatically and its needs this location to store the files.
Next, click the Backup Preferences tab. Set your backup details carefully.
Next, click the Listener tab. If you prefer, you can add a new listener DNS name and IP address for this Availability Group. I personally find this to be a fantastic new feature.
Next, specify the location that you wish to use as a backup and log transfer location.
The next step runs a validator for all of the settings that you entered. Looks like things look good!
The final screen simply shows you your configuration for final review.
During the Availability Group creation process, it shows you what it’s doing. It throws that warning on the quorum drive, but since we’re doing this on two separate VMs, this error is irrelevant.
Look! It even does the priming of the mirror data pump for you.
Nice touch. The new Availability Group listener is even up!
Now, with SSMS I can connect to each node individually, the cluster VIP, or the new listener as well.
Outstanding! My data is now synchronized! My available replicas are listed, the databases listed, and the listeners shown in an exceptionally clear presentation.
Oh Microsoft. You nailed it on this one. Thank you for a job very well done.
Stay tuned for more AlwaysOn details like adding a new node into an existing cluster and Availability Group, latency and lag testing, and more!
