Babulal Ghule's Blog

Microsoft System Center

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

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

  • Comments 14
  • Likes

Note : SQL Server AlwaysOn Availability Groups functionality is supported by all versions of System Center 2012 – Service Manager for the default server instance. However, SQL AlwaysOn Availability Groups functionality is not supported for a named instance. Refer TechNet : http://technet.microsoft.com/en-us/library/hh495585.aspx

 

In this blog post, I walk you through the steps to install Service Manager 2012 SP1 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 first scenario, we need to create availability group for already installed Service Manager Database and follow the Service Manager Database movement process (link) and use availability group listener as new SQL server name.

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

Here are the high level steps:

  1. Install two node windows server 2012 cluster.
  2. Install SQL Server 2012 on both the nodes.
  3. Create and configure AlwaysON Availability Group.
  4. Pre-requisite for Service Manager 2012 SP1 install on SQL AlwaysON.
  5. Install Service Manager.
  6. Test failover.

Let’s start with 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 this (link) TechNet for the details on SQL AlwaysON Availability Groups.

Step 1:  

Setup windows Server 2012 cluster, I recently wrote a blog on installing windows cluster on virtual machines.

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:

Install SQL Server 2012 on both the nodes with SQL Service running on domain account.

For detailed pre-requisite for SQL AlwaysON Availability Groups refer this link.

Step 3:

Enable SQL alwaysON feature on both the nodes.

  • SQL Server Configuration Manager -> SQL Server Services -> Open properties of the SQL Service
  • Click on “AlwaysON High Availability” tab and check “Enable AlwaysON Availability Groups” and restart SQL Service for changes to take effect.

 

 
 

Create SQL AlwaysON Availability Group

Pre-Requisite:

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

We will create sample database in order to create availability group for Service Manager Installation.

  • Create Sample database with full recovery mode and take the full backup of the database.
  • Create shared network location

I’m using same windows cluster setup which is described on this blog post.

  • Created share (\\iSCSITarget\SQLAlwaysON) on my iSCSI target server from Server Manager -> File and Storage Services -> Shares.

Create Availability Group (Refer this link for details)

  • Open SQL Management studio on any of the SQL Server node.
  • AlwaysON High Availability -> right click on Availability Groups  and select “New Availability Group Wizard”.

  

 

  • Specify Availability Group Name.

 

  • Select SampleDB on databases selection tab.

  • Add secondary SQL node on Specify Replicas tab.


 

  • Select the options on each of the four tabs (Replicas, Endpoints, Backup preferences and listener) which suit your requirement. This TechNet link has very good explanations on all four tabs and options available.


 

  • On Listener tab select Create an availability group listener.

 Listener DNS Name: Specify the network name of the listener. This name must be unique on 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 TPC port used by this listener.

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

                


 

  • On Initial Data Synchronization page, select option (Full, Join Only and Skip initial data synchronization) which suits your requirement. This TechNet link has very good explanations on all three options available.

 

 

  • On Validation page, see if all success.

  • Click on Finish on Summary page and then click on Close to finish the availability group creation.

          In SQL Management studio, this availability group and listener will look like as below.

On Failover cluster Manager, it will appear under Roles.

 

Now, it’s time to install Service Manager

  • Follow the planning and deployment guides for all the standard Service Manager installation pre-requisite.
  • During the installation specify the Database server as Availability Group listener DNS name.

  • And complete the installation steps as per the service manager deployment guide.

 

Configure Service Manager Database for Availability Groups

  • Service Manager Installation using the SQL availability group listener does not configure Service Manager Database in availability groups.
  • To configure Service Manager database in availability group, change the recovery model (refer this link for details on Recover Model) from Simple to Full
  • Take full backup of Service Manager Database.
  • Right click on Availability Databases ->  Add Database wizard and follow the instruction on wizard to add Service Manager Database to the Availability Databases.

 

 

  • Verify that if you see the Service Manager database on secondary node.


 

