Let’s face it, most people don’t have just one SQL Server
How many they tell Microsoft they have is another matter, but let the record show that I don’t condone licensing dishonesty. But going one step further, most places… Well, they’re ‘lucky’ if they have one DBA, never mind a team.
Everyone else: Give me your network people, your sysadmin, your huddled SAN group yearning to breathe free, the wretched refuse of your teeming developers.
Doing things on one server is aggravating enough. Doing things on a bunch of servers is even worse. Given some of today’s HA/DR features (I’m looking at you, Availability Groups, with your lack of a mechanism to sync anything outside of user databases. Rude.) people are more and more likely to have lots of SQL Servers that they need to tend to.
Sometimes just keeping track of them is impossible. If you’re one guy with 20 servers, have fun scrolling through the connection list in SSMS trying to remember which one is which. Because people name things well, right? Here’s SQLVM27\Instance1, SQLVM27\Instance2, SQLVM27\Instance3, and that old legacy accounting database is around here somewhere.
Register it and forget it
But don’t actually forget it. If you forget it and it goes offline, people will look at you funny. Turns out people don’t like offline servers much.
So what’s someone to do with all these servers? Register them! Hidden deep in the View menu of SSMS is the Registered Servers window
It will look pretty barren at first, just an empty folder. But you’ll fill it up quick, I’m sure. Can never have enough servers around, you know.
It’s pretty easy to populate, you can right click on the Local Server Group folder, or on servers you’re connected to in Object Explorer.
Either way, you get the same dialog box to add a server in. You can give it a friendly name if you want! Maybe WIN03-SQL05\Misc doesn’t tell a good story.
And if you hip and hop over to the Connection Properties tab, you can set all sorts of nifty stuff up. The biggest one for me was to give different types of servers different colored tabs that the bottom of SSMS is highlighted with. It’s the one you’re probably looking at now that’s a putrid yellow-ish color and tells you you’re connected and that your query has been executing for three hours. Reassuring. Anyway, I’d use this to differentiate dev from prod servers. Just make sure to choose light colors, because the black text doesn’t show up on dark colors too well.
Another piece of advice here is not to mix servers on different major (and sometimes minor) versions. The reason is that this feature gives you the ability to query multiple servers at once. If you’re looking at DMVs, they can have different columns in them, and you’ll just get an error. Even a simple query to sys.databases will throw you a bonk between 2012 and 2014.
Even if you’re running 2008R2, there are some pretty big differences in DMVs between SP1 and SP3. Microsoft has been known to change stuff in CUs (I’m looking at you, Extended Events).
On the plus side, you can use your multi-server connection to SELECT @@VERSION to help you decide how you should group them. If they have something better in common, like participating in Log Shipping, Mirroring, an AG, etc., all the better.
But my favorite thing, because I was a devotee to the Blitz line of stored procedures even before I got paid to like them, was that I could install them on ALL OF MY SERVERS AT ONCE! This was especially useful when updates came out. You know what it’s like to put a stored proc on 20 servers one at a time? Geeeeeet outta here!
Check that out. It’s on both of my servers. At once. That means simultaneously, FYI. If you have a DBA or Admin database that you keep on all your servers to hold your fancy pants scripts and tools, this is an awesome way to make sure they all have the latest and greatest.
You’re already better at your job
Even though this feature came out in 2008, I hardly see anyone using it. I found it really helpful comparing indexes and query plans across app servers that held different client data across them. It also exposes far less than Linked Servers; you need to worry less about access and level of privilege.
Just don’t forget to export your list if you change laptops!
Thanks for reading!
41 Comments. Leave new
I found out about this about a year ago and couldn’t go without it now. Seriously, the 10 minutes to set it up was worth every second.
What a sad, sad life I lived before that…
Also, you can export/import which is awesome for new hires or to move to the oh-so-fancy SSMS 2016 previews.
Hi Kris,
You might want to check out the ‘Central Management Server’ option, instead of the ‘Local Server Groups’, by using the ‘Central Management Server’ feature the server list is located on a SQL server you pick (for me i pick a centralized manage server who is also Unity Control Point), that way you don’t need to do the export and import for any new joiner.
You might also want to check out the Unity Control Point too, but it requires Enterprise edition.
Cheers
it is also good to know that you can manage/evaluate policies (policy-based management PBM) against a group of registered servers!
You can evaluate PBM against multiple servers but it must be interactive .. you can schedule policies to be evaluated but to do this you have to import the policy onto each server
…..
but if you want to evaluate a whole bunch of policies against multiple servers then powershells’ Get Child-Item (gci) and Invoke-PolicyEvaluation is the way to go … I’m just learning about this and planning to run scheduled daily checks to see if anybody has been fiddling with server settings.
I’m glad somebody mentioned CMS .. I was thinking that as I read.
It’s always nice to share.
Very good tip! I’ll definitely give it a shot.
I have 163 servers that I have registered, and only one dba…. (me). It’s the only way I can survive!
I know that feeling. Hope you also have a good monitoring tool.
163 servers?! my.god.
http://i.imgur.com/dJUqjMv.gif
This is a great tip, but why now take it one step further and register one of your instances as a Central Management Server? This provides the same benefits, and you can also have other team members connect to it so everyone shares the same list of instances. That way everyone is up to date! I manage about 80 instances like this.
I would be lost without this feature. I am the primary dba for 50ish instances, and secondary for another 50 or so, and have another 100 or so that I can be called on to back fill support for.
Im sure a lot of people will frown on it, but I also save passwords in the server registration, because it greatly simplifies things. Depending on the server/domain, I might use my domain account to connect, or I might have to use a sql account, so no having to go looking for passwords saves me a bunch of time.
I find using a CMS is ideal if you work with various DBA’s and/or programming teams – same concept pretty much. I send out periodic reminders to ensure they all use it so everyone can see the same listing of servers and don’t have to memorize, add/remove, etc. I also change the label of the servers and include a suffix to the name (SP for SharePoint, AX for Dynamics AX, etc.) – that way they know the general purpose of the instance. Organizing by “DEV ===============” and “PROD ===============”, then sub-folders helps even me keep things straight!
I also use the CMS as a dynamic data source for my SSRS reports so one report can cover all my instances rather than a report for each individual instance.
If you take this one step further, setup a Central Management Server (CMS) and register all your servers to it. Then all you need to do is to connect to the CMS to see all your servers. this is also helpful for a new coworker to quickly get a list of all servers. There are other advantages to CMS too.
One other thing that’s really handy is combining this with SsmsBoost. It adds a “Set as active connection” to the right-click menu for each server registration. I never use the regular connect dialog anymore. (They have yet to add a way to do that at the folder/group level.) I would also appreciate a “Add to current connection(s)” item on that same context menu. That way I could “build” a multi-connection window.
Ancillary pet peeve: SSMS calls them “servers”, but what you’re actually connecting to is a dbcat (database catalog) on an instance.
Oooo! And while I’m on the subject of whining, could someone make the connection process a *non-blocking* thing? It’s really frustrating to open a 20+ connection window and wait while SSMS’s UI-thread completely hangs.
I’ve been using this for years, I split my servers into production(50) and non production(45). I add version and edition to the name like ‘Server1 – 2008R2 Ent’ or Server2 – 2012 Dev’ way easier than trying to remember all the possible combinations. If I get a request from management for all the logins in production BAM! 1 query against all 50 servers at once and results in less than a minute. Morning checks are a script that I run against production and another one against non-production. Cannot imaging doing this 1 server at a time.
I use folders (called Server Groups) in the Registered server view and group servers by prod, dev, qa, then by SQL Server version.
Another useful tip, which I think I got from Kendra, is to check the Use Custom Color check box when registering a server so the status bar at the bottom of the window is a different color for different server types – I use red for prod, yellow for QA, and green for dev.
Also, to prevent inadvertent commands running on multiple servers, I change the status bar to a different color when connected to multiple servers – I like to use hot pink. Do this in SSMS Tools–> Options –> Text Editor –> Editor Tab and Status Bar and set the color for “Group Connections”
i’ll do you one better: this same trick can be used for poor man’s parallelization, when transforming a bunch of data on a *single* server is your goal. in addition to the setup you describe, i have a folder called “parallel” in which i have a folder for each server. in the server-level folder, i have sub-folders for however many threads i want, each of which connects to the server named in the folder.
ex:
parallel (folder)
—SQL001 (folder)
—–Thread001 (folder)
———SQL001 (registered server)
—–Thread002 (folder)
———SQL001 (registered server)
—–Thread003 (folder)
———SQL001 (registered server)
—–Thread004 (folder)
———SQL001 (registered server)
if you right-click on the SQL001 folder you know have 4 query windows pointed at the same server. if you write your query just right, each connection becomes a little “worker” that can split up lots of work you may need to do to every database on a server.
a bit ghetto, perhaps, but effective and less annoying than sql agent jobs and way easier than service broker. n.b. service broker is like my powershell, which (powershell) you slackers should definitely all stop whining about and learn to love already. =P
Interesting. What kind of stuff do you find is helpful to break up over multiple sessions like that?
Also, LA LA LA LA I CAN’T HEAR YOU
It’s a pity that SQL Server doesn’t do it’s own parallelisation ……
What do you mean it does? Since when?!
ha! well, it’s great when you have lots of similarly-schematized (but dissimilarly sized) databases on the same server and you need to perform the same potentiallly long-running operation in all of them, you don’t want the long-runners getting in the way of the small guys:
i.e. install a sproc, execute same to run initial population of large data set, build index post execution to support continued execution, blah blah
such a script would have to be able to be written against a central “queue” table of some kind (and thus requires a bit of extra overhead), and in theory you could just open up four windows and copy paste it, but having one set of output to watch (assuming you’ve written some progress reporting into the deal) when you need to keep something of an eye on progress to ensure you can intervene here or there when needed is gold.
if you have two or three or four such servers in your parallel folder, being able to kick off simultaneous parallel executions on all the servers with a single window (instead of 8 or 12 or 16 windows) quickly becomes worth it. plus, you can always up the ante if you find that you can handle more threads by just copy pasting your script into a new direct-connected window.
DEVILISH
Erik has opened a wonderful can of worms here (for those that enjoy fishing, worms are in fact wonderful).
I have a dedicated UTILITY instance for all of this stuff so you don’t have to rely on one of your business instances to be the CMS, PBM, MSX, etc.
This is what I’ve done:
http://allen-mcguire.blogspot.com/2014/11/work-smarter-not-harder-utility-instance.html
Great topic Erik!
Thanks, Allen. I didn’t expect it to be popular at all. That Utility instance is pretty cool, though I hope I never need one again 🙂
Hot tip for when you realize you can’t register your CMS on the CMS and so every time you want to run a query on all instances you have to also run it separately on the instance you are using as a CMS:
If you alias the instance you are using as a CMS you can register the alias to the CMS.
Also, as a side note, I wish you could tag instances so instead of having to put together lots of different groups so you could say ‘run this query on all instances that have the tags: 2014, QA and Availability Group’
I actually register my CMS via IP address – you can do it that way as well. Then just label it so the IP isn’t shown.
Allen – that’s one of my favorite tricks too!
Good point! We alias all of our instances so it never crossed my mind.
I just thought of a horrific April Fools joke where you relabel all the instances in the CMS and everyone runs queries on the wrong servers and loses their jobs… and now I’m going to go obsessively check that everything is labelled correctly
Great post! Articles like this are why I read this blog. I can’t believe I hadn’t known this until now…
HOW DID I NOT KNOW ABOUT THIS!?!? Thanks so much for the post!
Thanks for that nice article.
I am using this procedure for couple of months now. It’s the only way to execute scripts against multiple servers without a lot of overhead. Really, to manage a bunch of servers, I can’t live without it anymore, especially for answering requests like ‘I need a report xyz across all our production instances…!”. Sad, if you don’t know this method 🙂
But since I am using it, there is always that ugly “CENTRAL MANAGEMENT SERVERS” icon sitting right in front of me on my screen, each and every day… laughing at me! It causes that feeling … like…. I might be doing SOMETHING right with my registered local server group… but maybe there even is something more professional in place I just don’t know yet… After quickly jumping into some articles about CMS, I unfortunately still did not get the idea… maybe that were the wrong articles of poeple not able to write straightforward aticles…
Then, some day I realized it also somehow could have to do with management of Job Agents and jobs… maybe policies…. a job that still drives me crazy…
Now I see “CMS” in a lot of comments here of people who seem to know.
So…. MAYBE you could add another article like this straightforward one here about CMS, why this icon has to be where it is, why it laughs at me…. and what it is useful for – that would be amazing and possibly that feeling in my stomach could finally go away 🙂
A CMS is the same concept, but as the DBA I’m able to share the CMS instance with developers so they can see exactly what I’ve registered and what’s in our organization. If I add, remove or modify an instance – they don’t have to do anything – they’ll see what I’ve configured. If you use Registered Servers, only you see them – other’s cannot see what you personally have registered.
To set one up. there are a lot of posts online that will walk you through that. I control access to my CMS via an AD group – the instance I use as my CMS is not for general use by developers so I just assign the permissions they require to connect to it as the CMS.
https://www.simple-talk.com/sql/sql-tools/registered-servers-and–central-management-server-stores/
One thing I run into when adding Target Servers – you may have to tweak the registry value for MsxEncryptChannelOptions to 0 on the Targets. At least that’s what I always end up doing – it’s not the default.
Sorry, I mentioned how to created an SSRS report that can pull data (sp_BlitzCache and many of my other favorite Ozar tools) from all your instances via a dynamic data source pull-down/parameter. Perhaps someone will find this useful – it’s a neat little trick, but it pulls the info from the CMS instance so it’s always got a current list of your instances.
http://allen-mcguire.blogspot.com/2014/10/consolidate-your-reports-with-dynamic.html
Great tip that is not as widely know as it should be (just like that splitter button 🙂 )
One of my most awesome uses of a server group was I had to refresh the same database across our various dev/test/uat/staging environments.
Thought I’d have issues at the file system level, but 11 different servers restoring from a single 20GB backup file all at once ran in around the same time as a single server restore.
Thanks, Erik!
I’m currently juggling 13 servers over 4 different versions.
This is going to make my life so much easier!
Hi Erik
Nice to see that so many people are using Registered Server and some even Central Management Server. At the old shop I worked at we had 150 SQL Servers all nicely tucked away in a structure based on how we’d modify groups of SQL Servers.
1st level: DMZ / LAN
2nd level: 2005 / 2008 / 2012 / 2014
3rd level: PROD / TEST / DEV
I guess it depends what your requirements are.
At he new place I’m working at I just started to implement it, but I am facing some issues with 600+ databases on 80 instances on 53 servers, but separated by multiple firewalls and in multiple domains.
This is when I noticed slight differences between Registered Servers and Central Management Server.
– Registered Servers allows you to register the SQL server with a SQL Server Login (sa?), but CMS will only have Windows Authentication.
– CMS can be stored on a central SQL Server, and Registered Servers is only on your machine (well, until you export it…).
It’s a fantastic feature, but you have to choose wisely.
Thanks again for the article.
Unfortunately custom color settings are not saved if you are using CMS
CMS is an awesome idea !
For servers on the same windows domain 🙂
No cross domain, no SQL logins, shame.
Cross-domain access would depend on child/parent domain and also the domain trust settings .. so cross-domain may be possible 😉
Did anyone by chance ever stumble about a behaviour like this?
If you create a new server registration group and add more than 1 server, then add an Error 18456 Invalid Login to each of these (or run profiler to catch those events) and then open a new multi-server query window – you can succesfully login in both (all) servers and run queries – but at the same time get an invalid login alert from loggin into master database of the FIRST server on top of the list in the group. I tested around this a lot. And it looks like it does not matter at all if the used login is sysadmin or not, has a mapping to master or not…
If anyone knows more about this, please let me know in here:
http://dba.stackexchange.com/questions/138011/sql-server-management-studio-creates-18456-login-error-using-registered-server-g
No, I’d try to reproduce it on a different machine.