imageHi 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.

Lab environment:

Site information:

image

Existing SQL Server 2005 SP2 computer: PS1SQL
Site Server: PS1SRV
New SQL Server 2008 computer: PS1SSQL08

Preliminary Information

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:

image

User Account

Description

MANBLR\Administrator

This is a DOMAIN\Admin account for my domain.

MANBLR\PS1SRV

PS1 Site Server machine Account

MANBLR\SS1SRV

This is a secondary site server

BUILTIN\Administrators

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

image

Once you have the SQL server installed we’ll start with stopping your site as mentioned in Step 1 below.

Step 1:

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:

image

Once we have the site stopped we can move to our next step.

Step 2:

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:

image

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:

image

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:

C:\SQL Backup\SMS_PS1.bak

Once the backup is done, move on to the next step.

Step 3:

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.

B. Connect.

C. Right click on your Server Name choose properties.

D. We should check for the following:

image

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:

image

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:

image

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.

Step 4:

Click on the Restore Database under the database node in SQL MGMT Studio as shown below:

image

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:

image

Once you have clicked OK on the above page you will see the following:

image

So at this point the ConfigMgr DB is available on PS1SSQL08 and we can move to the last step.

Step 5:

Run the ConfigMgr setup from the start menu:

image

Click Perform site maintenance or reset this site on the Configuration Manager Setup Wizard Setup Options page as shown below:

image

Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page:

image

The setup will show you the old information about the SQL as shown below:

image

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:

image

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:

image

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:

image

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:

image

Also when I look at the Site Information it looks as expected:

image

When I pull up the ConfigMgr07 Service Manager I can see my new SQL server there:

image

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:

 image

References:

How to Configure an SPN for SQL Server Site Database Servers: http://technet.microsoft.com/en-us/library/bb735885.aspx

Best Regards,

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

clip_image001 clip_image002