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
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. Upgrading SQL Server can be a daunting prospect for a lot of SMS administrators out there, but have no fear you don't need a DBA to do this for you and the process is probably a lot easier than you think. If you don't want to upgrade SQL Server in-place, you can always move the site database to a remote SQL Server 2005 SP2 instance instead.
/* Disclaimer time.
Sure I said it's easy, but don't take that to mean you shouldn't test and verify the process for yourself in a lab before moving on to your production environment. The steps I used to upgrade the site database in this posting were performed in a small lab environment to verify the steps. Your mileage may vary and this process may cause drowsiness.
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 for upgrade. There's nothing more annoying that wondering why your site doesn't work correctly after upgrading and then discovering that it wasn't working before the upgrade either and you just wasted hours trying to troubleshoot an upgrade problem that didn't exist!
So here's the site I'll be upgrading (APR) happily chugging away at the SMS 2003 SP2 level:
And here are the steps to upgrade the site database server to SQL 2005 SP2 from SQL 2000 SP4:
- Back up the site using the integrated SMS 2003 backup site server maintenance task. If you don't want to wait for the scheduled backup time, you can start the process from the command line. From a command prompt type net start SMS_SITE_BACKUP and then watch the smsbkup.log to ensure that the site is successfully backed up (better safe than sorry in case something goes wrong).
- Stop the following SMS services. You can do this from a command prompt using net stop commands as well, ‘i.e.' net stop SMS_EXECUTIVE and so on. Technically, you could just disable these until you've finished upgrading SQL, but I think it's fun to play with the command prompt (may DOS never die!!!) and since I know I'm toying with the database, any odd status messages telling me that the site can't communicate with the database during this process (between steps 5 and 6) won't concern me. Anyway, these are the same services stopped during the normal SMS site backup maintenance task so go ahead and stop ‘em before proceeding:
- SMS_EXECUTIVE
- SMS_SITE_COMPONENT_MANAGER
- SMS_SQL_MONITOR
- Now that the SMS site installation has effectively given the site database "the hand" and won't be interfering with the SQL business, use the SQL Server backup task and back the database up from within SQL Server. Why? A straight SQL Server backup can not be used to fully restore a failed site, but it can be used to restore a failed site database or be used to move the site database to a remote SQL Server installation.
- Run SQL 2005 Setup to upgrade the database components and optionally install SQL 2005 workstation components. Remember, the same SQL Server edition already installed must be used to upgrade the SQL instance. For example, you can't go from Standard to Enterprise during an upgrade. SMS isn't really concerned with the edition of SQL anyway; it will run on either just as well.
- After SQL has been upgraded, restart the server.
- Now, go back and repeat step 2 to stop those same services again before moving on to step 7.
- Run the SQL 2005 SP2 installation to upgrade the SQL Server instance to SP2.
- After SQL has been upgraded again, restart the server again.
- Open the admin console to verify that everything is as happy as it was before you started this process. Oh yeah, if you disabled the services at step 2, make sure you enable them again!
That's it! You don't need to worry about setting any additional SQL Server settings like setting the SQL Server compatibility level to 90 (SQL Server 2005 default) for the site database because SMS 2003 doesn't use any SQL 2005 features and is perfectly happy with the site compatibility level at 80 (SQL Server 2000 default) and that's what the site database will be set to use after upgrading SQL.
To see the compatibility level the database is using, you can right-click the database name, click Properties, and then click Options:
Unlike SMS 2003, Configuration Manager does take advantage of SQL Server 2005 database features. For Configuration Manager site databases, the compatibility level must be set to 90 and some additional configuration changes made to the upgraded site database. Don't worry though, Configuration Manager Setup takes care of all the database configuration changes for you during the upgrade process.
After upgrading to Configuration Manager from SMS 2003, the site database will be configured to operate using the compatibility level of 90.
One of the SQL Server 2005 features that Configuration Manager uses are database roles. All of the smsdbrole_* database roles are created and populated with the appropriate site systems during the site upgrade process. These SQL Server database roles are used for securing remote site system access to the site database instead of the SMS_SiteSystemToSQLConnection_<site code> group used in SMS 2003.
See the new roles?
So that's it in a nutshell. Happy upgrading :-)
~Jeff