Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Project Plan Expand / Collapse
Author
Message
Posted Monday, February 7, 2005 1:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:18 PM
Points: 33, Visits: 14

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




Post #160127
Posted Tuesday, February 8, 2005 2:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, November 20, 2006 11:09 PM
Points: 220, Visits: 1
Even I was looking for the same
Post #160206
Posted Wednesday, February 9, 2005 8:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 1,234, Visits: 2,911

if it's a possibility  for me ,too.

Thanks in advance.

Stan Gabriel

stangabi@hotmail.com




Post #160549
Posted Wednesday, February 9, 2005 9:00 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 5, 2005 5:37 AM
Points: 344, Visits: 1
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.


Post #160569
Posted Wednesday, February 9, 2005 10:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:18 PM
Points: 33, Visits: 14

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




Post #160599
Posted Monday, February 28, 2005 10:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 25, 2010 12:18 PM
Points: 33, Visits: 14

 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

 

laceName w:st="on">RecyclelaceName> laceName w:st="on">SessionlaceName> laceType w:st="on">StatelaceType> & 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

 

 

 

 

 

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

 

 

 

 

 

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