DB Mirroring! Very fancy term which we all are hearing starting SQL Server 2005. Your DR Server is often called as “Hot Standby”( Real time Synch) when you are using DB mirroring where as it is considered as “Warm Standby” while your DR DB is Log Shipped!
Gotcha – If you are implementing Asynchronous mirroring Mode, Can you consider this as a real Hot Standby? I would say our all time favorite answer ” It Depends”. yes it really depends and I mean it.
PS I’m not going to explain how to configure DB Mirroring, Modes of Mirroring, Fail over Options in this blog post. That will be covered in another Blog post under the same label “Mirroring”.
So…….
First and foremost, Are the Principal and Mirror Servers are exactly identical in every aspect of windows and your SQL Server? This is a must to avoid surprises when a real disaster hits your data center and everyone expects your DR Database would behave exactly same as production database in an event of Prod DB failure.(Disaster)
There’s a well known so called Best practice saying that for OLTP Databases Mirroring works best and for any OLAP Database Log Shipping works best. I Strongly Disagree with this statement.
So, what would i really need to consider in terms of Database(s)?
Instead of differentiating OLAP/OLTP, try to understand Log Records Generation rate(in other words, your Transaction log Growth) and Size of your individual transactions. yes, very frequent huge Bulk imports will lead to many issues with Database Mirroring.
IMHO, this is something which as a DBA for your application you would(must) know which also covers effective T-Log Maintenance.( Imp – No matter how good network you are on, if your LDF is not properly maintained, your Mirroring session won’t work as you expect and your principal(Prod) Database would be in jeopardy!
Network:
DB mirroring is highly dependent on network( of course it works on TCP End points). So you should be on a high band width and low latent network. Less the latency between your Prod and DR Data center, more efficiently your Mirroring works.
LAN vs MAN vs WAN: Obviously LAN provides best performance in terms of latency.
Typical RTT(round trip times) are as follows:
MAN – 2 ms to 20 ms
WAM > 200 ms
Monitor:
You’ve to always keep an eye on your Mirroring state changes. You’ve to get notified with whatever alerting tool(s) you’ve in place in your enterprise. you can also create manual alerts from using a simple T-SQL Script via SQL Agent( You need DB Mail Profile being enabled on your SQL Server).
You’ve to keep an eye on number of VLF’s( Virtual Log Files) in your Principal Database, in other words keep an eye on your transaction log. VLF’s concept is beyond the scope of this blog post and will be explained in later posts.
Extremely Important: SQL Server will not truncate your Transaction log if log records are not sent to your Mirror Database even though you are taking a frequent log backups on your Principal Database. The Log records will not be copied to your DR Database as long as your Mirroring Session is “SUSPENDED/PAUSED” or “DISCONNECTED” which might grow your transaction log to the point where your Drive has no enough room to accommodate your log and Users start screaming ” Am not able to do any Inserts/Updates/Deletes and it’s an outage/Impact to our Application, What’s happening”.
So….again i want to stress, always keep a special eye on your Databases which are participating in DB Mirroring. How to and where to identify “Mirroring is causing not to truncate your Log”? – Will be covered in a later blog. Quick Tip – do “select name, log_reuse_wait_desc from sys.databases”. If you see any thing saying “mirroring” in your result set beside your database, that’s it 🙂
Always monitor Unsent portion of the log in Principal and Redo portion of the Log in your Mirrored databases. You can set up all kinds of Cool thresholds from your DB Mirroring Monitor in SSMS and you can set your own Alerts accordingly.
Last but definitely not the Least – Practice your DR Scenario as many times as you can. Convince your Management and your Application Partners for frequent DR(Disaster Recovery) Drills aka BCP(Business Continuity Plan) Exercices.
MSSQLTREK 