SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Bill Richards-377350
Bill Richards-377350
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 395
Comments posted to this topic are about the item Database Server Upgrades – The Plan, The Template, and The Task List
shashi kant
shashi kant
Old Hand
Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)Old Hand (303 reputation)

Group: General Forum Members
Points: 303 Visits: 547
Thanks Bill,

For sharing such a good Upgrade plan Specially the Excel sheet you have uploaded.
Pyay Nyein
Pyay Nyein
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 404
Thanks for the good article! we are planning a server upgrade in the near future and it will become very handy :-D
Jack Corbett
  Jack Corbett
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24268 Visits: 14905
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
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
David Jackson
David Jackson
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 1957
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 Smile

Any one else want to add to it?

HTH

Dave J


http://glossopian.co.uk/
"I don't know what I don't know."
Steve Jones
Steve Jones
SSC Guru
SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)SSC Guru (83K reputation)

Group: Administrators
Points: 83391 Visits: 19223
Excellent Job, Bill!

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Conan Whalen-McKain-370433
Conan Whalen-McKain-370433
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
Points: 274 Visits: 453
Outstanding article. This is an essential task that all DBAs do eventually have to do.
Sailor
Sailor
Mr or Mrs. 500
Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)Mr or Mrs. 500 (542 reputation)

Group: General Forum Members
Points: 542 Visits: 861
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.



Bill Richards-377350
Bill Richards-377350
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 395
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
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4739 Visits: 1619
Thanks Bill. Excellent list. Will be very handy when upgrading to 2008 version.

SQL DBA.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search