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

Request for migration advice Expand / Collapse
Author
Message
Posted Tuesday, November 13, 2012 1:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
Hi everyone,

I'm in the process of preparing for a migration to SQL Server 2008 R2 next week and would just like to know if anyone has any basic advice or pointers to checklists, articles, scripts, etc. I think I've covered most of the essentials, and we're going with a side-by-side migration strategy, but as always I am concerned that I might be missing something.

Does anyone have that kind of advice for someone going into the final week before a migration?

Thanks in advance for any help or advice!

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1384265
Posted Tuesday, November 13, 2012 1:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 2,934, Visits: 2,959
No,
I'd recommend you just to follow your own strategy and planning, not others... it won't help you.

Regards
IgorMi




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1384283
Posted Tuesday, November 13, 2012 2:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 12,910, Visits: 32,020
side by side migration, or an in place upgrade?

there's not a lot of difference in the preperation, as compared to a full on disaster recovery plan....if the new server doesn't come up, or the existing server goes kaput, it's the same thing;

my quickie checklist i have saved:

- Back up all databases(tail, without recovery)
- Export logins/hashed passwords using sp_help_revlogin
- Export any certificates/master keys/database keys
- Export linked servers
- Export Mail settings from msdb
Export jobs from msdb
- Reload
- Install SQL 2008 R2, and leave it unpatched
- Restore jobs script after find/replace(to get our maintenance plans back)
- Restore the user databases
- Import the logins/usernames and verify mapping
- Patch SQL up to current
few other things you may want to script out or keep track of if they apply:

• sp_configure
• Alerts
• Operators
• Jobs
• SQL Agent config
• Database Mail config
• Trace Flags DBCC TRACESTATUS
• Linked Servers
• Replication publications or subscriptions


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1384302
Posted Tuesday, November 13, 2012 2:32 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
Lowell (11/13/2012)
side by side migration, or an in place upgrade?

there's not a lot of difference in the preperation, as compared to a full on disaster recovery plan....if the new server doesn't come up, or the existing server goes kaput, it's the same thing;

my quickie checklist i have saved:

- Back up all databases(tail, without recovery)
- Export logins/hashed passwords using sp_help_revlogin
- Export any certificates/master keys/database keys
- Export linked servers
- Export Mail settings from msdb
Export jobs from msdb
- Reload
- Install SQL 2008 R2, and leave it unpatched
- Restore jobs script after find/replace(to get our maintenance plans back)
- Restore the user databases
- Import the logins/usernames and verify mapping
- Patch SQL up to current
few other things you may want to script out or keep track of if they apply:

• sp_configure
• Alerts
• Operators
• Jobs
• SQL Agent config
• Database Mail config
• Trace Flags DBCC TRACESTATUS
• Linked Servers
• Replication publications or subscriptions


Thanks, this is what I had in mind. Yes, it is a side-by-side upgrade. I realize, as Igor said, that my specific plan is more relevant than anyone else's advice, but I was just checking for any obvious things I am completely missing so we have as few surprises as possible on the big day. Things like linked servers, permissions, jobs, etc. It looks like your list covers most of what we have, though.

Just one question, though. Why do you leave the new server unpatched until later in the migration?

Thanks again for your help.

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1384305
Posted Tuesday, November 13, 2012 2:43 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 42,832, Visits: 35,964
What are you migrating from? That's a very important point in planning.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1384312
Posted Tuesday, November 13, 2012 2:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:47 PM
Points: 12,910, Visits: 32,020

Just one question, though. Why do you leave the new server unpatched until later in the migration?

it's just something i saved in my notes...i think it has more to do with getting a server up as soon as possiible for disaster recovery....you could patch at the close of business hours, when you might not be pressed for time.

vs making it perfect before allowing users to access the new server.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1384314
Posted Tuesday, November 13, 2012 3:12 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
GilaMonster (11/13/2012)
What are you migrating from? That's a very important point in planning.


Thanks, good point. We are migrating from two different servers into one (consolidation) -- SQL 2000 and SQL 2005. We have reviewed our code as well as we can to account for the changes from those versions, but do you have any other advice given that scenario?

Thanks again,
webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1384325
Posted Tuesday, November 13, 2012 3:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
Lowell (11/13/2012)

Just one question, though. Why do you leave the new server unpatched until later in the migration?

it's just something i saved in my notes...i think it has more to do with getting a server up as soon as possiible for disaster recovery....you could patch at the close of business hours, when you might not be pressed for time.

vs making it perfect before allowing users to access the new server.


OK - thanks. We have an official downtime, rare and not desirable from an availability standpoint, but we really want to move off of SQL 2000 and this is our best window, all things considered. In theory, we will have no new transactions, so if something fails during the migration, then we can back out to the point when we started the migration and re-enable the old servers.

However, I realize that if something happens after we have cut over and allowed people in, if there is an issue at that point, we wouldn't be able to go back to the old servers and would have to troubleshoot as is or use transaction log backups to piece things together. That's the part where I am having trouble being creative enough to imagine what could go wrong that I'm not considering.

Thanks again,
webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1384326
Posted Tuesday, November 13, 2012 3:18 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 42,832, Visits: 35,964
Upgrading from 2000 is a much bigger deal than from 2005, just because of the differences in the versions.

Before you upgrade the 2000 server, run the following:
DBCC CheckDB
DBCC CheckCatalog

If there are any errors at all, do not proceed with the upgrade until you've fixed the errors.

Once you've upgraded, run DBCC CheckDB WITH Data_Purity and make sure it comes back clean. Run DBCC UpdateUsage and update all statistics with fullscan.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1384328
Posted Tuesday, November 13, 2012 3:22 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
GilaMonster (11/13/2012)
Upgrading from 2000 is a much bigger deal than from 2005, just because of the differences in the versions.

Before you upgrade the 2000 server, run the following:
DBCC CheckDB
DBCC CheckCatalog

If there are any errors at all, do not proceed with the upgrade until you've fixed the errors.

Once you've upgraded, run DBCC CheckDB WITH Data_Purity and make sure it comes back clean. Run DBCC UpdateUsage and update all statistics with fullscan.


This is a great help. Thank you for this information.

- webrunner


-------------------
"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Post #1384329
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse