How should DBA plan for SQL Server 2008 Upgrade

Disclosure: Please note that this post is based on a post in mssqlbits which i came across while searching for upgrade process of SQL server. I really like mssqlbits and want to rewrite this post on my blog so that i can have a copy of it always with me and get benefitted anytime i need. I tried to be honest with the original post. I am writing this post as notes for myself and others, who wants to learn the upgrade process of SQL server 2008. It tried to make this post more compact and easy to understand for readers.

Question:

What are some of the key considerations as DBA plan to upgrade?  How much time should be spent on planning?  Who should do the planning?  what are the technical aspects a DBA should research?  What is the best way to manage the project?

Answer:

At many organizations, significant planning is needed before start upgrading to SQL Server 2008. In this post i will cover what planning is needed before starting an upgrade to SQL Server 2008.

As each upgrade is unique and different, so some of the recommendations in this post may not be applicable for an upgrade that you are facing, so particular aspects may not be included. 



Preliminary research

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

  • Inventory of the servers, applications and users

Ø  SQL Server versions and editions

§  Command to get Sever version, level and edition information.

Select SERVERPROPERTY(‘productversion’), SERVERPROPERTY(‘productlevel’), SERVERPROPERTY(‘edition’)

Ø  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

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 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 these 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. 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.
      • 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.
    • Reporting Services
    • Analysis Services
  • Post Upgrade tasks

Next Steps

Reference URL: http://www.mssqltips.com/sqlservertip/1933/sql-server-2008-upgrade-plan-for-dbas/

I hope you will like this post. As I want to learn more on SQL Server 2008 upgrade process, Please share your knowledge in SQL Server 2008 upgrade planning and your thoughts in the comment section.

Advertisements

About Ashish Jain

I am Ashish Jain, a software engineer by profession. My goal of creating this blog is to share my knowledge of SQL server with all other SQL enthusiasts and also to learn from them.
This entry was posted in SQL Installation and Upgrade and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s