Now, it’s time to test Service Manager Database failover

  • Open failover cluster manager and verify the owner node.

  • Right click on availability group and move to secondary node.
  • If you are using immediate failback option then this will immediately failback to primary.

In my case I’m using prevent failback.

 

  • Open Service Manager Console and verify if everything is working fine after failover.
  • After failover I got few errors in the OpsMgr logs, first thing I provided permission for Service Manager SDK service account on SM database on secondary node.
  • Restarted the SDK restart and got the below error.

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.

  

  • Executed below query on secondary node

            sp_configure 'clr
            enabled', 1

           go

           reconfigure

           go

 

I forgot to run this SQL query on secondary node before attempting to failover.

Now everything works as expected.

 

Hope this helps!

Comments
  • Thanks for your description of how to setup Service Manager Database on a SQL 2012 AlwaysOn Availability Group. A few questions that I have though are:

    - Can the Service Manager Database and the Service Manager DW Database be on the same Availability Group?

    - Since the DW Database uses SSRS... where would you install the SSRS features?  From my understanding of SSRS, it cannot be installed on a cluster node.

    Thanks!

  • 1. SM and SMDW DBs can co-exists on same AG, I don't see any issues but l'll be testing this weekend.

    2. SSRS, either you can install it on DW MS or on different server.

  • Hi Babulal,

    Hw to install service manager in HA mode.

    Regards,

    Nilesh

  • Can the Availability Group be a named instance?

  • What about installing SM when a multi  subnet AG is involved. i.e. where can I add the connection string parameter for multisubnetfialover=true. or is a multi subnet AG not supported for service manager ?

  • in our Company we do per policy can use only named inctances and now only Availability Groups with SQL2012.

    How can i do this?

  • @Dan: Multi Subnet Configuration within SQL works just fine for SCSM. You will need to use 1 IP when installing, then flip it to multi-subnet and test a failover. I recently installed this for a customer and it worked fine. You will also need to flip SQL back to 1 IP when installing the Self Service Portal as it gets confused by the multi-subnet just like the SCSM management server install does. One thing to note: During failover testing, we did see some problems with the data access service on the Primary Management Server. But after a re-boot and a couple of services restarts, it seemed to get back on line. The key steps are 1: Fail Over SQL, 2: Restart SCSM Data Access Service. For the SCSM DW. make sure to STOP all the DW Jobs via Powershell , Failover SQL, - restart Data Access Service on the SCSM DW. then start the SCSM DW Jobs again via powershell.

  • @John: Yes, you can give your SQL AG a DNS Name.

  • Hi Greg, Thanks for the answer!
    If i try to install it in the AG...it do not work...and installing on one node...i have no idea how to move the Analysis Database in the AG! Do anyone have an instruction...SCSM install on a named instance SQL2012 Availability Group with Analysis services

  • Hi Mike,

    SQL AlwaysOn Availability Groups on a named instance is not supported for Service Manager. I have updated blogs and this is also documented on TechNet.

    http://technet.microsoft.com/en-us/library/hh495585.aspx

    Regards,
    Babulal

  • Has anyone tried to use SQL AlwayOn with System Center 2012 R2 Service Manager and a new install? I am getting an error that A required SQL Server service is not running on.... when I try to use the Listener name. If I use the Listener IP Address, Failover Cluster Management Name, or Individual Server names, I am not get this error.

  • Hey Nik,
    Follow this guide from Steve Buchanan.
    http://www.buchatech.com/2014/05/error-installing-service-manager-with-sql-alwayson/

  • Hi Nik,

    I've had the same problem and MS gave us the following workaround:

    To resolve the issue we need to add the following REG_MULTI_SZ value on both nodes of the cluster.

    KEY: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters:
    Name: NoRemapPipes
    Type: REG_MULTI_SZ
    Data: winreg
    svcctl
    eventlog

  • Hi Babulal,
    Is it possible to install the service manager DWH on a SQL AlwaysOn Availability Group named instance?

    Thanks,
    Adrian

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment