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
- Specify "Enterprise Edition" of Windows server. The 'Failover Clustering' feature simply isn't present in Windows Server Standard Edition (http://technet.microsoft.com/en-us/library/cc725923(v=WS.10).aspx)
- The Server Administration team first created a computer object, then granted me Full Control on the computer object in Active Directory. (In addition to being a local administrator on the actual servers) http://technet.microsoft.com/en-us/library/cc731002.aspx
- After cluster creation, an IP address was reserved for the cluster object.
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.
- The Developer Edition of SQL Server 2012 has TCP/IP disabled. I had only enabled it on the secondary instances and had connected to them while connected via Remote Desktop to the primary instance. TCP/IP had to be enabled on the primary instance as well so the secondary instances could talk back. (http://connect.microsoft.com/SQLServer/feedback/details/693322/tcp-ip-disabled-by-default-on-sql-server-denali-ctp-3-refresh)
- All SQL Server instances were set to run as the local account \MSSQLSERVER. This was changed to use the same domain account on all servers in the group (http://technet.microsoft.com/en-us/library/ff878308.aspx#Accounts)
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.
