Previously on Tweak your SQL Server Management Studio…
Stop the Popups! I covered how to prevent the connection popup from appearing when you opened SSMS. In this post I want to show you why I decided to stop those popups in the first place.
What are registered servers? From BOL
Registering a server in SQL Server Management Studio allows you to store the server connection information for future connections.There are three ways to register a server in SQL Server Management Studio.
So I can use registered servers to connect to an instance, big deal right? You can do that with the connection popup… But with registered servers you can do more! First let’s set some up.
First let’s display the registered servers interface.
View -> Registered Servers
Expanding the Database Engine group there are two default entries, Local Server Groups & Central Management Servers. We can’t add anything to the Database Engine group, so under Local Server Groups we’ll right click and there are a few options.
Object Explorer – This will open EVERY INSTANCE under this folder in object explorer
Evaluate Policies – Perform On-Demand evaluation of best practices policies against one or more registered servers.
Import Policies – I’m assuming this is relating to the policies that were evaluated in the step above, but I’m still looking for documentation on this!
New Server Group – A logical grouping of servers, whatever your logic is!
New Server Registration – Creates a new server connection. Described in detail below.
Tasks – Other tasks, like exporting and importing settings, among others.
I’ll create three groups for this demo.
1) Invincibles (Henry, Bergkamp, Pires)
2) Production (Henry, Bergkamp)
3) Dev (Pires)
Here are the three groups under the Local Server Groups tree.
Here you can save your connection settings that we mentioned earlier. I’m going to repeat this for the other two instances. Optionally you can alter the “Registered Server Name” to make it more helpful.
I could repeat the process, but instead I’ll use the Export / Import Process. This is a fantastic way to migrate these to any desktop you work on. Thumb drive, shared folders, dropbox will all work.
Navigate to the folder in which you want this saved, give it a meaningful name, and click SAVE.
To import I simply right-clicked on the new directory in which I wanted the server, Development, and Imported the file.
Further I could I export the entire directory, and import it to any SSMS environment I’m working in.
So now after I open SSMS without a connection popup I’ll have this window open by default, assuming I leave it open when I close SSMS… A quick double click on each instance will automatically open it in object explorer, or I can right-click it and select new query and a new query window will open without the instance having to be open in the object explorer.
We can go further into this, if you right click on the group you can select Object Explorer and everything under that group will auto open in the tree view.
Or select New Query and you can query multiple instances at once! So I’ve right-clicked on the Invincibles directory and selected new query. Invincibles has 3 instances in it, and as you can see here I’m connected to the master database in 3 of 3 instances.
If there was a problem with one of them then I’d connect to 2/3.
Now that I have 3 new instances set up I can install the AdventureWorks2012 database to all three servers. Your script will vary, but running this script:
Hit F5 and voila!
Now I have 3 new AdventureWorks2012 databases restored on 3 new instances.
Tune in next week and for another SSMS Tip!