As I mentioned in my last post, I am going to be spending some time going over a couple of my favorite tools from the dbatools PowerShell module.

The one cmdlet that actually led me to the module in the first place, which has been a tried and true tool for me, is the Copy-SQLlogin. I am a relatively new to the work of a DBA, but one of the most difficult lessons I learned early on is that if you have a SQL Always On Availability Group (AG) setup the you have to maintain a copy of the Logins on all replicas where you need to potentially access the data from in a read capacity. From what I have read, to keep these groups in sync you would need to either grab the Login, “Create to Script” on the other replica or use partial containment to get around this problem.

This cmdlet however, offers a method similar to the first one, but with the options of syncing all of the Logins or only select Logins

My preferred method of using this command would be in the following syntax

Copy-SQLLogin -Source SQL2012 -Destination SQL2016 -Logins 'Domain\ServiceAccount','LocalSQLLogin'

This provides you the flexibility to copy the Logins for the databases that you may be moving. After doing this from the original source I would then switch the source and destination for the primary and any secondary replica in the cluster. One caviat that I found to this is that if you have another AG on the secondary that is not a readable secondary or is read-intent only this cmdlet may fail or provide a warning that not all the databases were available.

Another robust version of this cmdlet is just using it with the SyncOnly

Copy-SQLLogin -Source SQL2012 -Destination SQL2016 -synconly

This does not add or drop the user, but merely syncs up the objects securables. For Instances that are 2012 and newer this cmdlet can be used to move the SIDS, passwords, and other items. It makes it a powerful addition to any DBA’s tool belt.