Installing a replica MP in ConfigMgr 2012


In the first part of a two part blog series I will be installing and configuring a replica MP in a ConfigMgr environment. Replica MPs are used for two reasons, one – to offload some of the utilisation on the site server database in larger ConfigMgr implementations, two – to provide high availability if a primary site server goes down. The second part of the series will focus on what happens if a primary goes down and what the replica MP can deliver.

So let’s start to set up the environment for a replica MP.

Configure the Site Database Server

On the site database server, enable the SQL Server Agent in Services.

replicaMP-001

Re-run the SQL installation and choose to add features to an existing instance.

replicaMP-003

Modify the SQL installation to add replication.

replicaMP-002

Set the Site Database Server to Publish Data

Create a local group on the site database server called ConfigMgr_MPReplicaAccess and add the server that will become the replica MP server into the group.

replicaMP-004

On the site database server create a share called ConfigMgr_MPReplica

replicaMP-005

Add the following permissions to the share and NTFS.

  • Share Permissions:
    • SYSTEM: Full Control
    • ConfigMgr_MPReplicaAccess: Read
  • NTFS Permissions:
    • SYSTEM: Full Control
    • ConfigMgr_MPReplicaAccess: Read, Read & execute, List folder contents

Now enable the site database as a publisher. To do this open SQL Management Studio and create a new query.

Use CM_<sitecode>
Go
exec spCreateMPreplicaPublication

replicaMP-006

Configuring the replica DB and replication

Set the site server computer account to be local administrator on the replica MP server.

replicaMP-023

Install SQL on to the server that is hosting the replica MP. Ensure that SQL replication is installed when selecting the Database Engine Services.

replicaMP-007

Ensure that the SQL Server Agent service is set to run Automatic.

replicaMP-008

Once installed open SQL Management Studio and create a new database called <sitecode>_REPL. Set the database size to 3000MB and the log size to 128MB.

replicaMP-009

Navigate to Replication>Local Subscriptions. Right click and choose New Subscriptions.

replicaMP-010

When the New Subscriptions wizard opens click Next.

replicaMP-011

From the drop down menu choose ‘Find SQL Server Publisher’.

replicaMP-012

Enter the hostname of the site database server and click Connect.

replicaMP-013

Select ‘ConfigMgr_MPReplica’ and click Next.

replicaMP-014

On the Distribution Agent Location page, select ‘Run each agent at its Subscriber (pull subscriptions)’, and click Next.

replicaMP-015

From the Subscription Database drop down select the database you have created <sitecode>_REPL. Click Next.

replicaMP-016

Click the… button.

replicaMP-017

Select to run under the account running the SQL Server Agent service.

replicaMP-018

Set to run continuously and click Next.

replicaMP-019

Choose to initialise immediately and click Next.

replicaMP-020

Create the subscription and click Next.

replicaMP-021

Click Finish at the summary screen.

replicaMP-022

Once completed click Close.

replicaMP-024

To check the status the replication:

On the subscriber – right click the subscription under Local Subscriptions and choose View Synchronisation Status.

replicaMP-025

Currently a sync is in progress.

replicaMP-026

On the publisher – right click the publication under Local Publications and choose Launch Replication Monitor.

replicaMP-027

The job is currently running.

replicaMP-028

Create a new query on the <sitecode>_Repl database and execute.

exec sp_configure ‘clr enabled’, 1;
RECONFIGURE WITH OVERRIDE

replicaMP-029

Run the same query on the site database.

replicaMP-030

Configure the max text repl size

Both the site database server and replica server must support a Max Text Repl Size of 2 GB. To set this right click the server object in SQL Management Server and choose Properties.

replicaMP-031

Click Advanced and set the Max Text Replication Size to 2GB (2147483647 bytes).

replicaMP-032

Install the Management Point

Add in all pre-requisites for a Management Point as per the Technet documentation here.

When installing the Management Point choose to install a replica and enter the FQDN of the replica MP server and the database name of the replica DB (_REPL).

replicaMP-033

Check on the MP for successful installation of the role.

replicaMP-034

Site System Status will also confirm that the role is installed without an error.

replicaMP-035

Next enable Windows Authentication on the Management Point server.

replicaMP-036

Create a self signed certificate for the Database Replica Server

Open up PowerShell as administrator and run Set-ExecutionPolicy UnRestricted. Confirm with a Yes.

replicaMP-037

Copy and paste the following script and save as CreateMPReplicaCert.ps1 and save on the root of the system partition.

# Script for creating a self-signed certificate for the local machine and configuring SQL Server to use it.

Param($SQLInstance)

$ConfigMgrCertFriendlyName = "ConfigMgr SQL Server Identification Certificate"

