Project Plan

• Mat Hues

  Mr or Mrs. 500

  Points: 597

  More actions

  February 7, 2005 at 1:05 pm

  #85077

  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

• Hrudhay

  SSChasing Mays

  Points: 638

  More actions

  February 8, 2005 at 2:30 am

  #541079

  Even I was looking for the same

• Hadrian

  Hall of Fame

  Points: 3742

  More actions

  February 9, 2005 at 8:14 am

  #541357

  if it's a possibility  for me ,too.

  Thanks in advance.

  Stan Gabriel

  stangabi@hotmail.com

• ChrisMoix

  SSCommitted

  Points: 1568

  More actions

  February 9, 2005 at 9:00 am

  #541375

  I would perform the migration on a test server and generate your own project plan before I would trust anyone else's. There are too many variables when dealing with a conversion like this (I've done several). If you don't have access to a test cluster (and you can't perform a dry run on the production cluster before hand), look into VMWare of Virtual PC, etc.

• Mat Hues

  Mr or Mrs. 500

  Points: 597

  More actions

  February 9, 2005 at 10:19 am

  #541399

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

• Mat Hues

  Mr or Mrs. 500

  Points: 597

  More actions

  February 28, 2005 at 10:34 am

  #544462

   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]

   

   

  
  

   

  1.      Document History

  Version	Date	Author	Comments
  1.0	31 Jan 2005	Matthew Hughes	Document Creation
  1.1	04 Feb 2005	Matthew Hughes	Added Clustering Installation Worksheets and Checklists to Appendices
  1.2	07 Feb 2005	Matthew Hughes	Created Project Plan Overview
  1.3	11 Feb 2005	Matthew Hughes	Updated Project Team Added Project Schedule
  1.4	12 Feb 2005	Matthew Hughes	Added Change Document to Appendices
  1.5	14 Feb 2005	Matthew Hughes	Added Issues Document to Appendices; Updated Project Schedule – see Comments
  1.6	16 Feb 2005	Matthew Hughes	Updated Project Schedule – see Comments Updated Issues Document and Change Document
  1.7	18 Feb 2005	Matthew Hughes	Updated Project Schedule
  1.8	21 Feb 2005	Matthew Hughes	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.

   

   

  3.      Objectives and Consequences

   

  3.1 The SQL Server cluster project objectives include:

  • Reduced I/O bottlenecks
  • Increased query performance for reports and slow-loading web pages
  • Increased transaction throughput
  • Improve maintainability of SQL Server

  • Create a job responsibility matrix for all processes.  The SQL Job Matrix should enumerate all SQL jobs, descriptions, “criticality”, schedule, average run time, dependencies, and success / failure email groups. Identify escalation owners for each job in the event of job failure
  • Consider creating database maintenance plans that automatically rebuild indexes on tables identified as critical
  • Determine an automated way of daily moving production databases onto a development/test SQL Server

• Permit an automatic response to failed server or software without human intervention
• Provide fast and simple failover capabilities

• Perform upgrades without forcing users off the system for extended periods of time
• Reduce downtime due to routine server, network, or database maintenance or a failure
• Provide rapid fallback once the failed server is restored
• Clustering doesn't require any servers to be renamed. So when failover occurs, the SQL Server instance is relatively transparent (interface remains the same for client applications and end-users)

• Increase the scalability of an application.

 

3.2 Potential Disadvantages to SQL Server Clustering:

• More expensive than other failover alternatives, such as log shipping or stand-by servers.
• Requires more set up time than other alternatives.
• Requires more on-going maintenance than other alternatives.
• Requires more experienced DBAs and network administrators.
• Hard-coded dependencies (\\UNC) to the machine hosting a specific instance of SQL Server will fail if not updated during/after a failover.

 

 

 

 

 

 

 

 

 

 

 




 

4.      Project Team

 

Team Member	Initials	Responsibilities
		Owner (Support), QA
		Owner (Development), QA
		Network, Cluster, DBA
		DBA
		SmartStream, QA
		Web Applications, QA
		Web Applications, QA
		Jobs/Operators, QA
		QA
		QA

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 




 

