Database Server Upgrades – The Plan, The Template, and The Task List

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bill Richards » Database Server Upgrades – The Plan, The...

12 posts, Page 1 of 2

1 2 »»

Database Server Upgrades – The Plan, The Template, and The Task List

Rate Topic

Display Mode

Topic Options

Author

Message

Bill Richards-377350

Posted Thursday, May 07, 2009 10:05 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:52 AM
Points: 40, Visits: 370

Comments posted to this topic are about the item Database Server Upgrades – The Plan, The Template, and The Task List

Post #712598

shashi kant

Posted Friday, May 08, 2009 1:10 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 5:45 AM
Points: 162, Visits: 457

Thanks Bill,

For sharing such a good Upgrade plan Specially the Excel sheet you have uploaded.

Post #712667

Pyay Nyein

Posted Friday, May 08, 2009 3:12 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, June 14, 2013 9:52 AM
Points: 95, Visits: 344

Thanks for the good article! we are planning a server upgrade in the near future and it will become very handy BigGrin

Post #712738

Jack Corbett

Posted Friday, May 08, 2009 6:28 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:41 PM
Points: 10,613, Visits: 11,952

Great article Bill. As with most good articles you read it and say "Yeah, that makes sense", but then you realize how many times you leave out some of the steps.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #712830

David Jackson

Posted Friday, May 08, 2009 7:26 AM

SSC-Addicted

Group: General Forum Members
Last Login: Friday, March 08, 2013 11:15 AM
Points: 440, Visits: 1,785

I can't agree more with all of the above, good article. I posted a reply to an how do I move to a cluster once, let me find it....

Here is the answer I gave. It is relevant whether you are moving to newer, faster tin or a cluster.

Moving Production Databases from a stand alone server to a cluster is not much different in moving dbs to a new stand alone server. (because you are upgrading the tin for instance). I've got a check list some where, let me dig it out...

found it. This covers most (I can't guarantee all) things to think about.

Task_Name
Cluster Config
Check current Active-Active config ok
Convert to Active-Passive
Test Active-Passive Fail-over
Cluster Available

Install Oracle Driver for Linked Server (If required)
Install JDBC Exteneded Stored Procs (If required)
anything else specific to your site?

Create mail profile
Install Outlook
Configure SQL mail
Successful SQL Mail Test

Cluster Config Complete

Migration Prep

Identify all Dependent DBs to migrate

Check for Original Server references in views, SPs etc.
[list your db names here]
Create script to update relevant objects found above, (if any)

DTS Packages
Identify all packages to migrate
Manually "Save As" packages to NEW server
Update any affected pointers

Scheduled Jobs
Identify all Jobs to be migrated
Copy jobs to New Server
DO NOT COPY JOBS THAT RUN DTS PACKAGES. Reshedule packages instead
Update any affected pointers

Linked Servers
Copy linked servers
Test linked server

Security & Errs
Copy Server Level logins
Copy DB Logins
Test accounts
Copy User defined err messages (I always forget this)

New Backup Structure
Create new backup structure on your backup server

Terminal Servers
Identify all reg settings pointing to old server (If your app stores settings in the registary)
Create reg update pointing to new server

New Server DBs Preperation
Copy Migrating DBs to New Server (For test purposes)
[list your db names here]

Maintanence Plans
Recreate System DBs backup plan
Recreate Sys DB optimisation plan
Recreate User DB backup plans
Recreate User DB Integrity & optimisation plans
Test plans via Scheduled Job
Copy Old Server alerts - Check for Old Server references

Migration
Terminal Servers
Apply reg update to all TS's
Update ODBC setting from OLD Server to to New
Run your app from ***EVERY*** TS

Disbale old server Migrated Scheduled Jobs

Isolate Migrating DBs
[list your db names here] - place in Read-Only mode

Backup migrating DBs
[list your db names here]

Restore Migrating DBs to New Server
[list your db names here]

Check in FULL recovery mode

Run any UPDATE scripts

Enable New Server Scheduled Jobs

Update any DTS connections

Post Migration Checks

Terminal Servers
Run your app .exe from EVERY TS

Isolate dbs on old server, or better, turn it off.

Sunday Morning
Check maintenance task completed successfully
Check Scheduled Jobs completed successfully
Check backups copied to SQLBackups share

MIGRATION COMPLETE - CLUSTER GO LIVE

I'm sure this is not complete, but hopefully it is a good starter for 10 :)

Any one else want to add to it?

HTH

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Post #712883

Steve Jones - SSC Editor

Posted Friday, May 08, 2009 8:46 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 3:19 PM
Points: 31,526, Visits: 13,863

Excellent Job, Bill!

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #713000

Conan Whalen-McKain-370433

Posted Friday, May 08, 2009 9:43 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 15, 2011 2:37 PM
Points: 138, Visits: 453

Outstanding article. This is an essential task that all DBAs do eventually have to do.

Post #713083

Sailor

Posted Friday, May 08, 2009 11:41 AM

SSC Veteran

Group: General Forum Members
Last Login: Today @ 7:08 AM
Points: 236, Visits: 752

Very detailed. I am going through a server consolidation this Sunday with some 70 databases and two machines. (We need a stressed icon.)

Also we have web site monitoring, so that will have to be turned off so our phones aren't going nuts during the outage.

Just want to add that I will have profiler running to proactively look for problems and fix them.

Post #713215

Bill Richards-377350

Posted Friday, May 08, 2009 1:43 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 7:52 AM
Points: 40, Visits: 370

Thanks to everyone who read and commented on my article. I hope that it is useful for other DBAs who are developing an upgrade process. Also, after searching the internet, I did not find a database server upgrade template for SQL Server. My desire was to place a standard template on the internet for the SQL Server community to use in their upgrades.

Bill Richards

Post #713312

SanjayAttray

Posted Friday, May 08, 2009 2:16 PM

Hall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554

Thanks Bill. Excellent list. Will be very handy when upgrading to 2008 version.

SQL DBA.

Post #713339

« Prev Topic | Next Topic »

12 posts, Page 1 of 2

1 2 »»

Permissions