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,


Sunday, March 2, 2008


Templates are a great addition to any DBA’s toolkit.  I’ll walk you through an overview and even throw in some free code – you’ll be more productive by Friday even if today is Friday.


First, thanks for stopping by!


Repetition again?  Ugh…

I find that as a DBA, there are several things that are just repetitious.  I’ve found a few tricks and tools to speed up my work and remove some of the drudgery.  Today, I’ll talk about templates, and cover some of the others soon.


Templates aren’t new in SQL Server 2005 – they’re also available in 2000, and I won’t pretend to know or care about SQL Server 7.0 and previous versions – that’s someone else’s blog!  I’m not knocking them, but I’m not using them either.


The strength of templates is that they shorten repetitive but slightly different tasks.  One I use frequently, and display at the end of this post, helps me check index usage on a given table.  I like to snoop around my indexes occasionally, checking on different tables when mood or suspicion strikes me.  Today I may be concerned about dbo.customers, tomorrow it might be sales.orders.  Furthermore, I might be investigating problems in the Demo database one minute, Beta the next, and Production when usage peaks after lunch.


I might even find <gasp!> that indexes are different in different databases.  (And that might be okay…)


Sure, I could type out most of the code below, but why when templates will for me?  Plus, I  get quick, consistent answers when management is snooping around me.


So how do you get to these, you ask?  I illustrate with SQL Server 2005 Express Management Studio (SSMS) since that’s what’s on my laptop and mention any obvious differences on my work PC.


What and where?

There’s no special file extension for templates – they’re straight SQL text.  Templates located in the expected directory behave like templates in Excel, Word and many other tools – opening one, changing and saving triggers a Save As… dialog.  This protects the original template file by prompting you to save somewhere else.  Where is this expected directory, you ask?  Yours will vary on OS, version, install drive, and username, but on my laptop (Vista, SQL Express):


C:\Users\Will\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM\Templates\Sql 


My work desktop (WinXP, SQL2005 Standard):


C:\Documents and Settings\wcarter\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql


Up one directory and you’ll find templates for other installed tools, such as SQL Mobile, Analysis Services, etc.  Play with those, blog on them, comment below, or whatever…


To display a list of available templates, the short cut is Ctrl+Alt+T.  (View / Template Explorer if you prefer menus).  You get a file explorer style dockable window with templates grouped in folders.  Right click any one for options like New, Edit, Search, Rename, and Delete (Edit is the way to change the template rather than a single instance of it).  You can even add your own folders to stay organized.


Filling in the <Blanks, , >

The power is in the parameters!  They take the form of


<Parameter name, Type, Default value>


Put one wherever you have dynamic code.  You don’t even have to supply all the values – <P1, , > will be sufficient.


To replace them with specific values, Ctrl+Shift+M (Query / Specify Values for Template Parameters) brings up the Specify Values for Template Parameters window, listing each parameter’s:

·          name

·         type

·         a textbox where you can enter a value.  (If you specified a default, it’s already there.)


Especially for templates I don’t frequently use or those I share, I often “name” my parameters with a short phrase or question, like this:


<Table name?, sysname, customers>

<Audit date from:, datetime, dateadd(day, -1, getdate())>

<Audit date to:, datetime, getdate()>


Anything else?

Templates are also great for those occasional, moderately complex operations.  Build some templates to handle those customer account credits that can’t be done in your app’s UI; quarterly data archival and purge processes; sales force hierarchy changes; and so on.  You won’t have to remember where the file is or if you changed all instances of customer id, sales person name, date, etc; it’s just Ctrl+Alt+T, Ctrl+Shift+M away.


Of course, if you spend much time creating and customizing, find that directory above and back it up.  I even keep copies on my thumb drive for when I’m visiting someone else’s cube…


The Point?

Templates are readily accessible, customizable, and time-saving.  They make a great addition to any DBA’s toolkit.  Check them out – you’ll be more productive by Friday.


They’ve saved me a lot of keystrokes (so I had some left for this blog!)


Again, thanks for stopping by!


The Promised Example – Enjoy!



TEMPLATE: Snoop Indexes.sql

      Finds usage stats for all indexes for a given table

      Author: WillSQL4Food


use <db, sysname, WillSQL4Food>


-- Index usage (seeks, scans, updates, etc.)

select            object_name(i.object_id) table_name, i.[name] index_name

            ,     c.[name] column_name, ic.key_ordinal, ic.is_included_column

            ,     i.type_desc index_type, i.is_primary_key, i.is_unique, i.is_unique_constraint

            ,     s.user_seeks, s.user_scans, s.user_lookups, s.user_updates

            ,     s.last_user_seek, s.last_user_scan, s.last_user_lookup, s.last_user_update

  from            sys.indexes i

  join            sys.index_columns ic

      on          i.object_id = ic.object_id

      and         i.index_id = ic.index_id

  join            sys.columns c

      on          c.object_id = ic.object_id

      and         c.column_id = ic.column_id

  left join sys.dm_db_index_usage_stats s

      on          i.object_id = s.object_id

      and         i.index_id = s.index_id

      and         s.database_id = db_id('<db, sysname, WillSQL4Food>')

  where           object_name(i.object_id) = '<tablename, sysname, >'

  order by  object_name(i.object_id), i.is_primary_key desc, index_name, ic.is_included_column, ic.key_ordinal, c.[name]


-- Fragmentation

select * from sys.dm_db_index_physical_stats(db_id('<db, sysname, WillSQL4Food>'), object_id('<tablename, sysname, >'), null, null, null)


-- Statistics generation

select * from sys.stats s where s.[object_id] = object_id('<tablename, sysname, >')