SQLServerCentral / Discuss Content Posted by Jay Dave / Article Discussions / Article Discussions by Author / A Look at Database Mirroring / Latest Posts

RE: A Look at Database Mirroring

kapfundestanley — Mon, 12 Sep 2011 02:54:03 GMT

Can i fail-over databases because of power failure?

RE: A Look at Database Mirroring

george sibbald — Wed, 06 May 2009 05:02:45 GMT

thank you very much for all your input cmille, its good to know what can be achieved and pitfalls\advantages. I will definitely be insisting on replicating my system databases, and boot partition as well if at all possible.I owe you one,regardsgeorge

RE: A Look at Database Mirroring

cmille19 — Tue, 05 May 2009 16:26:28 GMT

[quote][b]george sibbald (5/5/2009)[/b][hr][quote]In DR scenario my AD administrators will update the DNS entry to point to the DR IP instead of the primary server IP. So in effect, I am renaming the hostname/DNS name. As long as all the connection strings use the DNS name all applications will continue to connect without needing to change connection strings.[/quote]Are you runnng SQL 2000 or 2005? I would have expected issues with msdb in 2005 in particular, as its maintenance plans would point back to the primary server and SSIS packages could potentially have incorrect connections.[quote]Yes, I'm booting from SAN and although I no longer replicate the SQL Server boot partitions I continue to do so for web and app servers. Keep in mind if you replicate the boot partition the DR server must be dark and you must bring the primary server down or isolate the DR network to avoid duplicates server names as the DR server will come onliine with the same name. If I were not using clustering I would replicate the boot partitions. Also if you're an HP shop you can use RILOE to power on servers remotely.[/quote]So both primary and DR\QA boot from SAN but their boot partitions are independant? Any problems with boot from SAN? Interesting that you say you would go back to replicating the boot partition, would this not lose you your QA environment and the ability to patch the DR server without affecting the primary and SRDF, which sounds to me a good ability to have.regardsgeorge[/quote]That's correct all servers boot from SAN and the QA and DR boot partitions are independent. Been using boot from SAN for 5 years and no issues. Since the QA partition is independent you would not loose the QA environment. If you're replicating the primary boot partition then there isn't separate patching process for the DR server. Patching the primary server patches the DR server. What I miss about replicating boot partition is in few cases DBAs did not setup the DR server with the same path and port numbers as the primary which then caused problems. when replicating the boot partition this type of thing doesn't happen because there isn't a separate installation to maintain.

RE: A Look at Database Mirroring

george sibbald — Tue, 05 May 2009 09:48:01 GMT

[quote]In DR scenario my AD administrators will update the DNS entry to point to the DR IP instead of the primary server IP. So in effect, I am renaming the hostname/DNS name. As long as all the connection strings use the DNS name all applications will continue to connect without needing to change connection strings.[/quote]Are you runnng SQL 2000 or 2005? I would have expected issues with msdb in 2005 in particular, as its maintenance plans would point back to the primary server and SSIS packages could potentially have incorrect connections.[quote]Yes, I'm booting from SAN and although I no longer replicate the SQL Server boot partitions I continue to do so for web and app servers. Keep in mind if you replicate the boot partition the DR server must be dark and you must bring the primary server down or isolate the DR network to avoid duplicates server names as the DR server will come onliine with the same name. If I were not using clustering I would replicate the boot partitions. Also if you're an HP shop you can use RILOE to power on servers remotely.[/quote]So both primary and DR\QA boot from SAN but their boot partitions are independant? Any problems with boot from SAN? Interesting that you say you would go back to replicating the boot partition, would this not lose you your QA environment and the ability to patch the DR server without affecting the primary and SRDF, which sounds to me a good ability to have.regardsgeorge

RE: A Look at Database Mirroring

cmille19 — Mon, 04 May 2009 19:00:44 GMT

[quote][b]george sibbald (5/4/2009)[/b][hr]cmille19, thanks for the prompt reply, appreciate it.sorry to bang on but I need to clarify some points. you are using SQL clusters yes? Because mine are standalone servers so I don't have a virtual instance name. I am struggling to understand why you don't need to rename the server or at least update sysservers in master and entries in msdb because the server name has changed. (the systemdbs have after all in effect come from a different server).updating msdb to run on a different server is easy in SQL 2000 but a nightmare in 2005.are you saying you have to use named instances?So your DR site is permanently used for QA, and if you failover you do without QA environment?As you are booting from SAN anyway you don't see attaching all the secondary LUNS to a piece of identiacal kit waiting to receive them aa a method of failing over?[/quote]Yes, I'm using clusters. The reality is what is stored in sysservers for the local server is not important. You can run sp_dropserver and sp_addserver and have a server name other than the physical or virtual server name any every application will continue to connect and work fine. Keep in mind there are two server names in Windows. There is a NETBIOS/WINS name and host/DNS name. In DR scenario my AD administrators will update the DNS entry to point to the DR IP instead of the primary server IP. So in effect, I am renaming the hostname/DNS name. As long as all the connection strings use the DNS name all applications will continue to connect without needing to change connection strings.Yes, I'm using named instances, however the same concept applies to default instances. My only point about named instance is ensuring the instance name is the same on both the primary and DR servers. For example primary site SQL instance is Z002\SQL1 and DR instance is Z002DR\SQL1. The SQL1 named instance portion is the same on both servers.Yes, my DR servers are dual purpose as QA. We run about 4 DR failover tests per year and on those weekends the QA environment is unavailable. In the even of an actual DR failover there would be no QA. For that matter since my development environment is at my primary site, there would be no development either.Yes, I'm booting from SAN and although I no longer replicate the SQL Server boot partitions I continue to do so for web and app servers. Keep in mind if you replicate the boot partition the DR server must be dark and you must bring the primary server down or isolate the DR network to avoid duplicates server names as the DR server will come onliine with the same name. If I were not using clustering I would replicate the boot partitions. Also if you're an HP shop you can use RILOE to power on servers remotely.