5.      Project Plan Overview

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 §          Restore production databases to Cluster; Compare all database objects between production and cluster §          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 Execute Contingency Plan; Communicate and Reschedule
7.	Cluster Maintenance	DB Maintenance Plan Databases are archived off the database server; Backups are automatically restored to the development/test server(s). Job Responsibility Matrix 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).

 




 

6.      Project Schedule

Task_Summary_Name	Task_Name	Resource Name[MJH7]	Start Date	Finish Date
Project Scope[MJH8]	MEETING: Project Scope	Team	1/31/2005	1/31/2005
	Determine Staff, Stakeholders and Goals		2/1/2005	2/1/2005
	Determine Staff, Stakeholders and Goals		2/1/2005	2/1/2005
	Produce Scope Document		2/1/2005	2/1/2005
(Requirements Analysis Phase)	MEETING: Kick Off		2/1/2005	2/1/2005
	Identify Order and Obtain Hardware		2/2/2005	2/2/2005
	Identify Order and Obtain Hardware		2/2/2005	2/2/2005
	Identify Order and Obtain Hardware		2/2/2005	2/2/2005
	SQL Server Configuration/Security		2/2/2005	2/2/2005
	SQL Server Configuration/Security		2/2/2005	2/2/2005
	Determine Communications Procedure		2/2/2005	2/2/2005
	Requirements Analysis Document		2/4/2005	2/4/2005
(Design and Development Phase)	Create Configuration Worksheets		2/7/2005	2/7/2005
	Build Test Plans and Checklists		2/10/2005	2/12/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	2/9/2005
	Create Contingency Plan (leave OLDSERVERNAME alone for Spring quarter)		2/9/2005	2/9/2005
	Create Project Plan/Schedule		2/8/2005	2/9/2005
	Create & Maintain Change Document - in case we revert back to OLDSERVERNAME		2/11/2005	2/19/2005
	Create & Maintain Change Document - in case we revert back to OLDSERVERNAME		2/11/2005	2/19/2005
	Communicate process/plan to Team		2/9/2005	2/9/2005
	Communicate Data Freeze Possibility to Field		2/14/2005	2/14/2005
TEST Phase [MJH9]
SQL Server (Test Phase)	Create script to restore dbs & permissions (db access)		2/14/2005	2/14/2005
	Test job alerts[MJH10] ; verify SQL Mailer setup MAPI; Job Responsibility Matrix)		2/10/2005	2/11/2005
	Test job alerts; verify SQL Mailer setup MAPI; Job Responsibility Matrix)		2/10/2005	2/11/2005
	Verify Security Settings (sp, tbl, udf, job, vue)		2/15/2005	2/15/2005
	Test File Transfer page – ftp from and to OtherOrganization.		2/16/2005	2/16/2005
	OtherOrganization FTP via VPN -		2/15/2005	2/15/2005
	Verify DTS Packages [MJH11] (already on Cluster)		2/16/2005	2/16/2005
	Setup QA Server- so we can restore veritas backups to SQL Server.		2/16/2005	2/16/2005
	Setup wireless network through a cable modem so we can test outside of domain		2/16/2005	2/16/2005
	Verify Databases - Full Recovery mode, AutoStat=On (scripted??)		2/17/2005	2/17/2005
	Cluster Failover and Failback - both directions		2/15/2005	2/16/2005
	Cluster Failover and Failback - both directions		2/15/2005	2/16/2005
SQL Server Helper Apps (Test Phase)	Test & Document Helper  Apps)		2/14/2005	2/14/2005
	Test & Document Helper2		2/14/2005	2/14/2005
Web Applications (Test Phase)	Setup web apps to point to cluster		2/14/2005	2/14/2005
	Setup Farm1 to point to cluster		2/14/2005	2/14/2005
	Test your specific web apps here against SQL Cluster - see Test Plans		2/15/2005	2/18/2005
	Test your specific web apps here against SQL Cluster - see Test Plans		2/15/2005	2/18/2005
	Test your specific web apps here against SQL Cluster - see Test Plans		2/15/2005	2/18/2005
	Test your specific web apps here against SQL Cluster - see Test Plans		2/15/2005	2/18/2005
	Test all web apps from outside of the network also		2/15/2005	2/18/2005
	Test all web apps from outside of the network also		2/15/2005	2/18/2005
	Test all web apps from outside of the network also		2/15/2005	2/18/2005
