June 2011 - Posts

Bare Metal Restore Part 2

I blogged previously about how Windows 2008 R2 has native "bare metal restore"   http://sqlblogcasts.com/blogs/grumpyolddba/archive/2011/05/13/windows-2008-r2-bare-metal-restore.aspx , see the Core Team's blog post here;  http://blogs.technet.com/b/askcore/archive/2011/05/12/bare-metal-restore.aspx

Well since then I’ve actually had the chance not only to put the process to the test but to see if I could go one step further.

I have a six identical IBM Servers, part of the development infrastructure, which I am rebuilding with windows 2008 R2, when one of these died I wondered if I could use the bare metal backup from a preciously built server to rebuild this one.
Creating images for building servers is nothing new, however I've never built a server this way before and I'm not aware of this feature ever being available out of the box in a windows o/s.

Just to recap on why you might want to to do this, when I built my first server I applied security patches and service pack 1 plus a number of other changes before I made my backup. This means the restore applied to another server avoids me having to go through all that again.

So the big question is can you use the windows 2008 R2 backup to build servers? The answer is yes it works perfectly and very quickly.
Did I have any problems? Yes as the backup wasn't created with this in mind so the computer name and ip addresses were duplicated which entailed a little bit of work.
When I build my next Server I will not name the server, leave it on dhcp and not add it to the domain; then I'll make my backup which I can use to build the remaining three servers as and when.

If you're wondering how easy it is to use this technique, I needed the o/s install dvd, a usb drive with the server storage drivers and another usb drive with the backup files created earlier, these were just under 11GB in my case.

If you're also wondering what this has to do with SQL Server, well you can use this backup to make an after install image for any SQL Server you've just built, it could also allow you to perform testing and then put it back where it was before. Obviously the more drives and applications the bigger your backup and I don't have a way to test if it works with SAN drives, but I see no reason why it shouldn't.

Despite some of the frustrations of "where is xxxx now !" I think praise is is due to microsoft for the advances in windows 2008 R2

SSMS Built in Reports for Server and Database Monitoring

This is a long post which I hope will format correctly – I’ve placed a pdf version for download here  http://www.grumpyolddba.co.uk/sql2008/ssmsreports_grumpyolddba.pdf

I sometimes discover that the built in reports for SQL Server within SSMS are an unknown, sometimes this is because not all the right components were installed during the server build, other times is because generally there’s never been great reporting for the DBA from the SQL Team so no-one expects to find anything useful for free!

We did get the performance dashboard with SQL 2005 and this can be made to work with SQL 2008 with just a few code changes.

We also have the MDW database/management reports, but although I have this running on a couple of servers I’ve never really fallen in love with it.

( Once upon a time you could download the rdls for these reports but as far as I can tell the links are no longer available, I don’t know if this is deliberate. )

I use a few of these reports now and again, I have my own dashboards, reports and queries which are customised to particular environments, but if I’m faced with an “unknown” server then these reports will often be my first port of call.

Like anything using the dmvs remember that under pressure dmvs may shed or stop collecting data. Data is usually cumulative since last service restart, is cleared on a service restart and certain data may be cleared under specific circumstances.

The only way to collect dmv data for analysis is to take regular snapshots of the data into permanent tables of your own and work from there. At best some dmv data can lead you into believing you have problems where you don’t.

The relationships between dmvs can sometimes be confusing; obtaining information about partitioned tables is a good example of where it’s not as easy as you might wish.

Anyway Reports are available by context when you right click in SSMS object explorer; here I’m clicking on the actual instance/server.

( Note that to protect the innocent names and data have been changed/blanked where appropriate )

1. Reports by Server/Instance

To start off open SSMS, register your Server and right click on the instance/server

Choose Reports – Standard Reports - to view available reports for that context.

Here’s the list of Standard Reports for the instance, 23 reports in all and appearing to cover a number of useful areas.

Click on the individual report you wish to run to bring it to screen.

  • I’m not going to screenshot every report but I will include a few examples.
  • The main problem is that some of what might appear useful take too long to drill down and your actual chance of finding what you’re looking for is very slim, unless you only have a few connections.
  • Some reports I’ve never been able to entice into producing an output.
  • I’ll summarize this briefly below.

Server Dashboard

  Lacks functionality

Configuration Changes

  Never managed to get this to show anything