# Get local computer name
$computerName = "$env:computername"

# Get the sql server name
#$key="HKLM:\SOFTWARE\Microsoft\SMS\MP"
#$value="SQL Server Name"
#$sqlServerName= (Get-ItemProperty $key).$value
#$dbValue="Database Name"
#$sqlInstance_DB_Name= (Get-ItemProperty $key).$dbValue

$sqlServerName = [System.Net.Dns]::GetHostByName("localhost").HostName 
$sqlInstanceName = "MSSQLSERVER"
$SQLServiceName = "MSSQLSERVER"

if ($SQLInstance -ne $Null)
{
    $sqlInstanceName = $SQLInstance
    $SQLServiceName = "MSSQL$" + $SQLInstance
}

# Delete existing cert if one exists
function Get-Certificate($storename, $storelocation)
{ 
    $store=new-object System.Security.Cryptography.X509Certificates.X509Store($storename,$storelocation) 
    $store.Open([Security.Cryptography.X509Certificates.OpenFlags]::ReadWrite) 
    $store.Certificates 
} 

$cert = Get-Certificate "My" "LocalMachine" | ?{$_.FriendlyName -eq $ConfigMgrCertFriendlyName} 
if($cert -is [Object])
{
    $store = new-object System.Security.Cryptography.X509Certificates.X509Store("My","LocalMachine") 
    $store.Open([Security.Cryptography.X509Certificates.OpenFlags]::ReadWrite) 
    $store.Remove($cert)
    $store.Close()
    
    # Remove this cert from Trusted People too...
    $store = new-object System.Security.Cryptography.X509Certificates.X509Store("TrustedPeople","LocalMachine") 
    $store.Open([Security.Cryptography.X509Certificates.OpenFlags]::ReadWrite) 
    $store.Remove($cert)
    $store.Close()    
}

# Create the new cert
$name = new-object -com "X509Enrollment.CX500DistinguishedName.1"
$name.Encode("CN=" + $sqlServerName, 0)

$key = new-object -com "X509Enrollment.CX509PrivateKey.1"
$key.ProviderName = "Microsoft RSA SChannel Cryptographic Provider"
$key.KeySpec = 1
$key.Length = 1024
$key.SecurityDescriptor = "D:PAI(A;;0xd01f01ff;;;SY)(A;;0xd01f01ff;;;BA)(A;;0x80120089;;;NS)"
$key.MachineContext = 1
$key.Create()

$serverauthoid = new-object -com "X509Enrollment.CObjectId.1"
$serverauthoid.InitializeFromValue("1.3.6.1.5.5.7.3.1")
$ekuoids = new-object -com "X509Enrollment.CObjectIds.1"
$ekuoids.add($serverauthoid)
$ekuext = new-object -com "X509Enrollment.CX509ExtensionEnhancedKeyUsage.1"
$ekuext.InitializeEncode($ekuoids)

$cert = new-object -com "X509Enrollment.CX509CertificateRequestCertificate.1"
$cert.InitializeFromPrivateKey(2, $key, "")
$cert.Subject = $name
$cert.Issuer = $cert.Subject
$cert.NotBefore = get-date
$cert.NotAfter = $cert.NotBefore.AddDays(3650)
$cert.X509Extensions.Add($ekuext)
$cert.Encode()

$enrollment = new-object -com "X509Enrollment.CX509Enrollment.1"
$enrollment.InitializeFromRequest($cert)
$enrollment.CertificateFriendlyName = "ConfigMgr SQL Server Identification Certificate"
$certdata = $enrollment.CreateRequest(0x1)
$enrollment.InstallResponse(0x2, $certdata, 0x1, "")

# Add this cert to the trusted peoples store
[Byte[]]$bytes = [System.Convert]::FromBase64String($certdata)

$trustedPeople = new-object System.Security.Cryptography.X509certificates.X509Store "TrustedPeople", "LocalMachine"
$trustedPeople.Open([Security.Cryptography.X509Certificates.OpenFlags]::ReadWrite)
$trustedPeople.Add([Security.Cryptography.X509Certificates.X509Certificate2]$bytes)
$trustedPeople.Close()

# Get thumbprint from cert
$sha = new-object System.Security.Cryptography.SHA1CryptoServiceProvider
$certHash = $sha.ComputeHash($bytes)
$certHashCharArray = "";
$certThumbprint = "";

# Format the bytes into a hexadecimal string
foreach($byte in $certHash)
{
    $temp = ($byte | % {"{0:x}" -f $_}) -join ""
    $temp = ($temp | % {"{0,2}" -f $_})
    $certHashCharArray = $certHashCharArray+ $temp;
}
$certHashCharArray = $certHashCharArray.Replace(' ', '0');