Windows Applications (Test Phase)	your specific win apps here		2/16/2005	2/16/2005
Reports (Test Phase)	your specific reports here		2/17/2005	2/17/2005
	your specific reports here		2/17/2005	2/17/2005
Network/Machine paths to/from SQL Server (Test Phase)	Test Folder setup for SQL backups, jobs, config settings [MJH12]		2/16/2005	2/16/2005
	ODBC Connection setup (your specific apps here)		2/15/2005	2/16/2005
	hard-coded paths[MJH13] , permissions, file exists, rename to NewServerName, bottlenecks		2/15/2005	2/16/2005
	hard-coded paths[MJH14] , permissions, file exists, rename to NEWSERVERNAME, bottlenecks		2/15/2005	2/16/2005
SQL Server connectivity (Test Phase)	SS/Sybase linked Server		2/9/2005	2/9/2005
	Documented Test plan [MJH15] for all connections		2/10/2005	2/10/2005
	Documented Test plan for all connections		2/10/2005	2/10/2005
	Test connection for every application, batch, job and process		2/12/2005	2/14/2005
	Test connection for every application, batch, job and process		2/12/2005	2/14/2005
SmartStream Helper VB Apps (Test Phase)	Test every SS VB app - normal use (and failover?)		2/14/2005	2/15/2005
SQL Server Maintenance (Test Phase)	Veritas backup working correctly		2/10/2005	2/10/2005
	Veritas backup working correctly		2/10/2005	2/10/2005
	Create Backup schedules[MJH16] , directories, archiving off-server (compression? Auto restore to dev server? Available backups? Test restore?)		2/11/2005	2/11/2005
	Create a DB Maint Plan for backups if Carl hasn’t setup veritas yet		2/11/2005	2/11/2005
	Optimization Plans (indexes, statistics, etc)		2/18/2005	2/18/2005
	Optimization Plans (indexes, statistics, etc)		2/18/2005	2/18/2005
	Optimization Plans (indexes, statistics, etc)[MJH17]		2/18/2005	2/18/2005
Load Testing [MJH18] <Optional> (Test Phase)	Test failover in both directions during load test; analyze indexing strategies[MJH19]		2/15/2005	2/15/2005
	Test failover in both directions during load test		2/15/2005	2/15/2005
	Review performance results from MACTest during the failover/failback period		2/16/2005	2/19/2005
Testing Sign Off (Test Phase)	MEETING: Cluster Deployment GO/NO GO - Review Test Results; Probability of Success		2/18/2005	2/18/2005
	Communicate Data Freeze Time to Field		2/18/2005	2/18/2005
	Create Deployment document & Rollback Document[MJH20]		2/18/2005	2/18/2005
	Review Deployment document & Create Deployment Schedule		2/18/2005	2/18/2005
	Maintain Deployment document & Deployment Schedule		2/18/2005	2/18/2005
Cluster Migration (Deployment Phase)	Restore production databases to cluster early AM		2/19/2005	2/19/2005
	Test basic functionality, connectivity, permissions		2/19/2005	2/19/2005
	Update DNS for WebApp		2/19/2005	2/19/2005
	Disable Transmit (Lathem interface) and setup static home page to let field users know site is down.[MJH21]		2/19/2005	2/19/2005
	Enable ftp Jobs		2/19/2005	2/19/2005
	Update application connections/ODBCs (archive old conn)		2/19/2005	2/19/2005
	Replace StateServer path on web farms 2 & 3 - point to OLDSERVERNAME after deploy		2/19/2005	2/19/2005
	Deploy SmartStream VB Helper Apps (archive old apps)		2/19/2005	2/19/2005
	MEETING[MJH22] : Cluster Success Sign Off		2/20/2005	2/20/2005
	Communicate [MJH23] Cluster status to Field		2/20/2005	2/20/2005
