In order to upgrade an SMS 2003 SP2 or SP3 site to Configuration Manager 2007 the site database must be at the SQL Server 2005 SP2 level. If you don't want to upgrade a SQL Server 2000 instance hosting a site database in-place, then you'll need to know how to move the site database to a new SQL 2005 default instance with SP2 installed.
/* Disclaimer time.
This is a fairly simple process, but as with everything, you should test and verify the process for yourself in a lab before moving on to your production environment. The steps I used in this posting were performed in a small lab environment to verify the steps. Remember, objects in the mirror are actually behind you and do not iron clothes on body.
Disclaimer time. */
If you're going to do this in a lab, then the first thing you need is a fully functional SMS 2003 site with at least SP2 installed. Clients should be installed and some basic site configuration done (inventory, software dist, etc...) to verify that the site is ready to be reconfigured to use the new SQL Server 2005 computer to host the site database.
So here's the site I'll be reconfiguring (APR) happily chugging away at the SMS 2003 SP2 level:
When you're sure that your existing SMS 2003 SP2, or above, site is functioning as expected, you're ready to move the site database. The first thing to do is to ensure that the SQL Server database collation is the same on the remote SQL Server as the one the site database is currently on. To check the SQL Server collation on the SQL Server 2000 instance, open up SQL Server Enterprise Manager and expand the console until the SQL Server instance the site database is hosted on is displayed. Right-click the SQL Server name and click Properties. Down at the bottom of the instance properties you can see the server collation setting:
Next, open up SQL Server Management Studio on the SQL 2005 server. Right click the default instance name (SMS 2003 site databases must be installed on the default instance) and click Properties. With General selected, look once again for the Server Collation setting:
If both of the servers are using the same collation, then you're ready to continue. If not, the easiest thing to do is reinstall SQL Server 2005 and ensure that the correct collation is selected during installation.
OK, now it's time to get down to business. Moving a site database is really a four step process:
- Back up the site database on the SQL Server 2000 computer.
- Restore the site database on the remote SQL Server 2005 computer.
- Use SMS 2003 Setup to perform site maintenance to reconfigure the site to use the remote SQL Server 2005 computer to host the site database.
- Reconnect Administrator consoles.
You'll notice that I didn't say anything about configuring SQL Server 2005 features for the site database-SMS 2003 doesn't use any SQL Server 2005 features so you don't need to perform any additional configuration.
Back up the site database on the SQL Server 2000 computer
Now that you've verified the remote SQL instance is using the same collation, the next step in moving the site database (whether you're going to a new version of SQL Server or not) is to get the site database on the remote SQL Server instance.
Begin by making a SQL Server backup of the site database. Before you can do that though, you'll need to close all administrator consoles connected to the site database and stop the following site server services. These are the same services stopped during the normal SMS 2003 site backup maintenance task:
- SMS_EXECUTIVE
- SMS_SITE_COMPONENT_MANAGER
- SMS_SQL_MONITOR
Tip: If the site database is already on a remote SQL Server, then this service will be installed on the remote SQL Server computer and named SMS_SQL_MONITOR_<site server name>.
Tip for the Tip: If you're looking for this service in a Configuration Manager installation, stop-that service has been incorporated as a thread of the SMS_EXECUTIVE service in Configuration Manager.
You can use a command prompt to stop these services using net stop commands like: net stop sms_executive and so on.
With all database connections effectively stopped, you're ready to create the database backup from within SQL Server Enterprise Manager:
- Right-click the site database name, click All Tasks, and then Backup Database...
- On the SQL Server Backup - <site database name> General tab, ensure that the site database is selected in the Database field, type a friendly name for the database backup in the Name field and optionally a description.
- Leave the option to create a complete database backup enabled.
- If you've enabled the backup site server maintenance task, the backup destination location displayed will be set to the Backup SMS Site Server maintenance task backup location setting. Highlight it and click Remove (don't worry the next time the site backup task starts, it will reset the backup destination location).
- Click Add. Enter a path to an existing directory to store the database backup and a name for the backup file with a .BAK extension. For example, if you want to store the database backup in D:\SQLBackup and name the backup file backup.bak, your file name would be: D:\SQLBackup\backup.bak.
- Enable the Overwrite existing media option just in case there is already a backup at the backup destination.
- Now click over to the Options tab and select the Verify backup upon completion option and click OK to get the party started. If all goes well, you should see a dialog box similar to the following:
- After the database backup has completed, you can close SQL Server Enterprise Manager.
- Using Windows Explorer, browse to the backup destination location and find the database backup you just created.
- Copy the backup file (bak) to the remote SQL 2005 Server.
- Restart the site server computer.
Restore the site database on the remote SQL Server 2005 computer.
- On the remote SQL Server 2005 server, open up SQL Server Management Studio for the default instance, right-click Databases and then click Restore Database...
- On the Restore Database General page, type the name of the site database in the To database: field (SMS_APR in this example). Select the From device: option, click the browse button (...) and click Add.
- Browse to the location of the site database backup file (.bak) copied from the SQL Server 2000 computer and click OK, and OK again to close the Specify Backup dialog.
- In the Select the backup sets to restore section, check the Restore option next to the site database backup and click OK. Viola!
Use SMS 2003 Setup to perform site maintenance to reconfigure the site to use the remote SQL Server 2005 computer to host the site database
- On the primary site server computer, start SMS 2003 Setup from the start menu.
- On the Setup Options page, select Modify or reset the current installation and click Next.
- On the Database Modification page, type the name of the remote SQL 2005 Server you restored the database backup to and verify that the database name is correct.
- Continue through Setup and click Finish to begin the site database setting reconfiguration process. If all goes well, you should see something like:
Reconnect the Administrator consoles
At this point, when you open the Administrator console for the site it will fail to connect to the site database. This is because it's still looking for the old site database and the site server says nope, it's not there anymore. To reconnect the Administrator console to the new site database server, you need to use the Site Database Connection Wizard.
- From within the Administrator console, right-click on the failed connection and click Delete.
- Right click Systems Management Server at the top of the console and click All Tasks, Connect to Site Database to start the Site Database Connection Wizard.
- On the Locate Site Database page, select the primary site server computer name-not the remote SQL Server name-click Next and finish the wizard.
- The Administrator console should be successfully connected to the new site database at this point.
- Just to verify the move was successful, you can expand the console and view the site systems for the site. The remote SQL Server computer should now be listed as the SMS SQL Server component role.
So there you go, that's it :-)
~Jeff