SQL Server 2008 Upgrade Plan for DBAs

By:   |   Updated: 2010-02-02   |   Comments (2)   |   Related: > Upgrades and Migrations


Problem

At our organization we are in the planning stages of upgrading to SQL Server 2008. What are some of the key considerations as we plan to upgrade? How much time should be spent on planning? Who should do the planning? As a DBA, what are the technical aspects I should research? What is the best way to manage the project?

Solution

Upgrading to SQL Server 2008 will require significant planning at many organizations. This could be one of a handful of key IT projects for your organization in the near term, so be sure to properly plan for the project. In this tip we will try to cover what planning for an upgrade could mean, based on our recent upgrades.

Unfortunately, each upgrade is unique and different, so some of the recommendations in this tip may not be applicable and/or we may have not worked through an upgrade that you are facing, so particular aspects may not be included. Nevertheless, please let us know your experiences in the forum. We want to hear from you. With that being said, let's jump in...

Preliminary research

As you begin your preliminary research, be sure to being to educate yourself on the following items:

  • Inventory of the servers, applications and users
    • SQL Server versions and editions
    • Application versions and editions
    • User location, operating hours, etc.
  • 32 bit versus 64 bit platform
  • Hardware support from your vendors
  • Business application support from your vendors and/or co-workers
  • Upgrade paths
  • Rollback plans
  • Development and test environments
  • Testing
  • Relational engine upgrade
  • DTS to SSIS upgrade
  • Reporting Services upgrade
  • Analysis Services upgrade
  • Opportunities for improvement
  • Post upgrade tasks
  • Adopting new SQL Server technologies

Base on our experiences, these were the keys aspects for one or more recent upgrade projects, so we wanted to pass them along. One key item that I want to mention again is figuring out which users are involved in the upgrade. Be sure to get them involved early and often as you continue the project.

Project Scope

Here is critical step that I think is missed all too often. Some people call it 'figuring out how big the bread box is' while others assume they know it. In my opinion scoping and managing a project does not need to be a rigid process, but it is important. At this phase in the project try to understand the following:

  • Why does the business or department want to upgrade to SQL Server 2008?
  • What SQL Server are in scope?
  • What applications access the SQL Servers?
  • What users leverage the applications?
  • What are the user operating hours?
  • Who are the points of contact\management for the users?
  • What automated processes leverage the SQL Servers?
  • What third party applications, controls, plug-ins, etc are needed?
  • What are the upstream and downstream applications?
  • Who are the key IT team members?
  • When does the project need to be completed?
  • Which management members are supporting the project?
  • Which budgets are paying for the project?
  • Have the budgets been finalized or is management waiting for estimates?

As a DBA, do a quick sanity check here and make sure all looks correct. If you are not comfortable with the findings, run a tool like Profiler to determine if any users, applications, processes, etc are not being taken into consideration. One group that always seems to be overlooked are the Reporting Groups, so be sure to include them.

Project Plan

In some circumstances, the preliminary research generated more questions to vendors, partners and developers. In other circumstances, it lead to a project plan to work through the upgrade. In my opinion, building a project plan is the next logical step in formally beginning the upgrade process. Some companies have a formal project planning methodology, others do not and use task lists more so than project plans. Regardless of your environment, be sure to have a plan that is shared (physically and electronically) with everyone on the team that includes the following items:

  • Definable tasks, with the person responsible as well as the start and end date
  • Budgets, estimates and purchases for hardware, software, FTE time and consulting time
  • Weekly or daily status meetings
  • Project scope and requirements
  • Environment setup and configuration
  • Preliminary analysis
  • Testing including user, unit, load and DBA
  • Upgrade dates including application freeze, upgrade and thaw
  • Go | No Go Meeting
  • Upgrade and post upgrade testing and DBA tasks
  • Lessons learned

Hardware and Software Needs

Although I believe SQL Server has introduced a number of great features, the one that I believe will be adopted on a more wide spread basis is support for 64 bit. Although, 64 bit versions of Windows and SQL Server have been available since SQL Server 2000, I have not seen a wide spread adoption across numerous companies. I hope with SQL Server 2008 upgrades this changes.

