The following steps are how I moved an existing Windows Server 2008 R2/SQL 2008 ConfigMgr database residing on the site server to a separate standalone install on Windows Server 2016/SQL 2016.
Install SQL 2016, only “Database Engine Services” and “Reporting Services – Native” need to be installed.
If you start your SQL services as a domain user, you will need to register the SPNs for the user. Use
setspn for this using both the NetBIOS name and FQDN of the SQL Server
setspn -a MSSQLSvc/SERVERNAME:1433 wascodst\SMSAdmin
setspn -a MSSQLSvc/SERVERNAME.DOMAIN.COM:1433 wascodst\SMSAdmin
On the new SQL Server open the ports for SQL and WMI between the site server and the new SQL server.
New-NetFirewallRule -DisplayName "SQL Server TCP 1433" -Direction Inbound –LocalPort 1433 -Protocol TCP -Action Allow -RemoteAddress "SITESERVERIP"
New-NetFirewallRule -DisplayName "SQL Server TCP 4022" -Direction Inbound –LocalPort 4022 -Protocol TCP -Action Allow -RemoteAddress "SITESERVERIP"
Set-NetFirewallRule -DisplayName “Windows Management Instrumentation (WMI-In)” -Enabled true -RemoteAddress "SITESERVERIP"
Next, add the computer account of the ConfigMgr Site Server to the Administrators group on the new SQL 2016 server.
The Common Language Runtime (CLR) needs to be enabled on the SQL Server. In SQL Management Studio, run the following:
sp_configure 'clr enabled',1
You will also need to create a self-signed certificate on the SQL server for secure communication, you can do this with PowerShell.
New-SelfSignedCertificate -DnsName FQDN.DOMAIN.COM -CertStoreLocation cert:\LocalMachine\My -FriendlyName SQLServerCert -KeySpec KeyExchange -NotAfter $(Get-Date).AddYears(+10)
This will create a self-signed certificate with an expiration of 10 years.
Open SQL Server Configuration Manager and under SQL Server Network Configuration, Properties, Certificate tab, select the newly-created certificate.
Now restart the SQL Service.
This certificate will also need to be added to the ConfigMgr site server.
Open the Certificates MMC and export the certificate from
Local Computer\Personal Certificates store.
Transfer the certificate to the ConfigMgr site server and import it to the
Trusted Root Certification Authorities store.
Importing the certificate corrects an error you might see in
C:\ConfigMgrSetup.log during the database move. Those errors would be:
***Failed to connect to the SQL Server, connection type: SMS ACCESS.
INFO: SQL Connection failed. Connection: SMS ACCESS, Type: Secure
*** [-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: the certificate chain was issued by an authority that is not trusted.
*** [-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection
The next step is to stop the site services on the site server:
C:\Program Files\Microsoft Configuration Manager\bin\x64\00000409\preinst.exe /stopsite
Now take a backup of the SQL database for the site server. Right-click the Database in SSMS and select All Tasks, Backup.
Once the backup is complete, move this file to the destination SQL server, open up SSMS and Restore the Database.
On the destination SQL Server, a few options will need to be turned back on after restoring, run the following query:
ALTER DATABASE dbname SET ENABLE_BROKER
ALTER DATABASE dbname SET TRUSTWORTHY ON
ALTER DATABASE dbname SET HONOR_BROKER_PRIORITY ON;
dbname is your restore ConfigMgr SQL database.
To verify that the options were turned on successfully, you can run:
select is_trustworthy_on, is_broker_enabled, is_honor_broker_priority_on from sys.databases where name = 'dbname'
To complete the move, we need to go back to the ConfigMgr site server and run ConfigMgr Setup from the Start Menu.
Choose Site Maintenance
Choose Modify SQL Server Configuration
And set the FQDN of your new SQL server install
If that is successful, your database should now be operational.
Note: I encountered an issue where I was getting the following error after running site maintenance to move the database:
ERROR: Failed to find the file C:\Program Files\Microsoft Configuration Manager\CMUStaging\0D256560-ED2C-45B5-8D75-4D38AB3F758C\redist\msxml6_x64.msi
I fixed this by creating the folder:
mkdir "C:\Program Files\Microsoft Configuration Manager\CMUStaging\0D256560-ED2C-45B5-8D75-4D38AB3F758C\redist"
Then re-downloading the prerequisite files with:
C:\Program Files\Microsoft Configuration Manager\bin\x64\setupdl.exe
Then copying them to the folder created above.