RE: A Look at Database Mirroring

george sibbald — Mon, 04 May 2009 05:49:08 GMT

cmille19, thanks for the prompt reply, appreciate it.sorry to bang on but I need to clarify some points. you are using SQL clusters yes? Because mine are standalone servers so I don't have a virtual instance name. I am struggling to understand why you don't need to rename the server or at least update sysservers in master and entries in msdb because the server name has changed. (the systemdbs have after all in effect come from a different server).updating msdb to run on a different server is easy in SQL 2000 but a nightmare in 2005.are you saying you have to use named instances?So your DR site is permanently used for QA, and if you failover you do without QA environment?As you are booting from SAN anyway you don't see attaching all the secondary LUNS to a piece of identiacal kit waiting to receive them aa a method of failing over?

RE: A Look at Database Mirroring

cmille19 — Sun, 03 May 2009 19:10:11 GMT

[quote][b]george sibbald (5/3/2009)[/b][hr][quote][b]cmille19 (7/20/2007)[/b][hr]That's correct at the DR site there is an identical server where all volumes are replicated at the disk level from one SAN to another SAN over two OC3 pipes using SRDF/A. The DR server does not have the disks mounted, they are unmounted and a series EMC commands are run to mount the volumes. The DR SQL Server is configured as follows:One time setup -- Use a temporary volume to install SQL Server service using same instance name and path as primary site. Set SQL Server service to manual at DR site, remove temporary volume, setup SRDF/A to replicate all volumes except the OS this includes the system databases (master...). The repliated volumes remain unmounted. In event of disaster or during several annual tests run EMC scripts to mount the volumes, start SQL Server service and swing DNS entry. When SQL Server comes online its a mirror image of primary site SQL Server. The actual time it takes to mount the volumes is almost instaneous. The disk replication is within one minute of the primary site and setup to be consistent across a set of disks. The particular setup we are running is nothing speical in that you can have EMC design a identical solution. Here's one more cool thing about using SAN based replication. Since its a SAN based solution we use the same technique for all of servers including Unix, Windows, web, app and of course SQL Server. On the Web and App servers we maintain dark servers and replicate teh boot partition i.e. the C drive. Because of MS cluster setup at the primary site and the lame setup of storing IP Addresses as part of the cluster configuration we cannot replicate the boot partitions of the SQL Servers. Although we have replicated boot partitions for non-clustered SQL Servers in the past, however as I now longer have non-clustered SQL Servers.[/quote]cmille19, hope you see this! I will be setting up SRDF for our DR to replace log shipping, (non-clustered servers) and am looking for the best design to achieve fastest failover and failback. So your set up above sounds good to me as I wish if possible to replicate the system databases as well.A few questions, if you installed on SQL DR as the same instance name the server name must have been the same, so how do you get round having two servers of the same name active? Is the DR server renamed after initial install and again at failover time?How do you apply patches to SQL on the DR server, by replicating the C drive as well or splitting SRDF whilst you do it? Are you booting from SAN?cheersgeorge[/quote]I SRDF/A the system databases also. That way I don't have to attach databases just mount the EMC LUNs and start the SQL Server. To SAN replicate the system databases you'll need to install the DR SQL installation with the same path as the primary server. As far as server name. The server name is different on the DR server. I use strictly named instances for example Z002\SQL1, so just the instance name is the same. To avoid changing connection string the developers are instructed to use fully qualified DNS names for example Z002.acme.com. If the server is named instance the connection string would be the hostname followed by comma and the port number: Z002.acme.com,2507. In a DR scenario the DNS servers are sent a updated hostname to IP list which points to the DR IP with the original primary site DNS name.Before adoption clustering as an HA solution for my primary site, I replicated the boot partition. All of our Web and App servers use boot partition replication. Although I no longer replicate boot partition on SQL Servers, I'm still using boot from SAN.For patching, I'll have a second set of LUNs mounted on the DR server. These LUNs are used for QA environment. The system databases are the path as the DR and primary servers. A secondary benefit to this setup is that patches can be applied to both OS and SQL to keep in sync with primary server. In a DR scenario the QA LUNs are unmounted and the DR LUNs mounted.

RE: A Look at Database Mirroring

george sibbald — Sun, 03 May 2009 17:03:53 GMT

