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

Advertisements