SQL Server Optimization & Monitoring (Stabilization Phase)	SQL Server deadlocks		2/20/2005	3/9/2005
	Code Optimization/T-SQL best practices		2/20/2005	3/9/2005
	SQL Server settings optimized (utilities to monitor, track?)		2/20/2005	3/9/2005
	SQL Server settings optimized (utilities to monitor, track?)		2/20/2005	3/9/2005
	Capture metrics on performance, deadlocks, bottlenecks		2/20/2005	3/9/2005
	Analyze metrics; create and test alternative strategies		2/20/2005	3/9/2005
	<st1laceName w:st="on">Recycle</st1laceName> <st1laceName w:st="on">Session</st1laceName> <st1laceType w:st="on">State</st1laceType> & SQL Server?		2/21/2005	2/21/2005
Post Cluster Migration (Stabilization Phase)
	MEETING: Lessons Learned Review		2/24/2005	2/24/2005
	Add Farm1 to the web farm		3/9/2005	3/9/2005
	Add Farm1 to the web farm		3/9/2005	3/9/2005
	Schedule failover Test for Tue [MJH24] 2/15		2/7/2005	2/7/2005
	Document/Archive Everything - if cluster fails, we fall back to OLDSERVERNAME		2/10/2005	2/20/2005
	Coordinate with Interactive before and during Failover - esp for Load Tests		2/15/2005	2/18/2005
	Capture benchmark on StateServer cpu/ram % - determine reqs for dedicated stateServer		2/24/2005	2/28/2005
	Optional: Set up AWStats		2/12/2005	2/12/2005
	DB Maintenance Plan: Optimizations		---------	-----------
	Get		-------	-----------
	Team Member Name	Initials

 




 

7.      Appendices

 

7.1 Cluster Configuration Worksheets[MJH25] 

 

NewServerName A

Logical Disk	Size	RAID Partition	Owned By	Use
C:\			Local System	OS
			Instance 1	Transaction Log
			Instance 1	Data Log

 

 

NewServerName B

Logical Disk	Size	RAID Partition	Owned By	Use
C:\			Local System	OS
			Instance 2	Transaction Log
			Instance 2	Data Log
Q:\			Server Cluster	Quorum[MJH26]

 

 

SQL Server Cluster Configuration

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

 

 

NEWSERVERNAMEA Node Configuration

Parameter	Value
Cluster Name
Cluster Domain Adminstrator Account
Cluster Domain Administrator Password
DNS Server(s)
WINS Server
IP Address
IP Subnet
Quorum Drive Letter	Q:
Number of Nodes	2
Drives for Cluster Use	5

 

 

 

 

 

NEWSERVERNAMEB Node Configuration

Parameter	Value
Cluster Name
Cluster Domain Adminstrator Account
Cluster Domain Administrator Password
DNS Server(s)
WINS Server
IP Address
IP Subnet
Quorum Drive Letter	Q:
Number of Nodes	2
Drives for Cluster Use	5

 

 

Shared Cluster Disk Partition Configuration

RAID Partition	RAID Level	Type Of Disk	Number Of Disks	Partition Size

 

Linked Server

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.


7.2 Post-Installation Cluster Checklist[MJH27] 

 

Pre-SQL Server 2000 Virtual Server Installation Checklist

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	
	

 

Post-Installation Windows Clustering Installation Checklist

Activity	Pass	Sign Off
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	        Node 1         Node 2
Ping the individual address of each network adapter on each node from a machine not in the cluster	        Node 1         Node 2
Ping the name of each individual node from each node of the cluster	        Node 1         Node 2
Ping the name of each individual node from a machine not in the cluster	        Node 1         Node 2
Verify that RPC service is running on all nodes	        Node 1         Node 2
Verify cluster disks can be seen by all nodes (power each server on one at a time)	        Node 1         Node 2
Verify the cluster disks are formatted with NTFS	
Domain level administrator account created for administering	
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	

 

Post-SQL Server 2000 Virtual Server Installation

Activity	Pass	Sign Off
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()	




 

7.3 Original SQL Cluster & Web Farm Migration Project Plan[MJH28] 

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
	<st1laceName w:st="on">Install.NET</st1laceName> <st1laceName w:st="on">Session</st1laceName> <st1laceType w:st="on">State</st1laceType> 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
	Install .NET 1.0, 1.1
	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
	Restore Databases to Cluster
	Enable SQL FTP Jobs
	Update Other App Cns
	Final SQL Testing
	Final Application Testing
	Legacy/Helper app Deployment
	Restore Databases to Cluster
	Update DNS for web app
	Update ODBCs

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 




 

7.4 Lessons Learned

ID	Phase	Lessons Learned
1.	Requirements Analysis & Scope	§          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.
2.	Design and Development	§          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) §
3.	Production Hardware Setup	§
4.	Install Cluster	§          If you bring someone in to setup your cluster, don’t let him/her out of the building until they provide appropriate documentation.
5.	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 §
6.	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
7.	Cluster Maintenance (follow up after deployment)	§          Script all db objects for all databases and add to vss §          Ensure vss is backed up to tape Monitor all critical jobs

 

 

 

 

 

 

 

 

 

 

 




 

7.5 Change Document [MJH31] 

This document records all changes made to migrate SQL Server from OLDSERVERNAME to the SQL Cluster (NEWSERVERNAMEA and NEWSERVERNAMEB).

ID	Machine	Application	Changes Made[MJH32]
1.		SQL Server	Linked server provider
2.		SQL Server - Jobs
3.		SQL Server – Helper Applications
4.		SQL Server – Permissions
5.		Web App Connections &  Configuration
6.		Web App Deployment
7.		Helper Apps
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

 

 




 

7.6 Issues Document

This document tracks all of the issues that came up during the Cluster Migration project.

ID	Issue Name	Day[MJH33]	By[MJH34]	Issue Details	Resolution[MJH35]	ü
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.

 

 

 

 

 

 

 




7.7 Deployment Document[MJH36] 

 

1. Pre-Deployment Tasks – Friday, February 18, 2005

	Deployment Task	Owner	Time
	Create Deployment Document & Rollback Document
	Approve Deployment Document
	Approve Rollback (to OLDSERVERNAME) Document
	GO/NO GO Meeting = GO
	Communicate to Field
	Create downtime site with temp host
	Search ODBCs on OLDSERVERNAME

 

2. Pre-Deployment Tasks – Saturday, February 19, 2005

	Swap over your web apps and stop default site on Farms
	Point farm2 and 3 to test cluster web app- cluster.blahblah.com
	Disable SQL Jobs on OLDSERVERNAME
	After site is down, run sp_who to make sure no process is running against OLDSERVERNAME
	Backup ALL databases off OLDSERVERNAME
	Copy ALL databases over to NEWSERVERNAMEA

 

3. Point your specific web apps here to Cluster (web farm servers)

	Copy this file \\YourWebServer1\ Rename old to OldServerNamefor rollback - \\YourWebServer2\e$\WebSites\WebApp.com\common\Dsn\inc_servername.asp \\YourWebServer3\e$\WebSites\WebApp.com\common\Dsn\inc_servername.asp
	Restart IIS

 

4. SQL Server – OLDSERVERNAME

	After all databases are backed up and restored to new server, stop SQL Server service and SQL Server Agent on OLDSERVERNAME
	Set SQL Server service to manual instead of automatic on OLDSERVERNAME and change sa pwd

 

5. SQL Server – SQL1\WebApp (Cluster)

	Restore to cluster [MJH37] NewServerNamea\c$\SQL\CLUSTER_DEPLOY Make sure restore script grabs ALL dbs from this mornings’ backup from OLDSERVERNAME Make sure all permissions are in script Make sure all db changes are scripted to folder
	Make sure all jobs are enabled on Cluster
	Disable jobs:
	Compare all db objects and data[MJH38]  after restore/scripts

 

6. Legacy/Helper Apps

	Deployment Task	Owner	Time
	Deploy your specific helper apps here
	Test linked server & your specific helper apps here

 

7. your specific web apps here

 

8. Post-Deployment TESTING Tasks – Saturday, February 19, 2005

	Execute test scripts & FOS Report
	Execute test [MJH39] scripts, test Reports & WebApp 1.0
	Execute SS VB Helper Apps

 

---------- STOP DATA CHANGE TESTING [MJH40] at 2 PM: Only view pages/reports after 2 PM

