SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted
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.
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 StatusLet's start Database Mirroring Monitor to verify that database mirroring is working. Below we can see that the databases are synchronized.
Drop SQL Server LoginNow, 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.
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.
To learn more about database mirroring read these tips:
- Database Mirroring Automating Failover for Multiple SQL Server
- SQL Server Database Mirroring Tips
- Implementing Database Mirroring in SQL Server 2005 across domains
- SQL Server 2005 Database Mirroring Primer
- Monitoring SQL Server Database Mirroring with Email Alerts
- Adjusting the automatic failover time for SQL Mirroring
- SQL Server Database Mirroring Performance Monitoring
- ADO.NET Connection Strings with SQL Server 2005 Database Mirroring
About the author
View all my tips