SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted

By: Matteo Lorini | Updated: 2012-05-22 | Comments (1) | Related: > 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.

Drop Windows User

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

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.

Restart SQL Server

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

Check Mirroring Status

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

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:

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2012-05-22

Comments For This Article

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

Mirroring breaking? 7 years of bad luck!

:-)