[quote][b]cmille19 (7/20/2007)[/b][hr]That's correct at the DR site there is an identical server where all volumes are replicated at the disk level from one SAN to another SAN over two OC3 pipes using SRDF/A. The DR server does not have the disks mounted, they are unmounted and a series EMC commands are run to mount the volumes. The DR SQL Server is configured as follows:One time setup -- Use a temporary volume to install SQL Server service using same instance name and path as primary site. Set SQL Server service to manual at DR site, remove temporary volume, setup SRDF/A to replicate all volumes except the OS this includes the system databases (master...). The repliated volumes remain unmounted. In event of disaster or during several annual tests run EMC scripts to mount the volumes, start SQL Server service and swing DNS entry. When SQL Server comes online its a mirror image of primary site SQL Server. The actual time it takes to mount the volumes is almost instaneous. The disk replication is within one minute of the primary site and setup to be consistent across a set of disks. The particular setup we are running is nothing speical in that you can have EMC design a identical solution. Here's one more cool thing about using SAN based replication. Since its a SAN based solution we use the same technique for all of servers including Unix, Windows, web, app and of course SQL Server. On the Web and App servers we maintain dark servers and replicate teh boot partition i.e. the C drive. Because of MS cluster setup at the primary site and the lame setup of storing IP Addresses as part of the cluster configuration we cannot replicate the boot partitions of the SQL Servers. Although we have replicated boot partitions for non-clustered SQL Servers in the past, however as I now longer have non-clustered SQL Servers.[/quote]cmille19, hope you see this! I will be setting up SRDF for our DR to replace log shipping, (non-clustered servers) and am looking for the best design to achieve fastest failover and failback. So your set up above sounds good to me as I wish if possible to replicate the system databases as well.A few questions, if you installed on SQL DR as the same instance name the server name must have been the same, so how do you get round having two servers of the same name active? Is the DR server renamed after initial install and again at failover time?How do you apply patches to SQL on the DR server, by replicating the C drive as well or splitting SRDF whilst you do it? Are you booting from SAN?cheersgeorge

RE: A Look at Database Mirroring

Lucy Shi — Tue, 30 Dec 2008 14:58:29 GMT

We have SRDF/S for our SQL Server 2000 and 2005 DR, both work fine. Due to distance constrain, we will have to look into SRDF/A. According to EMC documentation about SRDF/A, we have to be very careful with initial disk assignment, otherwise the roll failure will occur. Would you please elaborate more on how you design the disk allocation for SQL Server log and data? Thank you.

RE: A Look at Database Mirroring

Paul Mu — Fri, 31 Oct 2008 04:33:50 GMT

Read this DM best practice article from Technet: http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx

RE: A Look at Database Mirroring

reuben.anderson — Mon, 07 Apr 2008 07:17:02 GMT

Just wondering - has anyone actually used the high-safety mode on a production database? Under what server architecture and is there a serious or noticeable performance difference? .. any stats on this? I'd prefer to use the high-performance mode generally, but 'clever' old microsoft have made this an Enterprise feature and that's not within the budget for our server population. I note that the TechNet guide says; [i]"Despite the close coordination between principal and mirror when safety is FULL, database mirroring is not a distributed transaction and does not use a two-phase commit."[/i]... but really isn't that just semantics? The primary still waits for confirmation of the secondary commit ... As for the debate on DM vs Clustering... I think it's missing the point. Mirroring is a cheap DR solution. It's an intermediate step before the high end hardware replication kit. It's a better DR solution that replication, because it is a full mirror... and yeah it is just a suped-up log shipping. The key advantage is that it's so simple to work with. Finally, with respect to the login / job / maint plan transfer problem ... how about implementing a DDL trigger? .. Fire off the same code on the mirror server?

RE: A Look at Database Mirroring

Mike Mortensen — Mon, 30 Jul 2007 16:20:00 GMT

Jeff - There is no way to specify a named instance with the SET PARTNER = xxx, but you are able to use an alternate TCP port.Each SQL Server instance should use a different port for database mirroring.serverA 192.168.1.4:5022serverA/instance1 192.168.1.4:5023serverA/instance2 192.168.1.4:5024Hope that helps!Mike

RE: A Look at Database Mirroring

Mike Mortensen — Mon, 30 Jul 2007 16:12:00 GMT

Just wanted to give a big THANK YOU to Michael for assembling the non-domain member setup script - worked great!Re: the clustering/DM debateDepends on what you're doing - for me, DM was a better solution than clustering. Production site is 2 web, 2 db (+ witness)IT Team is 1 person (me)Application is brand new, so ADO/Connection strings are not a problemLow database count (< 5)Small database size (< 1 GB)All Servers are on VMWare - SQL cluster was more headache not to mention the licensing + hardware burden - Virtual Network removes many failure/redundancy issues - Hardware Failures handled by VMotion to an alternate ESX ServerGreater peace of mind has been achieved! Windows OS failures are easily covered by this process, except a bad windows update of course The only thing missing is accessing the mirrored data for reporting - but that can be bypassed by replicating the mirrored database, which is next on my list.Mike

RE: A Look at Database Mirroring

ckempste — Sat, 21 Jul 2007 21:42:00 GMT

