Saturday, November 5, 2011

64 Database Mirroring

Database must be  in FULL Recovery mode and can not have the file stream file group.

Database mirroring is the SQL Server Enterprise Edition feature. The Principal and Mirror database must use Enterprise Edition. The Witness server can be Express.

The default port for the SQL Server mirroring endpoint is 5022 but it can be changed to any unused port.

A mirroring session that includes a witness

Below example is setting up the database mirror on the high safety with  automatic failover or called Synchronous Database Mirroring (High-Safety Mode)

  • SG1 is the principal SQL Server host.
  • SG21 is the Mirror SQL Server host.
  • SG13 is the Witness SQL Server host.

Restore Database

Restore Database from principal to Mirror database with non recovery and restore Log from Principal to Mirror database with non recovery

  1. RESTORE DATABASE [AdventureWorks] from DISK = '\\SG11\C$\DUMP\AdventureWorks_20111105_3.db' with replace,norecovery
  2. RESTORE LOG [AdventureWorks] from DISK = '\\SG11\C$\DUMP\AdventureWorks_20111105_3.log' with norecovery

Create the Endpoint on Principal, Mirror and witness server

You will need to change the port number if they are on the same server. However, it is recommend you put in the different server ( for High availability of course Smile )  

Create end point on both Principal and Mirror server

  2.     STATE = STARTED
  3.     AS TCP ( LISTENER_PORT = 7051 )
  5. GO

Create the end point on the witness server

  2.     STATE = STARTED
  3.     AS TCP ( LISTENER_PORT = 7051 )
  5. GO


Setup the Mirroring database

Run below on the Mirroring database server

  1. ALTER DATABASE AdventureWorks
  2.    SET PARTNER = 'TCP://SG11:7051'

Setup the principal and Witness server

Run below on the principal server.

  1. ALTER DATABASE AdventureWorks
  2.    SET PARTNER = 'TCP://SG21:7051'
  3. ALTER DATABASE AdventureWorks
  4.    SET WITNESS = 'TCP://SG13:7051'

The SQL Agent job will be created on the mirroring database server, we can use  msdb.dbo.sp_help_job to review the job detail.

Use below tables to view the Mirror database and endpoint configuration. This can be run on either Principal, mirror and witness server.

  1. use master;
  2. SELECT name, port FROM sys.tcp_endpoints;
  3. select * from sys.database_mirroring_endpoints;
  4. SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints;
  5. select * from sys.database_mirroring_witnesses;
  6. select * from sys.database_mirroring;


Access data on the mirroring database

