How to configure SQL 2012 AlwaysOn Availability Groups in System Center 2012 Virtual Machine Manager Service Pack 1

How to configure SQL 2012 AlwaysOn Availability Groups in System Center 2012 Virtual Machine Manager Service Pack 1

  • Comments 12
  • Likes

imageHello 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

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.

Step 3: Create a sample database on both SQL Server Instances, making sure the Recovery model is set to Full – See below:

clip_image002

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"

clip_image003

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…”

clip_image004

· Click Next on the Introduction Screen

clip_image006

· Provide a name to the Availability Group

clip_image008

· Select the test database to be added into the Availability Group.

clip_image009

· Specify Replicas – Add the other instances (Node2\MSSQLSERVER) to assume the role of secondary for this user database

clip_image011

clip_image012

· Create an availability group listener.

clip_image014

· Specify the shared folder as location store the data.

clip_image016

· Assuming validation all passes, click Next and then Finish.

clip_image017

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.

clip_image019

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.

clip_image020

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)

clip_image021

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:

clip_image001 clip_image002

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/

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • 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?

  • Hello Dipti

    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:

    pradeep.macro@gmail.com

  • Hi Dipti

    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.

    Thanks

    Preeti

  • We use a named instance while install servicemanager db  on AG. It is supposed to be a bug.

  • Thanks Goyal

  • Thanks Goyal

  • 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