Wednesday, November 6, 2013

Setting up SQL AlwaysOn cluster for SCVMM 2012

SQL AlwaysOn is a new feature in MS SQL 2012 that supports SQL cluster without the need of a shared storage. The primary node will host the database file in its local storage and sync with the other standby copy in the secondary node. Since SCVMM 2012 supports this feature for HA, I followed this Technet blog for guidance.

I installed SQL server as standalone on each node using default values. The objective is to create an Availability Listener object for VMM to connect to the new database. I managed to follow through until the last step to replicate the test database failed.

After searching through the Internet, I realized that it's not correct to use default mode for SQL HA.

1) Always use domain-based managed service account for SQL instance (don't leave it to Network service).

2) Ensure the service account has access right to the end-point listener.

  • On SQL mgmt console, find out the name of mirroring endpoints
  • SELECT name, role_desc, state_desc FROM sys.database_mirroring_endpoints
  • Grant the service account access right to this endpoint 
  • GRANT CONNECT on ENDPOINT::{Mirroring_Endpoint} TO [Adomain\Otheruser]

3) After creating the new availability group successfully, check that the TestDB synchronization state on both nodes are "synchronized" (not "synchronizing" or "not synchronized"). Right click on the AG and click "Show Dashboard".

4) If they are not synchronized, click on "Properties" of the AG. Change all availability mode to "Synchronous Commit". Test the failover manually.

No comments:

Post a Comment