locked
SQL Migration which is the btter approach RRS feed

  • Question

  • Source server is SQL 2005 Enterprise Edition 64 bit on Windows Server 2003 64 bit

    Target server is SQL 2008 R2 Enterprise Edition cluster on Windows Server 2008 R2

    Which is the better approach

    Migration using backup/restore method and then transferring logins and metadata one by one or

    or

    Migration using the copy database wizard?


    Senior Technical Consultant, MDS Computers

    Wednesday, September 18, 2013 9:23 AM

Answers

  • Migration using BAckup and Restore is the Best Option,

    migrates users using sp_revlogin sp_revlogin

    stored procedure

    and generate script and execite jobs for jobs migration


    Ramesh Babu Vavilla MCTS,MSBI

    • Proposed as answer by Shanky_621MVP Wednesday, September 18, 2013 10:05 AM
    • Marked as answer by Shoaib Hassan Wednesday, September 18, 2013 1:12 PM
    Wednesday, September 18, 2013 9:26 AM
  • Migration using backup/restore method is the best option

    Steps for Migration

    Pre Upgrade (DB Engine)

    • Keep .dll convenient place if extended procedure is in use.
    • Keep Source code of encrypted procedure handy if encrypted procedure in use. Source code needed if it contains obsolete command.
    • Keep Start-up procedure source code handy
    • Run the SQL Server 2008 Upgrade Advisor against the legacy instance.
    • Prepare list of impacted objects from upgrade advisor report.
    • Remove any upgrade blocker issues as much as possible in legacy system i.e. in SQL Server 2005 environment.
    • Run the SQL Server 2008 Upgrade Advisor against the legacy instance.
    • Check consistency

    Upgrade Task (DB Engine)

    • Take Full backup of SQL Server 2005 database and validate it.
    • Script the logins

    Use sp_help_revlogin to generate login scripts available in MSDN.

    • Script linked server
    • Create the logins on SQL Server instances using scripts generated in 2<sup>nd</sup> step
    • Move the SQL Server 2005 database backup file to the location of the SQL Server 2008 instance.
    • Restore backup files in SQL Server 2008 instance. Keep database name same for both the instances.
    • Review error log.
    • Fix Orphaned user if any
    • Change SQL Server authentication to Windows authentication
    • Create the jobs and alert on the SQL Server 2008 instance by running job scripts generated in 3<sup>rd</sup> step
    • Create the linked server on the SQL Server 2008 instance by running job scripts generated in 4<sup>th </sup>step.
    • Migrate startup sp / extended sp / encrypted sp / mail to SQL Server 2008.
    • Remove any upgrade blocker issues, which need to be resolve in SQL Server 2008 environment.
    • Convert DTS to SSIS
    • Migrate SSRS / SSAS etc
    • Create maintenance plan

    Post Upgrade Task (DB Engine)

    • Change the DB Compatibility level option of the database to 100 that correspond to SQL Server 2008 level.

    ALTER DATABASE [DB_Name] SET COMPATIBILITY_LEVEL = 100

    • Check Object Integrity

    DBCC CHECKDB command checks the integrity of the objects in a database. But, it does not check the integrity of the data in the column for databases created in version prior to SQL Server 2005. Adding DATA_PURITY option causes the CHECKDB command to look for column values that are invalid or out of range.

    DBCC CHECKDB [DB_Name] WITH DATA_PURITY

    • Correct inaccurate row and page counts for tables and indexes

    DBCC UPDATEUSAGE [DB_Name]

    • Set database page verification method to CHECKSUM
    • Update statistics on all databases after you upgrade them.

    EXECUTE sp_MSForeachtable 'UPDATE STATISTICS ?'

    Or

    EXEC sp_updatestats

    • Reorganize Indexes
    • If xp_cmdshell is in use then enable it.
      • Update any scripts affected by SQL Server 2008 behavior changes. E.g. SQL Server Native Client 10.0.
      • Do high level verification of database, its objects, and data. Use information collected in Pre Upgrade Task 3<sup>rd</sup> step.
      • Make sure that the relational databases are working correctly by executing a sample set of queries.

    Privileges requirement

    To run Upgrade Advisor one should have Administrator privileges at OS level. Upgrade advisor collect data from windows registry.

    Thanks

    Saravana Kumar C

    • Marked as answer by Shoaib Hassan Wednesday, September 18, 2013 1:13 PM
    Wednesday, September 18, 2013 9:52 AM
  • Agree with babu,Please use backup restore method.

    For transferring logins you can refer to below MS articles

    http://support.microsoft.com/kb/918992

    http://support.microsoft.com/kb/246133


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

    • Marked as answer by Shoaib Hassan Wednesday, September 18, 2013 1:14 PM
    Wednesday, September 18, 2013 10:09 AM

