Thursday, December 26, 2013

SQL Server 2012 AlwaysOn Availability Group - My experience with a proof-of-concept

Just had my experience building our organization's first SQL Server 2012 AlwaysOn Availability Group.  This is a proof-of-concept environment that will be used to demonstrate if we should build out this environment across physical locations.  This post summarizes the resources used in this initial build.
The white papers from Microsoft were the more comprehensive resource.   Used these when planning, and justifying the proof-of-concept.
Once it got time to actually build, these step-by-steps guides or checklists were used:
Here were some of the issues I ran into, and lessons learned
Separation of Duties - Server Administration and Database Administration
In my organization, there is a separate Server team controlling virtualization and Server OS while the 'Database' team has full rights to the database software.  Our first build required some back-and-forth collaboration to resolve issues that went above and beyond a standard database server
Lessons learned:
Setup up through the Windows Server Failover Cluster step mostly involves the Server Administration role.  Expect some collaboration needed between Server Administration and Database Administration in new environments
AlwaysOn Availability Group configuration
The AlwaysOn Availability Group configuration itself was extremely straight-forward.  The issues I ran into turned out to be items that, either by habit or due the environment, were overlooked when creating a test.  Might not run into these in production with the stricter controls on individual SQL Server instances
Note that the database to be made 'AlwaysOn' should be attached or restored to the 'Primary' SQL Server instance in the cluster.  During the AlwaysOn group creation, it can be restored to any secondary instances. 
Lessons learned:
Assess the SQL Server instances to be included in an AlwaysOn group against the standard build for a standalone SQL Server in production.