Comparing SQL Server Logins on Mirror Servers

I think it is great to pass on a hobby like fishing to the future generations.  I really enjoy taking my kids out fishing, and I’m glad they seem to enjoy it as much as they do.  Unfortunately, at the younger ages you often have to keep a close eye on them to make sure they aren’t going to hook themselves or cast into a tree.

SQL Server database mirroring also requires a rather watchful eye.  One problem that can occur is when a login is created on the principal server but not on the mirror.  Mirroring does not automatically move logins from the principal to the mirror servers, so monitoring is required to make sure the logins stay in sync.

This can be done with a linked server from one to another, but we may not always want to allow a linked server for security purposes.  Instead, I’ve created a Powershell script to compare the two servers and send an email if the mirror server is missing logins that the principal server has.  I use a Windows Scheduled Task to run this script on a regular basis.

$MirrorSQLLoginList = Invoke-SQLCmd -query "SELECT FROM master.sys.syslogins AS s INNER JOIN master.sys.server_principals AS sp ON sp.sid = s.sid WHERE sp.is_disabled = 0 ORDER BY;" -Server "MirrorServer" | select -Expand name
$PrincipalSQLLoginList = Invoke-SQLCmd -query "SELECT FROM master.sys.syslogins AS s INNER JOIN master.sys.server_principals AS sp ON sp.sid = s.sid WHERE sp.is_disabled = 0 ORDER BY;" -Server "PrincipalServer" | select -Expand name
$MissingLogins = $PrincipalSQLLoginList | Where {$MirrorSQLLoginList -NotContains $_}
if ($MissingLogins.count -gt 0)
    $AlertEmail = new-object Net.Mail.MailMessage
    $AlertEmail.IsBodyHTML = $true
    $AlertEmail.From = ""
    $AlertEmail.Subject = "Logins on the primary database server missing from the mirror"
    $AlertEmail.Body = ($MissingLogins -join "<br/>")
    $smpt = new-object Net.Mail.SMTPClient("","25")



Set Up Database Mirroring in SQL Server

I’ve been really excited by how enthusiastic my four year old son is to go fishing. There’s never a time when he isn’t in the mood to go catch some fish, although he still needs to be consistently catching fish or else he gets bored pretty quickly. Last Sunday we had talked about fishing on a small lake near our house, but as we were preparing to leave it started raining. It was a cool day and pretty windy, and I knew we’d get cold and wet fairly quickly so I asked him if he really wanted to go. Ever the fisherman, he assured me that he wanted to fish despite the poor weather. We ended up catching only a single fish, but it was his first crappie ever, so it was worth the trip.

Nate with Crappie

I got a tip on another good panfish lake near our home, so we’ll be trying that one in the next couple of weeks. Hopefully the weather will cooperate a little better so we can have a more enjoyable trip. The key catching enough fish to keep small children interested is practice. Panfish are usually quite plentiful, so you need only to practice to learn where the fish like to congregate. Then you can keep your children happy for a couple hours, and make some great memories at the same time.

Practice is also important as a DBA, particularily when attempting a large project. As I have noted in a few of my recent posts, we are going to be doing a server migration at work in the next couple months. We have a brand new production server and need to move SQL Server, including SSIS and SSRS, over to the new server. Downtime isn’t a huge concern for my current employer based on our system configuration and requirements, but I’m still going to try to minimize it as practice for the future when it will be important. I’ve decided the method for moving the databases over to the new server will involve setting up database mirroring from the new server to the old. This will allow the servers to be pretty much in sync when it’s time to cut over. First I need some practice.

Today I’m going to demonstrate the steps I take to set up database mirroring for our Test environment. We aren’t going to be ready to cut over our Production environment for a few months, but I want to make sure I’ve got the process down before attempting it. The first step to set up database mirroring is to take a full backup and log backup of the database. I’m going to start with our largest database which has nine data files and is roughly 54 GB in size. Since this is our test environment I first need to change the database into Full Recovery mode.


Next I take a full backup and a transaction log backup of the database. This is occuring on the current Test environment SQL Server, which I’ll call the Principal for the remainder of this blog post.

NAME = N'App_Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

NAME = N'App_Test-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

Now I’ll restore these backups on the new server, which I’ll call Mirror for the remainder of this blog post. I moved the backup files to a shared drive on a storage server so the Mirror server could access it. The one important option is to keep the database in NORECOVERY so that we can apply the additional transaction log backups and the database stays offline. A database that has been restored with NORECOVERY will show up in SSMS in the Restoring… status:

20150514 Set Up Database Mirroring NORECOVERY

USE [master];
RESTORE DATABASE [App_Test] FROM  DISK = N'\\server\SQLBackup\AppTestFull.bak' WITH  FILE = 1,  
MOVE N'cwidb_Data' TO N'D:\DataFiles\App_Test.mdf',  
MOVE N'AppTest2' TO N'D:\DataFiles\AppTest2.ndf',  
MOVE N'AppTest3' TO N'D:\DataFiles\AppTest3.ndf',  
MOVE N'AppTest4' TO N'D:\DataFiles\AppTest4.ndf',  
MOVE N'AppTest5' TO N'D:\DataFiles\AppTest5.ndf',  
MOVE N'AppTest6' TO N'D:\DataFiles\AppTest6.ndf',  
MOVE N'AppTest7' TO N'D:\DataFiles\AppTest7.ndf',  
MOVE N'AppTest8' TO N'D:\DataFiles\App_Test8.ndf',  
MOVE N'AppTest9' TO N'D:\DataFiles\App_Test9.ndf',  
MOVE N'cwidb_Log' TO N'E:\LogFiles\App_Test_1.ldf',  

RESTORE LOG [App_Test] FROM  DISK = N'\\Server\SQLBackup\AppTestLog.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10

Next go to the Mirroring tab of the database properties on the Principal server.  Click Configure Security…20150514 Set Up Database Mirroring Principal Database Properties

A wizard will start to guide you through the setup. First it will ask if I want to include a Witness Server. This server watches the principal so you can automatically failover to the mirror if the principal becomes unavailable. Since I am only using mirroring to prep the Mirror server to be the Production server, I’m not going to set up a Witness.

20150514 Set Up Database Mirroring Wizard No Witness

The next screen allows us to set up some basic information about the Principal Server. I will leave the defaults for this.

20150514 Set Up Database Mirroring Set up Principal

The next screen is for setting up the Mirror server. Again, I will leave the defaults for this.

20150514 Set Up Database Mirroring Mirror Database Setup

The next screen lets you specify accounts for the mirror process. Since the service accounts that run SQL on both these instances already have SQL logins with CONNECT permissions, I originally tried to leave these text boxes blank.  However, I got a connect error (at the end of the process) when I did this:

20150514 Set Up Database Mirroring Port Not Found

So I ended up filling in the boxes with the domain account running SQL:

20150514 Set Up Database Mirroring Wizard Service Accounts

Lastly I get a summary screen. Press Finish to run the Configuration.

20150514 Set Up Database Mirroring Wizard Summary

After pressing Finish, it took a very short time for the configuration to complete:

20150514 Set Up Database Mirroring Configuration Complete

Press Close and another message box will start up asking if I want to start mirroring. I do want to start mirroring, but first I need to change the Operating mode. It is currently set to synchronous, which is high safety without automatic failover. Since this is just to prep a server migration, I don’t want the mirror to be slowing down transactions on the principal server. I need to choose asynchronous, which means that transactions will occur on the principal server immediately, and then be transfered over to the mirror. I choose Do Not Start Mirroring:

20150514 Set Up Database Mirroring Start Mirroring

Next I go back to the properties and change the operating mode to asynchronous:

20150514 Set Up Database Mirroring Change Operating Mode

At this point I chose to start mirroring:

20150514 Set Up Database Mirroring Working

20150514 Set Up Database Mirroring Working2

That is how you set up mirroring.  Now when I am ready to migrate servers, I will have all the data already on the new server.