All replies

  • Migration using BAckup and Restore is the Best Option,

    migrates users using sp_revlogin sp_revlogin

    stored procedure

    and generate script and execite jobs for jobs migration


    Ramesh Babu Vavilla MCTS,MSBI

    • Proposed as answer by Shanky_621MVP Wednesday, September 18, 2013 10:05 AM
    • Marked as answer by Shoaib Hassan Wednesday, September 18, 2013 1:12 PM
    Wednesday, September 18, 2013 9:26 AM
  • Please refer the below article:

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


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 18, 2013 9:32 AM
  • Migration using backup/restore method is the best option

    Steps for Migration

    Pre Upgrade (DB Engine)

    • Keep .dll convenient place if extended procedure is in use.
    • Keep Source code of encrypted procedure handy if encrypted procedure in use. Source code needed if it contains obsolete command.
    • Keep Start-up procedure source code handy
    • Run the SQL Server 2008 Upgrade Advisor against the legacy instance.
    • Prepare list of impacted objects from upgrade advisor report.
    • Remove any upgrade blocker issues as much as possible in legacy system i.e. in SQL Server 2005 environment.
    • Run the SQL Server 2008 Upgrade Advisor against the legacy instance.
    • Check consistency

    Upgrade Task (DB Engine)

    • Take Full backup of SQL Server 2005 database and validate it.
    • Script the logins

    Use sp_help_revlogin to generate login scripts available in MSDN.

    • Script linked server
    • Create the logins on SQL Server instances using scripts generated in 2<sup>nd</sup> step
    • Move the SQL Server 2005 database backup file to the location of the SQL Server 2008 instance.
    • Restore backup files in SQL Server 2008 instance. Keep database name same for both the instances.
    • Review error log.
    • Fix Orphaned user if any
    • Change SQL Server authentication to Windows authentication
    • Create the jobs and alert on the SQL Server 2008 instance by running job scripts generated in 3<sup>rd</sup> step
    • Create the linked server on the SQL Server 2008 instance by running job scripts generated in 4<sup>th </sup>step.
    • Migrate startup sp / extended sp / encrypted sp / mail to SQL Server 2008.
    • Remove any upgrade blocker issues, which need to be resolve in SQL Server 2008 environment.
    • Convert DTS to SSIS
    • Migrate SSRS / SSAS etc
    • Create maintenance plan

    Post Upgrade Task (DB Engine)

    • Change the DB Compatibility level option of the database to 100 that correspond to SQL Server 2008 level.

    ALTER DATABASE [DB_Name] SET COMPATIBILITY_LEVEL = 100

    • Check Object Integrity

    DBCC CHECKDB command checks the integrity of the objects in a database. But, it does not check the integrity of the data in the column for databases created in version prior to SQL Server 2005. Adding DATA_PURITY option causes the CHECKDB command to look for column values that are invalid or out of range.

    DBCC CHECKDB [DB_Name] WITH DATA_PURITY

    • Correct inaccurate row and page counts for tables and indexes

    DBCC UPDATEUSAGE [DB_Name]

    • Set database page verification method to CHECKSUM
    • Update statistics on all databases after you upgrade them.

    EXECUTE sp_MSForeachtable 'UPDATE STATISTICS ?'

    Or

    EXEC sp_updatestats

    • Reorganize Indexes
    • If xp_cmdshell is in use then enable it.
      • Update any scripts affected by SQL Server 2008 behavior changes. E.g. SQL Server Native Client 10.0.
      • Do high level verification of database, its objects, and data. Use information collected in Pre Upgrade Task 3<sup>rd</sup> step.
      • Make sure that the relational databases are working correctly by executing a sample set of queries.

    Privileges requirement

    To run Upgrade Advisor one should have Administrator privileges at OS level. Upgrade advisor collect data from windows registry.

    Thanks

    Saravana Kumar C

    • Marked as answer by Shoaib Hassan Wednesday, September 18, 2013 1:13 PM
    Wednesday, September 18, 2013 9:52 AM
  • Agree with babu,Please use backup restore method.

    For transferring logins you can refer to below MS articles

    http://support.microsoft.com/kb/918992

    http://support.microsoft.com/kb/246133


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

    • Marked as answer by Shoaib Hassan Wednesday, September 18, 2013 1:14 PM
    Wednesday, September 18, 2013 10:09 AM
  • As proposed by others, please go with backup and restore option.

    Wednesday, September 18, 2013 10:14 AM