Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

A Look at Database Mirroring Expand / Collapse
Author
Message
Posted Tuesday, June 19, 2007 4:40 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 28, 2014 6:52 AM
Points: 214, Visits: 367
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jDave/3046.asp
Post #375123
Posted Tuesday, June 19, 2007 10:44 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, August 11, 2014 9:39 AM
Points: 3,461, Visits: 350
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
Post #375151
Posted Monday, July 16, 2007 11:12 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:57 AM
Points: 2,608, Visits: 1,545

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
Post #382195
Posted Tuesday, July 17, 2007 1:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 3, 2014 7:55 AM
Points: 17, 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

Post #382229
Posted Tuesday, July 17, 2007 1:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:57 AM
Points: 2,608, Visits: 1,545

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
Post #382234
Posted Tuesday, July 17, 2007 7:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 12:57 PM
Points: 33,206, Visits: 15,361
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
Post #382363
Posted Tuesday, July 17, 2007 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 3:07 PM
Points: 4, Visits: 78

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

Post #382536
Posted Tuesday, July 17, 2007 3:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, April 18, 2014 8:01 AM
Points: 258, Visits: 701

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).



Post #382556
Posted Tuesday, July 17, 2007 3:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 12, 2014 3:07 PM
Points: 4, Visits: 78

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

Post #382578
Posted Tuesday, July 17, 2007 4:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 2:06 PM
Points: 37, Visits: 176

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




Post #382591
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse