Hello, my name is Matt Garton and I am part of the service engineering group within the Enterprise Client & Mobility (ECM) group here at Microsoft. This blog is part of a series of posts that aims to answer, “How does Microsoft do it” and today I am going to discuss how we are migrating our ConfigMgr workloads to Azure. This post will focus on how we provide high availability for the WSUS Database (SUSDB) utilizing Azure and SQL Always On Availability Groups.
Recently, with the support of SQL Always On for ConfigMgr, it has been a goal to move our database infrastructure into Azure and SQL Always On, in order to provide high availability. Stay tuned for a future post on how we accomplished moving the ConfigMgr site database to an Always On Availability Group hosted in Azure. As with many ConfigMgr environments, our on premise SQL Servers host a variety of databases including the WSUS database. As additional workloads are moved to Azure, and on premise servers are decommissioned, the need arises to provide the same high availability as on premise clustered SQL servers. The aim of this blog is to outline the process of moving the SUSDB into a SQL Always On Availability Group (AG) hosted in Azure. It applies to both ConfigMgr customers using WSUS for software updates and customers using standalone WSUS who may want to move the database to Always On for high availability in Azure. Let’s get started!
This post assumes that you have already created a SQL Always On Availability Group running in Azure. If not, please review the following, which will walk you through the creation of an Availability Group in Azure:
Introducing SQL Server Always On availability groups on Azure virtual machines
Note: Successful configuration and use of availability groups requires you to be comfortable with configuring SQL Server and SQL Server availability groups. The procedures outlined in this topic rely on additional documentation and procedures found in the SQL Server/ Azure documentation library.
Step 1: Verify necessary components are in place
A quick overview of the environment is necessary to understand what infrastructure is in place. For this example, we have a ConfigMgr primary site running on one Azure VM and the site database in an availability group running on dedicated SQL virtual machines. Two software update points (SUPs) have been configured as Azure VMs and are running in an Availability Set. An Azure Availability Set allows us to group similarly configured virtual machines to create additional redundancy, for more information on availability sets see the following:
How to configure an availability set for Windows virtual machines in the classic deployment model
The SUPs are currently sharing the same SUSDB/ WSUS content store. The goal is to move the SUSDB that is currently being shared from a on premise SQL cluster to an Availability Group running in Azure. Since we already have the site server database in Azure, we will create a separate availability group for the SUSDB and update the WSUS machine(s) with the necessary configuration changes to point them to the new AG listener that is created.
Step 2: Create new availability group and listener for SUSDB
Similar to creating an availability group for the ConfigMgr database, we will create a separate AG that will contain the SUSDB. In SQL Mgmt Studio on the primary SQL node, navigate to Object Explorer, go to AlwaysOn High Availability > Availability Groups, right-click and select New Availability Group Wizard.
Go through the wizard, naming the availability group and use a database that meets the prerequisites. In this case I created a blank database named StagingDB and set recovery model to Full and have already taken a full backup. On the Replicas tab, ensure the secondary SQL node has been added, then select Automatic Failover, Synchronous Commit, and Readable Secondary set to Yes. Select Full data synchronization and add a shared network location where both replicas have Modify permissions. Continue through the validation and ensure the availability group is created successfully.
Note: There is a warning for the listener configuration because the availability group listener has not yet been configured. You can safely ignore this warning on Azure virtual machines as the listener is created after configuring the Azure load balancer.
Once created, we will use the existing load balancer in Azure and add an additional IP address for our new AG. If this is the first AG being created, then you will need to create the Azure Load Balancer. The following link will walk through the process of using PowerShell to add an IP address to an existing load balancer. Each IP address on a load balancer will require its own load balancing rule, probe, and front end port.
Example script: Add an IP address to an existing load balancer with PowerShell
Once we have an additional IP address added to the load balancer, we can configure the listener. You can use the above link to walk through the remaining steps to create a new availability group listener for the SUSDB. Test the connection to the listener and ensure you are able to connect before proceeding.
Now that we have a separate AG and listener created, we can move the SUSDB to our Always On configuration.
Step 3: Move SUSDB to SQL Always On Availability Group
The next section outlines how to get the SUSDB into our newly created availability group. For this step, we will need to connect to the SUSDB via SQL Mgmt Studio. Determine if you have installed WSUS database on WID or SQL. If installed on WID and running WinSvr 2012 or newer, you can connect via the following named pipe (you may need to run SQL Mgmt Studio as Admin to connect):
Note: Consider running WSUS cleanup prior to moving the database. A smaller database for this step will reduce the amount of down time required for WSUS when migrating to an availability group.
On the WSUS server(s), first stop IIS / WSUS services. Once connected to the SUSDB in SQL Mgmt Studio, change database recovery model to Full by going to database Properties > Options and setting the Recovery Model: Full.
Then backup the database and specify a local path and name SUSDB.bak and start the back up. Once completed, copy the back up over to a local directory on the Primary Replica for the availability group that was created. Navigate to the primary SQL node of the availability group in SQL Mgmt Studio and right-click Databases and select Restore Database. Select Device then browse and add the .bak file that was copied locally and restore the database. Once restored, the WSUS server login needs to be created, open a new query window and create the login for the WSUS machine(s):
Create Login [domain\machineName$] From Windows
Once login(s) are created, open Properties for the login(s) and under User Mapping select db_datareader, public, and webService. Ensure you Create Login for the WSUS machine(s) on the secondary SQL server node once the SUSDB has been moved into the availability group.
At this point we can now move the SUSDB into the availability group. First, navigate to Availability Groups > Availability Databases and remove the staging database that was added previously. Now right-click and select Add Database, select the SUSDB and for data synch network location, ensure network location added during availability group creation is correct. Follow prompts in wizard and ensure you are able to connect to secondary SQL node and accept remaining defaults. For all steps, you can copy script to automate process for remaining SUSDBs if your WSUS infrastructure is in a hierarchy with multiple databases that will be moved to availability groups. Once successfully added, ensure you connect to secondary SQL node and create the WSUS server machine login(s).
Step 4: Update WSUS Server
The final step of the process is to update the WSUS server(s) to point to the AG listener that is now running the SUSDB. Ensure that IIS/ WSUS services are still stopped. Before making any changes, ensure you have backed up the below registry keys on the WSUS server(s). Then we will configure the following keys (if SUSDB was on SQL previously you can update only the SQLServerName with new listener name):
- HKLM\Software\Microsoft\Update Services\Server\Setup: SqlServerName (reg_expand_sz)
- Specify the Availability Group Listener that was created
- HKLM\Software\Microsoft\Update Services\Server\Setup\Installed Role Services: UpdateServices-WidDatabase (reg_dword)
- Rename the key to UpdateServices-Database
- This key only needs to be changed if migrating from WID. This will ensure WsusUtil does not attempt to service the WID rather than the SQL Instance.
Finally, we can restart IIS / WSUS services and open the WSUS Admin console to ensure our connection is successful. You can also monitor %ProgramFiles%\Update Services\LogFiles and review the SoftwareDistribution log as the WSUS service starts to verify connection to the database is restored.
Once connected, open the WSUS console and begin to validate that WSUS is working as expected. If you attempt to failover SQL nodes and see connection errors in the SoftwareDistribution log, ensure that you have added the appropriate WSUS server machine login(s) on the secondary SQL node for the SUSDB availability group.
Give yourself a high five at this point as you have now successfully configured the SUSDB in an Azure SQL Always On Availability Group!
There are some important considerations when moving the SUSDB into an availability group. Servicing the database through WSUSUtil command line utility will no longer run and will produce the below error message. As part of this process, the SUSDB will be placed into single user mode. A database participating in an availability group or mirroring session, will not be able to be placed into single user mode. At times where the SUSDB needs to be serviced, you will need to move the SUSDB out the availability group, service the database, and move back into the availability group. This can be accomplished by using the SQL AG wizard to move the database out of the availability group. Then updating the WSUS server(s) registry keys following the process outlined in Step 4, renaming to the primary node SQL server that is now running the SUSDB. Finally, servicing the database and using the above steps to move back into the AG and update WSUS with AG listener value.
From the ConfigMgr perspective, it is recommended to use a separate availability group that does not host the CM DB. If the CM and SUSDB availability groups are being hosted on the same SQL server replicas, you should ensure that the servers are configured to handle the combined database load if they were separate servers previously.
I hope this blog has helped to outline the process of moving the SUSDB to an Availability Group. If you have any questions or feedback let us know in the comments below!
6/5/2017 Update: Edited the "Update WSUS Server" section to reflect official SUSDB migration steps found here:
Migrating the WSUS Database from WID to SQL
Disclaimer: This WSUS configuration may not be explicitly supported and this blog post is for informational purposes. This post is provided “AS IS” with no warranties and confers no rights.