Schema changes

  Only ever worked after installing Sharepoint when it listed every table/index installed, or so it seems.

Scheduler Health

  Takes far too long to find anything interesting – has some value

Activity - xxxx

  I find it takes too long to find anything really useful.

  It will depend upon your environment of course.

Memory Consumption

  Could be better

Top Transactions by xxxx

  I have better ways to get this information.

  Top Transactions by Age does allow you to drill into the sql statement concerned, however it shows   statements, which is fine, but no object id or name so doesn’t reference the stored procedure containing the statement.

Performance - xxxx

  • Often ( very ) slow to run.
  • There are better ways to view this type of data.
  • It does allow you to see how much rubbish msdb produces which doesn’t parameterise and takes up valuable memory.
  • Certainly the TOP xxxx reports are interesting
  • A Top Procedures would be useful, only returning statements whilst useful doesn’t pin point that proc which is performing badly.
  • Charts don’t have click through to link to statement.

Service Broker

  I don’t use so can’t tell

Log Shipping

  I don’t use Microsoft log shipping so can’t comment.

Starting with Server Dashboard which sounds better than it is:

  • There are 24 databases on this server and as you can see the two charts do not display correctly, in fact I am totally unable to make them display correctly and neither the export or print option will produce a correctly formatted report.
    • Right click to export to pdf or excel, pdf never formats correctly
    • I personally would have like web archive as an option here.
  • There’s no drill down and to be honest not a great deal of much use.

Non Default Configuration Options expand to show server options – I’m not sure I quite agree with “ Non Default” this sort of infers you’re not running a standard box, anyway this is from a production box showing what you might see. ( these are my “non default” settings! )


You might like to compare to the front screen of the Performance Dashboard which drills down on just about everything and also auto refreshes.

( Note that this report has been visually formatted to give a look and feel which matches other reports I use )

Back to SSMS Reports - Here’s the memory report, there’s drill down by object type too, not shown here.

Here’s one of my own reports: ( sorry can’t make it smaller and readable ! )


The Processes Report – generally far too unwieldy. ( drill down  just too many clicks  shown after )

Top Queries ( statements ) by cpu:

  • · Can’t click on the chart to select a statement
  • · Statements are abridged
  • · Drill down does give object ID so you can track back to the procedure.

    2. Reports by Database – right click on a user database for these

Disk Usage xxxx

  The first three reports are probably the only reports I ever use, however that said I do have a fairly  comprehensive reporting structure of my own.

Backup and Restore Events

  If you have nothing else it’s good.

xxxx Transactions

There’s better ways to extract transaction/user data.

On my systems I didn’t find these reports useful at all, assuming they returned any information. Again far too many clicks to drill down to information of dubious use.

Resource Locking Statistics

Again on my systems am unable to find any useful output

Object Execution Statistics

The Object Execution Statistics are ok but top xx type reports are better ; on a production server this report lists nearly 2,000 objects which is just too much.

Database Consistency

Does what it says

Index Usage Statistics

Index Physical Statistics

The index usage reports are ok, but in the end you’ll probably want to write your own, they’re also cumulative so for real time diagnostics the averages are little use if your server has been running for several months.

:)  One area these reports do work is where there are many tables, I support a well-known Financials and HR application, sadly there are in excess of 87,000 tables created in the database so SSMS just dies if you attempt to open up the tables TAB.

Schema Changes

I’ve found this to be inconsistent across my environments so it’s not something I’m likely to use.

User statistics

Never found this useful

The basic disk usage report is useful, especially as it shows autogrow activity – which should never be like this for a production database – but I don’t fully administer this system. ( as usual names changed to protect the innocent! )

Backup information.

Successful operations list each backup with file details etc. If your logs back up every 5 mins then this is a lengthy report!

Execution Stats:


  3. Reports for SQL Agent

Right clicking on the SQLAgent will show the following two reports.

Top Jobs report is interesting, I’m not sure about the Job Steps !

   4. Reports for Management

There are also two reports available from Management, but I am unable to see what value they have.

To summarise; If you have no reporting and you didn’t know these reports were available then you will probably find these better than nothing.

The main advantage these have over a query is that most are graphical and that well known phrase “ A picture paints a hundred words ” still holds true.

© www.grumpyolddba.co.uk May 2011
download as a pdf here  http://www.grumpyolddba.co.uk/sql2008/ssmsreports_grumpyolddba.pdf