I need to sync logins.Just the logins? Sure just go find the best version of sp_help_revlogin. Oh wait you want the server roles, okay well you can steal some of this script here. Oh wait you want the server perms also and default databases? Okay then do a search for this script. Here you go. OH!! You are doing a migration from 2005 to 2014?
So maybe that’s not really how the conversation in your head goes every time. But you’ve probably experienced a chat like that before. Or maybe you want to sync agent jobs between servers. Or credentials. Or Linked Servers. Or Proxy Accounts. Or Operators. Or maybe you want to do a migration in a somewhat automated way.
I was introduced to a tool, thanks to the power of Twitter’s #SQLHelp hashtag ,which is still powerful after so many years, to an amazing tool. It’s a PowerShell library. It’s open source. It’s started from someone who knows what she’s doing and it’s been contributed to by a community of people who want to make it better and expand on it.
This experience inspired me to start a simple blog post series here. I’ve been away from blogging on this URL for too long. I want to write about a different Free SQL Server DBA tool each week. This week I’m starting with one that is going to be in the top of my list moving forward. And I expect to contribute to it also.
DBATools – DBA Peace and Tranquility
Well maybe that’s too lavish of praise? There are a lot of amazing scripts out there. I’ve written so many over the years in my own library of scripts. There are so many on the web. There is Robert Davis’ amazing Database Mirroring login sync script (which works great with Always on Availability Groups also). There are the Paul Randal Wait States library resources. There are the Blitz family of scripts from Brent. Etc. Etc. There is SP_Whoisactive which is still quite useful and helpful even today many years after the last update. Let’s face it we exist in a spoiled community.
Here’s the story. I use Robert Davis’ script or derivatives of it when necessary to sync logins between members in an Availability Group. So many amazing things about Availability Groups are out there. They have their frustrations and challenges, but I like them for HA/DR and I like them for offloading some reporting workloads. Well the other day I was going to grab the Mirror syncs script and couldn’t find it in my slightly messed up folder structure so I went to download it. It wasn’t there. Robert had changed hosts. So I lamented on the SQLHelp hashtag. Mike Fal (THANK YOU MIKE!) was waiting for me apparently, he said “try this” and sent me a link to this suite of tools and I started smiling.
DBATools.IO is the link he gave me. And I just gave you a link to the features page for the tool. That page alone should make you drool a little and want to check this out. I know. I know. Still, after all these years, some DBA types are still a little bit afraid of PowerShell. Seriously, though, go and look at it. Chrissy LeMaire (@CL on twitter, dbatools.io for this cool tool and some blog notes) is who you have to thank for the genesis of this tool. Please go find her (I love that short twitter handle!) and thank her. And check out this tool! Basically this is a PowerShell module that is growing with features all the time of many tasks that DBAs love to do and need to do. Check it out!
Look at this video – just for syncing logins (Please note. I didn’t ask Chrissy for permission to post this, but it is freely available on YouTube and I’m taking no credit for this tool). You should thank Chrissy for this tool, download it, contribute to it and be amazed by it. The video is just about migrating logins but you get the point.)
See? Wow. 🙂 Go spend some time and learn about the tool.
One Application I See Instantly
Syncing logins for Availability Groups – both new logins added on any of the nodes, and changes made to existing logins when a DB changes or a server role or permission changes. Probably for Agent jobs or other server wide settings. May need to make some tweaks. Maybe with some more time learning PowerShell, maybe this could become a module. An AG “sync it all” module that checks for existence, updates changes, inserts new, removes, etc. But the point is? It’s useful. It’s free. It’s amazing. And you should learn about it.
Thanks you for such a wonderful write-up, Mike! I’m happy that you’re enjoying the tool set. It’s a lot of fun to make, and not having that conversation (I’ve had it a bunch of times, too) each time I migrate has been such a relief.
Thanks again for the tool Chrissy. And thanks for the comment on the blog. Looking forward to contributing (don’t tell anyone on the internet, but I would consider PowerShell a weakness of mine. I hope to get to explore the module and use that as a learning tool 🙂 )