December 30, 2013 at 10:10 am
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.
January 2, 2014 at 5:50 am
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" 😉
January 2, 2014 at 8:00 am
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.
---------------------------------------------------------------------
January 6, 2014 at 11:57 am
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.
January 6, 2014 at 12:58 pm
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.
---------------------------------------------------------------------
January 6, 2014 at 4:07 pm
george sibbald (1/6/2014)
Some white papers comparing the various HA techniqueshttp://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.
January 6, 2014 at 5:11 pm
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
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" 😉
January 7, 2014 at 11:35 am
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