A Look at Database Mirroring
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jDave/3046.asp
-
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.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com -
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
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
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
-
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
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
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.
-
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 DBToMirrorName
SET RECOVERY FULL
Backup database DBToMirrorName
to disk = 'c:\share\DBToMirrorName.bak'
with format
backup log DBToMirrorName
to disk = 'c:\share\DBToMirrorNameLog.bak'
with format
--On the Mirror Server
-- Copy over the backups to c:\
RESTORE database DBToMirrorName
FROM DISK = 'c:\DBToMirrorName.bak'
with NORECOVERY
RESTORE log DBToMirrorName
FROM DISK = 'c:\DBToMirrorNameLog.bak'
WITH FILE=1, NORECOVERY
/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE PRIMARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password' -- Replace with real password
GO
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 Mirroring
GO
DROP CERTIFICATE SECONDARY_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password' -- Replace with real password
GO
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 Mirroring
GO
DROP CERTIFICATE WITNESS_CERT
GO
DROP MASTER KEY
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'password' -- Replace with real password
GO
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_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'C:\Share\SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:\share\WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
/* enable inbound from the primary */
DROP CERTIFICATE PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:\PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* enable inbound from the witness */
DROP CERTIFICATE WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO
CREATE CERTIFICATE WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:\WITNESS_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO
/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */
/* enable inbound from the mirror */
DROP CERTIFICATE SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO
CREATE CERTIFICATE SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:\SECONDARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO
/* enable inbound from the primary */
DROP CERTIFICATE PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = 'password' -- Replace with real password
GO
CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO
CREATE CERTIFICATE PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:\PRIMARY_CERT.cer'
GO
GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO
/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */
ALTER DATABASE DBToMirrorName
SET PARTNER OFF
GO
ALTER DATABASE DBToMirrorName
SET PARTNER = 'TCP://xxx.xxx.xxx.xx2:7024'; --Replace IP
GO
/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */
ALTER DATABASE DBToMirrorName
SET PARTNER OFF
GO
ALTER DATABASE DBToMirrorName
SET PARTNER = 'TCP://xxx.xxx.xxx.xx3:7024'; --Replace IP
GO
/* -------- 9. SET PRIMARY'S (and thus the Mirror's) WITNESS TO THE WITNESS SERVER -------- */
ALTER DATABASE DBToMirrorName
SET WITNESS OFF
GO
ALTER DATABASE DBToMirrorName
SET WITNESS = 'TCP://xxx.xxx.xxx.xx4 :7024'; --Replace IP
GO
--Mike
-
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).
-
All true, but it still a nice option that fits between a single DB and Clustering. Just another tool.
-
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
-
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
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
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.
-
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.
-
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
Simon Liew
Microsoft Certified Master: SQL Server 2008 -
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.
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply