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