SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Look at Database Mirroring


A Look at Database Mirroring

Author
Message
sqldba-294117
sqldba-294117
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1212 Visits: 396
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jDave/3046.asp
Sugesh Kumar
Sugesh Kumar
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10481 Visits: 358
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
SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4493 Visits: 1855

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
Magical Horn
Magical Horn
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 179

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


SimonLiew
SimonLiew
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4493 Visits: 1855

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
Steve Jones
Steve Jones
SSC Guru
SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)SSC Guru (183K reputation)

Group: Administrators
Points: 183328 Visits: 19498
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
michael merrill
michael merrill
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 79

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


cmille19
cmille19
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1645 Visits: 726

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

  1. 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
  2. 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
  3. DM only addresses a single database and non-SQL Server services will not failover. Examples include Schedulers, and backup agents
  4. 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
  5. DM uses source, mirror and witness. The witness will require an additional SQL Server license albeit a small per seat license
  6. Need to maintain logins manually on both servers since this is at the instance level and DM does not cover master, model or msdb
  7. DM does not handle failing over of SQL jobs, downloads, or external jobs
  8. Async uses single thread for DM
  9. Performance – higher log generation, network latency with Safety-full less tran/sec on source system
  10. Log growth issues until mirror is available even though tran has committed on principal
  11. If witness is lost no auto failover. Mirror loss tran log grows until available. Witness and Mirror loss everything stops
  12. Transactions can take twice as long to commit with full-safety
  13. 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).




michael merrill
michael merrill
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 79

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


JRoughgarden
JRoughgarden
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 195

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





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search