SQL Server Upgrade Recommendations and Best Practices - Part 1



Server Upgrade Recommendations and Best Practices

Part 1 – Upgrade Overview and

Project Planning


Written by:


Kadlec of Edgewood Solutions


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:



1 – Upgrade Overview and Project Planning



Server 6.5 and 7.0 Critical Upgrade Decisions and Redundant Upgrade




Server 6.5 and 7.0 Upgrade Checklist and Application



Upgrades to SQL Server 2000



from SQL Server 2000 to Yukon



Oracle and Data Upgrades to SQL Server 2000



SQL Server 2000 Upgrade Recommendations


– 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.


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




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



Access has grown from a desktop database to a department of users that are severely

stressing the database ultimately leading to corruption and frustration



party Applications need to be upgraded in order to leverage new functionality released

by the vendor and needed for the business



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.


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.

Business Justification



Supporting Information


Total Cost of Ownership3


Total Cost of Ownership (TOC) lower than any

other DBMS in the market


System Performance3


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


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’


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


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


Level of Automation


The level of automation from the SQL Server tool



Enterprise Manager


Query Analyzer




Data Transformation Services (DTS)


New Capabilities2


Analysis Services




XML Integration


Optimizer Enhancements






Log Shipping


New Replication Models


Full Text Indexing


Database Recovery Models


Linked Servers


Third Party Products


SQL LiteSpeed – Compressed and Encrypted backups –




Entegra – Enterprise Auditing Solution – www.lumigent.com/products/entegra/entegra.htm



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/



SQL Management Suite – Enterprise Monitoring and Alerting - www.netiq.com/products/sql/default.asp


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:



the major project phases1



the project phases to granular tasks in the proper sequence1



time frame and responsibility per task1



meetings, sign-off and hyperlinks to existing information into the plan1



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.



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

Upgrade Project Phases1





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



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


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


Functional, Integration, End User and Load



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


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




GO | NO GO Meeting


Execute the Upgrade Checklist


Sign-Off SQL Server 2000 Upgrade


Monitor SQL Server Performance


Sign-Off – SQL Server 2000 Upgrade


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!


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


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.


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’.