Hi allHas anyone used it to complement clustering? is it possible?We run a number of SQL clusters at prod data centres, they are SAN connected and also use SAN mirroring to the DR data centre. On top of this we also log-ship databases via a tivoli storage agent to the DR server(s). Has anyone used or considered database mirroring to complement/replace log-shipping within a clustered scenario in prod?This scenario is typical in the Oracle world, where the prod site runs RAC and uses data guard (where I see SQL Server's data mirroring competing as a technology).Thoughts/comments?CheersCk

RE: A Look at Database Mirroring

cmille19 — Fri, 20 Jul 2007 17:14:00 GMT

That's correct at the DR site there is an identical server where all volumes are replicated at the disk level from one SAN to another SAN over two OC3 pipes using SRDF/A. The DR server does not have the disks mounted, they are unmounted and a series EMC commands are run to mount the volumes. The DR SQL Server is configured as follows:

One time setup -- Use a temporary volume to install SQL Server service using same instance name and path as primary site. Set SQL Server service to manual at DR site, remove temporary volume, setup SRDF/A to replicate all volumes except the OS this includes the system databases (master...). The repliated volumes remain unmounted. In event of disaster or during several annual tests run EMC scripts to mount the volumes, start SQL Server service and swing DNS entry. When SQL Server comes online its a mirror image of primary site SQL Server. The actual time it takes to mount the volumes is almost instaneous. The disk replication is within one minute of the primary site and setup to be consistent across a set of disks. The particular setup we are running is nothing speical in that you can have EMC design a identical solution. Here's one more cool thing about using SAN based replication. Since its a SAN based solution we use the same technique for all of servers including Unix, Windows, web, app and of course SQL Server. On the Web and App servers we maintain dark servers and replicate teh boot partition i.e. the C drive. Because of MS cluster setup at the primary site and the lame setup of storing IP Addresses as part of the cluster configuration we cannot replicate the boot partitions of the SQL Servers. Although we have replicated boot partitions for non-clustered SQL Servers in the past, however as I now longer have non-clustered SQL Servers.

RE: A Look at Database Mirroring

JRoughgarden — Fri, 20 Jul 2007 16:29:00 GMT

I attempted to implement DM on a 3-box workgroup using the GUI wizard, but failed, apparently because the wizard's logic does not work for workgroups. So next I tried to implement Michael Merriil excellent script for non-domain DM. Everything worked fine (endpoints, certificates, etc.) until it came to actually setting up the mirroring. Then it failed again.

I am wondering if it has to do with the presence of named instances. For example the primary is called jaguar/sql05dev. The primary box also has another instance jaguar/sqlexpress. The other boxes also have named instances, although only one SQL 2k5 instance exists on each. Anyway, I am suspicious of

alter database aspnetdb set partner = 'TCP://192.168.1.4:5022' as there is no named instance specification.

How do I specify a named instance with the IP address notation?

Thanks,

Jeff

RE: A Look at Database Mirroring

sqldba-294117 — Fri, 20 Jul 2007 15:56:00 GMT

There are many ways to write the connection string, but below is one example. In this example 'ServerA' is the principal, 'ServerB' is the mirror, AdventureWorks is the database when using Windows Authentication to connect to the SQL Servers:

"Data Source=ServerA;Failover Partner=ServerB;Initial Catalog=AdventureWorks;Integrated Security=True;"

Below are some more helpful links that might answer most of your questions\concerns.

http://www.connectionstrings.com/?carrier=sqlserver2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

http://msdn2.microsoft.com/en-us/library/ex6y04yf(vs.80).aspx (What's new in ADO.net 2.0)

http://msdn2.microsoft.com/en-us/library/5h52hef8(VS.80).aspx (Connection String Info)

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(VS.80).aspx (SqlConnection.ConnectionString Property )

RE: A Look at Database Mirroring

Simon-413722 — Thu, 19 Jul 2007 19:16:00 GMT

You point sounds valid enough. We’re talking about different situation. So, I wouldn’t say clustering is any bad, its just difference in approach for different purpose.

Just a few more things to understand on your disk mirroring. If I understand correctly, mirroring on the disk level just merely brings the DBs/Logs to a different SAN location. Say if you primary location has an indefinite power outage (or assume its one day), how would you made sure the SQL can be brought up in say, the next hour or so. Do you have another SQL box at your DR location ready and connected to that replicated SAN disks? Do you re-attach the DB? What about the SAN zoning to the disks? Do you already have fibre laid to the SAN at DR site?

From what I can see, you can replicate an active DB across to another SAN but cant have any SQL connects to the DB. The disk mirroring basically just replicate contents across.

I appreciate you sharing your experience. It would help me to think of different approaches. I’m very interested to know how you would be able to bring up your DBs in layout you’ve mentioned.

Cheers,

Simon

RE: A Look at Database Mirroring

cmille19 — Thu, 19 Jul 2007 18:48:00 GMT

To clarify, I use MS clustering for local failover high-availability in an active-passive configuration and no way implied clustering as site failover alternative. For failover to our remote site which is more than 1,000 miles away we use SAN based EMC SRDF/A to identical H/W at our dark site. We too manually swing the DNS entry upon failover to the remote server and both the DNS swing and EMC commands are scripted. Our failover time even with mounting the R2s is is pretty close to the time it takes to swing the DNS entry.Based on my experience, yours may vary, I can state the following generalizations:Hardware solutions such SRDF/A are generally better than S/W solutions such as Data Mirroring.Our spend on a hardware i.e. SAN solution is much more than the spend on S/W solution using DM.Data Mirroring I would characterize as similar to log shipping i.e. phyiscal operation of replaying log restores to a remote host. Having used log shipping both the MS delivered and customized for several years and given the choice over DM/Log Shipping and H/W based disk replication I would choose disk based replication. And finally having run disk based replication over SRDF/A for over two years, I can attest that it works and requires 0 DBA hours. I can not say the same for log shipping. I would also state that SRDF/A is generally better than MirrorView/A (You get what you pay for).My two biggest complaints with DM is that it requires rewriting your applicaitons to be DM aware if you want automatic failover and it is a physical operation instead of a logical operation.

RE: A Look at Database Mirroring

Simon-413722 — Tue, 17 Jul 2007 22:41:00 GMT

I do agree that clustering has its benefit. But for me, its still a single point of failure. Clustering is availability vs DM is redundancy. I don’t doubt the reliability of SAN but sometimes shits just happen.

We too, had SAN that replicate at disk level (MirrorStore) replicating to another site. But unfortunately, not all DBs are available on SAN and I’m not used to the concept of sharing a disk for few DBs/app, especially those busy OLTPs. Say SAN replication is enabled, how would you put the DB up if your building is burnt? Would business wait until the SQL instance is up? You’ll still need a SQL instance somewhere to access the replicated DB, and this is what I’m using DM for. Sometimes, businesses enforces that the system should never be down for mere few hours. It costs them too much.

There are even more expensive solution such as setting up mirror + clustering, which quadruple the hardware and costs. But, I reckon these are case by case. Nothing is “one” best solution. I’m sure you’ve got your reasons for clustering, and its working for you.

I would also like to share a bit of my experience on log shipping.

Log shipping was a headache for me too because it requires intermittent attention, although it’s a bit less now. It doesn’t work very well with VLDB as it constantly giving error messages on backup/restore alerts jobs. Fortunately we’ve got MOM for monitoring and would know which ones are not false alarms and making sure the alerts are minimised (E.g increase log backup frequency, set log restore to be a bit higher, increase log shipping monitor alert threshold, etc). So far, we’ve reduced a lot of false alarm and manage to monitor all DBs pretty well (but not without 0 effort though). That’s why I’m interested to slowly use DB mirroring if it proves superior over log shipping. There’s a custom solution building log shipping + SQL Lite Speed to compress the transaction logs before sending over the network. I'm pretty sure there are codes out there that can be downloaded and tested. I remember by a person name Chris Kempler who wrote a similar solution.

The business I’m in requires redundancy, even at some point of time it might sacrifice a bit of performance.

Ok, now back to DM. I’ve not changes any of my application to be re-written to DM aware. All our DRP plans require manual intervention, which is my preference too. I don’t like if a system switches itself back and forth when we’ve got network glitch rather than a real DR situation. We created a DNS CNAME for our SQL Server and application are config to connect using this CNAME. If primary server is down, our network guys can quickly change the CNAME to have the DR IP. A forced DNS/IP refresh can happen at any time. So, from the application point of view, no config changes would need to be performed. This requires manual intervention, which is part of our policy. This CNAME in some way works the same as clustering virtual config where CNAME = SQL virtual name, CNAME IP = SQL virtual IP and CNAME IP can be changed at any time.

An important point to DM, the default failover time for DM is 10s. This is way too low! I would say at any time, set it to a higher value if you've got an automatic failover enabled, e.g. 90s

ALTER DATABASE <DB Name> SET PARTNER TIMEOUT 90;

With or without clustering, the server needs to be spec-ed to support the DBs load its holding. I don’t think clustering helps in many ways in terms of performance. Funnily enough, I found its harder to support clustering than DM. M$ specifically do not recommend clustering for separate geographic location as likely you’ll need to involve hardware manufacturer in any issue. Read the part on the Node Location

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx

I would be very interested how you architect your servers for a business objects and its pros and cons.

Simon

RE: A Look at Database Mirroring

JRoughgarden — Tue, 17 Jul 2007 18:13:00 GMT

Thank you, Simon, and all others who participated in this thread.

FWIW, I was DBA Manager and inherited paired clustered servers at six sites throughout the US, all doing merge replication. It was a real headache; clustering was down more often that the SQL Servers were.

RE: A Look at Database Mirroring

cmille19 — Tue, 17 Jul 2007 17:28:00 GMT

I use clustering over database mirroring. Clustering has its issues also but at least I can use without rewriting the client applications to be DM aware. Like most SQL Servers DBAs I'm still waiting for Microsoft's answer to Oracle RAC. For DR site failover scenarios I used to use log shipping for several years and probably spent about 20% of the time maintaining it. Now I use EMC SRDF/A and replicate at the disk level maintaining a dark site within one minute and requiring 0 DBA hours. This is cool stuff if you can afford it.

RE: A Look at Database Mirroring

Simon-413722 — Tue, 17 Jul 2007 17:25:00 GMT

Hi Cmille,

Part of my disaster recovery strategy are involves using DM. it’s not to say I disagree with you, but I’ve got following reason why I use it. If you think DM is not a good tool, do you have any other suggestions in mind? I’m always looking for ways to improve my disaster recovery plan.

Our organisation have SLA (I believe most organisations would have too), and system must be up within xxx time. There would be a chance where whole site fails indefinitely. Sounds weird, but it is.

Disk space – unlike SQL Server clustering a separate copy of the database must be maintained resulting in twice the amount of disk space. This is a big deal for VLDBs

This really depends on the SLA. If DB mirroring is not used, log ship or any other method would need to be used in place. So, in any way, disk space would need to be allocated and it’ll be expensive. Backup/Restore is just not viable for a 2hours RPO/RTO especially true for VLDB. Things also to consider -> whole server down, so where can you put VLDB without a warm stand-by? Clustering is for same-site SQL availability.

SAN fabric – One of the arguments for DM is since you have separate disk copies you eliminate a disks as a single point of failure. This is only partially true, since all SAN connects are routing through a single SAN fabric

SAN fabrics are always redundant, no? When you buy a 1 x server + 1 x SAN, you’ll make sure it has 2 HBAs to 2 SAN controller/switch, etc. But I reckon some would go with configuring SAN to performance instead of redundancy, and therefore they have this single point of failure.

DM only addresses a single database and non-SQL Server services will not failover. Examples include Schedulers, and backup agents

True. Script all across DR server but disable them.

Requires application changes in order for automated failover to be used. Either SNAC or ADO.NET 2.0 must be used in application code, so that the application is DM aware

True. My preference would be high performance though.

DM uses source, mirror and witness. The witness will require an additional SQL Server license albeit a small per seat license

You can use SQL Express edition to host the witness to reduce costs.

Need to maintain logins manually on both servers since this is at the instance level and DM does not cover master, model or msdb

Transfer logins to DR server. If there are any user logins mapped to DB, fail-over mirror to DR, create the login and default to DB, and switch back to primary. User ids can be easily fixed with sp_change_users_login. I believe jobs can be scheduled to sync logins between both servers too. We’re using lots of web app + DB, therefore, SQL passwords are not changed frequently.

DM does not handle failing over of SQL jobs, downloads, or external jobs

Agree. Therefore, would need to transfer them during implementation.

Async uses single thread for DM

• In SQL Server Standard Edition, the mirror server always uses a single thread to roll forward the database.

• In SQL Server 2005 Enterprise Edition, mirror servers on computers with fewer than five CPUs also use only a single thread.

• With five or more CPU's, a SQL Server 2005 Enterprise Edition mirror server distributes its roll forward operations among multiple threads during a failover (known as parallel redo) and is optimized to use one thread for every four CPUs. This improves the failover time on the mirror server as the log records can be rolled forward in parallel.

Performance – higher log generation, network latency with Safety-full less tran/sec on source system

Very true. Its preferred to set high performance if you’ve got issues with performance

Log growth issues until mirror is available even though tran has committed on principal

True. Transaction log will grow until mirror is available. I would suggest if mirror is down indefinitely, remove mirror and fix the mirror before re-establishing DM.

If witness is lost no auto failover. Mirror loss tran log grows until available. Witness and Mirror loss everything stops
Transactions can take twice as long to commit with full-safety

Agree. High safety full should be used when the servers are side by side

Database Mirroring is a physical operation i.e. equivalent to log shipping/DB restore and not a logical operation like replication or Oracle Data Guard making it more likely to encounter issues and difficult to use as a reporting server (granted you can do some kind of lame database snapshot).

I’ve not had any issues with my smaller databases. I’ve got 2 separate sites with ~ 20 DBs mirrored across. All servers are tuned to support DB load on the primary. Very expensive architecture, but its all about business and SLAs.

Replication are definitely not a good option for DADR.

Cheers,

Simon

RE: A Look at Database Mirroring

JRoughgarden — Tue, 17 Jul 2007 16:04:00 GMT

To Newbie and MIke,

First, to Newbie, I am not clear on the motivation for sections 1 to 6 that establish ingoing and outgoing connections among the three servers. Why is this necessary? What do you mean by this code is for a non-domain environment?

Mike, good caveats all, but tehn what do you recommend in place of mirroring?

Thanks

Jeff Roughgarden

RE: A Look at Database Mirroring

michael merrill — Tue, 17 Jul 2007 15:40:00 GMT

All true, but it still a nice option that fits between a single DB and Clustering. Just another tool.

RE: A Look at Database Mirroring

cmille19 — Tue, 17 Jul 2007 15:13:00 GMT

Nice summary of database mirroring as side note I came with 13 reasons why you wouldn't use database mirroring:

Disk space – unlike SQL Server clustering a separate copy of the database must be maintained resulting in twice the amount of disk space. This is a big deal for VLDBs
SAN fabric – One of the arguments for DM is since you have separate disk copies you eliminate a disks as a single point of failure. This is only partially true, since all SAN connects are routing through a single SAN fabric
DM only addresses a single database and non-SQL Server services will not failover. Examples include Schedulers, and backup agents
Requires application changes in order for automated failover to be used. Either SNAC or ADO.NET 2.0 must be used in application code, so that the application is DM aware
DM uses source, mirror and witness. The witness will require an additional SQL Server license albeit a small per seat license
Need to maintain logins manually on both servers since this is at the instance level and DM does not cover master, model or msdb
DM does not handle failing over of SQL jobs, downloads, or external jobs
Async uses single thread for DM
Performance – higher log generation, network latency with Safety-full less tran/sec on source system
Log growth issues until mirror is available even though tran has committed on principal
If witness is lost no auto failover. Mirror loss tran log grows until available. Witness and Mirror loss everything stops
Transactions can take twice as long to commit with full-safety
Database Mirroring is a physical operation i.e. equivalent to log shipping/DB restore and not a logical operation like replication or Oracle Data Guard making it more likely to encounter issues and difficult to use as a reporting server (granted you can do some kind of lame database snapshot).

RE: A Look at Database Mirroring

michael merrill — Tue, 17 Jul 2007 14:23:00 GMT

If you are setting up mirroring in a non-domain environment, here are the scripts I "created" to set it up.

/* ---------- 0. Prerequisites- do these before you begin ----------- */

-- On All servers involved, edit the hosts file to have server names and ip addesses for other servers in group.-- Replace IP addresses, passwords, share locations below as needed.

--Run each section on the appropriate server!!

--On the Primary Server

ALTER Database DBToMirrorNameSET RECOVERY FULL

Backup database DBToMirrorNameto disk = 'c:\share\DBToMirrorName.bak'with format

backup log DBToMirrorNameto disk = 'c:\share\DBToMirrorNameLog.bak'with format

--On the Mirror Server-- Copy over the backups to c:\

RESTORE database DBToMirrorNameFROM DISK = 'c:\DBToMirrorName.bak'with NORECOVERY

RESTORE log DBToMirrorNameFROM DISK = 'c:\DBToMirrorNameLog.bak'WITH FILE=1, NORECOVERY

/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

DROP ENDPOINT MirroringGODROP CERTIFICATE PRIMARY_CERTGODROP MASTER KEYGO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' -- Replace with real passwordGO

CREATE CERTIFICATE PRIMARY_CERT WITH SUBJECT = 'PRIMARY_CERT for database mirroring', START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'GO

CREATE ENDPOINT Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE PRIMARY_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )GO