Since the mirroring database is under the recovery and mirroring state. We can not directly access the data. As alternative, we can create the snapshot and access the snapshot data.

  1. CREATE DATABASE [S_AdventureWorks_20111105]
  2. ON (NAME='AdventureWorks_data' ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSG21\MSSQL\DATA\')
  3. AS SNAPSHOT of  [AdventureWorks]

Manual failover

In the Synchronous Database Mirroring (High-Safety Mode), the failover will automatically happen in the event the principal database is not available. If you set the mirror mode as high performance mode, this will require the manually failover and use  FORCE_SERVICE_ALLOW_DATA_LOSS

To manually failover in the High-Safety mode, we can run below on the principal server.

  1. Alter database [AdventureWorks]set partner failover

Once it is done, when we select from sys.database_mirroring, we can see the original principal database will become “Mirror” and the old mirror will become ‘Principal’.

Remove the witness

Anytime during the mirroring, we can disable the witness by running below command.


Remove the database mirroring

  • Remove the mirroring database session

Run on any of the partner server

  • Recover the mirroring database (Optional)

If any snapshots were created, the needs to be drop before we can recover the database.

  • Remove the mirroring monitor agent job (Optional)
  1. msdb.dbo.sp_delete_job @job_name='Database Mirroring Monitor Job'
  • Remove the end point (optional)
  1. DROP ENDPOINT mirror

Database Mirroring Monitor

We can use this to monitor the database mirroring status.



A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.


Database Mirroring Overview


  1. In this competitive world embedded systems are very important for our daily life, feeding from electronic devices, medical equipments, automobiles, telecom instruments, etc. Most of the people are using embedded systems and it was rising day by day. It has been changing our life.
    Embedded Training in Chennai | Embedded system Training in Chennai AWS Training in Chennai | AWS course in Chennai

    1. IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes. IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble.Final Year Projects for CSE

      Spring Framework has already made serious inroads as an integrated technology stack for building user-facing applications. Spring Framework Corporate TRaining .

      Specifically, Spring Framework provides various tasks are geared around preparing data for further analysis and visualization. Spring Training in Chennai

      The Angular Training covers a wide range of topics including Angular Directives, Angular Services, and Angular programmability.Angular Training

  2. Pretty article! I found some useful information in your blog, it was awesome to read, thanks for sharing this great content to my vision, keep sharing.
    AWS Training | AWS Training in Chennai

  3. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android training in chennai
    Ios training in chennai

  4. Needed to compose you a very little word to thank you yet again regarding the nice suggestions you’ve contributed here.selenium training in bangalore

  5. I am really enjoying reading your well written articles.
    It looks like you spend a lot of effort and time on your blog.
    I have bookmarked it and I am looking forward to reading new articles. Keep up the good work..
    hadoop institute in bangalore
    best institute for bigdata in bangalore
    big data coaching in bangalore
    Core Java Training in Bangalore
    Best Java Training in Bangalore
    Advanced Java Training in Bangalore

  6. Thanks for the great post on your blog, it really gives me an insight on this topic.I must thank you for this informative ideas. I hope you will post again soon.
    AWS Training Institutes in Vadapalani
    AWS Training in Thirumangalam
    AWS Course in Bangalore
    Aws Certification in Bangalore

  7. Really very happy to say, your post is very interesting to read. I never stop myself to say something about it. You’re doing a great job. Keep it up…

    Learn Best Cognos Training in Bangalore from Experts. Softgen Infotech offers the Best Cognos Training in Bangalore.100% Placement Assistance, Live Classroom Sessions, Only Technical Profiles, 24x7 Lab Infrastructure Support.

  8. Right here is the right site for everyone who really wants to understand this topic. You know a whole lot its almost tough to argue with you (not that I really will need to…HaHa). You certainly put a brand new spin on a subject which has tech been written about for a long time. Wonderful stuff, just excellent!

  9. I admire this article for the well-researched content and excellent wording. I got so involved in this material that I couldn’t stop reading. I am impressed with your work and skill. Thank you so much. good luck
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

  10. It has been simply incredibly generous with you to provide openly what exactly many individuals wouldíve marketed for an eBook to end up making some cash for their end, primarily given that you could have tried it in the event you wanted good
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

  11. It is a useful sharing...Thank you very much for sharing this awesome post. oracle training in chennai

  12. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your site
    AB INITIO training in bangalore
    best AB INITIO training institutes in bangalore

  13. Very informative blog! I am glad that I came across your article. I'm learning a lot from here. Keep us updated by sharing more such blogs.
    AWS Course in Chennai
    AWS Online Course
    AWS Course in Coimbatore

  14. Grab the extraordinary Oracle Course with PLSQL from Infycle Technologies, the best software training institute in Chennai. Infycle offers the Best Oracle PLSQL Training in Chennai, with various IT demanding courses such as Big Data, Python, DevOps, Selenium, Full-Stack development, etc., in complete hands-on practical training with professional tutors in the field. In addition to that, the mock interviews will be done for the candidates so that they can face the interviews with total confidence. To have all these within your hands, call 7502633633 for having a free demo.Best Oracle PLSQL Training in Chennai | Infycle Technologies \

  15. Learn Amazon Web Services for excellent job opportunities from Infycle Technologies, the Excellent AWS Training in Chennai. Infycle Technologies gives the most trustworthy AWS course in Chennai, with full hands-on practical training from professional trainers in the field. Along with that, the placement interviews will be arranged for the candidates, so that, they can meet the job interviews without missing them. To transform your career to the next level, call 7502633633 to Infycle Technologies and grab a free demo to know more



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates