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

  • 發行項

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 : https://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.

https://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” recoverymode.
  • 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

  • Anonymous
    January 01, 2003
  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.
  • Anonymous
    January 01, 2003
    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

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

    Thanks,
    Adrian
  • Anonymous
    July 25, 2013
    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!

  • Anonymous
    October 01, 2013
    Hi Babulal, Hw to install service manager in HA mode. Regards, Nilesh

  • Anonymous
    December 04, 2013
    Can the Availability Group be a named instance?

  • Anonymous
    December 06, 2013
    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 ?

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

    How can i do this?

  • Anonymous
    March 25, 2014
    The comment has been removed

  • Anonymous
    March 25, 2014
    The comment has been removed

  • Anonymous
    March 28, 2014
    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

  • Anonymous
    May 14, 2014
    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.

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

  • Anonymous
    August 21, 2014
    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_MACHINESYSTEMCurrentControlSetServicesLanmanServerParameters:
    Name: NoRemapPipes
    Type: REG_MULTI_SZ
    Data: winreg
    svcctl
    eventlog

  • Anonymous
    October 21, 2014
    Hey Adrian,
    I don't think a named instance is supported for AlwaysOn SCSM setup.
    Thanks
    Prab

  • Anonymous
    October 21, 2014
    Hey Adrian,
    I don't think a named instance is supported for AlwaysOn SCSM setup.
    Thanks
    Prab