October 2010 - Posts

SQL 2008 top 5 things for a succesful Upgrade and Migration
31 October 10 07:21 AM | MartinCroft | 1 comment(s)

We have completed several upgrades from SQL 2000 and SQL2005 to SQL2008 and I thought I would blog about the top 5 things for a successful upgrade. There are lots of things that make an upgrade a success, these are just some of them.

  1. Get a great project team together, this might seem obvious but it can make or break the project. Get expertise in all the areas that the project covers SQL,storage SAN's, VMware, Infrastructure,SSIS dependent on what the project covers. Have people available on phone support if possible. Get a good cross over of skill sets
  2. Goes without saying TEST, if your lucky enough to have like for like infrastructures then its easier but in high usage OLTP,OLAP systems which service lots of users and lots of apps this is not always easy. Test to the maximum that achievable, there are usually business constraints, resource constraints. If its not tested it need to be raised as a risk. You could blog entirely on testing
  3. Document everything. I have a subset of documents I believe are a must for an upgrade / migration. In no particular order as it probably would take me far to long to decide which document has greater requirements 
    • External Influences Document - This was an important document that details all aspects of the SQL server and all external influences, such as applications that connect, services, windows application, web.config files, url for web applications, usage, user accounts for apps. External SQL servers that connect through linked servers, DTS packages, SSIS packages, other non SQL connectivity etc. Any outputs to other SQL Servers, Application servers Webservers, file structures FTP. This document should contain a diagram of all the systems that are touched and a high level of detail around the connectivity. SQL server access to the server, groups, users and permission. During upgrades it can be found the DBA team are the main players in the upgrade and the individual teams who develop the applications on the server take a back role during the project, this is the document you need to get their input into, that way there should be no surprises down the line. This will be used for configuring the test environments, knowing the scope of the project and start of building up requirements for go live steps. 

    • Go live steps spread sheet - the list of actions for the migration, numbered, with the server the action is carried out on, what the action is. Reference to scripts, owner, who carried out the step, time , who checked the step ( dependent on project!)

    • Lessons Learned Document- This is very useful and should not be seen as a witch hunt at the end of projects, but more for the next project, what went wrong how can we avoid it next time, how can we be more efficient next time. We reviewed previous Lessons Learned Documentation from previous projects when creating the go live steps to give us the best information to succeed in the project. 
    • Risk and Issues logs, Highlight reports - goes without saying we need to keep the focus of the project with the business and cover ourselves when the business puts constraints down that don't allow you to do everything you would in the perfect world.
  4. Learn from experience - I mentioned the Lessons Learned document, learn from these. over the last several months we upgraded 5 core systems from SQL2000 & SQL2005, each one presented different challenges, with different complexness, but each one we learned and took into the next one what we learned. 
  5. Use the technology available to you once upgraded. DMV's, Data collector, Resource Governor. I'll be blogging a set of DBA procedures that utilise the DMV's to show whats running, who's connected, what indexes are being used, what indexes aren't and how much space they save. What procedures are executed , how frequently , average times etc.
There are many other elements that contribute to a successful upgrade but I believe these are some of the more important ones.