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