# SQL needs the thumbprint in lower case
foreach($char in $certHashCharArray)
{
    [System.String]$myString = $char;
    $certThumbprint = $certThumbprint + $myString.ToLower();
}

# Configure SQL to use this cert
$path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL"
$subKey = (Get-ItemProperty $path).$sqlInstanceName
$realPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\" + $subKey + "\MSSQLServer\SuperSocketNetLib"
$certKeyName = "Certificate"
Set-ItemProperty -path $realPath -name $certKeyName -Type string -Value $certThumbprint

# restart sql service
Restart-Service $SQLServiceName -Force

Since the Ps1 script is being run against the default instance of SQL, right click the file and choose ‘Run with PowerShell’. For other options on running the file check here.

replicaMP-038

Once the script has run check to ensure that the SQL Server Agent is running. If not then restart the Agent.

A certificate will reside in the Personal store of the local computer. This can be exported out if any other remote MPs are installed that use this replica DB.

replicaMP-039

Configure the SQL Server Service Broker

‘For System Center 2012 Configuration Manager SP1 and later:

To support client notification with a database replica for a management point, you must configure communication between the site database server and the database replica server for the SQL Server Service Broker. This requires you to configure each database with information about the other database, and to exchange certificates between the two databases for secure communication.’ – Source: TechNet

Open up SQL Management Studio on the Management Point server.

Connect to the database replica <sitecode>_REPL.

Run the following query:

ALTER DATABASE <sitecode>_Repl SET ENABLE_BROKER,
HONOR_BROKER_PRIORITY ON WITH ROLLBACK
IMMEDIATE

replicaMP-040

Next create another query and run the following command:

EXEC sp_BgbConfigSSBForReplicaDB
‘<FQDN of MP replica server>’, ‘<sitecode>_Repl’,
‘path to export\sql.cer’

This will export a certificate that will need to be imported into the site server db.

replicaMP-041

replicaMP-042

Copy and past the sql.cer onto the site database server. In SQL Management Studio connect to the ConfigMgr database (CM_<sitecode>) and run the following query:

EXEC sp_BgbConfigSSBForRemoteService
‘REPLICA’, ‘4022’,
‘path to cert file\sql.cer’,
”, ‘<sitecode>_REPL’

replicaMP-043

Next run another query on the site database server to export the certificate. Use the following command:

EXEC sp_BgbCreateAndBackupSQLCert
‘path to export\sqlpri.cer’

replicaMP-044

replicaMP-045

Copy the sqlpri.cer to the replica DB server. In SQL Management Studio on that server connect to the <sitecode>_REPL database and run the following query to import it.

EXEC sp_BgbConfigSSBForRemoteService
”, ‘4022’,
‘path to cert file\sqlpri.cer’

replicaMP-046

Now that the Service Broker is configured the traffic can be monitored using the following log files:

bgbmgr.log file on the site server and bgbserver.log on the Management Point.

What next for the replica MP?

For my next blog post I will be putting the replica MP to the test to show how it can be utilised to increase high availability in a ConfigMgr environment. I’ll be bringing down my site server and testing what still works and services can’t operate no matter what you try and do.

Thanks

A quick shout out to the following ConfigMgr experts whose material has been of great assistance when compiling this blog post.

Kent Agerlund (@agerlund) ECM MVP – for his comprehensive replica MP Configuration Guide

Brian Mason (@abetterpc) ECM MVP – for his awesome demo on replica MPs from back in 2012

TechNet documentation library for the step by step run through

and finally WMUG friend and ECM MVP Robert Marshall (@RobMVP) for some thoughtful insights on replica MPs.

Part Two to this blog series ‘Replica MP and ConfigMgr 2012 High Availability’ is available here

