Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Transactional Replication Questions and Concerns Expand / Collapse
Author
Message
Posted Monday, December 30, 2013 10:10 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 142, Visits: 286
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.
Post #1526551
Posted Thursday, January 2, 2014 5:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 6,194, Visits: 13,350
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"
Post #1527063
Posted Thursday, January 2, 2014 8:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 5,976, Visits: 12,887
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.


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

Post #1527126
Posted Monday, January 6, 2014 11:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 142, Visits: 286
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.
Post #1528213
Posted Monday, January 6, 2014 12:58 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 5,976, Visits: 12,887
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.


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

Post #1528231
Posted Monday, January 6, 2014 4:07 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 142, Visits: 286
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.
Post #1528283
Posted Monday, January 6, 2014 5:11 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:25 PM
Points: 6,194, Visits: 13,350
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"
Post #1528292
Posted Tuesday, January 7, 2014 11:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 142, Visits: 286
Thanks to both of you. I'm going to start testing log shipping.
Post #1528613
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse