How to install System Center 2012 Service Manager SP1 with a SQL 2012 AlwaysON Availability Groups

How to install System Center 2012 Service Manager SP1 with a SQL 2012 AlwaysON Availability Groups

  • Comments 1
  • Likes

GrayAndYellowGearsHi, Babulal Ghule here, and in this post I will walk you through the steps to install System Center 2012 Service Manager SP1 (SCSM) with a SQL 2012 AlwaysON Availability Groups. First, let’s understand the two scenarios for using SQL AlwaysON availability Groups for Service Manager:

1. Service Manager is already installed.

2. New installation of Service Manager.

In the first scenario, we need to create an availability group for the existing Service Manager Database and follow the Service Manager Database movement process here and use the availability group listener as the new SQL server name.

For the second scenario, we create the availability group and use the availability group listener name as the SQL server name when installing service manager.

The high level steps:

1. Install two nodes in a Windows Server 2012 cluster.

2. Install SQL Server 2012 on both nodes.

3. Create and configure an AlwaysON Availability Group.

4. Install Service Manager.

5. Test failover.

A brief overview of SQL AlwaysON Availability Groups

An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of primary databases and one to four sets of corresponding secondary databases. Availability groups leverage Windows Server Failover Clustering (WSFC) functionality and enable multiple features not available in database mirroring.

Please refer to TechNet for the details on SQL AlwaysON Availability Groups: http://technet.microsoft.com/library/ff877884(v=SQL.110).aspx

Let’s Get Started

Step 1: Creating the cluster

Setup a Windows Server 2012 cluster. I recently wrote a post on installing Windows Cluster on virtual machines that you can reference here.

http://blogs.technet.com/b/babulalghule/archive/2013/02/16/how-to-configure-two-node-windows-server-2012-cluster-on-virtual-machines-for-testing.aspx

Step 2: Installing SQL Server

Install SQL Server 2012 on both nodes and configure the SQL Service to run using a domain account.

Note: For detailed prerequisite for SQL AlwaysON Availability Groups please see http://technet.microsoft.com/en-US/library/ff878487.

Step 3: Creating the AlwaysON Availability Group

In SQL Server Configuration Manager, go to SQL Server Services and open the properties of the SQL Service. Click on the “AlwaysON High Availability” tab and check “Enable AlwaysON Availability Groups” and restart SQL Service for changes to take effect.

clip_image001

 

Prerequisites:

  • Need a minimum of one database to create an availability group.
  • Any databases that you are planning to add to availability groups should have “Full” recovery mode enabled.
  • Take a full backup of any database that you are adding to availability groups.
  • You need a shared network location that is accessible to both nodes.

We will create a sample database in order to create an availability group for the SCSM installation so the next step is to create this sample database with full recovery mode and take a full backup of the database. Now is also the time to create our shared network location. I created the share (\\iSCSITarget\SQLAlwaysON) on my iSCSI target server from Server Manager -> File and Storage Services -> Shares.

For the cluster, I’m using same Windows cluster setup that is described in my post here.

Create the AlwaysON Availability Group (Refer to this link for details)

1. Open SQL Management studio on any of the SQL Server nodes.

2. Go to AlwaysON High Availability and right-click on Availability Groups and select “New Availability Group Wizard”

clip_image002

Specify the Availability Group Name.

clip_image004

Select SampleDB (or the name of your database) on databases selection tab.

clip_image006

Add a secondary SQL node on the Specify Replicas tab

clip_image008

 

clip_image010

Select the options on each of the four tabs (Replicas, Endpoints, Backup preferences and listener) that suit your requirements. This TechNet article has a very good explanation of all four tabs and the options available.

clip_image012

 

clip_image014

On the Listener tab, select Create an availability group listener.

Listener DNS Name: Specify the network name of the listener. This name must be unique in the domain and can contain only alphanumeric characters, dashes (-), and hyphens (_), in any order. When specified by using the Listener tab, the DNS name can up to 15 characters long.

This DNS Name will be used as SQL Server Name for Service Manager Installation.

Port: Specify the TCPC port used by this listener.

This port will be used as SQL Server port for Service Manager Communications with database.

clip_image016

On the Initial Data Synchronization page, select the option (Full, Join Only or Skip initial data synchronization) that suits your requirement. This TechNet article has very good explanations of all three options that are available.

clip_image018

On the Validation page, make sure all results report Success.

clip_image020

Click Finish on the Summary page and then click on Close to finish Availability Group creation.

In SQL Management studio, this availability group and listener should look something like this:

clip_image021

In Failover Cluster Manager it will appear under Roles:

clip_image023

4. Install Service Manager

Note: Be sure you follow the planning and deployment guide for all standard Service Manager installation prerequisites.

During the installation of SCSM, specify the Database server using the Availability Group listener DNS name.

clip_image025

Then simply complete the installation steps as per the Service Manager deployment guide.

Configure the Service Manager Database for Availability Groups

The Service Manager Installation using the SQL Availability Group listener does not configure the Service Manager Database in availability groups so we’ll need to do that next. To configure the Service Manager database in an Availability Group, change the recovery model from Simple to Full. See Recovery Models for more information.

Take a full backup of the Service Manager database, then right-click on Availability Databases -> Add Database Wizard and follow the instruction in the wizard to add the Service Manager database to the Availability Databases.

clip_image026

Verify this on secondary node:

clip_image027

5. Test Service Manager Database failover

Open Failover Cluster Manager and verify the owner node.

clip_image029

Right-click on the Availability Group and move to the secondary node. If you are using the immediate failback option then this will immediately failback to the primary.

clip_image030

In my case I’m using prevent failback.

clip_image032

Open the Service Manager Console and verify that everything is working properly after failover.

In my case, after failover I got an error in the OpsMgr logs because I forgot to add permissions for the Service Manager SDK service account on the database on the secondary node. I fixed that then restarted the SDK and saw this event:

Log Name: Operations Manager
Source: DataAccessLayer
Date: 16-02-2013 12:37:17
Event ID: 33333
Task Category: None
Level: Warning
Keywords: Classic
User: N/A
Computer: SCSMSP1.monlab.com
Description:
Data Access Layer rejected retry on SqlError:
Request: ObjectTemplateByCriteria -- (LanguageCode1=ENN), (LanguageCode2=ENU), (TypeID0=4b1e00f8-1f3d-ad95-acdb-5587b3cf7147), (LastModified0=16-02-2013 19:43:53)
Class: 16
Number: 6263
Message: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Looks like I forgot to enable the "clr enabled" configuration option but fortunately that’s an easy fix. Just run this SQL query on the secondary node:

sp_configure 'clr enabled', 1
go
reconfigure
go

Now everything works as expected.

Hope this helps!

Babulal Ghule | Support Escalation Engineer | Microsoft CTS Management and Security Division

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up: http://blogs.technet.com/b/systemcenter/
System Center – Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/
System Center – Data Protection Manager Team blog: http://blogs.technet.com/dpm/
System Center – Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
System Center – Operations Manager Team blog: http://blogs.technet.com/momteam/
System Center – Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center – Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm

Windows Intune: http://blogs.technet.com/b/windowsintune/
WSUS Support Team blog: http://blogs.technet.com/sus/
The AD RMS blog: http://blogs.technet.com/b/rmssupp/

App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv

The Forefront Endpoint Protection 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
  • We cannot install servicemanager db on AG with a named instance. It is supposed to be a bug.