Wanting to move your current Database on ConfigMgr to another Server? Let's take a look at how we can do that today.
The reason why I am doing this is because made a bad decision about splitting up the SQL DB and Primary Site instead of putting them in a single box. The issue here is that every time I want to start my ConfigMgr lab I have to turn on 2 VMs instead of 1 and that it takes awhile for the services to be ready. So to in order to fix the issue and have an optimized performance, I will be migrating the ConfigMgr DB back to the primary Site Server.
I have already setup a SQL Server on the Primary site so if you haven't done that you'll need to to it first.
Things to note:
1) You can only migration to the same SQL Version or higher
2) During the migration ConfigMgr Services will not be available
Let start the migration shall we?
Stopping ConfigMgr Services
1. Open command prompt
2. Change directory to your ConfigMgr Installation Directory (C:\Program Files\Microsoft Configuration Manager\bin\X64\00000409 in my environment)
3. Run the command preinst.exe /stopsite
3. Run the following Powershell commands
Stop-Service -Name AI_UPDATE_SERVICE_POINT Stop-Service -Name CONFIGURATION_MANAGER_UPDATE Stop-Service -Name SMS_NOTIFICATION_SERVER
4. Ensure that the services are stopped by running the following Powershell commands
Get-Service -Name AI_UPDATE_SERVICE_POINT Get-Service -Name CONFIGURATION_MANAGER_UPDATE Get-Service -Name SMS_NOTIFICATION_SERVER
Backing up the Database
- Launch Microsoft SQL Server Management Studio
2. Expand Database, Right Click on the ConfigMgr Database and select Task followed by Back up...
3. Click Add...
4. Click the ... Button
5. Enter a filename, Click OK
6. Click OK
7. Select Media Options and check the following and click OK to start the backup process
- Verify the backup when finished
- Perform checksum before writing to media
8. Click OK
9. Once Backup is completed copy the .bak file to the Primary Server or the new SQL Server
Restoring the Database on the New SQL Server
- On the new SQL Server, Launch Microsoft SQL Server Management Studio, right click Databases, select Restore Database...
2. Select Backup as Device and add the location of the .bak file and Click on Files
3. Change the location where you wish to store the files if needed. Click OK when done
4. Click OK
5. Run the following SQL Query to enable CLR
EXECUTE sp_configure 'clr enabled', 1; RECONFIGURE WITH OVERRIDE;
6. Run the following SQL Query to enable the SQL Broker on the Site database
Note: Change SCM to your site code
USE master; GO ALTER DATABASE CM_SCM SET ENABLE_BROKER GO
7. Run the following to set the Site Database as trustworthy
USE master; GO ALTER DATABASE CM_SCM SET TRUSTWORTHY ON GO
8. Run the following to set the Database to honor the HONOR_BROKER_PRIORITY
USE master; GO ALTER DATABASE CM_SCM SET HONOR_BROKER_PRIORITY ON; GO
9. Run the following to verify the configuration
select name, collation_name, user_access_desc, is_read_only, state_desc, is_trustworthy_on, is_broker_enabled, is_honor_broker_priority_on from sys.databases
Reconfigure ConfigMgr to use the new Database
- On the ConfigMgr Primary Server, run Configuration Manager Setup
2. Click Next >
3. Select Perform site maintenance or reset this site and click Next >
4. Select Modify SQL Server Configuration and click Next >
5. Update the SQL Server name and Click Next >
6. Click on View Log to monitor the status
7. Click Close when setup is completed
Once you have reached this step, the SQL Database migration is completed.
Post Database migration Check
Check sitecomp.log and verify that bootstrap operations are completed before restarting the server