Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Request for migration advice


Request for migration advice

Author
Message
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3752
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
Igor Micev
Igor Micev
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4166 Visits: 4847
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3752
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
What are you migrating from? That's a very important point in planning.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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


Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14955 Visits: 38949

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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3752
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3752
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47204 Visits: 44367
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, MVP, M.Sc (Comp Sci)
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


webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3028 Visits: 3752
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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

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