Friday, March 14, 2008

Registered Servers

Registered servers make a great shortcut if you manage more than a few SQL Server instances. I’ve got a quick little tip for you…

First - thanks for stopping by!

Here’s another tool for you...

In SQL Server Management Studio (SSMS) you’ll find several embedded tools. I talked about templates the other day, and today I’m reviewing the Registered Server pane. Ctrl+Alt+G (View – Registered Servers in the menu) will bring this pane into view, and you can dock it as you like. You can set it to auto-hide to save space as well.

Right-click in the pane and one of the options is New, which has two choices:

· Server Group… allows you to create a folder for like servers (more in a minute)

· Server Registration… brings up a modified connection dialog which allows you to specify the server, choose the name, and add descriptive text (this is displayed when you mouse over the server registration – I find it helpful to include some database names here.)

Some highlights:

1 – Types: Depending on the services you have installed, you can register various types – Database Engine, Analysis Services, Reporting Services, SQL Server Compact Edition, and so forth.

2 – Groups: You can group servers with a folder structure, similar to templates.

3 – Connections: Once registered, connecting with the Object Explorer or a new query window is quick and easy.

4 – Portability: Other right-click options allow you to export or import the server list to an XML file (more on this in a minute).

Server Groups:

Now that my company’s got a foot print on four continents (we recently added Europe and Africa), I have several more instances to manage. Most locations have at least two servers with different purposes, and then there are development and QA servers, and other miscellaneous servers (think back office). My registered servers are grouped into Production, Reporting, Development, QA, and Other. I haven’t needed to sub-group yet, but these four simple groups have helped a lot!


The ability to export and import server registrations is a nice touch – kudos, Redmond! Right-click, choose export, and specify a file name and all your registrations are stored in an XML file. The extension is regsrvr, but a simple text editor allows you to edit at need. You can also choose to export only a group, or even a single server.

I set up my environment on one machine, exported the entire list (allowing it to store passwords – relax, they’re encrypted!). Then, I copied the file to my other machine and imported. Voila! Now all my servers are at my fingertips whichever keyboard I use. After this, I destroyed the file, exported again without passwords and put a copy in source control. This way I can hand that to other a new DBA one day and simplify that initial setup process.

If you have a new developer starting, and he needs access to only a few instances, simply export only the Development group (or single server) needed. Pass the file to the developer and she’s all set!

And here at willsql4food HQ, I can have personal stuff in other groups for research, home use, and any consulting work I might do.

The more servers you manage, the more you’ll come to appreciate this simple, yet powerful tool in SSMS. Hope this helped you!

Thanks again for visiting,


No comments: