| Author |
Topic |
|
eddiefdz
Starting Member
21 Posts |
 Posted - 2011-06-21 : 16:07:26
|
| Hello,I currently have 3 databases that are about 240GB in size each. They are hosted in datacenter A. For disaster recovery, i have setup a direct link to datacenter B using a metro-e running at 75Mb. What do you guys recommend i should do in order to keep my main databases synced from datacenter A to B? By the way, we are currently changing the database structure and applying updates, so I want to keep away from transactional replication and mirroring if possible because i will need to be constantly transferring and restoring backups to datacenter B which will be a pain. I am open to any third party utilities.Thoughts??Thanks!Eddie FernandezIT DirectorMTech |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-21 : 16:21:02
|
| I agree. For the initial synch you can drive/mail/fedex backups to the other data center and restore them, then set up mirroring or log shipping.240 GB is plenty small enough to fit on a cheap drive too. |
 |
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-21 : 16:29:40
|
| We run a 24/7 SAAS solution for Hotels. We cannot afford to be down for hours while getting the other side restored. I need a solution where the most that we could be down is about 1 to 2 hours. That's why I am looking for some sort of real-time replication or mirroring.Mirroring poses a problem. I don't know if any of you have noticed, but running maintenance on the databases (index defrags, update stats) causes the transaction log to constantly grow. When you try to truncate/shrink the transaction log, you are not able to do so if mirroring is enabled. You would have to break mirroring, truncate the log, backup the original database, transfer it to the failover location, restore it, then re-enable mirroring. That is a pain especially since we run maintenance every night and we grow the transaction log about 10 gigs on every maintenance. Also, it reaches a point when mirroring will break once the transaction log grows beyond a certain size. It has already happened to me.Any other ideas?Eddie FernandezIT DirectorMTech |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-21 : 16:32:10
|
| Sounds like you aren't performing log backups frequently enough.You should only be down for a couple of minutes getting the other side restored. |
|
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-21 : 16:35:23
|
| I run transaction log backups every hour. The LDF file size grows to 20, 30 gigs. Yes, the transaction log backup runs, and empty's the space, but the LDF size constantly grows although the internal space may be cleared. It gets to a point where mirroring breaks. By the way, I am using SQL 2005 ENT SP3, not sure if this may be a bug.Eddie FernandezIT DirectorMTech |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-21 : 16:37:35
|
| Truncate/shrink the transaction log? Oh my! You shouldn't be doing that with or without mirroring. Also, mirroring will not break when it hits a certain size. How often are you running tlog backups?No downtime will be incurred to setup mirroring. We setup mirroring quite frequently (new databases get deployed pretty often), and zero downtime is incurred. russell, what downtime are you referring to when setting up mirroring? I'm a little confused.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-21 : 16:40:29
|
| I run the transaction logs once an hour. What do you do when the LDF file continues to grow in size? You just let it grow indefinitely? Just one cycle of maintenance for us takes the LDF file from 1gig to 10gigs (although the t_log backups continue to run). It gets to a point where the LDF file is so big, that mirroring no longer works.Eddie FernandezIT DirectorMTech |
|
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-21 : 16:42:42
|
| I am on 9.0.4035Eddie FernandezIT DirectorMTech |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-21 : 16:47:22
|
| No, 10GB is not a problem, but when it gets to about 50GB, mirroring seems to stop working and i have to kill it, truncate/shrink the t_log, then re-enable mirroring.My MDF file is about 250 GB in size.Eddie FernandezIT DirectorMTech |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-21 : 16:54:24
|
| Build 4035, which you are on, contains a mirroring bug. 4035 is the RTM version of SP3, meaning no hotfixes are applied after SP3. You should install SP4 and then at least CU2. But if you aren't ready for SP4, then install a minimum of SP3 CU8. Both SP4 CU2+ and SP3 CU8+ contain the mirroring bug fix.Define "mirroring seems to stop working". We'll need more details. It's very unlikely due to your file size. I have much bigger transaction logs and do not have issues with mirroring in regards to the file sizes. The only issue we encountered was the suspended status as a result of the mirroring bug. This issue has gone away since we've patched them.Your LDF file needs to be at a minimum the size of your largest transaction. This often means 125% of the size of your largest index due to the index rebuilds that take place.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-21 : 16:59:08
|
| Yes, it changes state. It stays on "synchronizing" and never actually goes back to "synchronized". I have left it for days to see if it ever sync's and never does. So i have to disable it completely and go through the motions of re-enabling it.Eddie FernandezIT DirectorMTech |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-21 : 17:12:57
|
| Well synchronizing is okay, it means it is working, it's just behind a bit. You would need to check the mirroring monitor for how far back it actually is though and decide whether or not it can keep up. The monitor will also show you the send rate, restore rate, and approximate time for when it'll catch up. If it appears it will never catch-up enough, you'll need to investigate the link between the two sites. Mirroring is your best bet for what you want, so you'll need to ensure that your link is fast enough to keep up with your changes.By the way, how are you performing the index rebuilds? If it's through the maintenance plan, you may want to consider custom code instead which makes a much better decision on what to rebuild. You could also consider using reorganize instead of rebuild.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-21 : 20:15:01
|
| Eddie, I'm pretty good, but Tara is better in darn near every aspect of DBA work. I don't often fail to follow her advice, or disagree with her.Tara, what I meant about downtime was recovery time only. Initially, I was talking about a first time synch restoring backups, but it looks like he's way beyond that.And for the record, I've never used mirroring. We have far too many cross-database dependencies and a whole lot of databases, to make it really viable for us (our acceptable annual downtime is measured in seconds, not minutes or hours). And we've successfully implemented geographically dispersed transactional replication as well as SAN level replication.Anyway, long winded explanation for a very short question... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-21 : 20:59:28
|
| Maybe we both deserve raises lol. |
|
|
|
eddiefdz
Starting Member
21 Posts |
Posted - 2011-06-22 : 13:34:56
|
| I do all the maintenance with custom code. If the fragmentation is over 30% then i do an online rebuild or re-organize depending on the indexes that are fragmented. Maybe transaction log shipping may be the way to go. I have never tried using that in the past.Eddie FernandezIT DirectorMTech |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Next Page
|
Disaster recovery for my DB's