SQL Server – breaking bad habits, improving knowledge and awareness

The hardest part of being a DBA is to keep up with all the things that appear in connection to our job.

  • SQL Server new features
  • SQL Server best practices
  • SQL Server new DMVs, DMFs
  • SQL Server procedures specific to each company
  • …..

Although this information is available and reachable by checking Microsoft web sites, SQL Server specific blogs, your corporate e-mail or corporate intranet, most of the time due to the daily workload or other distractions we miss it. The answer to this will be a reminder or even better a notification. I found the notification a better option because I always like the way in which Microsoft Outlook notifies you when a new e-mail arrives.  While trying to duplicate that behavior I found out that is very hard without a good understanding of .NET programming. In order to still have what I wanted I had to accept that

  • the notification will still consume resources, it will still appear although you cannot see it – the idea behind was to check if you have more than one monitor and if yes to check if any application is running in full screen mode or covers a specific portion of the last monitor. If there will be no application that have windows that covers the lower left corner of the screen then the notification will appear otherwise the notification will not appear since you cannot see it.

Because these days especially in the office we have at least 2 displays the idea of having a notification on the third one that runs every 10 seconds and stays there for another 10 seconds reminding me about a new DMV or reminding me about something else is beneficial. Most of the time will be ignored but from time to time while contemplating the screen or waiting for a task to complete we can peek to that notification and in time we will know the new feature or what is new or other things that you want your notification to display.

Below is an example of how it looks like.

In order to close the notifications we need to double click it.

The script can be downloaded from here

Compare 2 SQL Server instances

During sql server installations or migrations of old instances to new instances we need to gather information quick in order to be sure that we are installing or configuring the instances in the same way. Sometimes when it comes to performance issues we need to compare instances settings to spot any differences that might cause it. One of the methods to spot differences while comparing 2 instances is to put them side by side and to check the property that you want using one of the built in tools, SSMS, tsql code, SQL Server Configuration Manager, Services management console, SQL Server Facets and others that might expose the setting that you want. Unfortunately this method is sometimes

  • time consuming
  • ineffective when the properties that you want to check are accessible using different tools
  • not appropriate when you want to share your results with someone else

Fortunately we can overcome all the above using Powershell and SMO to build a basic GUI ( inspired from this script # A GUI WMI explorer and WMI Method Help generator available on www.ThePowerShellGuy.com) that displays some of the information that we need. The script attached to this post is displaying properties for

  • server collections
  • jobs collections
  • logins collections
  •  users collections
  • databases collections

For other collections you can check the SMO Object Model Diagram and add them to the script together with the modifications that might be required.

Below is an example of the script output

Some of the properties are at their turn collections and for those when selected the output will show sometime only the powershell object type and not the collection.

As with almost all of the scripts provided here, this script is only something basic that can help us to provide specific type of answers faster.

The script can be downloaded from here