32 comments

  1. Hi Paul

    Great set of instructions, however after completing them I was seeing ‘Call to HTTPSendRequestSync failed for port 80 with status code 500’ errors in the mp_control.log and ‘ERROR: SQL exception when retrieve task from DB. Exception: The Execute permission was denied on the object ‘MP_BgbGetPushMessage’, database ‘P01_Repl’, schema ‘dbo’ -2146232060′ in the BgbServer.log.

    The only way I could resolve this was to grant the execute permission on the dbo schema to the NT Authority\System account. When running the SQL Server Agent service as local system on the replica is there something specific you need to do with the SQL permissions?

    Thanks in advance

    1. Hi Paul

      I have tried it in 1511 and 1606 and see the same problem, I tried the suggested fix in another of your blog posts but that didn’t resolve the issue.

      Steve

      1. Ah ok . I was going to point you to that ;-). There’s nothing specific I had to do regards permissions. Might be worth posting the issue on the SCCM TechNet forums to see if anyone else out there has seen this and is aware of it. Cheers Paul

      2. Thanks Paul

        I’ve posted on the forum already hopefully someone else has seen this issue as it is really beginning to bug me as I can replicate this every time just can’t work out what I’m missing. Out of interest what version of SQL have you done this on?

  2. Will do, I’m using SQL 2012 SP2 by the way. I’m beginning to wonder if something hasn’t replicated properly, on your replica DB do you see any of the smsdbroles replicated? Did you install the replica on Current Branch or did you do this initially on 2012 and then upgrade to current branch?

      1. Cheers Paul, I might build myself a 2012 environment to try and determine if it’s a bug in current branch.

        Thanks again for your help.

  3. Wow!!!! How did I missed this post, I am going to test this in SCCM current branch and with SQL 2016. Just one question, so if I managed restore/recover my Primary site server, does machine change back it’s original management point itself? Well, anyway I will test this. THANK YOU!!!

    1. Sandy

      Not sure what you mean by this. The idea is that no mp is assigned to a site server and replica mps are in the hierarchy. So in theory the clients aren’t aware the site server is offline as they’ll get policy from the remote mp replicas.

      1. I mean you have a Primary site server that has MP installed, when you take your Primary Site Server offline, client start tall to your mp replicas. Did I understand this correct? So if you put your Primary back online, does client talk to Primary site server MP or the MP replicas?

      2. It depends on how you have set up your boundary groups and preferred MP options. But to be honest if you went for the HA set up you would remove MP from the site server along with DP’s/App Catalog and SUPs.

      3. Oh, yes, I can assign mp in boundary groups, honestly I forgot that. I am actually thinking what is the best way to prepare if Primary Site Server is dead and need to do SCCM recovery. I know about the backup and recovery, I have practiced that many times. But I would like to have a setup that during recover Primary Site Server, client should still be able to download applications, install SUPs, or even deploy OS. I knew that should put DP, App Catalog and SUPs in site system, not Primary site server, so I should have MP in each site systems that are same subnet as client? Not put MP in the Primary Site Server?

      4. You can get away with the MP on the site server. If it’s down clients will quickly move to another MP. It’s the other roles for sure that you need to keep off the box.

  4. Hello again. Build up my test lab again and test this today. Using SCCM TP1609, SQL 2016. I only have time tested Application and package, both works with new software center, App Catalog doesn’t work, as you have tested already. I only have problem when tried to use “View Synchronisation Status”, SQL gave me error message “Cannot apply value ‘null’ to property ServerInstance: Value cannot be null.. (Micorosoft.SqlServer.ConnectionInfo),”. But replica seems works, I checked Policy counts, that matched.

  5. I figured it out, kind of. It is smss 2016 problem. “View Synchronisation Status” oopened nicely when I use smss 2014.

  6. Need some help… I uninstall the replica MP because need to update SCCM. When I tried to build replica MP again, I stuck in this step EXEC sp_BgbConfigSSBForRemoteService ‘REPLICA’, ‘4022’, ‘C:\sql.cer’, ‘CMRMP01.z-it.com’, ‘ZIT_REP’.

    Error:
    Msg 102, Level 15, State 1, Line 15
    Incorrect syntax near ‘ConfigMgrEndpointLoginREP’.
    ERROR: Failed to setup SSB for remote service REP

    Do I need to do this Service Broker again?

      1. When you ran Disable Publishing and Distribution did it remove the publication? Are the subscriptions on the replica DB’s still there?

  7. I removed publication and subscription manualy, then Disable Publishing and Distribution. Only the broker certificate step has problem…

    1. Sandy – I’m not sure what steps you have performed but if you Disable Publishing and Distribution this will remove the publication. It should also remove the subscriptions. I updated to 1610 a couple of days ago and it failed to remove the subscriptions so I manually removed these.

      Afterwards you have to run the SP to recreate the publication

      Use CM_
      Go
      exec spCreateMPreplicaPublication

      and then recreate the subscriptions back to the publisher.

      You don’t have to re-run all the SQL broker parts of the install again

      Do you have a valid publisher and subscriptions and is the replication traffic flow all good?

  8. ok. then I don’t have any issues. I only wasn’t sure about the SQL broker, since it didn’t allow me to import those certifcation again in the SQL broker. I thought I need to do all those steps again, include SQL broker. 🙂

  9. HI,

    Thanks for this, great stuff.

    One question. Does the MP have to be installed to the replica SQL Server or can they be separate Servers?

Leave a Reply to Sandy Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s