T-SQL Tuesday #77- SMO is my favorite feature

tsql2sday150x150

This is a post for TSQL Tuesday #77 about my favorite SQL Server Feature.  I write a lot of automations using PowerShell and I use SMO (Shared Management Objects) all the time. This is my favorite feature because it allows me to get information from SQL Server using an object model instead of using TSQL.  I am pretty good at TSQL and have a lot of SQL scripts that I use, but when I am gathering information or just need a quick piece of information, SMO can give it to me with a canned script that I call with parameters and there is no real client that I have to have installed on the server or my workstation.  It is really lightweight and I can have the information back in an object form.

SQL Server has a lot to offer, but one maybe little known option for all you SQL Server people, is that Microsoft releases Feature Packs for each version of SQL Server/Service Pack so that you can have the objects without having to use the Management Studio and other tools.  To get started you will need the latest version so that you can connect backwards to lower versions. You get these objects for 2014 here.

Then you will need the following to get started in the order below: (either x86 or x64 for your platform for each item)

  • SqlClrTypes
  • Shared Management Objects (SMO)
  • PowerShell Tools

The SqlClrTypes must come first because it has objects for the types that are CLR, like HierarchyID, and geography types, etc. The Shared Management Objects contain the libraries that you use to access SQL objects via a .NET object. Then there is the PowerShell Tools which contains the SQL Server provider that allows you to import a module in PowerShell and have access to SQL Server in a path like structure.

Once you have these installed, you can then use PowerShell, or VB or C# to connect to SQL Server and use them. I will give you an example below to show you how they work and you can take it from there.


Add-Type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -Args localhost  # replace this with your server name

$server.Databases | Format-Table * -AutoSize

This is just a quick example of how to get information from SQL Server using SMO.  The command above basically is generic and not always the most useful, but it illustrates the point that the objects have properties that you are used to seeing in Management Studio like the Name of the database and Recovery Model. These properties are not always easy to get to in the tools, but in PowerShell and SMO they are.

If you want more in depth information on how to get started with SQL PowerShell, take a look at my Stairway on SQL PowerShell on www.sqlservercentral.com and many other Stairways.

Happy TSQL Tuesday #77.

Using SQL Authentication with SQLPS Module

In PowerShell you know that you can import the module SQLPS when the tools are installed for use on the machine. When you import the module you are dumped to a folder of SQLSERVER:\ > and it automatically uses SSPI or Windows Authentication. Many users would like to be able to use SQL Authentication toContinue Reading

TSQL Tuesday – Monitoring

There are many forms of monitoring. When I thought of monitoring, I used to always refer to things like Red-Gate, SQL Sentry, Idera or Confio Ignite (Solarwinds DPA).  But recently I have began to dive more into monitoring from the perspective of proactive checking things out. I will share a PowerShell script that I useContinue Reading