1. On the site database server, set the SQL Server Agent to automatically start.

2. On the site database server, create a local user group with the name ConfigMgr_MPReplicaAccess. You must add the computer account for each database replica server that you use at this site to this group to enable those database replica servers to synchronize with the published database replica.

3. On the site database server, configure a file share with the name ConfigMgr_MPReplica.

4. Add the following permissions to the ConfigMgr_MPReplica share:

   Note
   If the SQL Server Agent uses an account other than the local system account, replace SYSTEM with that account name in the following list.

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

5. Use SQL Server Management Studio to connect to the site database and run the following stored procedure as a query: spCreateMPReplicaPublication

When the stored procedure completes, the site database server is configured to publish the database replica.

1. On the database replica server, set the SQL Server Agent to automatic startup.

2. On the database replica server, use SQL Server Management Studio to connect to the local server, browse to the Replication folder, click Local Subscriptions, and select New Subscriptions to start the New Subscription Wizard:

   1. On the Publication page, in the Publisher list box, select Find SQL Server Publisher, enter the name of the sites database server, and then click Connect.
      
      
   2. Select ConfigMgr_MPReplica, and then click Next.
      
      
   3. On the Distribution Agent Location page, select Run each agent at its Subscriber (pull subscriptions), and click Next.
      
      
   4. On the Subscribers page do one of the following:
      
      
      • Select an existing database from the database replica server to use for the database replica, and then click OK.
        
        
      • Select New database to create a new database for the database replica. On the New Database page, specify a database name, and then click OK.
        
        
   5. Click Next to continue.
      
      
   6. On the Distribution Agent Security page, click the properties button (.…) in the Subscriber Connection row of the dialog box, and then configure the security settings for the connection.
      
      

      Tip
      The properties button, (….), is in the fourth column of the display box.

      Security settings:
      
      
      • Configure the account that runs the Distribution Agent process (the process account):
        
        
        • If the SQL Server Agent runs as local system, select Run under the SQL Server Agent service account (This is not a recommended security best practice.)
          
          
        • If the SQL Server Agent runs by using a different account, select Run under the following Windows account, and then configure that account. You can specify a Windows account or a SQL Server account.
          
          

        Important
        You must grant the account that runs the Distribution Agent permissions to the publisher as a pull subscription. For information about configuring these permissions, see Distribution Agent Security in the SQL Server TechNet.Library.

      • For Connect to the Distributor, select By impersonating the process account.
        
        
      • For Connect to the Subscriber, select By impersonating the process account.
        
        
      After you configure the connection security settings, click OK to save them, and then click Next.
      
      
   7. On the Synchronization Schedule page, in the Agent Schedule list box, select Define schedule, and then configure the New Job Schedule. Set the frequency to occur Daily, recur every 5 minute(s), and the duration to have No end date. Click Next to save the schedule, and then click Next again.
      
      
   8. On the Wizard Actions page, select the check box for Create the subscriptions(s), and then click Next.
      
      
   9. On the Complete the Wizard page, click Finish, and then click Close to complete the Wizard.
      
      

3. Review the synchronization status to validate that the subscription is successful:

   • On the subscriber computer:
     
     
     • In SQL Server Management Studio, connect to the database replica server and expand Replication.
       
       
     • Expand Local Subscriptions, right-click the subscription to the site database publication, and then select View Synchronization Status.
       
       
   • On the publisher computer:
     
     
     • In SQL Server Management Studio, connect to the site database computer, right-click the Replication folder, and then select Launch Replication Monitor.
       
       

4. To enable common language runtime (CLR) integration for the database replica, use SQL Server Management Studio to connect to the database replica on the database replica server, and run the following stored procedure as a query: exec sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE

5. For each management point that uses a database replica server, add that management points computer account to the local Administrators group on that database replica server.

   Tip
   This step is not necessary for a management point that runs on the database replica server.

The database replica is now ready for a management point to use.

1. On the database replica server, open a PowerShell command prompt with administrative privileges, and then run the following command: set-executionpolicy UnRestricted

2. Copy the following PowerShell script and save it as a file with the name CreateMPReplicaCert.ps1. Place a copy of this file in the root folder of the system partition of the database replica server.

   Copy Code

   # 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 hex 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

3. On the database replica server, run the following command that applies to the configuration of your SQL Server:

   • For a default instance of SQL Server: Right-click the file CreateMPReplicaCert.ps1 and select Run with PowerShell. When the script runs, it creates the self-signed certificate and configures SQL Server to use the certificate.
     
     
   • For a named instance of SQL Server: Use PowerShell to run the command %path%\CreateMPReplicaCert.ps1 xxxxxx where xxxxxx is the name of the SQL Server instance.
     
     
   • After the script completes, verify that the SQL Server Agent is running. If not, restart the SQL Server Agent.
     
     

1. Perform the following steps on the database replica server to export the server’s self-signed certificate:

   1. Click Start, click Run, and type mmc.exe. In the empty console, click File, and then click Add/Remove Snap-in.
      
      
   2. In the Add or Remove Snap-ins dialog box, select Certificates from the list of Available snap-ins, and then click Add.
      
      
   3. In the Certificate snap-in dialog box, select Computer account, and then click Next.
      
      
   4. In the Select Computer dialog box, ensure that Local computer: (the computer this console is running on) is selected, and then click Finish.
      
      
   5. In the Add or Remove Snap-ins dialog box, click OK.
      
      
   6. In the console, expand Certificates (Local Computer), expand Personal, and select Certificates.
      
      
   7. Right-click the certificate with the friendly name of ConfigMgr SQL Server Identification Certificate, click All Tasks, and then select Export.
      
      
   8. Complete the Certificate Export Wizard by using the default options and save the certificate with the .cer file name extension.
      
      

2. Perform the following steps on the management point computer to add the self-signed certificate for the database replica server to the Trusted People certificate store on the management point:

   1. Repeat the preceding steps 1.a through 1.e to configure the Certificate snap-in MMC on the management point computer.
      
      
   2. In the console, expand Certificates (Local Computer), expand Trusted People, right-click Certificates, select All Tasks, and then select Import to start the Certificate Import Wizard.
      
      
   3. On the File to Import page, select the certificate saved in step 1.h, and then click Next.
      
      
   4. On the Certificate Store page, select Place all certificates in the following store, with the Certificate store set to Trusted People, and then click Next.
      
      
   5. Click Finish to close the wizard and complete the certificate configuration on the management point.
      
      

1. Use SQL Server Management Studio to connect to database replica server database, and then run the following query to enable the Service Broker on the database replica server: ALTER DATABASE <Replica Database Name> SET ENABLE_BROKER, HONOR_BROKER_PRIORITY ON WITH ROLLBACK IMMEDIATE

2. Next, on the database replica server, configure the Service Broker for client notification and export the Service Broker certificate. To do this, run a SQL Server stored procedure that configures the Service Broker and exports the certificate as a single action. When you run the stored procedure, you must specify the FQDN of the database replica server, the name of the database replicas database, and specify a location for the export of the certificate file.

   Run the following query to configure the required details on the database replica server, and to export the certificate for the database replica server: EXEC sp_BgbConfigSSBForReplicaDB '<Replica SQL Server FQDN>', '<Replica Database Name>', '<Certificate Backup File Path>'

   Note
   When the database replica server is not on the default instance of SQL Server, for this step you must specify the instance name in addition to the replica database name. To do so, replace <Replica Database Name> with <Instance name\Replica Database Name>.

   After you export the certificate from the database replica server, place a copy of the certificate on the primary sites database server.

3. Use SQL Server Management Studio to connect to the primary site database. After you connect to the primary sites database, run a query to import the certificate and specify the Service Broker port that is in use on the database replica server, the FQDN of the database replica server, and name of the database replicas database. This configures the primary sites database to use the Service Broker to communicate to the database of the database replica server.

   Run the following query to import the certificate from the database replica server and specify the required details: EXEC sp_BgbConfigSSBForRemoteService 'REPLICA', '<SQL Service Broker Port>', '<Certificate File Path>', '<Replica SQL Server FQDN>', '<Replica Database Name>'

   Note
   When the database replica server is not on the default instance of SQL Server, for this step you must specify the instance name in addition to the replica database name. To do so, replace <Replica Database Name> with <Instance name\Replica Database Name>.

4. Next, on the site database server, run the following command to export the certificate for the site database server: EXEC sp_BgbCreateAndBackupSQLCert '<Certificate Backup File Path>'

   After you export the certificate from the site database server, place a copy of the certificate on the database replica server.

5. Use SQL Server Management Studio to connect to the database replica server database. After you connect to the database replica server database, run a query to import the certificate and specify the site code of the primary site and the Service Broker port that is in use on the site database server. This configures the database replica server to use the Service Broker to communicate to the database of the primary site.

   Run the following query to import the certificate from the site database server: EXEC sp_BgbConfigSSBForRemoteService '<Site Code>', '<SQL Service Broker Port>', '<Certificate File Path>'

A few minutes after you complete the configuration of the site database and the database replica database, the notification manager at the primary site sets up the Service Broker conversation for client notification from the primary site database to the database replica.