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 12»»

Database Server Upgrades – The Plan, The Template, and The Task List Expand / Collapse
Author
Message
Posted Thursday, May 7, 2009 10:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 5:29 AM
Points: 40, Visits: 380
Comments posted to this topic are about the item Database Server Upgrades – The Plan, The Template, and The Task List
Post #712598
Posted Friday, May 8, 2009 1:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:27 AM
Points: 162, Visits: 510
Thanks Bill,

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


Post #712667
Posted Friday, May 8, 2009 3:12 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:58 AM
Points: 95, Visits: 362
Thanks for the good article! we are planning a server upgrade in the near future and it will become very handy
Post #712738
Posted Friday, May 8, 2009 6:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:31 PM
Points: 11,265, Visits: 13,027
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
Posted Friday, May 8, 2009 7:26 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, July 12, 2014 6:44 AM
Points: 441, Visits: 1,799
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
Posted Friday, May 8, 2009 8:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
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
Posted Friday, May 8, 2009 9:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Friday, May 8, 2009 11:41 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:33 AM
Points: 246, Visits: 791
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
Posted Friday, May 8, 2009 1:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 5:29 AM
Points: 40, Visits: 380
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
Posted Friday, May 8, 2009 2:16 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Thanks Bill. Excellent list. Will be very handy when upgrading to 2008 version.

SQL DBA.
Post #713339
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse