Mirroring – MSSQLTREK

DB Mirroring from SQL 2008SP2 to 2008 RTM or from SQL 2005 to SQL 2008?

Have you ever thought about this? Can we Mirror a Database from SQL Server 2008SP2(Principal) to SQL Server 2008(Mirror) RTM? Well, how about Mirroring from SQL Server 2005 to SQL Server 2008?? Let me show you the prior case.

Scenario 1:

Principal DB Engine Version: SQL Server 2008 SP2

Mirror DB Engine Version: SQL Server 2008 RTM

DB Name: DB_Mirror

Answer: Yes, we can! You can see below screenshot.

You should not see any Issues with Failover/Switching DB Roles from Principal to Mirror and Viceversa.

Scenario 2:

Principal DB Engine Version: SQL Server 2005 SP3

Mirror DB Engine Version: SQL Server 2008SP2/2008R2.

I don’t have a SQL Server 2005 Instance to show you how it works, but Yes, we can! Very Important point to consider in this case is, Once you failover(Switch the Mirroring Roles) the Database from SQL 2005 to 2008, you can’t failback! This is One-Way approach.

Note: You can’t setup Mirroring from 2008(as Principal) to 2005(as Mirror). Well, DB Version can’t be downgraded. In other words, we can’t restore a DB to lower Versions!!

Hope this is Informative and something which you can think about as an option for upgrading/Migrating Large Databases with very less acceptable downtime! ( Make sure, you understand all the caveats before trying this in your Prod Environments).

How to Move a Mirrored Database MDF and LDF Files?

Couple of days ago, I had a conversation with one of my colleagues, on how to move database files from one location to another which is has DB Mirroring enabled. In this blog post, let’s see that in action..

Let’s consider the below Scenario:

1. You’ve a Large Database(let’s say 500+ GB DB) which is participating in DB Mirroring as a ‘Principal’ Database.

2. That’s the only database sitting on this Instance, a Dedicated Instance for your Application(In other words, detaching-attaching DB will cause almost the same impact as SQL Services Restart)

3. You want to Move your MDF and LDF files to another LUN (because of space constraints or you are doing SAN Expansion or something else)

4. You do not want to Break Mirroring completely, because you do not want to re-configure everything from scratch…which takes considerable amt. of time and not acceptable to your Business, running without DR for that long period of time.

5. What is the option we have in this case?

Solution: First thing to note, we can’t detach a Database while it is participating in Database Mirroring. You’ll get the below error if you try to do so..

“The operation cannot be performed on database “Mirror_tst” because it is involved in a database mirroring session.
ALTER DATABASE statement failed.”

so what to do now..? Remember, we’ve ALTER DATABASE…..MODIFY FILE….to do this trick.

Demo:

I’ve a database called ‘Mirror_tst’ which is being mirrored from PROD Instance to DR Instance as you can see below. Also, please note the current database file locations in the below screenshot(As of now, they are in default location)

Now, Let’s update the database file(s) location in system catalogs to “C:\Backs\” folder. For this I’ll issue the below commands.

ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst,FILENAME=’C:\Backs\mirror_tst.mdf’)
GO
ALTER DATABASE mirror_tst
MODIFY FILE(NAME= mirror_tst_log, FILENAME= ‘C:\Backs\Mirror_tst_log.ldf’)
GO

and i received below messages:

The file “mirror_tst” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “mirror_tst_log” has been modified in the system catalog. The new path will be used the next time the database is started.

So, the database file locations were modified in system catalog, all we have to do now is Stop SQL Service, move the physical files and start SQL Services. Let me do that!

I issued SHUTDOWN WITH NOWAIT on my PRINCIPAL Server as you can see below.( Be very cautious with this statement, understand what exactly you are doing before running this statement on your Prod Instance, believe me guys, Running this single statement unknowingly might cost you, loosing your Job!!..)

As you can see SQL is stopped and now, I moved the Physical files to ‘C:\Backs” folder as you can see below.

Now, I’m starting SQL Services and let’s see what happens to our database and more importantly to our Mirroring Status, keeping my fingers crossed 🙂

Once Restarted, the SQL Server was happy to bring up this Mirrored Database and our Mirroring Process is running happily without any issues as you can see below:) 🙂 Notice Physical files new location and the Mirroring Status now. Everything is pretty Normal:)

Hope this post helps,if you get into same scenario….Cheers!

Mirroring – Things to Consider

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.