Post reply

Migrate from SQL 2005 to SQL 2008 -- Pre/Post Migration Steps?

  • November 19, 2013 at 10:08 am

    #300670

    Hello,

    I'm preparing to move a production database from SQL 2005 to SQL 2008, and wanted to know what pre/post migration steps would be necessary on the database?

    I've already made a trial run of the migration, just simply backing up the current production database in 2005 and restoring it to the 2008 instance, and things are running 5x5.

    My basic plan on the database side is to run a database integrity check and then a rebuild index before cutting over. Then I'll back up the database from 2005, and then restore it to 2008. After that, I'll run another database integrity check and rebuild the indexes.

    Is that all I should do on the database side? We're moving from a physical host/OS on 2005 to a SQL 2008 VM running Server 2012 on a VMware box (if that makes any difference). I've got the database/transaction logs backups handled as well as SSIS packages, but I'm mainly concerned about the database itself in terms of performance/rebuilding on the new VM.

    Thanks for any ideas you have!

  • November 19, 2013 at 11:11 am

    #1667509

    just a few things i'd throw in there, but it sounds like you've got it covered pretty well.

    Post-Migration, make sure you rebuild statistics for all tables, in each database with FULLSCAN.

    pre migration, you already mentioned you have SSIS packages under control, but what about:

    any procs/functions/tables in master that might still be being used?

    linked servers?

    database mail settings?

    any traces setup?

    any extended events or service brokers setup?

    any procedures setup to start on service starting?

    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!

  • November 19, 2013 at 11:57 am

    #1667527

    One thing that's caught me in the past is that the SQL users that are created on the destination might not have logins created yet. Whether they already have logins or you create them, the SIDs won't match between the logins and users, so effectively the SQL users won't have any permissions on the destination database.

    You have to script the creation of the login on the new server if necessary. You also have to script the drop and recreation of the user and assignment of the permissions they had before you dropped them.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 19, 2013 at 12:18 pm

    #1667538

    Lowell (11/19/2013)

    just a few things i'd throw in there, but it sounds like you've got it covered pretty well.

    Post-Migration, make sure you rebuild statistics for all tables, in each database with FULLSCAN.

    pre migration, you already mentioned you have SSIS packages under control, but what about:

    any procs/functions/tables in master that might still be being used?

    linked servers?

    database mail settings?

    any traces setup?

    any extended events or service brokers setup?

    any procedures setup to start on service starting?

    I did some research on rebuilding indexes vs updating statistics with the FULLSCAN option, but it's not light reading 😛 Right now, I'm using the rebuild index option in my database maintenance plan (which was created using the wizard). I know I probably need to tweak this a bit manually to see if there are options I can set which may not be set by default to maximize the effectiveness of the rebuild. At any rate, my question is is there a difference between rebuilding index vs using the FULLSCAN option, or is one covered by the other?

    Nothing in master that I can see that we've added.

    We have a linked server to a MySQL database living somewhere in the cloud. Had to fight getting the ODBC Connector for MySQL configured properly on the new server to allow existing functionality to work the same as before. We are switching from 32-bit to 64-bit SQL Server and apparently the 64-bit MySQL driver works differently when trying to update data living in MySQL through my linked server object. Fought hard through that, and ultimately had build "wrapper" views to allow this interface to work. Thanks for the reminder on checking those!

    No database mail, traces, extended events, or service brokers.

    No procedures are triggered upon SQL Server/Agent starting up.

    Thanks for the tips!

  • November 19, 2013 at 12:21 pm

    #1667541

    Ed Wagner (11/19/2013)

    One thing that's caught me in the past is that the SQL users that are created on the destination might not have logins created yet. Whether they already have logins or you create them, the SIDs won't match between the logins and users, so effectively the SQL users won't have any permissions on the destination database.

    You have to script the creation of the login on the new server if necessary. You also have to script the drop and recreation of the user and assignment of the permissions they had before you dropped them.

    Yes, I ran into user id issues in the past like that (I think it was during a migration from SQL Server 7.0 to 2000) and had to do that (script logins and permissions) since the internal IDs of the user ids were different...so I did the same thing this time too.

    The front-end applications seem to work during testing, so I think I'm covered.

    Thanks for the suggestion!

  • November 19, 2013 at 12:36 pm

    #1667546

    rebuilding the indexes updates the statsitics for the columns of the indexes, but not all the statistics for the given table.

    statistics on non indexed columns let the query engine make better selections, and the updating after migration is one of those gotchas i see posted here a lot

    lots of posts screaming "my new super server after upgrading is slower than my old one!"

    one recent example:

    RE: Mass Delete Just Hangs

    here's a decent code snippet i use:

    DECLARE @Exec VARCHAR(MAX)

    SELECT @Exec = ''

    SELECT @Exec = 'UPDATE STATISTICS ' + QUOTENAME(schema_name(schema_id))+ '.' + quotename(name) + ' WITH FULLSCAN ; ' + CHAR(13) + CHAR(10) + @Exec FROM sys.tables ORDER BY name DESC

    --excellent chance that this is > 8000 chars and undisplayable in SSMS

    PRINT LEN(@Exec)

    PRINT @Exec

    EXEC(@Exec)

    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!

  • November 19, 2013 at 12:46 pm

    #1667550

    Lowell (11/19/2013)

    rebuilding the indexes updates the statsitics for the columns of the indexes, but not all the statistics for the given table.

    statistics on non indexed columns let the query engine make better selections, and the updating after migration is one of those gotchas i see posted here a lot

    lots of posts screaming "my new super server after upgrading is slower than my old one!"

    Funny -- we ran into that on the network/file system side. Two weeks ago, our network guy changed the file server from Server 2005 SBS to a Server 2012 VM on a screaming fast box (quad proc, 6 core, 32 GB RAM). Of course, that VM doesn't have all of those resources allocated, but that gives you an idea of the host. Anyway, processes which previously took 3-5 seconds to run were taking in upwards of 5 minutes to run. Then, file copies from file share to local machine were EXTREMELY slow! They finally got it figured out -- something with SMB and IPv6 conflicting with XP workstations.

    Of course, my SQL VM hasn't been put through its paces yet, so I'm trying to be proactive and get what I can optimized on the new server once I cutover. I'm more of an apps guy, but we don't have a true SQL Server DBA -- it just kinda fell on my lap over the years just b/c I know how to write SQL queries 😀

    I'll add that script of yours to my bag of tricks. Thanks again!

  • November 19, 2013 at 12:52 pm

    #1667554

    Maverick28 (11/19/2013)

    I'm more of an apps guy, but we don't have a true SQL Server DBA -- it just kinda fell on my lap over the years just b/c I know how to write SQL queries 😀

    Where it "just falls into you lap" is what's commonly called an "accidental DBA" around here. I'd recommend a book on this site at http://www.sqlservercentral.com/articles/books/76296/ for just such a scenario. It's more common than you might think. The electronic copy of the book is free or they sell paper copies.

    Good luck with the endeavor. One thing's for sure...you won't run out of learning opportunities any time soon.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • November 19, 2013 at 1:18 pm

    #1667564

    Ed Wagner (11/19/2013)

    Maverick28 (11/19/2013)

    I'm more of an apps guy, but we don't have a true SQL Server DBA -- it just kinda fell on my lap over the years just b/c I know how to write SQL queries 😀

    Where it "just falls into you lap" is what's commonly called an "accidental DBA" around here. I'd recommend a book on this site at http://www.sqlservercentral.com/articles/books/76296/ for just such a scenario. It's more common than you might think. The electronic copy of the book is free or they sell paper copies.

    Good luck with the endeavor. One thing's for sure...you won't run out of learning opportunities any time soon.

    Thanks for the link to the book. Just took a peek at it -- good stuff! Wish I could dive into it this week, but gotta prep for a silly SQL migration on Saturday! LOL

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply