| Author |
Topic |
|
epikto
Starting Member
4 Posts |
 Posted - 2009-03-23 : 13:03:39
|
| We're running a number of 2005 databases in a production environmnent and log shipping them to a DR environment.One of the databases has a high activity level which causes it to have large transaction files each night (I also think that some of the maintainence activity is contributing to this).However because we have to ship this large trn file (approx .5 GB+) accross the wire the sql server log shipping copy job attempts to copy the log file earlier resulting in an incomplete log being shipped every night, requiring manual intervention.Again a review:Sql Server log shippingFTP transfer of Trn logs from coast to coast america.5 GB Plus Trn log every nightcopy job only copies .25 gb of the trn file to the restore destination.Potential solutions looked into thus far:having another copy process to copy the log filesfinding a way to reduce the size of this log (however should we have legitimate traffic causing large log files this would not be a solution)Any information to assist would be appreciated. |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-23 : 13:30:54
|
| We had a problem similar to this and had to resort to Litespeed to compress the tlog and make it doable. You could also consider 2008 with it's compression feature. What is your destination DB used for? Reporting? DR? Maybe there's an alternative to log shipping like Replication or Mirroring or a backup/restore which might work better.Mike"oh, that monkey is going to pay" |
 |
|
|
epikto
Starting Member
4 Posts |
Posted - 2009-03-23 : 14:26:00
|
| Thanks for the reply.We're attempting to faciliate cutting logs every 15 minutes for a DR solution.We're in mid cycle of our review of 2008. It's sounding like more of a reason to get on the ball with adoption.I can also check out compressing the files as another potential solution.Any on else tackle this problem successfully? |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-03-23 : 14:29:33
|
| For DR you might be better served looking into mirroring or transactional replication if you're having to ship half a gig of log files. The other solutions would allow you to send transactions instead of the log file which would be significently more reliable and reduce your loss of data exposure depending on how you set them up.Mike"oh, that monkey is going to pay" |
|
|
|
epikto
Starting Member
4 Posts |
Posted - 2009-03-23 : 17:13:45
|
| Will do. I've always been concerned about transactional replication having to modify my schema and with the distance and potential latency problems mirroring didn't seem to be the correct solution.However I will take second looks at these as they are begginning to look like they may be a better fit. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2009-03-23 : 17:29:11
|
| You do not need to modify your schema to use transactional replication, but I certainly wouldn't recommend that technology as a DR solution.Latency isn't an issue with database mirroring if you use asynchronous database mirroring. I would seriously look into database mirroring again instead of log shipping. Database mirroring should be used as a DR solution instead of log shipping unless you have multiple servers to ship to.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog"Let's begin with the premise that everything you've done up until this point is wrong." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
epikto
Starting Member
4 Posts |
Posted - 2009-03-23 : 18:30:27
|
| Excellent. Database mirroring seems the way to go. That's SQL Server Enterprise Edition correct?What do you think of additional solutions such as SCDPM? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
lgayles
Starting Member
1 Post |
Posted - 2009-05-09 : 19:47:22
|
| Database mirroring is avialble in SQL 2005 STD, but you'll only be able to use the two synchronous mode. If you want to use the High performance (async) mode you'll need to upgrade to EE. |
|
|
|
|
Problem with large trn files log shipping