See all of the top support solutions for our most common issues here
Hi everyone, Anurag Shukla here. The following article discusses how to move the Configuration Manager (ConfigMgr 2007) site database (DB) from a SQL Server 2005 SP2 based computer to a SQL Server 2008 based computer. Note that this may not be the only way to successfully migrate the DB but it’s a method I’ve used successfully in the past.
Existing SQL Server 2005 SP2 computer: PS1SQL Site Server: PS1SRV New SQL Server 2008 computer: PS1SSQL08
I am using a Windows Server 2003 computer for PS1SSQL08 which is where I installed SQL Server 2008. While I was installing the SQL 2008 on PS1SSQL08 I also added the following users as the part of installation:
This is a DOMAIN\Admin account for my domain.
PS1 Site Server machine Account
This is a secondary site server
Built-in administrators group.
I also add the primary site server machine account in the Local Administrator groups of PS1SSQL08 as shown below:
NOTE: This should be there before you install SQL Server 2008 on the server
Once you have the SQL server installed we’ll start with stopping your site as mentioned in Step 1 below.
Before we can move our ConfigMgr 2007 DB to a different site system we should have a backup of our ConfigMgr Database. Before you start the SQL backup, ensure that you run PREINST /STOPSITE to stop the Site Components. Here is what you will see, when you’ll run that command:
Once we have the site stopped we can move to our next step.
Use the SQL MGMT Studio to take the backup of your ConfigMgr DB. After using the context menu on the Database, click Backup and you’ll see a page like this:
After you’ll click ok on the BACKUP page it’ll take some time to take the backup of database. Once complete you ‘ll see the following prompt:
The time this take and the size of the resulting backup file will depend on the actual size of the ConfigMgr DB but in my was only 247 MB. Copy this BAK file to your new SQL 2008 server. I copied this file to the following location on the SQL 2008:
Once the backup is done, move on to the next step.
Before you restore the ConfigMgr database on the SQL 2008 computer, please verify the following :
- Make sure you are using the same collation settings on both your MS SQL servers. To check the collation settings complete the following:
A. Open Microsoft SQL Server Management Studio.
C. Right click on your Server Name choose properties.
D. We should check for the following:
E. I also checked the same settings on PS1SQL which was hosting ConfigMgr07 DB and both the servers had the same collation settings which was SQL_Latin1_General_CP1_CI_AS.
- Make sure that the CLR integration is enabled. To make sure that is enabled in SQL 2008 follow these steps:
A. First, right-click on the instance name that you would like to configure in SQL Server Management Studio and select "Facets" from the context menu as shown below:
This will display the View Facets dialog box that will provide you with a drop down of all the available facets that can be configured for the instance.
Select "Surface Area Configuration" from the Facet drop down to display the properties exposed by that facet. Make the appropriate changes and select OK to apply them to the server.
If CLR integration is enabled it will look like this:
If CLR integration is not enabled, please change it to true and restart the SQL services. Once you have done that it’s time to restore the Database in the next step.
Click on the Restore Database under the database node in SQL MGMT Studio as shown below:
Select the BAK file that we created in the Step 2. In the “to database” field ensure that we are typing the database name. Choose the source “From Device” and when the file is found make sure your check the restore radio button. Press OK to begin importing the database. These three options are highlighted in yellow below:
Once you have clicked OK on the above page you will see the following:
So at this point the ConfigMgr DB is available on PS1SSQL08 and we can move to the last step.
Run the ConfigMgr setup from the start menu:
Click Perform site maintenance or reset this site on the Configuration Manager Setup Wizard Setup Options page as shown below:
Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page:
The setup will show you the old information about the SQL as shown below:
In my case it was running on the PS1SQL server, I was also using a Named Instance “SCCM1”. Also it’s showing the ConfigMgr DB name as SMS_PS1. We need to enter the information about our new SQL 2008 server and in our example it is installed using the default instance so it’ll look like this:
Here you can see that PS1SSQL08 is my new SQL Server.
After that click next and you will see the process of reinstalling the Site System:
Once this process completes, go ahead and restart the Configuration Manager Server. After you reboot the server try opening the ConfigMgr Console. Below is what I see in mine. Under the COMPONENT STATUS I see my new SQL Server which is PS1SSQL08 as shown below:
I also checked the Site System Status and here I also see the PS1SSQL08 and PS1SQL (the old SQL server). However, PS1SQL has the Distribution Point role:
Also when I look at the Site Information it looks as expected:
When I pull up the ConfigMgr07 Service Manager I can see my new SQL server there:
Finally, after I removed the Distribution Point (DP) role from PS1SQL, I see the option to delete PS1SQL in the Context menu as shown below:
How to Configure an SPN for SQL Server Site Database Servers: http://technet.microsoft.com/en-us/library/bb735885.aspx
Anurag Shukla | System Center Support Engineer
The App-V Team blog: http://blogs.technet.com/appv/ The WSUS Support Team blog: http://blogs.technet.com/sus/ The SCMDM Support Team blog: http://blogs.technet.com/mdm/ The ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/ The SCOM 2007 Support Team blog: http://blogs.technet.com/operationsmgr/ The SCVMM Team blog: http://blogs.technet.com/scvmm/ The MED-V Team blog: http://blogs.technet.com/medv/ The DPM Team blog: http://blogs.technet.com/dpm/ The OOB Support Team blog: http://blogs.technet.com/oob/ The Opalis Team blog: http://blogs.technet.com/opalis The Service Manager Team blog: http: http://blogs.technet.com/b/servicemanager The AVIcode Team blog: http: http://blogs.technet.com/b/avicode The System Center Essentials Team blog: http: http://blogs.technet.com/b/systemcenteressentials The Server App-V Team blog: http: http://blogs.technet.com/b/serverappv