9. SUCCESS Tasks – Saturday, February 19, 2005

	Temporarily kill http://cluster.WebApp.com while MH restores
	Restore all dbs again & create DB Maint BACKUP Plan
	Run thru 5 SQL Server - SQL1\WebApp (Cluster) again
	Compare db objects and db data between SQL1 and OLDSERVERNAME
	After restore, bring http://cluster.WebApp.com up and MH emails testers to begin VIEWING the site
	VIEW all pages/reports in WebApp WITHOUT making changes – this data is LIVE PRODUCTION data
	Monitor jobs

 

10. GO/NO GO Conference – Sunday, February 20, 2005

	IF SUCCESS: Swap WebApp.com back over on Farm 2 & 3
	Reboot servers YOURWEBSERVER2 & YOURWEBSERVER3 NEWSERVERNAMEA
	IF FAILURE: Implement Rollback Document

 

Reminders for next migration/deployment

§          Data compare after each restore §          Db object compare before first restore, after first (test) restore, and after live restore §          Run all jobs after 1st (test) restore – test for success first, then failure §          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 §          Make sure to log in to Web1 as DevUser after a reboot

Follow after migration/deployment

§          Script all db objects for all databases and add to vss §          Ensure vss is backed up to tape Monitor all critical jobs Update everything using sa account to a dba account so the sa password can be changed independently

 

 




 

7.8 Test Plans[MJH41] 

 

Introduction

Basically, this test script walks the user through testing the basic functionality of all of the Setup pages in OurWebApp 2.0 (cluster.WebApp.com).  Please be as thorough as possible – TRY TO BREAK IT. Although we’re not testing the web site’s functionality, we do want to look for missing permissions/access based on using the sql cluster instead of OLDSERVERNAME. 

Note: We need to test all of this functionality on both NEWSERVERNAMEA and NEWSERVERNAMEB, so remember to test everything the same after the failover.

 

Test Case Title (Path to web page)

Description	Description of Test Case
Author	Employee(s) created/modified this test case
Date	Date test case is created
Revised	Last mod date
Actors	Entities (people, process, program) that interface with this test case
Pre-conditions	Requirements for test case
Actions	List of enumerated steps, generally in an ordered sequence; steps that have no dependencies can be bulleted to show there is no defined order
Post-conditions	Results of test case
Includes	List of dependencies/relationships to other test cases
Additional Notes	Caveats, warnings, etc.
Business Rules	Test or reminder for business rules that should be applied.  Reference to Use Case &/or Configuration of business rules.

 




 

7.9 Test Index[MJH42] 

 

         I.      SQL Server Procedures and Functions

A.      SQL Server Jobs

1.       Verify all jobs moved over correctly and run without errors.

B.      SQL Server Databases

1.       Verify security settings.

a)       Procedures

b)       Tables

c)       Functions

2.       FTP – create a dummy ftp with exact format of files received by OtherOrganization and test ftp jobs

C.      DTS packages

1.       Package that pull information in from SQ1

 

       II.      Applications

A.      Web Applications

1.       OurWebApp

2.       OurWebApp 2.0

3.       Access Reports

B.      Windows Applications

C.      SQL Server Helper Applications

D.      Paths to SQL Server In all Apps

1.       Check for hard-coded paths/locations on network (permissions, file exists, rename to NEWSERVER, bottlenecks)

2.       Check for hard-coded paths/locations on local box (permissions, file exists, rename to NEWSERVER, bottlenecks)

3.       Helper Apps

E.      Folder Setup for SQL Jobs

F.      Connection Strings (db.config)

1.       YOURWEBSERVER2

2.       YOURWEBSERVER3

G.     ODBC Connection Setup

 

      III.      Job Handlers and Notifications

A.      Verify all jobs have correct notification setup.

B.      Verify SQL Mailer setup (MAPI)

C.      Job schedules and Job Operator/Responsibility list

 

    IV.      SQL Server Maintenance

A.      SQL Cluster

1.       Test failover in both directions in the middle of a MACTest

2.       Review performance results from MACTest during the failover/failback period

B.      LegacyApps to SQL Server Connectivity –

C.      Create test plan for LegacyApps to SQL Server connections

1.       Test LegacyApps to SQL Server connection for every application/batch/job/process

2.       Test LegacyApps connectivity/applications during failover/failback

D.      Backup schedules

E.      Maintenance Plans.

F.      Users and Security

 

      V.      SQL Server Optimization

A.      Test SQL Server Deadlocks

1.       Run reports/processes that have the most likelihood for contention problems using MACTest

B.      Code Optimization

 




 

8.      References

 

SQL Server 2000 Failover Clustering

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx

 

SQL Server 2000 Clustering Tips

http://www.sql-server-performance.com/clustering_2000.asp

 

SQL Server Cluster Groups

http://groups-beta.google.com/group/microsoft.public.sqlserver.clustering?hl=en&lr

 

Failover Cluster in SQL Server 2000

http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part4/c1261.mspx

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

---

 [MJH1]I created this project plan and managed the cluster migration project at my work.  I am providing this document as an idea generator for your cluster migration. Please email me [reusable] ideas you would add to this project plan, along with any constructive criticism. Be gentle – it was my first experience as a project manager!

 [MJH2]Of course, you’ll replace my info with your details.

 [MJH3]Do a global replace on OldServerName with the server you’re replacing.

 [MJH4]Do a global replace on NewServerA and NewServerB with the cluster.

 [MJH5]See 6. Project Schedule.

 [MJH6]See Appendices: 7.7 Deployment Document

 [MJH7]I put initials of team members here, but b/c my team was also busy with their regular tasks (web apps), I colour-coded their initials to make it easy for them to see everything they were assigned. Feel free to kill the (gaudy) colours.

 [MJH8]Any requirements analysis was done before I came on staff, so there are no documents for these phases. I added to the project so we know the next time to make sure to understand what our goals and needs are.

 [MJH9]Test on both servers (Node A and Node B)!!

 [MJH10] Test for success and failure (pages, emails, etc).

 [MJH11]NOTE: I used DMO to migrate DTS packages (to rename server settings), but the version history didn’t come over. You might want to copy over sysdtspackages to your new server and then manually rename the server settings.

 [MJH12]If there are directories on your old sql server box, replicate them in new server environment and check for permissions, etc.

 [MJH13]Manually update server.config after failover

 [MJH14]Found for jobs, procs and udfs

 [MJH15]Doc after cluster

 [MJH16]After cluster

 [MJH17]AFTER Cluster migration

 [MJH18]OMITTED: No time to load test.

 [MJH19]The test period was the BEST time to analyze our current indexing strategy, and test alternate indexes. We really missed an opportunity by not testing here.

 [MJH20]Rollback document consists of the Deployment Document and Change Document

 [MJH21]Added 2/14/05

 [MJH22]Rescheduled from 2/21 to 2/20 phone conf

 [MJH23]Rescheduled from 2/21 to 2/20

 [MJH24]Failed over to B Wed; failed over to A Thur; failed back to A Fri

 [MJH25]Grabbed this from SQL2k Resource Kit

 [MJH26]contains information about the state of the cluster

 [MJH27]Grabbed this from SQL2k Resource Kit

 [MJH28]This is the original project plan my boss created.

 [MJH29]We have RedGate.

 [MJH30]By restore, I mean the restore process – restore dbs, update scripts, etc.

 [MJH31]Had to kill all my changes, b/c they’re specific info in our environment.

 [MJH32]I added owner of change here, but you could add another column.

 [MJH33]Project was in 2 weeks, so I omitted month/year from date.

 [MJH34]Initials of team member who found issue.

 [MJH35]I added initials of team member who resolved the issue.

 [MJH36]Removed or replaced specific info

 [MJH37]IDEA: Compare all db objects BEFORE first restore – see if someone made changes that weren’t scripted during the test week – Added 2/21.

IDEA:  [MJH38]Added 2/21

 [MJH39]Db object compare all dbs – Added 2/21

 [MJH40]Data is tentatively LIVE after 2:00 PM – no changes!!

 [MJH41]Obviously, this was a huge part of the project plan – but all of our test cases were specific to our applications. We used the Test Case template for only more complex testing – and we used a simple format for just clicking around testing basic web availability.

 [MJH42]Cut out a lot of our specific stuff – but it’s good to create a list of high level apps/connections/processes to test.