Hello I am Dipti Goyal and I am a part of the System Center Virtual Machine Manager Team. System Center 2012 Virtual Machine Manager (VMM 2012) Service Pack 1 can now be configured with SQL 2012 Availability Groups and I am summarizing that process below.
Availability groups are set of failover partners (a.k.a. availability replicas) that provide high availability to user databases in Microsoft SQL Server 2012.
Setting up an Availability Group
Step 1: Install Windows Cluster; create a Windows Server 2008 R2 two-node cluster using Windows Hyper-V and a Domain Controller.
Step 2: Install a Standalone SQL Server 2012 instance on both nodes.
· Use the domain account for the SQL service account. · Open Port 1433 and 5022 or disable the Windows Firewall.
· Use the domain account for the SQL service account.
· Open Port 1433 and 5022 or disable the Windows Firewall.
Step 3: Create a sample database on both SQL Server Instances, making sure the Recovery model is set to Full – See below:
Step 4: Back up this test database to a shared folder.
Step 5: Enable the AlwaysOn feature on both instances.
· Open SQL Server Configuration Manager · Select SQL Server Services · Right-click on your SQL Server (in our example it is named MSSQLSERVER) and select Properties · Select the “AlwaysOn High Availability” tab and check "Enable AlwaysOn Availability Groups"
· Open SQL Server Configuration Manager
· Select SQL Server Services
· Right-click on your SQL Server (in our example it is named MSSQLSERVER) and select Properties
· Select the “AlwaysOn High Availability” tab and check "Enable AlwaysOn Availability Groups"
Step 6: Create an Availability Group
· Choose any one instance to become the PRIMARY (say Node1\MSSQLSERVER) · Open SQL Server Management Studio on Node2\MSSQLSERVER · Expand the Management folder · Right-click “Availability Groups” and select “New Availability Group Wizard…”
· Choose any one instance to become the PRIMARY (say Node1\MSSQLSERVER)
· Open SQL Server Management Studio on Node2\MSSQLSERVER
· Expand the Management folder
· Right-click “Availability Groups” and select “New Availability Group Wizard…”
· Click Next on the Introduction Screen
· Provide a name to the Availability Group
· Select the test database to be added into the Availability Group.
· Specify Replicas – Add the other instances (Node2\MSSQLSERVER) to assume the role of secondary for this user database
· Create an availability group listener.
· Specify the shared folder as location store the data.
· Assuming validation all passes, click Next and then Finish.
Step 7: Install VMM 2012 on the created Availability Group Listeners.
After installing the Availability Group (AG) successfully, you will see a resource that has the same name as the AG in the Failover Cluster Manager Panel. We can install VMM 2012 using the Listeners DNS name as database server.
After the VMM 2012 installation, go to SQL Server Management studio and change your VMM 2012 database’s recovery mode to full and do a backup. Then, go to Availability Databases and go through the Add Database wizard to add your VMM database to the Availability Databases.
Step 8: To test, failover from Node 1 to Node 2.
· Open Failover Cluster Manager, navigate to Services and applications ->’AG’. · Right-click ‘Move this service or application to another node’ –> ’Move to Node 2’ (or whatever the name of your other node is)
· Open Failover Cluster Manager, navigate to Services and applications ->’AG’.
· Right-click ‘Move this service or application to another node’ –> ’Move to Node 2’ (or whatever the name of your other node is)
If everything is configured properly then VMM 2012 should not have any issues with this action.
Dipti Goyal | SDET | Fabric Management
Get the latest System Center news on Facebook and Twitter:
App-V Team blog: http://blogs.technet.com/appv/ ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/ DPM Team blog: http://blogs.technet.com/dpm/ MED-V Team blog: http://blogs.technet.com/medv/ Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/ Operations Manager Team blog: http://blogs.technet.com/momteam/ SCVMM Team blog: http://blogs.technet.com/scvmm Server App-V Team blog: http://blogs.technet.com/b/serverappv Service Manager Team blog: http://blogs.technet.com/b/servicemanager System Center Essentials Team blog: http://blogs.technet.com/b/systemcenteressentials WSUS Support Team blog: http://blogs.technet.com/sus/
The Forefront Server Protection blog: http://blogs.technet.com/b/fss/ The Forefront Endpoint Security blog : http://blogs.technet.com/b/clientsecurity/ The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/ The Forefront TMG blog: http://blogs.technet.com/b/isablog/ The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/
Really good article. Only thing to point out which I don't see mentioned is that Always On Availability Groups require SQL Server 2012 Enterprise Edition so there may be a significant cost involved.
Licensing for System Center only allows for the use of SQL Server Standard edition at zero cost (as long as the SQL instances only host System Center databases).
Thanks for this very helpful article, specific to the AlwaysOn step by step configuration.
There is a catch, one should never use CluAdmin to failover Availability groups. Do this from within SQL Server.
wonderful article .we were missing the same in our design document earlier .
@yup: Please clarify why AG failover should not be done using cluadmin?
The Blog is really good.
Can you Please give me step by step for windows server 2012 or Win2K8 R2 Cluster Configuration.
Please send to this Address:
We have configured a 4 node geo cluster with VMM 2012 SP1 and on SQL 2012 AG.
Everything was working fine.
But lately we had some issues in VMM and MS suggested reinstalling the VM by retaining the DB and using the same DB again while installing.
But when we try and reinstall the VMM we get the following error.
Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
ALTER DATABASE statement failed.
The above suggests that we need to dissolve the AG to continue but ideally AG is supported by VMM and it has been working.
Could you suggest what be the issue.
We use a named instance while install servicemanager db on AG. It is supposed to be a bug.
Hi Dipti,Seems after added the VMM database to the Availability Databases, before To test, failover from Node 1 to Node 2.It need to do one important step which mentioned in http://technet.microsoft.com/en-us/library/dn168137.aspx On the secondary node computer in the cluster that is running SQL Server, create a new login with the following characteristics:◦ The login name is identical to the VMM service account name.◦ The login has the user mapping to the VMM database.◦ The login is configured with the database owner credentials.If not, the VMM service will failed and stop the HA VMM.Thanks