September 21, 2009 at 3:41 am
Hi,
I just tried to setup database mirroring over the weekend. The database was in the SYNCHRONIZING state for about 7.5hrs before I decided to remove mirroring and re-enable log shipping. I was just wondering if anyone has experienced any similar issues or knows of a cause/resolution?
We currently have two main production databases (on same server) and I setup database mirroring on the smaller (~160GB) of the two last week. The configuration used certificates as it's working between non-trusted domains. Everything worked as expected and the database was able to reach the SYNCHRONIZED state after only a few seconds.
As mirroring worked without any issues for the smaller database last week I decided to enable mirroring on the larger database (>7TB). I took a log backup and restored it to the DR server just before enabling mirroring. The commands to setup mirroring worked ok but the database was in the mirroring state for a long period of time. I realised that the full backup was running at this time and thought that this might have something to do with it. I waited for the backup to complete and allowed time for the database to reach the SYNCHRONIZED state - 7.5hrs later and the database is still synchronizing. I removed mirroring and tried the setup again - same result. I also tried taking and applying another log backup beforehand - same result. I eventually decided to remove mirroring and resume log shipping.
There is no interesting information in the error log. The only error message I got was this one when I attempted to enable high performance mode:
The command failed because the database mirror is busy. Reissue the command later
I also checked database mirroring monitor - there was no unsent log to explain the synchronizing state. The send rate would change occasionally indicating that transactions were flowing to the mirror server. The value of the oldest unsent transaction was increasing - this seemed a bit strange. I checked for any open transactions - there was none.
Any suggestions appreciated. 🙂
Thanks,
David
DBA Dash - Free, open source monitoring for SQL Server
September 21, 2009 at 7:54 am
It's got to be network or I/O limiting your ability to catch up. You mentioned a backup, is this part of nightly maintenance? Any chance that indexing was going on? A rebuild needs to be sent (it's logged) to the mirror and that can be a lot of data.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 21, 2009 at 9:58 am
Hi,
This is a new mirroring setup - converting from log shipping to database mirroring. The backup that was running was our normal weekly full backup - At first I thought the mirroring might need to wait for this to complete for some undocumented reason but that doesn't seem to be the case as I tried again after the backup completed.
There were no index rebuilds or any other maintenance operations when I attempted to enable mirroring. Also, I was setting mirroring on Sunday - this is a period of low activity for us (Even more so when I attempted it a second time after 9pm). Before setting up mirroring I generated a log backup and applied it to the mirror server so there should have been very little data to sync up. Also, database mirroring monitor confirmed that there was 0KB of log waiting to transfer, yet the status was still synchronising.
The speed of the link isn't good enough for us to run high safety mode, but it's still sufficient to transfer the logs with only a small amount of latency. We currenly do log backups every 30min and transfer these over the network for log shipping - I've calculated that mirroring will have a 30% overhead as we currently use a custom log shipping mechanism that compresses the log backups. We are using SQL 2005 so no native backup compression or compression for mirroring. Despite the lack of compression, the network link should be able to handle the increased load without any problems.
The database is quite large (>7TB), but I don't think this should be a factor in how long it takes to reach the synchronized status - I'm guessing this would be down to the size of the log records that need to be synchronized? As I applied a log backup before setting up mirroring, the amount of log data would be tiny.
Any suggestions appreciated. 🙂
Thanks,
David
DBA Dash - Free, open source monitoring for SQL Server
September 22, 2009 at 10:35 am
Hi David
I don't have an explanation for you, but, if you haven't done so already, it might be worth examining the values of the Database Mirroring performance counters:
http://msdn.microsoft.com/en-us/library/ms189931.aspx
I realise that the mirroring monitor that you have already used relies on a selection of these counters, but I don't have a copy of SSMS to hand to verify whether there are any additional counters listed above that might be useful to you.
Cheers
Chris
September 22, 2009 at 11:29 am
You're correct. Once the db is restored, the size of it doesn't matter. It's the logs that have to be restored.
Did it ever catch up? I'm almost wondering if you have some network limitation that is preventing the secondary communicating with the primary through the DM endpoints and letting it know it's caught up. If log shipping was able to keep up, I'd expect mirroring to.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 22, 2009 at 12:00 pm
Hi,
I didn't like the idea of leaving mirroring in that state during the working week so I switched back over to log shipping. I still want to get mirroring working, but I'll wait until the weekend before I try again. Mirroring is working ok between the two servers for our other database -this suggests that the endpoints etc are configured properly.
I'm not sure what to try differently this weekend... I'll take a look at the performance counters, but I'm not sure if these will be able to help me get to the bottom of the issue. Any other suggestions, please let me know. 🙂
Thanks,
David
PS
I'll post back if I find anything this weekend. Also, I'm thinking it might be worth getting Microsoft Support involved if I'm unable to resolve this issue. I thought SSC would be my best first point of call, but sometimes problems like this are a bit obscure for community support. We get some free support incidents as a gold partners anyway. 🙂
DBA Dash - Free, open source monitoring for SQL Server
September 22, 2009 at 1:12 pm
You might also look at the perfmon counters on the disk drives of the mirror server. It may be that it's having no trouble sending the log data across the network, but the disks are having trouble getting all the data hardened to the log. I've run into that before, especially when mirroring more than one database at a time. I've also seen overloaded mirror server problems solved by simply upgrading to 64-bit.
Also, (someone else chime in if I'm wrong), I don't know why you couldn't mirror and log ship at the same time for awhile and see if it eventually catches up.
The Redneck DBA
September 25, 2009 at 3:22 am
Jason Shadonix (9/22/2009)
You might also look at the perfmon counters on the disk drives of the mirror server. It may be that it's having no trouble sending the log data across the network, but the disks are having trouble getting all the data hardened to the log. I've run into that before, especially when mirroring more than one database at a time. I've also seen overloaded mirror server problems solved by simply upgrading to 64-bit.Also, (someone else chime in if I'm wrong), I don't know why you couldn't mirror and log ship at the same time for awhile and see if it eventually catches up.
It's a reasonable assumption that the problem is due to network bandwidth or insufficient server hardware - I'm pretty sure if this is not the case though. Log shipping is able to transfer the data over the network without any problems and is able to apply the logs quickly enough. The 7hrs I left mirroring running would be more than enough to apply a full day's worth of logs (a busy weekday) via log shipping. Mirroring only had a tiny amount of log data to catch up on as I recently applied a log backup. Initially it probably had 10-15min of logs to catch up on (during a period of very low activity). Also, using database mirroring monitor I was able to verify that there wasn't any bottleneck either transferring log data or applying log records at the mirror. It seems quite strange that they were unable to synchronize...
The server is already 64bit. We can't logship and mirror at the same time as we would require an additional 7TB+ of space on our DR server. The existing log shipping solution that we want to convert to mirroring is working fine though.
I'm hoping to give it another go this weekend. I'm thinking it could be a bug in SQL Server - I'm guessing it's not a very common one though.
DBA Dash - Free, open source monitoring for SQL Server
September 25, 2009 at 8:43 am
I would lean towards a bug, or a network issue. Can you try this? Create a small db, say 1MB, mirror it to the DR server. Create a table in it, and a job that inserts a row into the table every 15 minutes. see if it catches up.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2009 at 8:46 am
Could there be a firewall or something involved that's messing with you?
The Redneck DBA
September 25, 2009 at 10:14 am
Steve Jones - Editor (9/25/2009)
I would lean towards a bug, or a network issue. Can you try this? Create a small db, say 1MB, mirror it to the DR server. Create a table in it, and a job that inserts a row into the table every 15 minutes. see if it catches up.
Seems logical to test with a smaller database, but we already have a database that is mirrored between the two servers. This was setup without any problems - it synchronized within a few seconds and it's been running without any problems for a few weeks.
DBA Dash - Free, open source monitoring for SQL Server
September 25, 2009 at 10:16 am
Jason Shadonix (9/25/2009)
Could there be a firewall or something involved that's messing with you?
There is a firewall, but it's been configured to allow traffic on the port used by mirroring. I can confirm that mirroring is working ok as I have a mirror setup for another database (same live/dr server combination)
DBA Dash - Free, open source monitoring for SQL Server
September 25, 2009 at 10:36 am
Mirrored on this same server? I'm worried that the firewall or network is limiting the comm back from the DR server to the production server. That's why I'd test a small one on those servers.
Otherwise, maybe it was an anomaly. Maybe just set it up again and see.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 25, 2009 at 11:24 am
Steve Jones - Editor (9/25/2009)
Mirrored on this same server? I'm worried that the firewall or network is limiting the comm back from the DR server to the production server. That's why I'd test a small one on those servers.Otherwise, maybe it was an anomaly. Maybe just set it up again and see.
Yeah - we have two production databases on the same server that previously had log shipping solutions to the same DR server. I converted the smaller database (~100GB) to database mirroring first and this worked without any issues. A week later I tried it with the larger database and ran into the synchronization issue.
I'll give it another try this weekend. If it still doesn't work I'll try mirroring another database on that server - just to make sure I'm able to mirror 2 databases without any problems. I didn't configure the firewall myself, but I'm pretty sure there won't be any exotic rules setup to limit the number of connections etc - I'll double check though.
DBA Dash - Free, open source monitoring for SQL Server
September 26, 2009 at 2:57 am
I've just configured database mirroring - same procedure as last time, but this time it worked without any problems and synchronized within a few seconds. 🙂
It's great that it's working, but it's rather annoying that I'm unable to explain the failure last week. The DR server was re-booted yesterday - this is the only change I can think of that might have enabled mirroring to work this time. Exactly how a re-boot would enable it to work this time I have no idea - I guess it's a tried and tested method of fixing 99% of computer problems...
I'll be keeping a close eye on latency etc, but for now it seems to be working very well. Thanks for the suggestions everyone. 🙂
DBA Dash - Free, open source monitoring for SQL Server
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply