SQL Server Upgrade Recommendations and Best Practices - Part 1

,

SQL

Server Upgrade Recommendations and Best Practices

Part 1 – Upgrade Overview and

Project Planning

 

Written by:

Jeremy

Kadlec of 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.

Business Justification

ID

Justification

Supporting Information

1

Total Cost of Ownership3

¨      

Total Cost of Ownership (TOC) lower than any

other DBMS in the market

2

System Performance3

¨      

Unprecedented System Performance for both OLTP

and OLAP environments

¨      

Improved ability to scale up and out by

leveraging expanded hardware resources

o      

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

o      

Enterprise Manager

o      

Query Analyzer

o      

Profiler

o      

Data Transformation Services (DTS)

7

New Capabilities2

¨      

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 phases1

¨     

Expand

the project phases to granular tasks in the proper sequence1

¨     

Determine

time frame and responsibility per task1

¨     

Incorporate

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

¨     

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

Upgrade Project Phases1

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!

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

Rate

Share

Share

Rate