BACKUP CERTIFICATE PRIMARY_CERT TO FILE = 'C:\share\PRIMARY_CERT.cer'GO

-- then copy certificate to other two machines

/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

DROP ENDPOINT MirroringGODROP CERTIFICATE SECONDARY_CERT

GODROP MASTER KEYGO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' -- Replace with real passwordGO

CREATE CERTIFICATE SECONDARY_CERT WITH SUBJECT = 'SECONDARY_CERT for database mirroring', START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'GO

CREATE ENDPOINT Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE SECONDARY_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )GO

BACKUP CERTIFICATE SECONDARY_CERT TO FILE = 'C:\SECONDARY_CERT.cer'GO

-- then copy certificate to other two machines

/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */

DROP ENDPOINT MirroringGODROP CERTIFICATE WITNESS_CERTGODROP MASTER KEYGO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password' -- Replace with real passwordGO

CREATE CERTIFICATE WITNESS_CERT WITH SUBJECT = 'WITNESS_CERT for database mirroring', START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'GO

CREATE ENDPOINT Mirroring STATE = STARTED AS TCP ( LISTENER_PORT=7024 , LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE WITNESS_CERT , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL )GO

BACKUP CERTIFICATE WITNESS_CERT TO FILE = 'C:\WITNESS_CERT.cer'GO

-- then copy certificate to other two machines

/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

/* enable inbound from the mirror */

DROP CERTIFICATE SECONDARY_CERTGODROP USER MIRROR_SECONDARY_USERGODROP LOGIN MIRROR_SECONDARY_LOGINGO

CREATE LOGIN MIRROR_SECONDARY_LOGIN WITH PASSWORD = 'password' -- Replace with real passwordGO

CREATE USER MIRROR_SECONDARY_USER FOR LOGIN MIRROR_SECONDARY_LOGINGO

CREATE CERTIFICATE SECONDARY_CERT AUTHORIZATION MIRROR_SECONDARY_USER FROM FILE = 'C:\Share\SECONDARY_CERT.cer'GO

GRANT CONNECT ON ENDPOINT::Mirroring TO MIRROR_SECONDARY_LOGINGO

/* enable inbound from the witness */

DROP CERTIFICATE WITNESS_CERTGODROP USER MIRROR_WITNESS_USERGODROP LOGIN MIRROR_WITNESS_LOGINGO

CREATE LOGIN MIRROR_WITNESS_LOGIN WITH PASSWORD = 'password' -- Replace with real passwordGO

CREATE USER MIRROR_WITNESS_USER FOR LOGIN MIRROR_WITNESS_LOGINGO

CREATE CERTIFICATE WITNESS_CERT AUTHORIZATION MIRROR_WITNESS_USER FROM FILE = 'c:\share\WITNESS_CERT.cer'GO

GRANT CONNECT ON ENDPOINT::Mirroring TO MIRROR_WITNESS_LOGINGO

/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

/* enable inbound from the primary */

DROP CERTIFICATE PRIMARY_CERTGODROP USER MIRROR_PRIMARY_USERGODROP LOGIN MIRROR_PRIMARY_LOGINGO

CREATE LOGIN MIRROR_PRIMARY_LOGIN WITH PASSWORD = 'password' -- Replace with real passwordGO

CREATE USER MIRROR_PRIMARY_USER FOR LOGIN MIRROR_PRIMARY_LOGINGO

CREATE CERTIFICATE PRIMARY_CERT AUTHORIZATION MIRROR_PRIMARY_USER FROM FILE = 'c:\PRIMARY_CERT.cer'GO

GRANT CONNECT ON ENDPOINT::Mirroring TO MIRROR_PRIMARY_LOGINGO

/* enable inbound from the witness */

DROP CERTIFICATE WITNESS_CERTGODROP USER MIRROR_WITNESS_USERGODROP LOGIN MIRROR_WITNESS_LOGINGO

CREATE LOGIN MIRROR_WITNESS_LOGIN WITH PASSWORD = 'password' -- Replace with real passwordGO

CREATE USER MIRROR_WITNESS_USER FOR LOGIN MIRROR_WITNESS_LOGINGO

CREATE CERTIFICATE WITNESS_CERT AUTHORIZATION MIRROR_WITNESS_USER FROM FILE = 'c:\WITNESS_CERT.cer'GO

GRANT CONNECT ON ENDPOINT::Mirroring TO MIRROR_WITNESS_LOGINGO

/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

/* enable inbound from the mirror */

DROP CERTIFICATE SECONDARY_CERTGODROP USER MIRROR_SECONDARY_USERGODROP LOGIN MIRROR_SECONDARY_LOGINGO

