Transactional Replication Questions and Concerns

  • Hello,

    I'm working on instituting a new DR plan and need to replicate/mirror/copy/log ship to another server in the cloud. The original plan was to use log-shipping but there are some limitations and other factors that drove me away from this. Instead, I am thinking of doing transactional replication.

    - I need to replicate a large number of databases, some have limited transactions while others have huge amounts of inserts and updates each day.

    - I need to make sure that the subscriber databases are available if our publisher db's go down

    - I would like to limit latency, downtime, and transmission times as much as possible

    - I would like this to have as little maintenance as possible so that we don't need another full-time person monitoring the process. I'd prefer to not have this take up 75% of my day

    Here are some of my questions,

    - Is this even the best way to go about this?

    - Is there anything I should consider regarding normal backups, t-log configurations, restores, etc.

    - What happens if someone decides it's a smart idea to do an insert in the subscriber DB when Unique PK's are enabled? Or what happens if someone deletes records from the subscriber DB?

    - What happens if there is a connection issue

    - How difficult is it to maintain replication versus log-shipping or mirroring?

    - Are there any other issues or pitfalls I should consider? Maybe there's even a better way to do this.

    Also, it would be great if someone could point me to a workflow/technical diagram of the exact logic behind the process.

  • JoshDBGuy (12/30/2013)


    and need to replicate/mirror/copy/log ship to another server in the cloud.

    First thing to do is to decide on which you will use sounds like you have thought about it at length which is good.

    JoshDBGuy (12/30/2013)


    The original plan was to use log-shipping but there are some limitations and other factors that drove me away from this.

    Such as?

    Why did you feel that LS was not a viable route?

    JoshDBGuy (12/30/2013)


    - I would like this to have as little maintenance as possible so that we don't need another full-time person monitoring the process. I'd prefer to not have this take up 75% of my day

    Ok, know this from the start. Replication is good but there is a possibilty of high maintenance and all for the reasons you have already queried.

    JoshDBGuy (12/30/2013)


    - How difficult is it to maintain replication versus log-shipping or mirroring?

    IMHO, mirroring and log shipping are far easier to maintain than replication. Mirroring and log shipping ship the whole database whereas replication is done at an object level.

    As I have already said, "why did you feel that mirroring or log shipping were not suitable?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • sounds like you want the secondary database to be updateable, is that correct? If so replication in a form that allows updateable subscribers is your only option, .

    If this is purely for DR I would never use replication, its the most difficult to maintain. I would go for mirroring except where the network connectivity was poor.

    If you just want the secondary database to be readable then it comes down to latency, and it sounds like you want mirroring, with maybe log shipping for the low use databases. Failover of these can be scripted and automated. A mirroring failover in particular takes seconds.

    If you can I would advise SQL2012 always on for this scenario if updating not needed.

    ---------------------------------------------------------------------

  • Thank you both for the responses.

    (12/30/2013)


    Such as?

    Why did you feel that LS was not a viable route?

    My concern has to do with contact backups on the primary database, transmissions failures, etc. From what I've seen, when transmitting a large number of logs because of potential connection issues and the number of backups we run, log-shipping may be a problem. Also, we do need the secondary DB to be readable. Which I know can be done with log-shipping but I thought it would be easier to use replication. This may not be the case.

    (12/30/2013)


    - IMHO, mirroring and log shipping are far easier to maintain than replication. Mirroring and log shipping ship the whole database whereas replication is done at an object level.

    As I have already said, "why did you feel that mirroring or log shipping were not suitable?

    From what both of you have said, mirroring may be the way to go. I have never implemented mirroring between servers at different data locations. We do have a fast connection so even if there is a couple gigs of data to mirror, do you see this as being a problem? Also in terms of maintenance, is it easier to maintain log shipping or mirroring.

    We don't need the DR server to be up and running the instant one of the primary DB's is down so I definitely don't want there to be automatic failover.

  • Some white papers comparing the various HA techniques

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

    http://technet.microsoft.com/library/Cc917680

    Do you have standard or enterprise edition? To make the secondary databases readable with mirroring you need enterprise edition so you can snapshot the secondary.

    How big are the databases, and how many is a large number?

    Mirroring is easier to setup and maintain but harder to make the secondary readable. Log shipping is easier to understand but a bit more more effort to set up and failover, easier to set secondary read only though and can be done with standard edition. If you want the secondary readable without interruptions you would have to hold off restoring the logs to the secondary to pre-defined times.

    These HA techniques though are much more suited to DR (scale up) than HA (scale out). Gets difficult when you want the secondaries readable.

    These sounds like an 'it depends' question. You need to decide what you really want from the secondaries, DR or readability, it can be a mistake to try and make a secondary fulfill both roles.

    ---------------------------------------------------------------------

  • george sibbald (1/6/2014)


    Some white papers comparing the various HA techniques

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

    http://technet.microsoft.com/library/Cc917680

    Do you have standard or enterprise edition? To make the secondary databases readable with mirroring you need enterprise edition so you can snapshot the secondary.

    How big are the databases, and how many is a large number?

    Mirroring is easier to setup and maintain but harder to make the secondary readable. Log shipping is easier to understand but a bit more more effort to set up and failover, easier to set secondary read only though and can be done with standard edition. If you want the secondary readable without interruptions you would have to hold off restoring the logs to the secondary to pre-defined times.

    These HA techniques though are much more suited to DR (scale up) than HA (scale out). Gets difficult when you want the secondaries readable.

    These sounds like an 'it depends' question. You need to decide what you really want from the secondaries, DR or readability, it can be a mistake to try and make a secondary fulfill both roles.

    Thanks George.

    Standard Edition and I've been testing mirroring but because this is for DR, I really need Mirroring to be asynchronous. It sounds like I might have to go with Log Shipping even though Mirroring is much nicer. The price of the enterprise edition of SQL is too great right now.

  • JoshDBGuy (1/6/2014)


    My concern has to do with contact backups on the primary database, transmissions failures, etc. From what I've seen, when transmitting a large number of logs because of potential connection issues and the number of backups we run, log-shipping may be a problem.

    With LS you control the frequency of

    • t-log backups on primary
    • t-log backup file copies to secondary
    • t-log backup restores to secondary

    In the past i've log shipped a large database from UK to Arizona over a less than ideal link, by compressing the backups you'll save bandwidth. Quests Litespeed log shipping or Redgates sqlbackup log shipping are both excellent for this. If using 2008 R2 std you have native compression available too, no excuses 😉

    JoshDBGuy (1/6/2014)


    Also, we do need the secondary DB to be readable. Which I know can be done with log-shipping but I thought it would be easier to use replication. This may not be the case.

    The fact you want readable rules out mirroring. Yes you could snapshot but the overhead to maintain the snapshots, plus the disk space required. Hmm, naaaah thanks.

    (12/30/2013)


    From what both of you have said, mirroring may be the way to go.

    No that's not what I'm saying

    JoshDBGuy (1/6/2014)


    I have never implemented mirroring between servers at different data locations.

    Even more reason to give it a wide berth then 😀

    JoshDBGuy (1/6/2014)


    We do have a fast connection so even if there is a couple gigs of data to mirror, do you see this as being a problem? Also in terms of maintenance, is it easier to maintain log shipping or mirroring.

    I'm confused, you won't use LS cos you're worried about log copies sucking bandwidth, but you have bandwidth to support mirroring!!

    Log shipping is the easiest to maintain and still provides a readable secondary, lots of bang for your buck 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thanks to both of you. I'm going to start testing log shipping.

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

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