Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted


By:   |   Read Comments (1)   |   Related Tips: More > Database Mirroring

Problem

Will SQL Server database mirroring stop working if the user used to create the endpoints is removed?  In this tip we will walk through the steps to find out.

Solution

Before I am able to provide with a definitive answer to the above problem, I created the following test environment.

  • A Windows 2008 VM (Virtual Server) named SQLTEST1 with SQL 2008 Developer installed
  • A Windows 2008 VM (Virtual Server) named SQLTEST2 with SQL 2008 Developer installed
  • Since these servers are not in a domain I created a Windows user WinUser on both VMs and used this account to setup mirroring.
  • I created a test database called mytest and mirrored it between the two SQL Servers SQLTEST1 and SQLTEST2.

Check Mirroring Status

Let's start Database Mirroring Monitor to verify that database mirroring is working. Below we can see that the databases are synchronized.

use database mirroring monitor to check status

Drop SQL Server Login

Now, let's try to drop SQLTEST1\WinUser login by executing the following. As expected, the SQL login cannot be dropped because it owns the Mirroring endpoint. Since this failed I did not bother to try this on SQLTEST2.

tsql code to drop sql server login

Drop Windows User

Now let's try to drop the Windows user WinUser on both servers.  This was successful on both servers.

steps to delete a windows login

Check Mirroring Status

Now that WinUser has been deleted on both servers, let's check if database mirroring is still working.  With a bit of surprise, database Mirroring is still working. To further verify it, I created tables and inserted data on the mytest database on the principal server SQLTEST1 and verified that such information was successfully replicated to the mirroring server SQLTEST2.

use database mirroring monitor to check status

Restart SQL Server

Now, let's restart the SQL Server service on both servers.

restart sql server services

Check Mirroring Status

If we check the mirroring status again we can see that it has failed.

use database mirroring monitor to check status

Conclusion

In order to avoid a possible mirroring outage it is appropriate to verify that a Windows user does not own mirroring endpoints, because if the user is removed from Active Directory or a local group, mirroring will break the next time the server is restarted.  If you do use a Windows user you need to make sure that your accounts are well documented and not accidently deleted.

Next Steps

To learn more about database mirroring read these tips:



Last Update:






About the author
MSSQLTips author Matteo Lorini Matteo Lorini is a DBA and has been working in IT since 1993. He specializes in SQL Server and also has knowledge of MySQL.

View all my tips
Related Resources





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, May 22, 2012 - 9:09:57 AM - Henk Back To Top

Mirroring breaking? 7 years of bad luck!

:-)

 


Learn more about SQL Server tools