I'm looking for a decent Project Plan (in any format) for moving from SQL Server 2000 to a SQL Server 2000 Clustered environment. I welcome articles that discuss clustering and testing, but I'm really looking for a Project Plan (Tasks, Duration, Resource, etc).
Thanks,
Matthew
if it's a possibility for me ,too.
Thanks in advance.
Stan Gabriel
stangabi@hotmail.com
Chris,
Thanks- we've already done the Cluster migration, although we're still live with the old box. We are planning on flicking the switch (point live web farm to cluster) in a few weeks. I just want a project plan to see what items i could be missing. I just assumed someone from an organization that has more formal processes (i.e., documentation) would already have a Proj Plan lying around and be able to post it.
Everyone else: once i'm done creating my (God, I hope I know what I'm doing) Project Plan, I'll post to site. I've grabbed a lot from Cluster articles, but who knows...
NOTE: Sorry guys- not sure how to attach a MS Word doc, and i'm in a hurry, so i just copied/pasted my project plan here. The comments as footnotes suck, so if you want the word doc, post your email address and i'll send you what i have. Good luck!!!
Project Title
SQL Server Cluster Migration Project Plan[MJH1]
Start Date
February 1, 2005
End Date
February 20, 2005
Project Manager
Matthew J. Hughes
Systems Developer, CompanyNameHere
Mat_hues@hotmail.com
(###) ###-####
[MJH2]
Version
Date
Author
Comments
1.0
31 Jan 2005
Matthew Hughes
Document Creation
1.1
04 Feb 2005
Added Clustering Installation Worksheets and Checklists to Appendices
1.2
07 Feb 2005
Created Project Plan Overview
1.3
11 Feb 2005
Updated Project Team
Added Project Schedule
1.4
12 Feb 2005
Added Change Document to Appendices
1.5
14 Feb 2005
Added Issues Document to Appendices;
Updated Project Schedule – see Comments
1.6
16 Feb 2005
Updated Issues Document and Change Document
1.7
18 Feb 2005
Updated Project Schedule
1.8
21 Feb 2005
Updating Project Schedule upon completion of cluster migration;
Added Deployment Document;
Updated Issues Document for deployment weekend
Added Test Plans and Lee’s Test Index
2. Introduction
The cluster migration project is moving data from the OURCOMPANY production SQL Server OldServerName[MJH3] to the new SQL Server cluster (NewServerA[MJH4] , NewServerB), including all database objects, helper applications, appropriate security/permissions and network access. See 7.5 Change Document in the Appendices for the changes made (connection, configuration) for the migration.
Team Member
Initials
Responsibilities
Owner (Support), QA
Owner (Development), QA
Network, Cluster, DBA
DBA
SmartStream, QA
Web Applications, QA
Jobs/Operators, QA
QA
ID
Phase
Description
1.
Requirements Analysis & Scope
§ Determine Aims and Objectives
§ Hold a Kick-Off Meeting to properly start the project
§ Determine if we need Freeze and Thaw Dates for actual deployment; Communicate possibility to management
§ Setup Roles and Responsibilities in order to establish Project Accountability
§ Determine SQL Server Hardware Needs via Capacity Planning (disks, memory, processors, etc.)
v * MILESTONE: Sign-Off – Requirements Analysis
2.
Design and Development
§ Create Configuration Worksheets and Installation Checklist
§ Build Test Plans for Functional, Integration, End User and Load Testing
§ Based on Test Plans and Checklists, setup a comprehensive Project Plan with tasks granular enough to assign to a single individual on the project.
§ Determine Cluster Date and Time with the associated Downtime
§ Communicate the process to the team (configurations, test, schedule, assignments)
§ Create Contingency Plan in case cluster fails on deployment
v * MILESTONE: Sign-Off – Project Plan
3.
Production Hardware Setup
§ Server Assembly as well as Windows and SQL Server 2000 Installation
§ Configure, setup and burn-in the new hardware
v * MILESTONE: Sign-Off – Hardware Setup Complete
4.
Install Cluster
§ Install Cluster using Installation Checklist
§ Complete Cluster Configuration Worksheets
v MILESTONE: Sign-Off – Cluster Checklist
5.
Functional, Integration, End User and Load Testing[MJH5]
§ Restore production databases to Cluster; Compare all database objects between production and cluster
§ 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 (MS Application Center Test)
§ Complete Functional, Integration, End User and Load Testing
§ Manage the Testing Exceptions until Completion for the Cluster
v * MILESTONE: Sign-Off – Testing Complete
6.
Cluster Deployment[MJH6]
§ GO | NO GO Meeting
§ Test all permissions and dependencies; Execute the Cluster Checklist
§ Monitor SQL Server Performance, functionality and integration.
v * MILESTONE: IF SQL Server Cluster succeeds
o Sign-Off – SQL Server 2000 Cluster Total Success
Else: Cluster Fails
7.
Cluster Maintenance
v * MILESTONE: Sign-Off –Maintenance Plan Document
* NOTE: Every Milestone also includes documented Lessons Learned, which are to be appended in the ‘Lessons Learned’ section in the Appendices. All changes and issues are also documented and added to the project plan (7.5 Change Document, 7.6 Issues Document).
Task_Summary_Name
Task_Name
Resource Name[MJH7]
Finish Date
Project Scope[MJH8]
MEETING: Project Scope
Team
1/31/2005
Determine Staff, Stakeholders and Goals
2/1/2005
Produce Scope Document
(Requirements Analysis Phase)
MEETING: Kick Off
Identify Order and Obtain Hardware
2/2/2005
SQL Server Configuration/Security
Determine Communications Procedure
Requirements Analysis Document
2/4/2005
(Design and Development Phase)
Create Configuration Worksheets
2/7/2005
Build Test Plans and Checklists
2/10/2005
2/12/2005
Create Contingency Plan (leave OLDSERVERNAME alone for Spring quarter; create Change Document to fallback to OLDSERVERNAME)
2/9/2005
Create Contingency Plan (leave OLDSERVERNAME alone for Spring quarter)
Create Project Plan/Schedule
2/8/2005
Create & Maintain Change Document - in case we revert back to OLDSERVERNAME
2/11/2005
2/19/2005
Communicate process/plan to Team
Communicate Data Freeze Possibility to Field
2/14/2005
TEST Phase [MJH9]
SQL Server
(Test Phase)
Create script to restore dbs & permissions (db access)
Test job alerts[MJH10] ; verify SQL Mailer setup MAPI; Job Responsibility Matrix)
Test job alerts; verify SQL Mailer setup MAPI; Job Responsibility Matrix)
Verify Security Settings (sp, tbl, udf, job, vue)
2/15/2005
Test File Transfer page – ftp from and to OtherOrganization.
2/16/2005
OtherOrganization FTP via VPN -
Verify DTS Packages [MJH11] (already on Cluster)
Setup QA Server- so we can restore veritas backups to SQL Server.
Setup wireless network through a cable modem so we can test outside of domain
Verify Databases - Full Recovery mode, AutoStat=On (scripted??)
2/17/2005
Cluster Failover and Failback - both directions
SQL Server Helper Apps
Test & Document Helper Apps)
Test & Document Helper2
Web Applications
Setup web apps to point to cluster
Setup Farm1 to point to cluster
Test your specific web apps here against SQL Cluster - see Test Plans
2/18/2005
Test all web apps from outside of the network also
Windows Applications
your specific win apps here
Reports
your specific reports here
Network/Machine paths to/from SQL Server
Test Folder setup for SQL backups, jobs, config settings [MJH12]
ODBC Connection setup (your specific apps here)
hard-coded paths[MJH13] , permissions, file exists, rename to NewServerName, bottlenecks
hard-coded paths[MJH14] , permissions, file exists, rename to NEWSERVERNAME, bottlenecks
SQL Server connectivity
SS/Sybase linked Server
Documented Test plan [MJH15] for all connections
Documented Test plan for all connections
Test connection for every application, batch, job and process
SmartStream Helper VB Apps (Test Phase)
Test every SS VB app - normal use (and failover?)
SQL Server Maintenance
Veritas backup working correctly
Create Backup schedules[MJH16] , directories, archiving off-server (compression? Auto restore to dev server? Available backups? Test restore?)
Create a DB Maint Plan for backups if Carl hasn’t setup veritas yet
Optimization Plans (indexes, statistics, etc)
Optimization Plans (indexes, statistics, etc)[MJH17]
Load Testing [MJH18] <Optional>
Test failover in both directions during load test; analyze indexing strategies[MJH19]
Test failover in both directions during load test
Review performance results from MACTest during the failover/failback period
Testing Sign Off
MEETING: Cluster Deployment GO/NO GO - Review Test Results; Probability of Success
Communicate Data Freeze Time to Field
Create Deployment document & Rollback Document[MJH20]
Review Deployment document & Create Deployment Schedule
Maintain Deployment document & Deployment Schedule
Cluster Migration (Deployment Phase)
Restore production databases to cluster early AM
Test basic functionality, connectivity, permissions
Update DNS for WebApp
Disable Transmit (Lathem interface) and setup static home page to let field users know site is down.[MJH21]
Enable ftp Jobs
Update application connections/ODBCs (archive old conn)
Replace StateServer path on web farms 2 & 3 - point to OLDSERVERNAME after deploy
Deploy SmartStream VB Helper Apps (archive old apps)
MEETING[MJH22] : Cluster Success Sign Off
2/20/2005
Communicate [MJH23] Cluster status to Field
SQL Server Optimization & Monitoring
(Stabilization Phase)
SQL Server deadlocks
3/9/2005
Code Optimization/T-SQL best practices
SQL Server settings optimized (utilities to monitor, track?)
Capture metrics on performance, deadlocks, bottlenecks
Analyze metrics; create and test alternative strategies
laceName w:st="on">RecyclelaceName> laceName w:st="on">SessionlaceName> laceType w:st="on">StatelaceType> & SQL Server?
2/21/2005
Post Cluster Migration
MEETING: Lessons Learned Review
2/24/2005
Add Farm1 to the web farm
Schedule failover Test for Tue [MJH24] 2/15
Document/Archive Everything - if cluster fails, we fall back to OLDSERVERNAME
Coordinate with Interactive before and during Failover - esp for Load Tests
Capture benchmark on StateServer cpu/ram % - determine reqs for dedicated stateServer
2/28/2005
Optional: Set up AWStats
DB Maintenance Plan: Optimizations
---------
-----------
Get
-------
Team Member Name
Logical Disk
Size
RAID Partition
Owned By
Use
C:\
Local System
OS
Instance 1
Transaction Log
Data Log
Instance 2
Q:\
Server Cluster
Quorum[MJH26]
Parameter
Value
Cluster Name
Cluster Domain Adminstrator Account
Cluster Domain Administrator Password
DNS Server(s)
WINS Server
Cluster IP Address
Cluster IP Subnet
Quorum Drive Letter
Q:
Number of Nodes
2
Drives for Cluster Use
5
IP Address
IP Subnet
RAID
Level
Type Of Disk
Number Of Disks
Partition Size
We used linked server to other/legacy databases, and had a lot of information here. Skip or omit this section if you don’t use Linked Servers.
Activity
Pass
Sign Off
No errors that interfere with Install in Event Viewer
Domain accounts created for SQL Server administration
All resources moved to node which is going to initiate the installation process of SQL Server
All nodes have access to the installation path if using a network share
Ping the local loopback 127.0.0.1
Node 1
Node 2
Ping the individual address of each network adapter on each node from each node
Ping the individual address of each network adapter on each node from a machine not in the cluster
Ping the name of each individual node from each node of the cluster
Ping the name of each individual node from a machine not in the cluster
Verify that RPC service is running on all nodes
Verify cluster disks can be seen by all nodes (power each server on one at a time)
Verify the cluster disks are formatted with NTFS
Domain level administrator account created for administering
All SQL Server resources online and configured
All dependencies configured correctly
Ping SQL Server virtual IP Addresses from all nodes in cluster
Ping SQL Server virtual IP Addresses from a machine not in the cluster
Ping SQL Server virtual server network name from all nodes in cluster
Ping SQL Server virtual server network name from a machine not in the cluster
Fail resources for the SQL Server virtual server to all nodes of the virtual server dWebAppition
Run in query analyzer:
SELECT * FROM ::fn_virtualServerNodes()
Resource Name
Task Name
Duration
Start
Finish
Cost
SQL Cluster Prep
Install Legacy/Helper app Drivers
Configure SQL Linked Server (linksrv)
Script Operators
Script SQL Jobs
Script Raw Data Restoration
Script Users
Script Database Permissions
Script DTS Packages
Install .NET 1.0, 1.1
Deploy SQL Helper apps to Cluster
Update Legacy/Helper app Cns
laceName w:st="on">Install.NETlaceName> laceName w:st="on">SessionlaceName> laceType w:st="on">StatelaceType> Service
Identify & Remove Unused SQL Logins
Remove web app from existing farm
Recompile Legacy/Helper app w. New Cn
Identify Other App Cns
Identify ODBCs
Web Farm Config (web server names)
Install 2003 Server
Configure Load Balancing
Configure IIS Sites
Deploy Web App
Configure Web App SQL Connection
Configure Web App Session Connection
Preliminary Testing
Restore Databases to Cluster
Disable SQL FTP Jobs
Test Linked Servers
Test SQL Jobs (Non-FTP)
Test Web Application
Test Load Balancing
Test Legacy/Helper apps
Production Deployment
Field Email Notification
Enable SQL FTP Jobs
Update Other App Cns
Final SQL Testing
Final Application Testing
Legacy/Helper app Deployment
Update DNS for web app
Update ODBCs
Lessons Learned
§ Know what your goals are before you begin this. List them in your project documentation. More ram and processors won’t make your apps faster if your design has bottlenecks.
§ Identify potential disadvantages to migrating, and create a risk mitigation strategy. I only had 2 weeks, so my risk mitigation strategy was to document every issue and change as it occurred, in case we had to fail back to our old sql server.
§ Document everything in your environment that touches your sql box – web apps, win apps, odbcs, helper apps, data, etc.
§ Create a test index (skeleton of issues to test)
§ Create test plans that your grandmother could use (flesh out the test index- see my test case template)
§
§ If you bring someone in to setup your cluster, don’t let him/her out of the building until they provide appropriate documentation.
Functional, Integration, End User and Load Testing
§ Execute test plans
§ Add missed items/issues to your test plans as they occur for next iteration of testing
Cluster Deployment
§ Use db comparison tools (RedGate [MJH29] or DBGhost) to compare data AND db objects before and after every restore[MJH30] during testing and especially before go live.
§ Run all jobs after 1st (test) restore – test for failure first (pages, operators), then success
§ Make sure no one from support or development is logged on any of the servers needed for the migration (remote, locked, etc)
§ Send out an email before and after rebooting servers to network admins
(follow up after deployment)
§ Script all db objects for all databases and add to vss
§ Ensure vss is backed up to tape
This document records all changes made to migrate SQL Server from OLDSERVERNAME to the SQL Cluster (NEWSERVERNAMEA and NEWSERVERNAMEB).
Machine
Application
Changes Made[MJH32]
Linked server provider
SQL Server - Jobs
SQL Server – Helper Applications
SQL Server – Permissions
Web App Connections & Configuration
Web App Deployment
Helper Apps
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
This document tracks all of the issues that came up during the Cluster Migration project.
Issue Name
Day[MJH33]
By[MJH34]
Issue Details
Resolution[MJH35]
ü
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
