November 13, 2012 at 1:11 pm
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2012 at 1:43 pm
No,
I'd recommend you just to follow your own strategy and planning, not others... it won't help you.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 13, 2012 at 2:22 pm
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
November 13, 2012 at 2:32 pm
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2012 at 2:43 pm
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
November 13, 2012 at 2:47 pm
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
November 13, 2012 at 3:12 pm
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2012 at 3:17 pm
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2012 at 3:18 pm
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
November 13, 2012 at 3:22 pm
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
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2012 at 3:45 pm
Hi All,
My answer was just one sentence, having in mind that I just started a migration process. So it matters what kind of migration you have. For example the above lists won't help me much. It is about the system you're migrating. There are huge lists to do more: table structures (additional design to merge in the existing), code changes (database and .NET codes), newer version of database, the business logic functionality, the test process, ... etc all these topics are the heavier part of a migration according to me. However I appreciate your answers a lot.
Regards
IgorMi
Igor Micev,My blog: www.igormicev.com
November 13, 2012 at 3:55 pm
IgorMi (11/13/2012)
Hi All,My answer was just one sentence, having in mind that I just started a migration process. So it matters what kind of migration you have. For example the above lists won't help me much. It is about the system you're migrating. There are huge lists to do more: table structures (additional design to merge in the existing), code changes (database and .NET codes), newer version of database, the business logic functionality, the test process, ... etc all these topics are the heavier part of a migration according to me. However I appreciate your answers a lot.
Regards
IgorMi
Thanks, Igor. Yes, I completely understood your perspective given that many migrations have so many complex parts not the least of which are any data changes or synchronizations that might have to take place. I was just looking for general basic things that I might have overlooked. Gail pointed out one, which is the DBCC for SQL 2000. I very much appreciate your feedback, though.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
November 13, 2012 at 3:59 pm
Yeah, I always appreciate more experienced members answers, especially Gail's
Thanks
IgorMi
Igor Micev,My blog: www.igormicev.com
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply