April 2, 2012 at 5:06 am
We want to decommission an old SQL 2005 physical cluster running 50 databases and migrate everything onto a brand-new (virtual) SQL 2008 R2 server.
Is the Copy Database Wizard and fixing user accounts the only things we need to do? What about connections and other stuff?
Are there more checks to perform? What kind of issues may arise?
Thank you!
April 2, 2012 at 5:26 am
DB's, Logins, Agent Jobs, SSIS Packages, Reports, Cubes..
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
April 2, 2012 at 5:37 am
Microsoft supplies a free tool, the Upgrade Advisor, which will validate that there are no code or structure issues keeping you from upgrading. I'd absolutely run that before starting any upgrade process.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2012 at 6:19 am
Thanks to Henrico and Grant.
Now we have more things to check before the real migration 🙂
Hopefully everything will be allright.
April 2, 2012 at 6:37 am
to add on to what Henrico and Grant have already said, it might be worth going through each folder in object explorer finding every objects and generating scripts for them or using SSIS to transfer the majority of them
DB's are easy as thats backup and restore, can use SSIS to transfer logins
Databases, Logins, Credentials, Backup Devices, Endpoints, Linked Servers, Server Level DDL Triggers, Replication, DBMail Settings, Jobs, SSIS packages, Alerts, Operators, Proxies, Logshipping, Mirroring, sp_configure settings, maintenance plans, SSRS reports, SSAS cubes
biggest thing I can say and this may sound contradicting (sorry if it does) but ensure you test, test, test then test again before releasing the server back into production use as once you have started using 2008 in live, you cant downgrade so it would have to be a script of all objects including databases objects then use SSIS to pump the data back into 2005 should you need to roll it back.
if you dont have the SSIS packages in solutions then the rar file will pull them out of MSDB for you into dtsx format so you can re-import them, into 2008
April 2, 2012 at 8:58 am
other option for logins is to use sp_help_revlogin
April 2, 2012 at 10:37 am
I just did an upgrade from 2005 to 2008 (not R2). The biggest problem we had was to be able to verify that every application would work (57 databases, about 300 EXEs and 40 DTS that got upgraded to SSIS and 50 SSIS packages).
The database part was by far the easiest. 2 hours worth of restores 😉 The total project took about 9 months. Maybe only about 1200 hours worth of time though.
April 2, 2012 at 10:43 am
Is the Copy Database Wizard and fixing user accounts the only things we need to do?
don't use the copy database wizard, its too slow, complicated and unreliable.
Use detach\attach or backup\restore. Preferably the latter for ease of failback.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply