April 13, 2012 at 12:29 pm
I'm working with a company that wants to have the ability, in a worst case scenario, of using an offsite SQL server environment connected to the network via a VPN connection with the most up to date state of data that could be used to minimize disruption to business if production database environments fails for some reason.
I was looking at database mirroring or transactional replication, but latency could be an issue (both sites would be connected to the Internet with business class cable modem). This is also SQL Standard so asynchronous/high performance (if using mirroring) is not an option. This leaves log shipping potentially. Given the scenario, what would be the best direction?
April 13, 2012 at 12:36 pm
Log shipping would work, you just need to make sure that the administrator at the time of any failure is well versed at bringing SQL server databases online and general recovery procedures.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2012 at 1:42 am
Yes,log shipping but this also heavily depends on the recovery point time and the recovery point objective for your company.Otherwise i would advice mirroring and you address the latency issue.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
April 19, 2012 at 10:13 am
The problem with mirroring is that they use Standard, not Enterprise so would have to go with high safety mode with a 3rd witness server and the principal server waits for transaction commitment verification from the secondary server before the transaction is committed on principal so that's been ruled that out due to latency issues.
It looks like throughput on the principal and offsite secondary/backup server will be in the neighborhood of 200KB/s (can request funds to increase that if absolutely necessary), and the next concern is that transactions logs from all databases every 15 minutes (default trans log backup interval with log shipping) average about 250MB, which would take just over 20 minutes to transfer and is obviously an issue. If we increase that to 500KB/s then that goes to 8 min, 20 secs. My question is what would happen if an abnormally large transaction log that took longer than 15 minutes to transfer was being passed to the secondary/backup? Would the process properly queue or would bad things happen?
April 20, 2012 at 1:18 am
'The problem with mirroring is that they use Standard, not Enterprise'...you can mirror in both these editions.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
April 20, 2012 at 7:03 am
kapfundestanley (4/20/2012)
'The problem with mirroring is that they use Standard, not Enterprise'...you can mirror in both these editions.
I know that - read the rest of that sentence:
lbrigham (4/19/2012)
The problem with mirroring is that they use Standard, not Enterprise so would have to go with high safety mode with a 3rd witness server and the principal server waits for transaction commitment verification from the secondary server before the transaction is committed on principal so that's been ruled that out due to latency issues.
My understanding with mirroring and SQL Standard is that we'd have to go with high safety operating mode where primary server waits for transactions to be committed on secondary before they're committed on primary, and the relatively slow connection from primary to secondary is of concern because of that.
synchronous database mirroring (high-performance mode) is supported only by Enterprise Edition
Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
April 20, 2012 at 7:09 am
Alright,I get your point now.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
April 20, 2012 at 12:19 pm
We haven't implemented log shipping quite yet. We're still in the process of testing out the 15 minute interval trans log backups and determining what bandwidth requirements are in order to get the trans logs over to backup server fast enough.
My question is, if log shipping is configured to run every 15 minutes, what would happen if a given transaction log wasn't completely copied to the share that the backup server reads from before the next transaction log is scheduled to be taken from primary and sent to share? Over time, could there eventually be a multitude of transaction log backups trying to be copied over to share simultaneously?
For example:
Extrapolate that out and eventually no TL's would make it to backup server. Is there a way to have TL shipping wait for the prior TL to be completely copied and restored to backup server before the next TL is produced and shipped?
April 21, 2012 at 3:00 am
lbrigham (4/20/2012)
synchronous database mirroring (high-performance mode) is supported only by Enterprise Edition
Asynchronous only is supported in Enterprise edition
lbrigham (4/19/2012)
the next concern is that transactions logs from all databases every 15 minutes (default trans log backup interval with log shipping) average about 250MB, which would take just over 20 minutes to transfer and is obviously an issue. If we increase that to 500KB/s then that goes to 8 min, 20 secs.
Obtain the budget and employ a 3rd party backup utility, Litespeed or Sqlbackup are both good products, with compression the log backups will be Kbs instead of mbs.
In a previous engagement I used Litespeed log shipping plans to replicate a fairly large database from the UK to the west coast of US over an extremely fragile network link.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply