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 | |
Start Date | February 1, 2005 |
End Date | February 20, 2005 |
Project Manager | Systems Developer, CompanyNameHere (###) ###-#### |
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.
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 |
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. | § 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. | § 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
| |
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 | Start Date | Finish Date | |
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 |
|
|
|
|
|
|
|
|
| |
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) |
|
|
|
| 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 |
|
| 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 |
|
| 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 |
|
| 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 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Logical Disk | Size | RAID Partition | Owned By | Use |
C:\ |
|
| Local System | OS |
|
|
| Instance 1 | Transaction Log |
|
|
| Instance 1 | Data Log |
Logical Disk | Size | RAID Partition | Owned By | Use |
C:\ |
|
| Local System | OS |
|
|
| Instance 2 | Transaction Log |
|
|
| Instance 2 | Data Log |
Q:\ |
|
| Server Cluster |
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 |
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 |
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 |
RAID Partition | 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 | |
|
| |
|
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 | |
|
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() | |
|
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 |
|
|
|
|
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
|
This document records all changes made to migrate SQL Server from OLDSERVERNAME to the SQL Cluster (NEWSERVERNAMEA and NEWSERVERNAMEB).
ID | Machine | Application | |
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. |
|
|
|
This document tracks all of the issues that came up during the Cluster Migration project.
ID | Issue Name | Issue Details | ü | |||
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. |
|
|
|
|
|
|
| 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 |
|
|
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 |
|
|
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 |
|
|
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 |
|
| |
|
|
| |
|
|
| |
|
|
|
Restore to cluster [MJH37] NewServerNamea\c$\SQL\CLUSTER_DEPLOY
|
|
| |
|
|
| |
Make sure all jobs are enabled on Cluster
|
|
| |
Disable jobs:
|
|
| |
Compare all db objects and data[MJH38] after restore/scripts |
|
|
| Deployment Task | Owner | Time |
Deploy your specific helper apps here |
|
| |
Test linked server & your specific helper apps here |
|
|
|
|
| |
|
|
|
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
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 |
|
|
IF SUCCESS: Swap WebApp.com back over on Farm 2 & 3 |
|
| |
Reboot servers
|
|
| |
|
|
| |
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
|
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 |
|
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. |
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
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.