Upgrade Recommendations and Best Practices

Reprinted with Permission by Quest Software April 2003

SQL Server Upgrade Recommendations and Best Practices
Part 1 – Upgrade Overview and Project Planning
Jeremy Kadlec, Edgewood Solutions

This article is the first of a multi-part series detailing the SQL Server Upgrade process from the technical, logistical and business perspective. In the coming weeks, expanded articles will be published in the following areas:

Part 1 – Upgrade Overview and Project Planning
SQL Server 6.5 and 7.0 Critical Upgrade Decisions and Redundant Upgrade Architecture
SQL Server 6.5 and 7.0 Upgrade Checklist and Application
Access Upgrades to SQL Server 2000
Upgrade from SQL Server 2000 to Yukon
Sybase, Oracle and Data Upgrades to SQL Server 2000
Post SQL Server 2000 Upgrade Recommendations

Introduction – SQL Server Upgrades

As the DBA in your organization, you are central to the success of the SQL Server environment. In the case of a system upgrade, you need to act as a ‘driver’ for an upgrade project to ensure success based on your technical expertise and role in the organization. Over this multi-part series, these articles will outline proven and recommended best practices for the upgrade process. This process is detailed from both technical and logistical perspectives which are both critical to the success of the project.

Needless to say, upgrading to SQL Server 2000 can be a daunting task based on the criticality of the systems, level of coordination and technical planning. As such, the series of articles will provide valuable explanations, charts and graphics to best illustrate the points to assist you in the project. With this being said, be prepared to work with new team members, wear new hats and resolve challenging issues in the course of upgrading to SQL Server 2000.

The motivation for this article is the realization that in many companies applications are in place, but the right tool for the job is not being leveraged. Too often, piece-meal applications are supporting business critical functions that cannot be leveraged to save time nor generate revenue. To further elaborate:

Companies are still running SQL Server 6.5 and limping along by having IT staff spending hours resolving server down, corruption and data integrity problems with minimal user productivity
Microsoft Access has grown from a desktop database to a department of users that are severely stressing the database ultimately leading to corruption and frustration
3rd party Applications need to be upgraded in order to leverage new functionality released by the vendor and needed for the business
Microsoft Excel is being used to run business critical functions and important data is scattered across the organization and is sometimes mistakenly lost

The bottom line contribution by the DBAs for the business is to improve efficiency and accuracy for the user community as well as save time and money for the business. The DBAs win by being able focus on more challenging IT projects on the latest and greatest technology. I am sure you can agree this is a WIN-WIN scenario for everyone involved.

Business Justification - SQL Server 2000 Upgrade

For those companies that have not migrated existing servers to SQL Server 2000, the rewards certainly outweigh the effort. The level of effort may be moderate to high, but the overall platform stability and feature rich capabilities of SQL Server 2000 are unprecedented. As a DBA, your ultimate responsibility is to ensure your systems are available to support the business needs to include the proper platform to efficiently and accurately process the transactions in a cost effective manner. Below outlines the Business Justification to leverage SQL Server 2000.

Table 1: Business Justification

ID	Justification	Supporting Information
1	Total Cost of Ownership³	Total Cost of Ownership (TOC) lower than any other DBMS in the market
2	System Performance³	Unprecedented System Performance for both OLTP and OLAP environments Improved ability to scale up and out by leveraging expanded hardware resources As much as 64 GB of Memory and 32 Processors
3	Microsoft Support	As SQL Server 6.5 ages, Microsoft is providing less support for the product and will eventually have few Support Engineers available to address critical needs Currently, if you have a business critical issue with SQL Server 6.5, the typical Microsoft Support recommendation is to ‘Upgrade to SQL Server 2000’
4	Regulated Industry Requirements	Upgrading to SQL Server 2000 becomes especially important for companies in regulated industries that may require a several year data retention period Relying on SQL Server 6.5 for the short term may not be an issue because staff is familiar with the technology
5	DBA Support	In five years, finding individuals to administer SQL Server 6.5 will be difficult and not attractive to DBAs who are typically interested in the latest and greatest technologies
6	Level of Automation	The level of automation from the SQL Server tool set Enterprise Manager Query Analyzer Profiler Data Transformation Services (DTS)
7	New Capabilities²	Analysis Services DTS XML Integration Optimizer Enhancements Functions DBCC’s Log Shipping New Replication Models Full Text Indexing Database Recovery Models Linked Servers
8	Third Party Products	SQL LiteSpeed – Compressed and Encrypted backups – www.sqllitespeed.com Lumigent Entegra – Enterprise Auditing Solution – www.lumigent.com/products/entegra/entegra.htm Lumigent Log Explorer – Review and Rollback Database Transactions - www.lumigent.com/products/le_sql/le_sql.htm Precise Indepth for SQL Server – Performance Tuning - www.precise.com/Products/Indepth/SQLServer/ NetIQ SQL Management Suite – Enterprise Monitoring and Alerting - www.netiq.com/products/sql/default.asp

Building the Upgrade Project Plan

An Upgrade project that is critical to the business requires project planning in order to efficiently and accurately complete the project. Due to the sheer size of the project and the number of individuals involved, completing the project properly becomes more of a challenge. Although this can be challenging, as the DBA you are the cornerstone of the SQL Server environment. You can take on this project to benefit the company and showcase your skills to demonstrate that you can take on more responsibility. In order to break down the SQL Server Upgrade project, a DBA must:

