locked
Babysteps towards SQL Migration RRS feed

  • Question

  • Hello All,

    We are having a task in our hands to migrate all our Databases from SQL Server 2005 Ent. Edition to SQL Servr 2008 R2 Ent. Edition. All of my team members are novice when it comes to migration and going through the SQL Migration Guide provided by Microsoft seems a big exercise when we see the time lines we have in front of us for migration.

    Though we are trying our best and going through whatever information is available, I want to know, how to handle the scenarios when we are continuously getting the data into our databases?

    Example 1: I have a process which runs every hour and takes around 50 minutes to finish has a step which populates around 900 tables (each having around 400 MB of size) every hour and also maintains 6 months of data at any given time into those tables which was populated by previous hour runs of this process. If I decide to recreate this process (SQL Agent job which is calling some SPs and those 900 tables as well) on my new server (SQL 2008 R2), which way should I also move the data that I have with minimal downtime?

    Example 2: There is a database of around 400 GB in size getting updated frequently by the users  as it is the database for one of our live application where the downtime is very very critical. Which approach should be considered in such scenario?

    I know this may seem a shortcut from my side as I am asking direct questions but we are also trying to collect information for our side and any guidance will be highly appreciated.

    Thanks!


    'In Persuit of Happiness' and ..... learning SQL.

    Wednesday, July 3, 2013 2:54 PM

Answers

  • This question is better asked in the Setup and Upgrade forum.

    I would highly recommend updating to SQL 2012 as it is the current version.

    Are you doing an inplace upgrade or new server?

    Please see "Migrating databases":

    http://msdn.microsoft.com/en-us/library/bb933942(v=sql.105).aspx

    • Proposed as answer by Fanny Liu Wednesday, July 10, 2013 9:49 AM
    • Marked as answer by Fanny Liu Monday, July 15, 2013 9:22 AM
    Wednesday, July 3, 2013 3:40 PM
  • Regarding migration (since ur team  is novice) i would suggest using  SIDE BY SIDE Migration

    In this you have to Install fresh edition of sql server 2008 r2 on new win box ...restore backup of ur db on this database ,migrate logins  and jobs by taking script,migrate packages if any

    Test ur application compatibility to sql server 2008 R2

    After migrating run Update stats and rebuild indexes...when ur New DB is readt point ur application to new db..if any thin goes wrong u still have old system ...point it back..

    Hope it helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Fanny Liu Wednesday, July 10, 2013 9:49 AM
    • Marked as answer by Fanny Liu Monday, July 15, 2013 9:22 AM
    Wednesday, July 3, 2013 3:54 PM

All replies

  • This question is better asked in the Setup and Upgrade forum.

    I would highly recommend updating to SQL 2012 as it is the current version.

    Are you doing an inplace upgrade or new server?

    Please see "Migrating databases":

    http://msdn.microsoft.com/en-us/library/bb933942(v=sql.105).aspx

    • Proposed as answer by Fanny Liu Wednesday, July 10, 2013 9:49 AM
    • Marked as answer by Fanny Liu Monday, July 15, 2013 9:22 AM
    Wednesday, July 3, 2013 3:40 PM
  • Log shipping is a simple and effective way to migrate databases with reduced downtime to a higher version.  For databases in simple recovery model you can also "ship" full and differential backups.

    In either case the downtime can be reduced to

    1) Start downtime

    2) Take final log or differential backup

    3) Apply the final log or differential backup on the new server

    4) Recover the database on the new server

    5) Cut over to the new server and end downtime

    The good thing is that steps 2-4 can be performed to test the upgrade process and to test application compatibility of the upgraded database prior to cutover.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Wednesday, July 3, 2013 3:46 PM
  • Regarding migration (since ur team  is novice) i would suggest using  SIDE BY SIDE Migration

    In this you have to Install fresh edition of sql server 2008 r2 on new win box ...restore backup of ur db on this database ,migrate logins  and jobs by taking script,migrate packages if any

    Test ur application compatibility to sql server 2008 R2

    After migrating run Update stats and rebuild indexes...when ur New DB is readt point ur application to new db..if any thin goes wrong u still have old system ...point it back..

    Hope it helps


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    • Proposed as answer by Fanny Liu Wednesday, July 10, 2013 9:49 AM
    • Marked as answer by Fanny Liu Monday, July 15, 2013 9:22 AM
    Wednesday, July 3, 2013 3:54 PM
  • You can also look at the Storage features. Storage devices like SAN can actually do this by copying each and every binary change to the destination. this is generally called SAN replication. So you just have to point your 2008R2 instance to that copied location and SAN will update the physical database files

    Thanks, Praneeth "Please Mark this as "Answered" if you find it helpful." http://www.praneeth-way2sqldba.blogspot.com/

    Wednesday, July 3, 2013 5:13 PM
  • Thanks all of you.. Certainly good starting points to think of.

    Please keep me posted if some more information is available.

    Regards


    'In Persuit of Happiness' and ..... learning SQL.

    Thursday, July 4, 2013 10:19 AM