ConfigMgr 1702+: Adding a new node (Secondary Replica) to an existing SQL AO AG

[umaikhan]

First published on TECHNET on Jul 17, 2017
https://blogs.technet.microsoft.com/umairkhan/2017/07/17/configmgr-1702-adding-a-new-node-secondary-...

Scenario:

We already have a working Primary and secondary replica and we know that ConfigMgr 1702 does support an extra replica i.e. 2 ^nd Secondary replica. So we are adding a freshly built node as a secondary replica.

The documentation we have around this can be found below.

https://docs.microsoft.com/en-us/sccm/core/servers/deploy/configure/configure-aoag#add-and-remove-r...

To add a new replica member

1. Add the new server as a secondary replica to the availability group. See Add a Secondary Replica to an Availability Group (SQL Server) in the SQL Server documentation library.
2. Stop the Configuration Manager site by running Preinst.exe /stopsite. See Hierarchy Maintenance Tool.
3. Use SQL Server to create a backup of the site database from the primary replica, and then restore that backup to the new secondary replica server. See Create a Full Database Backup and Restore a Database Backup using SSMS in the SQL Server documentation.
4. Configure each secondary replica. Perform the following actions for each secondary replica in the availability group:

1. Ensure the computer account of the site server is a member of the Local Administrators group on each computer that is a member of the availability group.
2. Run the verification script from the prerequisites to confirm that the site database on each replica is correctly configured.
3. If it’s necessary to configure the new replica, manually failover the primary replica to the new secondary replica and then make the required settings. See Perform a Planned Manual Failover of an Availability Group in the SQL Server documentation.

5. Restart the site by starting the Site Component Manager (sitecomp) and SMS_Executive services.

Issues with the above approach:

Now what we do not consider above is the fact that there are many things which are critical to us that is not synched when a new replica is set up. The SQL replica will not sync any instance\Server level objects but it only sync database level objects.

So what do we miss synching?

1. ConfigMgrEndpoint (This is the SSB endpoint and would not be synced as it is a server level object)
2. ConfigMgr SSB certificates (Same case as the above)
3. ConfigMgr Broker Logins ( The users that scope at the DB level are synched but the logins won’t be synched as they are Server level.)
4. ConfigMgr SQL Identification Cert (This is used to authenticate the Site server while connecting to SQL DB. We don’t have to manually create this as SiteComp has a check to create this. But does require manual intervention and restarting SiteComp twice to create this.)

Hence working on this issue we did come on the things to do for such addition. After some good back and forth troubleshooting, Sean Mahoney helped to get this check list compiled. Below is the scenario for adding a new node for a Primary Site. It is highly recommended to open a CSS case so that we can help you perform these things as they are dynamic dependent on where the steps are performed.

1. Validate that Site server is a Local Admin on SQL Server
2. Validate there is a SPN for new SQL Node
3. Validate SQL Aliases on SQL Server
4. Validate SQL Aliases on Site Server
5. Add New node to Windows Failover Cluster
6. Enable Always On to SQL Service on new Replica node and restart SQL Service
7. Backup SSB Cert on CAS

USE MASTER
Backup Certificate ConfigMgrEndpointCert TO FILE='C:\Temp\CAS.CER'

8. Copy Certificate to Primary
9. Add Site Server as New Replica DB:

CREATE LOGIN [DOMAIN\SITESERVER$] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\SITESERVER$]
ALTER SERVER ROLE [securityadmin] ADD MEMBER [DOMAIN\SITESERVER$]

9. Stop Transaction Log Backup
10. Add New SQL Replica to AO AG
11. Stop CM Site
12. Failover to New Replica and run script:

DECLARE @DBNAME NVARCHAR(128)
SELECT @DBNAME = 'CM_<Site>' -- DBName
 
EXECUTE ('
USE ' + @DBNAME + '
 
ALTER DATABASE ' + @DBNAME + ' SET HONOR_BROKER_PRIORITY ON
ALTER DATABASE ' + @DBNAME + ' SET TRUSTWORTHY ON
 
EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE;
 
EXEC sp_configure ''clr enabled'', 1;
RECONFIGURE;
 
EXEC sp_configure ''max text repl size (B)'', 2147483647;
RECONFIGURE;
 
EXEC sp_changedbowner ''sa'' ;
')
 

13. Start Transaction Log backup job
14. Fail back to original Replica
15. Start Services (had to restart sitecomp 2x to get SQL Certificates created)
16. Validate ConfigMgr SQL Server Identification Certificate is in Personal Store of new Replica SQL Server
17. Validate ConfigMgr SQL Server Identification Certificate is in the Trusted People certificate store on the Site Server
18. Manually Add the Certificate to the SQL Server Protocol using SQL Server Configuration Manager and Restart SQL Service on new Replica
19. Fail over to new Replica (This add the SSB Certificate to the CM Database)
20. Add SQL Broker Endpoint

declare @XMLParam XML;
select @XMLParam= Body from XMLConfigStore where name = 'ServiceBrokerConfiguration'
exec spConfigureServiceBroker @XMLConfig = @XMLParam, @SSBPort = 4022, @SqlCertFile = 'd:\CAS.cer', @ParentSiteCode ='<CASSiteCode>' , @ParentSiteSqlServerFqdn = '<CAS SQL Server FQDN>' 

21.    Export SSB Certificate from Primary

USE MASTER
Backup Certificate ConfigMgrEndpointCert TO FILE='C:\Temp\<PRISiteCode>.CER'

22. Copy Cert to CAS SQL Server

Assuming CAS is also running SQL AO AG with two nodes.

23. Import New Primary Site SSB Certificate to CAS Node1

Exec dbo.spCreateSSBLogin
@EndPointLogin='ConfigMgrEndpointLogin<PRISiteCode>',
@DestSiteCode='<PRISiteCode>',
@DestSiteCertFile='C:\<PRISiteCode>.cer',
@EndpointName='ConfigMgrEndpoint',
@DestSqlServerFqdn='<PRISQLNodeFQDN>'

24. Fail CAS over to Node 2
25. Import New Primary Site SSB Certificate to CAS Node 2

Exec dbo.spCreateSSBLogin
@EndPointLogin='ConfigMgrEndpointLogin<PRISiteCode>',
@DestSiteCode='<PRISiteCode>',
@DestSiteCertFile='C:\<PRISiteCode>.cer',
@EndpointName='ConfigMgrEndpoint',
@DestSqlServerFqdn='<PRISQLNodeFQDN>'

Repeat steps for third node if needed.

Now if the Node addition scenario happens to be a CAS site then the certificates from all primaries will need to be reimported on the new node.

We are working on to change this behavior for more automated way in ConfigMgr 1710.

Hope it helps!

Sean Mahoney | Sr. PFE, Microsoft
Umair Khan | SEE, Microsoft

Disclaimer: This posting is provided “AS IS” with no warranties and confers no rights.