Jump to content


wmmayms

Guide: Moving your site database

Recommended Posts

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:

post-2500-1234340780_thumb.jpg

 

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]

post-2500-1234354512_thumb.jpg

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

 

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)

post-2500-1234350705_thumb.jpg

 

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

post-2500-1234350899_thumb.jpg

 

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.

post-2500-1234351091_thumb.jpg

 

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

post-2500-1234351204_thumb.jpg

 

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

post-2500-1234351302_thumb.jpg

 

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.

post-2500-1234351718_thumb.jpg

 

Wait for this message to appear:

post-2500-1234351760_thumb.jpg

 

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"

post-2500-1234351897_thumb.jpg

 

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

post-2500-1234352198_thumb.jpg

 

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

post-2500-1234352356_thumb.jpg

 

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

post-2500-1234352785_thumb.jpg

 

Click next on the first Wizardpage:

post-2500-1234352826_thumb.jpg

 

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

post-2500-1234352874_thumb.jpg

 

Choose "Modify SQL Server Configuration". Click next.

post-2500-1234352972_thumb.jpg

 

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

post-2500-1234353117_thumb.jpg

 

Will look something liek this:

post-2500-1234353166_thumb.jpg

 

When all is completed press next.

post-2500-1234353204_thumb.jpg

 

Press Finish or view the log if you want ;)

post-2500-1234353274_thumb.jpg

 

Now restart your server!

post-2500-1234353479_thumb.jpg

 

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.

post-2500-1234353672_thumb.jpg

 

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

post-2500-1234353845_thumb.jpg

 

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

post-2500-1234346277_thumb.jpg

post-2500-1234346488_thumb.jpg

post-2500-1234346654_thumb.jpg

  • Like 1

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites

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... :huh:

 

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

Share this post


Link to post
Share on other sites

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... huh.gif

 

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...