ConfigMgr 1606 Move Database from SQL 2008 to SQL 2016 on separate server

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
reconfigure

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.

2017-03-14 09_54_5 - VMware Remote Console

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.

3457885678

23452476545674567

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
*** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]SSL Provider: the certificate chain was issued by an authority that is not trusted.
*** [08001][-2146893019][Microsoft][SQL Server Native Client 11.0]Client unable to establish connection

456346745874768

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.

2354634673

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;

Where 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

7689678945623

Choose Modify SQL Server Configuration

31452363467428

And set the FQDN of your new SQL server install

213413466458357

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.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s