locked
Log Shipping between MS SQL Standard and Enterprise edition RRS feed

  • Question

  • Thanks for your time. this is important to us .

    We have a production enviornment that runs an Enterprise edition 2008 SQL , we need a warm standby on our DR site where we have a 2008 standard edition.

    1- Is that possible to have logshipping setup between EE and SE ?
    2- If a failure happens and we switch to our DR site ; database will be updated on DR server during the outage ; is that possible to replicate the differences back to primary site ?

    Thanks

    Ali

    Tuesday, November 9, 2010 8:59 PM

Answers

  • Log shipping can be setup on SQL Server Standard Edition/Enterprise Edition, see the following including the link on the page to features - log shipping...

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

    I assume you have tested your database/application on the standard edition instance to confirm you are not using any of the enterprise features and also performance is not impacted. After the initial copy over of the full backup you can setup log shipping yourself using T-SQL for the log backup/restore commands inside SQL Agent Jobs, you can then also setup some monitoring to make sure the principle and secondary servers are up-to-date. Essentially log shipping is very quite forward, you are just backing up the log on the principle, copying it over to the secondary (or you can backup directly to a share on the secondary from the principle) and restoring it on the secondary with norecovery or standby. You need to decide the timings for your backups and how much potential data loss you can accept. You can also include steps in the process to compress/decompress the log backups using a third party compression tool. Anyway have a look at the above link. Oh and you will need to also plan for cleaning up of log backups similar to your existing backup process.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by Alex Feng (SQL) Wednesday, November 17, 2010 10:32 AM
    Tuesday, November 9, 2010 10:31 PM
  • Hi,

    >>here is my question , at the end of the day .. let's say we have a fully updated DR instance ; how I can get this database back to primary ? is that possible to do a
    >> differential ? or it has to be a full backup/restore ?

    You need to full backup of DR database restored on the primary database. By the way,you need to do full backup of DR database at least once before you can do a differential backup.

    Also, you need to resetup log shipping. Do full backup of DR database and leave it in the norecovery/standby mode, restore it on the primary database and bring primary database online and then resetup the log shipping topology from primary to your DR site.

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by PrinceLucifer Thursday, November 11, 2010 9:53 AM
    • Marked as answer by Alex Feng (SQL) Wednesday, November 17, 2010 10:32 AM
    Thursday, November 11, 2010 8:12 AM

All replies

  • Log shipping can be setup on SQL Server Standard Edition/Enterprise Edition, see the following including the link on the page to features - log shipping...

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

    I assume you have tested your database/application on the standard edition instance to confirm you are not using any of the enterprise features and also performance is not impacted. After the initial copy over of the full backup you can setup log shipping yourself using T-SQL for the log backup/restore commands inside SQL Agent Jobs, you can then also setup some monitoring to make sure the principle and secondary servers are up-to-date. Essentially log shipping is very quite forward, you are just backing up the log on the principle, copying it over to the secondary (or you can backup directly to a share on the secondary from the principle) and restoring it on the secondary with norecovery or standby. You need to decide the timings for your backups and how much potential data loss you can accept. You can also include steps in the process to compress/decompress the log backups using a third party compression tool. Anyway have a look at the above link. Oh and you will need to also plan for cleaning up of log backups similar to your existing backup process.

     

    Thanks


    /Neil Moorthy - Senior SQL Server DBA/Developer (OCA 11g, MCITP (2005), MCAD) Please click the Mark as Answer button if a post solves your problem
    • Marked as answer by Alex Feng (SQL) Wednesday, November 17, 2010 10:32 AM
    Tuesday, November 9, 2010 10:31 PM
  • Moort already pointed out that LogShipping is indeed possible between SE and EE servers, in both directions. Replicating the changes back after an outage is possible of course to, but it requires quite some work and downtime on the principal side. (As you need to go through a full backup/restore cycle...)

    The main question that comes to my mind is: Why are you going for logshipping? If you have an SE and an EE server, why not go for mirroring? Even an async mirror would lead to less data loss than logshipping, sync mirror (if your bandwith and server sizing allow it) even goes down to zero data loss, and in some scenarios you even have a quick way of resynching without too much work needed...

    Lucifer

    Wednesday, November 10, 2010 7:06 AM
  • Thanks Neil and Lucifer

    Yes , the application works on standrad edition.

    Our main database is hosted by a vendor and it's on a remote data center ; they have a 64 bit enterprise SQL and I think they run multiple databases on it ( for different customers.) that's why we couldn't use mirroing ( we have a 10 MB VPN connection to data center ) .

    our plan is to set logshipping to send logs every 20 minutes. logs should get restored on DR instance . If something happens ; we have a database which is 20 min. behind primary database. we will add those 20 min. transactions to DR database (manually from paper work) and we will let users work on the DR instance until the end of the day .

    here is my question , at the end of the day .. let's say we have a fully updated DR instance ; how I can get this database back to primary ? is that possible to do a differential ? or it has to be a full backup/restore ?

    Thanks

    Ali

    Wednesday, November 10, 2010 1:47 PM
  • Hi,

    >>here is my question , at the end of the day .. let's say we have a fully updated DR instance ; how I can get this database back to primary ? is that possible to do a
    >> differential ? or it has to be a full backup/restore ?

    You need to full backup of DR database restored on the primary database. By the way,you need to do full backup of DR database at least once before you can do a differential backup.

    Also, you need to resetup log shipping. Do full backup of DR database and leave it in the norecovery/standby mode, restore it on the primary database and bring primary database online and then resetup the log shipping topology from primary to your DR site.

    Hope this helps.

    Thanks,
    Chunsong


    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Proposed as answer by PrinceLucifer Thursday, November 11, 2010 9:53 AM
    • Marked as answer by Alex Feng (SQL) Wednesday, November 17, 2010 10:32 AM
    Thursday, November 11, 2010 8:12 AM