November 11, 2009 at 5:19 am
Hello all hope you are well!
I have not started the Mirroring setup yet, still in the planning phase. However i am foreseeing an issue with several of our databases.
The server in question is a Windows 2008 (SQL 2008) 64bit Dell 2950 (raid 10 15k SAS array).
The databases in question range from 50GB to 300GB+
These databases are part of our warehouse/purchasing system, and get hammered with inserts, updates and deletes all day and night meaning the indexes need regular reorganising (reorganised every night and a full rebuild at weekends). To avoid creating massive transactional data during these re-organise and rebuilds we switch the recovery mode to simple, run the index tasks, then switch back to full do a backup and resume transaction logs backups (all done by the maintinence plans). This works perfectly in our current environment.
However, this server is due to be mirrored as part of a DR overhaul of the entire company, I am guessing that switching a principle mirror db to SIMPLE will either not work, or seriously screw up the mirror? If so, what’s the best practise for such a situation?
Opinions/suggestions are MOST welcome!
Regards
Jordon.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
November 29, 2009 at 4:06 pm
Please read this article.
Database Mirroring Best Practices and Performance Considerations
November 29, 2009 at 4:44 pm
Hi
Cheers for the link mate, read that some time ago, meant to update this thread!
Basically after reading just about everything i could find on Mirroring, (i even bought 'Pro SQL Server 2008 Mirroring' by R Davis and Ken Simmons (very good read)). I have come to several conclusions.
Because i am dealing with SQL Server 2008 Standard, i am stuck with synchronous mirroring (company refuses to pay for enterprise processor licenses), which begs the question of the indexing overheads mentioned in my first post.
Up to know we (aka me) have been lazy in regards to index maintenance (first step is admitting it right?) and i have just relied on bog standard maintenance plans to do all the work (as mentioned in my first post). So in regards to maintaining indexes with my synchronous mirroring i have decided to adapt the excellent examples in this article to be much more intelligent, and rebuild/reorganise only when and if needed (thus reducing stress on my mirror). Also, i am able to re-build/re-index at night when the indexes are not needed, so i dont have to keep the indexes online during the build, which is good news for my clustered indexes; as i understand it, keeping an index online, especially on clustered indexes can cause a much greater level of stress to a mirror than an offline one (in synchronous mirroring anyways)?
I have some stress tests going as we speak now, so fingers crossed i should be implementing mirroring very soon!
Regards
Jordon.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
November 30, 2009 at 4:08 pm
Hi Jordon. First of all, thanks for buying my book!! 🙂
Secondly, it sounds like you have already made the right decision by adopting a more intelligent approach towards index maintenance.
I have actually done quite a bit of performance testing with index maintenance on mirrored databases. My suggestion is to consider switching the mirroring mode to asynchronous (high performance) during the index maintenance and then switching it back once the databases are resynchronized. This of course is dependent on your risk tolerance. If you can risk the increased potential for data loss and the loss of the ability to perform automatic failovers during this time, this would be a preferable approach.
Otherwise, you can see significant delays in query response times if the redo queue on the mirror gets backed up and these delays will be experienced by client users.
November 30, 2009 at 4:30 pm
Hi Robert, as I said In my post, I am stuck with using 2008 standard edition. I thought high performance mode mirroring was only available in enterprise editions? Or have I got that wrong?
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
November 30, 2009 at 6:05 pm
Sorry Jordon. I missed that tidbit in the original post. You are correct that asynchronous mirroring is Enterprise only. Standard edition is also single threaded for the redo, so you would probably see much better mirroring performance if you were able to use Enterprise Edition. Just throwing that in there in case the possibility of switching to EE comes up.
January 28, 2010 at 7:06 am
Hi Robert Davis,
I am novice on sql server mirroring. I need to know how I can switch the mirroring mode to asynchronous (high performance) for index maintenance as per your previous post? Is this simply going to database properties -> Mirroring and clicking on first tab (ie high performance). But what would happen to my witness. Do I need to configure witness again later when I switch this back after index maintenance.
-Lk
January 29, 2010 at 12:30 pm
You can switch to asynchronous using the Alter Database command on the principal server:
Alter Database <DB Name> Set Partner Safety Off;
And then to switch back:
Alter Database <DB Name> Set Partner Safety Full;
You can leave the witness as is and it will continue to function normally, but automatic failover will not be supported. The suggested route though is to remove the witness before switching safety mode and then add it back in after switching back. This is also doen using the Alter Database command on teh principal.
Alter Database <DB Name> Set Witness Off;
Alter Database <DB Name> Set Witness='TCP://witnessserver.domain.com:Port';
You won't be able to re-enable safety until the databases are in sync again. So you'll need to monitor sys.database_mirorring to determine the state of the mirroring session.
February 21, 2010 at 3:29 am
Jordon,
I have implemeted Synchronous mirroring in my organisation and here is wat i did
Before starting High batch loading / Index rebuilding,
use master
go
ALTER DATABASE TEST_MIRRORING SET PARTNER SUSPEND
GO
After this is completed
use master
go
ALTER DATABASE TEST_MIRRORING SET PARTNER RESUME
GO
Thsi will make secondary start to synchronize with Primary
Sundaresan R
February 21, 2010 at 12:23 pm
You can do it that way, but then it will take a longer time to sync up again because the mirror is doing nothing during the batch processing. This increases the length of time that your mirror is out of sync and increases the risk for potential data loss in case of an emergency forced failover.
February 21, 2010 at 1:13 pm
Robert Davis (2/21/2010)
You can do it that way, but then it will take a longer time to sync up again because the mirror is doing nothing during the batch processing. This increases the length of time that your mirror is out of sync and increases the risk for potential data loss in case of an emergency forced failover.
indeed, this thread is quite old now, and my mirroring has been up and running for a few months, by only rebuilding/reorgansing indexes based on their fragmentation and page count, i was able to reduce the 4 hour index tasks to about 18 minutes, which the mirror synced in about 19 minutes, it literally takes about a minute for the mirror to catch up. At first it was a little longer, so i took the liberty of isolating the largest and most common bad indexes (my index maintenance task emails me a report every night) and maintained them in a different time slot when the mirror was idle.
Knock Knock, Who's There?, sp underscore, sp underscore who?
spid1, spid2, spid3....
February 21, 2010 at 3:40 pm
Ah, nicely done!!
February 22, 2010 at 8:19 am
Hi Mr. Davis,
I'm running into a repeatable error on one of my mirroring sessions. They are 2 SQL 2008 SP1 Servers, using Transparent Data Encryption, running High Saftey Full. I logged onto my Principal Mirror and noticed that Mirroring was in a suspended state. When I tried to restart it I would get an error on the prinicpal that was non-descript, but on the mirror I got
Error message when you use database mirroring in SQL Server 2005 or in SQL Server 2008, and an assertion failure occurs intermittently: "SQL Server Assertion: File: <loglock.cpp>, line=759 Failed Assertion = 'result == LCK_OK'."
This was very similar to a fix in SQL 2008 SP1 CU 5
365468 974319 (http://support.microsoft.com/kb/974319/ ) FIX: Error message when you use database mirroring in SQL Server 2005 or in SQL Server 2008, and an assertion failure occurs intermittently: "SQL Server Assertion: File: <loglock.cpp>, line=823 Failed Assertion = 'result == LCK_OK'."
I downloaded and Ran CU 6, since it was the latest and greatest and should contain CU 5.
After the install and reboot, I continued to get the same error.
Now, whenever I try to set up mirroring when TDE is enabled I get
Msg 1418, Level 16, State 1, Line 1
The server network address "TDP://<FQDN>:<port>" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.
To further add to the confusion, I took TDE off of the Database, removed the database encryption key, did a full backup, and then a transaction log backup. Restored the backup and the transaction log to the mirror, and was able to get mirroring working just fine.
If I take that same mirrored DB, back it up and restore it as DB2, enable TDE, and set up mirroring and attempt to set up mirroring using a new backup and transaction log with TDE enable on it and it fails with the same error.
I've tried this with both servers in the role of prinicpal and mirror, I've currently got the same database mirroring between the 2 without TDE enabled. The endpoints are created, NETSTAT shows they are listening on the appropriate ports....not sure what else to try?
If you have any suggestions they would be very appriceated.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
February 22, 2010 at 11:34 am
First things first, this is probalby just a typo in your post, but you said that "The server network address "TDP://<FQDN>:<port>".
Can you verify that you are using TCP and not TDP in your server definition?
Are you taking the backups for mirroring setup after you enable TDE?
TDE should not be interfering with your ability to connect to the other instance which is what this error means.
Have you created the proper encryption keys on the mirror server so it can decrypt the data in it? You can test this simply by resotring the database with recovery and seeing if it restores correctly and if the data is readable.
February 22, 2010 at 11:48 am
....sometimes it is the most obvious thing. No you were right on I had copied my scripts into word and for some reason TDP was copied instead of TCP, the second I corrected that one line, everything fell into place.
Not sure why that happened, but wow that was it.....sometimes you just need another set of eyes
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply