A major problem facing many of us is that of sprawl. In my case, I have databases served by SQL Server 2000, 2005 & 2008. As if that’s not bad enough, the service pack levels on these servers varies, as does the edition of SQL Server being used. Therefore, the heat is on us to consolidate the servers onto a smaller, more manageable set of servers. The Boss is hoping for just the four – two of each of SQL 2005 & SQL 2008, test & live.
So what’s the big deal? Well… most of the databases we have are to support third-party applications, rather than in-house work. We do not necessarily have versions of those applications that support more recent releases of SQL Server. And we don’t have the appropriate documentation to get these (& other) problems solved.
While thinking about this problem, I have come up with the following checklists for migration / consolidation projects:
Things to do:
- Identify which databases need to be upgraded (ie are still on older versions of SQL Server)
- Of those databases, which are not supported on more recent versions of SQL Server? This is one for the appropriate vendor – so do we have contact details for the vendor?
- Of those non-supported databases, if this is just because the vendor hasn’t tried it, are we able to try and will they support is in our efforts?
- Identify maintenance routines and scheduled jobs associated with database
- Identify dependencies upon older technologies (eg DTS rather than SSIS), and work out an upgrade path for these
- What applications depend upon the database?
- What UserIDs are required?
- How do we configure the application to point to a new database?
- What else needs to be changed? (eg middleware servers)
- Are parts of the database subject to Replication?
- Is the database part of a Log Shipping routine?
- Is the database part of a Mirror set?
- What’s the recovery plan for the database?
- What’s the backup plan for the database?
- Are there any SSRS jobs relating to this database?
- What are they?
- Where are they?
- How do we migrate these across?
- What else depends upon those reports?
- …and similarly, are there any OLAP / SSAS dependencies?
It might also be worth thinking about the amount of data in the database:
- How much data have we got?
- How fast is the database growing?
- For how long do we need to retain this data?
- Can we archive anything off to improve performance?
Of course, all the above forms part of your database documentation, so it should be easily to hand, right?
The other things to work out include:
- How do we test this thing to ensure the migration is successful?
- How do we rollback if it isn’t successful?
- Point of contact for the supplier / vendor / development team
- Point of contact for the customer(s) / user(s)
Strewth. That’s a fair bit of legwork.
The problem with writing these lists is that I’m bound to have forgotten something. So, if you can spot any omissions (if that’s not an oxymoron), then please let me know!
Additional: I appear to have omitted to mention Microsoft’s SQL Server 2008 Upgrade Advisor. Please make use of this!