Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactional Replication Questions and Concerns


Transactional Replication Questions and Concerns

Author
Message
joshdbguy
joshdbguy
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 725
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.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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" ;-)
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6330 Visits: 13687
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.

---------------------------------------------------------------------
joshdbguy
joshdbguy
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 725
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.
george sibbald
george sibbald
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6330 Visits: 13687
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.

---------------------------------------------------------------------
joshdbguy
joshdbguy
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 725
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.
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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 :-D


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" ;-)
joshdbguy
joshdbguy
Old Hand
Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)Old Hand (313 reputation)

Group: General Forum Members
Points: 313 Visits: 725
Thanks to both of you. I'm going to start testing log shipping.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search