locked
SQL Server 2012 Migration RRS feed

  • Question

  • We are planning to migrate our existing environment to SQL 2012.

    Existing Environment:

    SERVER1

    OS: Windows Server 2003 Standard Edition

    SQL Server 2005 Standard Edition

    2 Node Active/Passive Clustering With Transactional Replication

    Number of Databases -> 2 (80 gb each).

    Now how to migrate above environment to SQL 2012 on Windows 2012?

    After migrating how to implement Log-shipping in above enronmnment.


    Friday, February 21, 2014 3:30 PM

Answers

  • In above environment how to implement log-shipping?

    For log shipping you need to procure a different windows box and install SQL Server 2012 on it this windows box wont be part of your cluster make sure you can connec to box from your LAN or surrorunding.Now take full backup of database and then transaction log backup restore both in standby mode.Refer to below article for configuring Log shipping

    http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/


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

    • Marked as answer by Sofiya Li Monday, March 3, 2014 9:39 AM
    Friday, February 21, 2014 4:19 PM
  • Hi Vijay,

    Once you setup a failover cluster and Restore the database and configure the database for log shipping and made the another off-site server for DR purpose(secondary database).

    Replication is at the object level but where as log shipping is at the database level. Replication is setup where data to be synchronized with subscription with very less latency. 

    Log shipping requires a backup of the database to be sent to the secondary server, the subsequent log backups are sent and applied to the destination server and it will either be in read only, or in recovering state

    Refer below link for how to configure Logshipping

    http://sqlserver-performance-tuning.net/?p=501


    -Prashanth

    • Marked as answer by Sofiya Li Monday, March 3, 2014 9:39 AM
    Friday, February 21, 2014 4:34 PM
  • log shipping doesn't support automatic failover. It requires a manually effort to fix in such cases.

    You need to share the log file location as a clustered resource which is part of the SQL Server cluster group so that its availble to secondary database.

    Because of this problem its always recommended to use cluster with database mirroring. Refer the below article

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55e1bbd-5b6f-4961-8e69-3de9ef4fb75f/use-db-mirroring-or-log-shipping?forum=sqldisasterrecovery#1bb9926e-1b39-47ad-9fe9-351d0fbaaf9f

    But this is not the case with replication, The replication will fail during the failover and start it's operation once its back online.

    Refer the below articles for more information

    http://technet.microsoft.com/en-us/library/ms151799.aspx

    http://www.mssqltips.com/sqlservertip/2337/sql-server-2008-high-availability-options/

    Since you are moving to SQL 2012, try to work on alwaysOn High availability groups.

    http://technet.microsoft.com/en-us/library/ff877884.aspx

    -Prashanth

    • Proposed as answer by Sofiya Li Monday, February 24, 2014 9:01 AM
    • Marked as answer by Sofiya Li Monday, March 3, 2014 9:39 AM
    Friday, February 21, 2014 6:38 PM

All replies

  • Windows Server 2008 does not support the direct upgrading of failover clusters from earlier versions of Windows Server. This is because significant security improvements in Windows Failover Clustering Services removed backward compatibility.

    Refer the below thread for more information

    http://social.technet.microsoft.com/Forums/en-US/7a03a102-b8ad-4a30-8c63-dac4b62a96b8/migrate-win-2003-r2-sql-2005-multiinstance-cluster-to-win-2008-r2-sql-2012-multiinstance-cluster?forum=winserverClustering

    Once you setup a cluster it will not that difficult to setup other HA solutions.

    -Prashanth

    Friday, February 21, 2014 3:50 PM
  • Hi Prashanth,

    I am not planning for Up gradation, I am just planning for side by migration.

    In above environment how to implement log-shipping?

    Friday, February 21, 2014 4:05 PM
  • In above environment how to implement log-shipping?

    For log shipping you need to procure a different windows box and install SQL Server 2012 on it this windows box wont be part of your cluster make sure you can connec to box from your LAN or surrorunding.Now take full backup of database and then transaction log backup restore both in standby mode.Refer to below article for configuring Log shipping

    http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/


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

    • Marked as answer by Sofiya Li Monday, March 3, 2014 9:39 AM
    Friday, February 21, 2014 4:19 PM
  • Hi Vijay,

    Once you setup a failover cluster and Restore the database and configure the database for log shipping and made the another off-site server for DR purpose(secondary database).

    Replication is at the object level but where as log shipping is at the database level. Replication is setup where data to be synchronized with subscription with very less latency. 

    Log shipping requires a backup of the database to be sent to the secondary server, the subsequent log backups are sent and applied to the destination server and it will either be in read only, or in recovering state

    Refer below link for how to configure Logshipping

    http://sqlserver-performance-tuning.net/?p=501


    -Prashanth

    • Marked as answer by Sofiya Li Monday, March 3, 2014 9:39 AM
    Friday, February 21, 2014 4:34 PM
  • Hi Prashanth,

    Now i got it.

    Some more doubts.

    For example: I have taken new box (with 2012) and configure log-shipping (Secondary) that's fine.

    In this case if fail-over occur in cluster, then how log-shipping secondary server identify primary server?

    Where we have to use sqlvirtual name while configuring log-shipping secondary server a what?

    How replication will work in this case?

    Friday, February 21, 2014 5:50 PM
  • log shipping doesn't support automatic failover. It requires a manually effort to fix in such cases.

    You need to share the log file location as a clustered resource which is part of the SQL Server cluster group so that its availble to secondary database.

    Because of this problem its always recommended to use cluster with database mirroring. Refer the below article

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c55e1bbd-5b6f-4961-8e69-3de9ef4fb75f/use-db-mirroring-or-log-shipping?forum=sqldisasterrecovery#1bb9926e-1b39-47ad-9fe9-351d0fbaaf9f

    But this is not the case with replication, The replication will fail during the failover and start it's operation once its back online.

    Refer the below articles for more information

    http://technet.microsoft.com/en-us/library/ms151799.aspx

    http://www.mssqltips.com/sqlservertip/2337/sql-server-2008-high-availability-options/

    Since you are moving to SQL 2012, try to work on alwaysOn High availability groups.

    http://technet.microsoft.com/en-us/library/ff877884.aspx

    -Prashanth

    • Proposed as answer by Sofiya Li Monday, February 24, 2014 9:01 AM
    • Marked as answer by Sofiya Li Monday, March 3, 2014 9:39 AM
    Friday, February 21, 2014 6:38 PM