Identify the major project phases¹

Expand the project phases to granular tasks in the proper sequence¹

Determine time frame and responsibility per task¹

Incorporate meetings, sign-off and hyperlinks to existing information into the plan¹

Leverage a Project Management tool like Microsoft Project 2002 – For more information refer to - http://www.microsoft.com/office/project/default.asp

The next section of the article provides a fundamental outline of the Upgrade Project Phases for the SQL Server 2000 project which can serve as a starting point for the Project Plan.

For additional IT Project Management information, be on the lookout for a Project Management eBook from Jeremy Kadlec in the summer of 2003.

Upgrade Project Phases

In order to properly address the SQL Server 2000 Upgrade, it is necessary to setup a project plan with the necessary components for your environment. Below outlines a set of recommendations for the upgrade project plan. Can be further broken down with dates and time frames

Table 2: Upgrade Project Phases¹

ID	Phase	Description
1	Requirements Analysis	Setup a comprehensive Project Plan with tasks granular enough to assign to a single individual on the project Hold a Kick-Off Meeting to properly start the project Determine Upgrade Date and Time with the associated Downtime Determine the Upgrade Freeze and Thaw Dates for Testing Purposes Setup Roles and Responsibilities in order to establish Project Accountability Submit a Change Management Request to notify key players in the corporation Determine SQL Server Hardware Needs via Capacity Planning (disks, memory, processors, etc.) Sign-Off – Requirements Analysis
2	Design and Development	Build an Upgrade Checklist to determine time frames and proposed processes to complete the Upgrade Test the Upgrade Checklist and verify the results Communicate the process to the team especially in terms of configurations Sign-Off – Upgrade Methodology
3	Functional, Integration, End User and Load Testing	Setup a Test Environment to include the necessary SQL, Middle Tier and Web Servers as well as a Client PC; these machines should be configured as closely as possible to the Production Environment to ensure project success Implement a Load Testing Tool Build Test Plans for Functional, Integration, End User and Load Testing Complete Functional, Integration, End User and Load Testing Manage the Testing Exceptions until Completion for the Upgrade Determine if Front End or T-SQL code must be applied prior to or following the upgrade in order to determine the code roll-out coordination Update previously submitted Change Management request based on Testing results Sign-Off – Testing
4	Production Hardware Setup	Server Assembly as well as Windows and SQL Server 2000 Installation Configure, setup and burn-in the new hardware Sign-Off – Production Hardware
5	Upgrade	GO \| NO GO Meeting Execute the Upgrade Checklist Sign-Off SQL Server 2000 Upgrade Monitor SQL Server Performance Sign-Off – SQL Server 2000 Upgrade

Part 2 – Critical Upgrade Decisions and Redundant Upgrade Architecture

In the coming weeks, the next article in the series will detail the Critical Upgrade Decisions related to ANSI NULLS, Quoted Identifiers, etc as well as a valuable Redundant Upgrade Architecture for the project. These decisions can make or break the upgrade and require fore thought at the inception of the project. Further, find out how to prevent management’s biggest fear during systems upgrades with a redundant architecture. Be sure to check it out!

Resources

Successful Project Management for Database Administrators – Jeremy Kadlec – http://www.edgewoodsolutions.com/resources/presentations.asp - November 2002 - SQL PASS 2002 Seattle Community Summit - Accessed 01.28.2003
SQL Server 2000 Books Online - Microsoft Corporation – Published January 2003 - Accessed 01.28.2003
Upgrading to SQL Server 2000 – Microsoft Corporation - http://www.microsoft.com/sql/howtobuy/Upgrade_to_SQL_Server_2000.doc - Published September 2000 – Accessed 01.28.2003

About the Author – Jeremy Kadlec

Jeremy Kadlec serves as the Principal Database Engineer at Edgewood Solutions. He has been building technical solutions for the last five years primarily focused on SQL Server 6.5, 7.0 and 2000. Mr. Kadlec has set and implemented a number of SQL Server standards to include Upgrades to SQL Server 2000, EMC Migrations, Unattended Installations, Hardware/Software Configurations, Disaster Recovery, Database Security, Server Maintenance and System Performance Tuning. Mr. Kadlec has been able to implement these solutions as a Project Manager and Lead DBA by standardizing a project management methodology which can be used across projects and passed on to new team members.

Edgewood Solutions

Edgewood Solutions (www.edgewoodsolutions.com) is focused on enhancing the Microsoft SQL Server platform and delivering database solutions to further benefit the investments companies have already made based on this product platform. We recommend and implement processes that are often overlooked, but should be part of every database installation. We have partnered with additional companies we feel enhance the SQL Server platform to include: Lumigent, DBAssociates and Precise Software Solutions.

In addition, our employees have taken an active role in the SQL Server community. We are members of PASS and one of our employees is the Vice Chair of the PASS DBA Special Interest Group. We had the distinct opportunity to provide two presentations at the PASS Summit in Seattle, ‘Change Management for SQL Server’ and ‘Successful Project Management for Database Administrators’.

Edgewood Solutions delivers the following SQL Server services:

Disaster Recovery Planning
Security Strategy
Performance Analysis and Tuning
Data Modeling and Database Development
Database Maintenance
System Upgrades
Change Management
Project Management
Customized Training

Learn more about how Edgewood Solutions can deliver ‘databases at their finest’ at www.edgewoodsolutions.com.

Some names and products listed are the registered trademarks of their respective owners.