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.

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

Modify the SQL installation to add replication.

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.

On the site database server create a share called ConfigMgr_MPReplica

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

Configuring the replica DB and replication

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

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

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

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.

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

When the New Subscriptions wizard opens click Next.

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

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

Select ‘ConfigMgr_MPReplica’ and click Next.

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

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

Click the… button.

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

Set to run continuously and click Next.

Choose to initialise immediately and click Next.

Create the subscription and click Next.

Click Finish at the summary screen.

Once completed click Close.

To check the status the replication:

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

Currently a sync is in progress.

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

The job is currently running.

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

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

Run the same query on the site database.

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.

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

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).

Check on the MP for successful installation of the role.

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

Next enable Windows Authentication on the Management Point server.

Create a self signed certificate for the Database Replica Server

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

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.

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.

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

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.

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’

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’

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’

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