Guide: Moving your site database

[wmmayms]

This guide will explain how to move your site database to another server.

Im using SCCM 2007 (mixed mode), MSSQL 2005 SP3 and Windows 2003 r2 throughout this guide.

My SCCM 2007 Server is called DSGCM1 and my new SQL server is called DSGSQL1

 

Before you begin make sure you have a MSSQL 2005 server setup with atleast SP2. (MSSQL Express is not supported)

 

Before starting the migration you need to check the following:

-----------------------------------------------

1. Make sure you are using the same collation settings on booth your MS SQL servers

This is how to check this:

1. Open Microsoft SQL Server Management Studio

2. Connect

3. Right click on your servername choose properties

4. It will look like this:

 

If you don´t do this you might end up with allot of status messages saying something like this:

” Microsoft SQL Server reported SQL message 468, severity 16: [42000][468][Microsoft][ODBC SQL Server Driver]

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS"”[/color][/i]

-----------------------------------------------
[b]2. Make sure you have enabled CLR intergration on the SQL server you are planning to move your DB to. [/b]
This is how to check this:
1.Open SQL Server Surface Area Configuration. Path: 
[attachment=1962:surface.JPG]
2. Press the link "Surface Area Configuration for Features"
[attachment=1964:sacff.JPG]
3. Enable CLR intergration
[attachment=1963:enable_clr.JPG]
4. Press Apply and your all done.

If you don´t do this you might end up with status messages like these ones:
[i][color="#FF0000"]"Microsoft SQL Server reported SQL message 6263, severity 16: [42000][6263][Microsoft][ODBC SQL Server Driver][sql Server]Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. : RoamingBoundaryIPRange_del"[/color][/i]
-----------------------------------------------
[b]3. Make sure you have atleast Servicepack 2 installed on your [u]new[/u] SQL 2005 Server. [/b]
This is how to check this:
Run the following query in microsoft SQL Management Studio (press "execute to run the query")
[codebox]SELECT  
SERVERPROPERTY('productversion'), 
SERVERPROPERTY ('productlevel'), 
SERVERPROPERTY ('edition')[/codebox]

-----------------------------------------------

 

When you have done all of these check you can go ahead and move your DB.

 

 

 

 

 

 

[size=5][b]This is how to move your site Database to another server:[/b][/size]

 

Begin with putting the SQL Primary site computer account in the Administrators group on your SQL server (the one you are moving your db to)

 

Then backup your site datbase by going into Microsoft SQL Management Studio. Expand the databases node and rightclick on your site database. Choose "tasks" --> "Back Up.."

 

Choose to backup to disk and backuptype=Full and then press Add button to choose where to save your backup. This will take some time depending on how big your database is.

 

Now copy the database from your old server to your new server.

 

On your new SQL server open Microsoft SQL Server Management Studio. Rightclick on the databases node and choose "Restore Database..."

 

Write the db name in the "to database:" field. Choose "From Device" and then find your backupfile, mine was located at d:\sms_dsg. When file is found make sure your check the restore radio button. Press OK to begin importing the database.

 

Wait for this message to appear:

 

Now go to your SCCM 2007 server and find the file "preinst.exe". Should be located where you have installed your SCCM. In my case this is: "F:\MICROSOFT CONFIGURATION MANAGER\bin\i386\00000409"

 

Now that you know where your preinst.exe file is located you can run it from the commandprompt with the following switch: /stopsite

In my case the command looked like this: "f:\MICROSOFT CONFIGURATION MANAGER\bin\i386\00000409\preinst.exe" /stopsite

 

You will get a few messages like these (wait for them all to finish):

 

On your SCCM site server open "All programs --> Microsoft System Center --> Configuration manager 2007 --> ConfigMgr Setup"

 

Click next on the first Wizardpage:

 

Choose to "perform site maintenance or reset this Site". Click next.

 

Choose "Modify SQL Server Configuration". Click next.

 

Change "SQL Server and instance, if applicable:". Wite the name of your new SQL server here. Databasename should still be the same! Click Next

 

Will look something liek this:

 

When all is completed press next.

 

Press Finish or view the log if you want

 

Now restart your server!

 

Open ConfigMgr Console and expand "Site Database --> Site management --> Site (in my case DSG - DSG primary Site) --> Site settings --> Site Systems". right click on ConfigMgr site system and choose properties. enter a FQDN for the new server, in my case dsgsql1.dsgdomain.local. Press OK.

 

Now reset all your status messages and after a while it will hopefully still look something like this: (you are all done)

 

Thx for reading. If you have questions please post them in the forum.

[anyweb]

excellent guide,

 

well done and thank you

 

what I'd like to see next is a guide for restoring a site from scratch using the backup files created using the backup feature in Tasks,

[anyweb]

adding the following link

 

ConfigMgr 2007: How to move the Site Database

[randy]

Are there any other steps involved with native mode?

[Kingskawn]

Thanks, will be really helpful for me as we want to migrate our DB.

Still don't know if we can migrate our x86 db to x64. I thought that if I am on SP2 I can...

 

Do you also have a guide to move the WSUS db to another SQL server? As those 2 dbs (SCCM & WSUS) are on the same shared SQL I want to move them to 1 dedicated SQL server

[Eswar Koneti]

Thanks, will be really helpful for me as we want to migrate our DB.

Still don't know if we can migrate our x86 db to x64. I thought that if I am on SP2 I can...

 

Do you also have a guide to move the WSUS db to another SQL server? As those 2 dbs (SCCM & WSUS) are on the same shared SQL I want to move them to 1 dedicated SQL server

 

Have you tried this link ? http://blogs.msdn.com/b/john_daskalakis/archive/2009/04/06/9533669.aspx

[vrkumar01]

Thanks a lot & i jsut completed moving my database successfully.

[RIX78]

Would the same steps apply for SQL Server 2008 R2 ?

[Casey.Robertson]

Great write-up! But how about this situation?

 

Existing DB server is remote - let's call it SCCMDBOLD. The plan is to migrate this SQL server to new hardware (SCCMDBNEW) and KEEP the same name. The plan is to build a new SQL server, copies databases etc etc, rename and re-IP the new DB server to be like the old one.

 

What steps do I follow then? Same as the steps you outline except when the time comes to run ConfigMgr setup will it let you fill in the EXISTING same database server name?

 

Every procedure I have found talks about moving to a new DB server with a NEW name. What if you are keeping the same name?

 

Also - why wouldn't you run preinst.exe /stopsite before running the DB backup and restore? Couldn't the DB get out of sync?

 

Yet another question - in your screenshot of the preinst.exe /stopsite - how does SCCM already know about DSGSQL1 if you haven't run the setup wizard yet to tell it about the new server?

 

Sorry....confused.....

 

Thanks,

Casey