Denali AlwaysOn Walk Through

Denali AlwaysOn Walk Through

I recently spent a free Saturday in Austin attending SQLSaturday #97. It was a great event with many knowledgeable speakers. First, a big thank you goes out to the speakers, organizers, volunteers and of course the sponsors.

Deciding what to write about was not an easy task. Luckily, there was one session that my spider senses tingle. The session featured Jim Murphy (Blog | Twitter) from SQLWatchmen and went over a few features and quick demo of upcoming Denali AlwaysOn Availability Groups. AlwaysOn utilizes components of the Windows failover cluster and Mirroring to provide an enterprise level high availability solution. To build out this walk through I will start with two Windows 2008 R2 Enterprise x64 VM’s with SQL Server Denali CTP3. For information on how I installed Denali see my previous post Installing Denali CTP3 Step-by-Step. The nodes have not yet been clustered and exist on the same subnet. Although, enterprise edition is required, I have been told that the nodes existing on the same subnet is not required! This would have huge benefits that I will not go into here.

Photobucket

1. Log onto node1 with a domain account that is an admin on every node you will be working with or a domain admin if you have that kind of power. Please remember with great power comes great responsibility.
2. Launch server manager.
3. Right-Click Features and Select Add Features

Photobucket

4. At the Select Features screen check Failover Clustering, and then click Next.

Photobucket

5. At the Confirm Installation Selections screen click Install.

Photobucket

6. At the Installation Complete screen click Close

Photobucket

7. If prompted to reboot, do so.
8. Repeat Steps 1-7 on node2 prior to proceeding.
9. Log back onto node1.
10. Launch Failover Cluster Manager.
11. Right-Click Failover Cluster Manager and select Create a Cluster

Photobucket

12. At the Before you Begin screen click Next.
13. At the Select Servers screen enter all servers that you will have in the cluster one at a time clicking Add after each one.

Photobucket

14. At the Access Point for Administering the Cluster screen enter the Cluster Name and appropriate address then click Next.

Photobucket

15. At the Confirmation screen click Next.
16. At the Summary screen click Finish!!
Congratulations you have set up a cluster. Kind of…

17. On node1 launch SQL Server Configuration Manager.
18. Right-Click the SQL Server service and select Properties.
19. At the SQL Server Properties screen select the AlwaysOn High Availability Tab. Check Enable AlwaysOn Availability Groups then click Apply.

Photobucket

20. At the service restart warning screen click Ok. Then click Ok at the SQL Server Properties screen.
21. Repeat steps 17-21 on node2 before proceeding.
22. Launch Management Studio on node1 and connect to your local instance.
23. Ensure the database to be mirrored is in Full recovery mode and take a full backup prior to proceeding.
24. Expand the instance then expand Management
25. Right-click Availability Groups then select New Availability Group Wizard.

Photobucket

26. At the Introduction screen click Next.
27. At the Specify Availability Group Name screen enter the appropriate name then click Next.
28. At the Select Databases screen select the appropriate database then click Next.

Photobucket

29. At the Specify Replicas screen select the appropriate Replication options then click Next.
Available options for Replica Mode are

Photobucket

Available options for Connection Mode in Secondary Role are

Photobucket

For my test I will be selecting.

Photobucket

30. Click Add Replica and connect to the instance on node two, then click Next.

Photobucket

31. At the Specify Availability Group Listener screen you can configure your listener now or select Skip then click Next.
32. At the Select Initial Data Synchronization screen specify a shared network location for the backup then click Next. A backup must be performed to bring everything in sync.

Photobucket

33. At the Validation screen ensure the results all list success then click Next.
34. At the Summary screen verify the information then click Finish.

Congratulations you have installed Microsoft SQL Server Denali CTP3 AlwaysOn feature. If you received any errors you can resolve them by selecting your newly created availability group’s properties and adjusting accordingly to fix the errors. Test the configuration by failing it back and forth. Maybe even failing an instance and let it “Fall over” automatically. I hope this helps you set up your first AlwaysOn Availability Group.