Tweak your SQL Server Management Studio! Tip #2

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.

Registered Servers!

SSMS Tips - Registered Servers 001

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

SSMS Tips - Registered Servers 002

When the interface opens you’ll see this, by default you can see database engines.  But you can also save analysis, reporting, and integration services instances. SSMS Tips - Registered Servers 003

 

 

 

 

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.

SSMS Tips - Registered Servers 004New Query – This will open a query window that connects to EVERY INSTANCE under this folder.

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.

 

Typically I like to categorize my instances like, by data center, by production / Dev / QA / etc, application, all of the above…  So I begin by creating New Server GroupsSSMS Tips - Registered Servers 005

I’ll create three groups for this demo.

1) Invincibles (Henry, Bergkamp, Pires)

2) Production (Henry, Bergkamp)

3) Dev (Pires)

 

SSMS Tips - Registered Servers 006

Here are the three groups under the Local Server Groups tree.

 

 

 

 

Next we’ll add the servers to each group.  First I will add all three instances to the Invincibles directory.  I’ll right-click the directory and choose New Server Registration.
SSMS Tips - Registered Servers 007a SSMS Tips - Registered Servers 007b

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.SSMS Tips - Registered Servers 008

 

 

 

 

 

SSMS Tips - Registered Servers 011I 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.

Right click on the group or instance you want to move and mouse over the TASKS menu.  Choose to export the settings.  

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.

SSMS Tips - Registered Servers 009So 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.

SSMS Tips - Registered Servers 010

 

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:

SSMS Tips - Registered Servers 012

Hit F5 and voila!

SSMS Tips - Registered Servers 014

 

SSMS Tips - Registered Servers 013

Now I have 3 new AdventureWorks2012 databases restored on 3 new instances.

 

Tune in next week and for another SSMS Tip!

Leave a Reply

Your email address will not be published. Required fields are marked *