As you are upgrading your hardware, be sure to include your development and test servers in that plan. Depending on the application, it might not make sense to have a 64 bit production platform and a 32 bit development and test platform. What do you think? As such, the resources spent on hardware may be more than what you would initially expect, so plan accordingly.

On a related note, if you organization has not been adopting virtual server technologies, this might be the time to research those vendors to determine if they make sense for your development and test needs. Many companies have adopted virtual server technologies while others have continued to leverage physical servers for each environment. These technologies have the potential to save a great deal of power, rack space, hardware, etc costs. Keep an open mind and see if they make sense for your organization.

SQL Server Upgrade Planning Tasks

When it comes to planning for the technical portion of the upgrade, it is wise to break this down some detailed processes into manageable checklists that various team members can use during the process. At a high level, here are some of those items:

  • Preliminary tasks
  • Hardware\Software setup
    • Environments
      • Development
      • Test
      • Production
    • Windows and SQL Server licensing
    • Third party tools
  • Development\recoding
  • Testing
  • Rollback plan
    • Building a rollback plan is your insurance plan in case a problem arises. I firmly believe in building a rollback plan and testing it out. I have seen a few situations where a problem occurs and the team just tries to work through it. If you do not know how much time your rollback plan will take, you can reach the end of your planned downtime and not have a system for users to support the business. This is not good.
    • I have seen other situations where a problem occurs and the team hits a wall and does not have a plan to revert back. At 2:00 AM in the morning when everyone is exhausted and no one can think straight they are frantically trying to get the system back up and running. Unfortunately, mistakes are made and they can be costly.
    • If you get push back, ask your team if they want to give up their personal insurance policies. See if it makes them as uncomfortable as it does you, by not building and testing a rollback plan.
  • Upgrade
    • Relational Engine
      • Preliminary tasks
      • In place upgrade (1 machine)
        • This option is fairly straight forward because the installation process will upgrade the SQL Server application as well as the SQL Server databases. The down side of this is that the rollback plan would require a uninstall of SQL Server 2008 and a reinstall of the previous version of SQL Server in addition to a restore of the databases. Most likely you would also need to reconfigure SQL Server.
        • Stay tuned for an upcoming tip on the technical aspects of this portion of the upgrade...
      • Upgrade to a new machine (2 machine upgrade)
        • When upgrading to a new machine the installation of SQL Server on the new machine can take place well in advance. Some companies even test with the new machines to understand how the system will perform under load, which I think is a good idea.
        • In terms of the upgrade itself, the databases can be either backed up and restored from the previous version of SQL Server to SQL Server 2008 or detached\attached. In either case, the upgrade process is fairly straight forward.
        • If you have a very slim window when you can upgrade, I would consider a third party tool which compresses the backups to perform the backup and restore operation. These tools can save a significant amount of time and disk space. This is one technique we used for SQL Server 2005 upgrades and the tools have worked well again for SQL Server 2008.
        • Stay tuned for an upcoming tip on the technical aspects of this portion of the upgrade...
    • Reporting Services
      • Stay tuned for an upcoming tip on the technical aspects of this portion of the upgrade...
    • Analysis Services
      • Stay tuned for an upcoming tip on the technical aspects of this portion of the upgrade...
  • Post Upgrade tasks
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-02-02

Comments For This Article




Monday, February 4, 2013 - 1:53:38 PM - Jeremy Kadlec Back To Top (21903)

Srinivasa,

It really depends on your environment, team members, time, dependencies, downtime available, etc.  I would work through a few sandbox, development or test upgrades to help give you a better sense of the level of effort based on the variables mentioned.  Hopefully that will help you make a reasonable assessment to plan your project.

Thank you,
Jeremy Kadlec
Community Co-Leader

 

Monday, February 4, 2013 - 12:26:56 PM - Srinivasa Rao V Back To Top (21902)

How determine each task in SQL server 2000 upgrade activities whether the task is simple, medium or complex and how to define metrics to estimate upgrade tasks














get free sql tips
agree to terms