CREATE LOGIN MIRROR_SECONDARY_LOGIN WITH PASSWORD = 'password' -- Replace with real passwordGO

CREATE USER MIRROR_SECONDARY_USER FOR LOGIN MIRROR_SECONDARY_LOGINGO

CREATE CERTIFICATE SECONDARY_CERT AUTHORIZATION MIRROR_SECONDARY_USER FROM FILE = 'c:\SECONDARY_CERT.cer'GO

GRANT CONNECT ON ENDPOINT::Mirroring TO MIRROR_SECONDARY_LOGINGO

/* enable inbound from the primary */

DROP CERTIFICATE PRIMARY_CERTGODROP USER MIRROR_PRIMARY_USERGODROP LOGIN MIRROR_PRIMARY_LOGINGO

CREATE LOGIN MIRROR_PRIMARY_LOGIN WITH PASSWORD = 'password' -- Replace with real passwordGO

CREATE USER MIRROR_PRIMARY_USER FOR LOGIN MIRROR_PRIMARY_LOGINGO

CREATE CERTIFICATE PRIMARY_CERT AUTHORIZATION MIRROR_PRIMARY_USER FROM FILE = 'c:\PRIMARY_CERT.cer'GO

GRANT CONNECT ON ENDPOINT::Mirroring TO MIRROR_PRIMARY_LOGINGO

/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */

ALTER DATABASE DBToMirrorName SET PARTNER OFFGO

ALTER DATABASE DBToMirrorName SET PARTNER = 'TCP://xxx.xxx.xxx.xx2:7024'; --Replace IPGO

/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */

ALTER DATABASE DBToMirrorName SET PARTNER OFFGO

ALTER DATABASE DBToMirrorName SET PARTNER = 'TCP://xxx.xxx.xxx.xx3:7024'; --Replace IPGO

/* -------- 9. SET PRIMARY'S (and thus the Mirror's) WITNESS TO THE WITNESS SERVER -------- */

ALTER DATABASE DBToMirrorName SET WITNESS OFFGO

ALTER DATABASE DBToMirrorName SET WITNESS = 'TCP://xxx.xxx.xxx.xx4 :7024'; --Replace IPGO

--Mike

RE: A Look at Database Mirroring

Steve Jones - SSC Editor — Tue, 17 Jul 2007 07:14:00 GMT

You definitely cannot access the mirror. This came up at TechEd with some of the engineers and they are looking at it, but right now you have no access to the mirror. Simon's suggestion above can work.

RE: A Look at Database Mirroring

Simon-413722 — Tue, 17 Jul 2007 01:21:00 GMT

Colin,

I dont think there's any way you can access a mirror DB directly as it needs to be RESTORE WITH NORECOVERY in order to set a database mirror.

However, you can create a snapshot of the mirror DB at a point in time and access that copy for your reporting. This mirror copy should be as up to date as the primary DB at any point in time, depending on which setting you're using for DB mirror.

The syntax to create a DB snapshot is

CREATE DATABASE <snapshot_name) ON

(NAME=N'DB_data_file_name',

FILENAME = N'C:\physical_file_name.snap')

AS SNAPSHOT OF database_name

Disadvantage of this snapshot is that the data it contains wont be refreshed once taken. you'll have to drop the snapshot and re-create it in order to refresh the DB. Hence, dropping users from your DB before allowing them to re-connect.

Unlike log shipping, you can restore the secondary DB WITH STANDBY and therefore, can access the read-only DB at anytime. But i think the system will kick you out of the DB when it performs transaction log restore on the secondary server.

Simon

RE: A Look at Database Mirroring

Magical Horn — Tue, 17 Jul 2007 01:09:00 GMT

THis was a very good introductin to the topic for a novice such as myself, but I have a question, which is:

Can I access the Mirror copy directly and, in effect, use it as a Reporting database to take part of the load from the Principal Server. I'm happy that it it may not be always absolutely up to date.

Regards

Colin

RE: A Look at Database Mirroring

Simon-413722 — Mon, 16 Jul 2007 23:12:00 GMT

Any plan to an article about database mirroring + log shipping? I've spoken to M$ and they were saying it is possible to combine both. Example in a simplified situation, you can stop mirroring, run reindexing, flush transaction logs to disks at certain time and re-apply at the mirror rather than having a constant stream of data congesting the network.

For some reason, he keep hinting to me that database mirroring impose more overhead than log shipping?? I had this thought that database mirroring was supposed to impose less overhead compared to log shipping since the logs were "logically" applied from the buffer (of course, after its committed to disk at the principal)

Anyway, I'm in the midst testing out migration of a VLDB (~1TB of OLTP) over 2 separate location and will test database mirroring performance (config to High performance). I've not had any problem with smaller DBs. Would really like to know if others has tried to mirror VLDB over 2 different sites. By the way, although its 2 different sites, but they're seamlessly connected via a high bandwidth. Still, if anyone has setup such sites, please share your experience..

Cheers,

Simon

RE: A Look at Database Mirroring

Sugesh Kumar — Tue, 19 Jun 2007 22:44:00 GMT

A simple and more precise document on database nmirroring. I have read the white paper in microsoft foe the same. Appulads to Dave he has brought the more than that within few pages the microsoft document was running into pages illustrating the same.

A Look at Database Mirroring

sqldba-294117 — Tue, 19 Jun 2007 16:40:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jDave/3046.asp