SQL Server 2008 not just for DBAs

I am sure SQL Server is administered by many ‘accidental’ DBAs i.e. IT Professionals who get saddled with looking after databases, while performing a bunch of other infrastructure management tasks.  One of my good friends Jonathan Noble at Newcastle University is one of these and so I thought it would be good to give him a bit of head start to migrate and consolidate the many SQL Server 2000 databases he has ‘inherited’.

The first thing I wanted to do was to install an environment for them to play with.  Even without any databases in, you can use the new client tools to look at and manage SQL Server 2000 and 2005 databases, and use such features as the configuration server and policy management to look at the existing estate.

That all went well and so our focus then turned to migration and the key tool in planning any migration is the Upgrade Advisor. This is essentially a reporting tool that you can run from any server or desktop to evaluate a given server or specific database for upgrade.(the latest version here).

One of the things you will notice about the reports when you run them is that they will always scan the msdb system database and always return these warnings if you’re examining a SQL Server 2000 server (btw the image is from the 2005 upgrade advisor):

This is because DTS packages can reside in msdb and even if you haven’t got any packages in there you will get the warnings as per this knowledge base article (KB). 

As I discussed with Jonathan, if you do have DTS packages to migrate then my top tip would be to manually recreate them in integration services as and when they need to be changed to reflect changing business needs. Until then run then you can edit ( to change the connections to reflect any server moves) and run them ‘as is’  by using the Backward compatibility components. In the SQL Server 2008 Feature pack.

You can also check Jonathan’s team blog WITter

Technorati Tags: SQL Server 2008,SQL Server 2000,migration,upgrade,Newcastle University