GAPTHEGURU

Geek with special skills

SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted

SQL Server DBA Tips SQL Server Developer Tips SQL Server Business Intelligence Tips SQL Server Career Tips SQL Server Tip Categories SQL Server Tutorials SQL Server Webcasts SQL Server Whitepapers SQL Server Tools SQL Server Questions and Answers MSSQLTips Authors About MSSQLTips SQL Server User Groups MSSLQTips Giveaways MSSQLTips Advertising Options

Problem

Will SQL Server database mirroring stop working if the user used to create the endpoints is removed?  In this tip I 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 principle 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.

05/23/2012 - Posted by | SQL Scripting, Sql Server, T-SQL | ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: