PowerShell Pipeline
Introduction to SQL Server Management Objects with PowerShell
Open up new reporting options for your SQL Server with these PowerShell tips.
Sometimes you become the accidental DBA, or you are the DBA by choice. Either way, you can choose to spend time working in SQL Server Management Studio (SSMS) to look at things such as backups or the size of your database or you can look at alternative approaches that leads to automating some of your repetitive day-to-day activities.
There are a number of PowerShell modules which can make your life much easier when it comes to managing and reporting on your SQL servers. There is even a built-in module (also available now as a download from the PowerShell Gallery) that you can use to manage SQL. Today I will not be going into any of those modules, but I will show off what they can do in another article.
Some of these modules use what is called the SQL Management Object (SMO) which allows you to load some assemblies to be able to connect to a SQL server and then explore the server and its database and tables like you would any other object in PowerShell. This really opens up the door on what you can do with SQL using PowerShell such as reporting on the state of your backups to viewing the size of the databases and tables. You can even take it further by using various methods to perform changes on the server or creating things such as databases or tables on the SQL server.
The first thing that I will do is load up the assemblies on my SQL server and then make the initial connection.
## Add Assemblies
add-type -AssemblyName "Microsoft.SqlServer.ConnectionInfo, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Smo, Version=12.0.0.0,
Culture=neutral,PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SMOExtended, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.SqlEnum, Version=12.0.0.0,
Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
add-type -AssemblyName "Microsoft.SqlServer.Management.Sdk.Sfc,
Version=12.0.0.0,Culture=neutral, PublicKeyToken=89845dcd8080cc91" -ErrorAction Stop
Note that I am running SQL Server 2014 so depending on your version, you may need to adjust the Version of the assemblies to something that better supports your SQL server.
Now that we have loaded the assemblies, we can now make the initial connection and verify that we have a good connection.
$sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList 'vsql'
$sqlServer
[Click on image for larger view.] Figure 1.
This is just a portion of the properties which are available to view at the SQL instance level. We can now begin digging down into our server to see our databases and even the tables of a particular database.
$sqlServer.Databases | Format-Table Name,Owner,Status,LastBackupDate,LastDifferentialBackupDate,
LastLogBackupDate,RecoveryModel,DataSpaceUsage -AutoSize
[Click on image for larger view.] Figure 2.
Here we can see some great information about our databases such as the owner, the status of the database as well as some backup information, RecoveryModel and database size. Definitely some useful information to know about your environment.
Taking it deeper, we can look at the tables of a particular database.
$db = $sqlServer.Databases['ServerInventory']
$db.tables | Format-Table Name, Owner, CreateDate, DateLastModified, RowCount, DataSpaceUsed
[Click on image for larger view.] Figure 3.
We can event look at and see all of the database users and what their permissions are on the database by using the EnumDatabasePermissions() method.
$db.EnumDatabasePermissions() | Format-Table Grantee,GranteeType,PermissionType,PermissionState,Grantor -AutoSize
[Click on image for larger view.] Figure 4.
Some other important information to know is the logins on the SQL server which we can find under the logins property of our $sqlserver variable.
$sqlServer.Logins | Format-Table Name, ID, CreateDate, IsDisabled, LoginType, WindowsLoginAccessType
[Click on image for larger view.] Figure 5.
Curious about what is currently happening on your SQL Server? You can use the EnumProcess method to show all of the activity on your SQL server.
$sqlServer.EnumProcesses() | Format-Table Login,Database,Command,Status,CPU,MemUSage,Host -AutoSize
[Click on image for larger view.] Figure 6.
We have really just dipped our toe into the water that is using SMO with PowerShell to explore and report on various portions of SQL server and its database and tables. We can also do things such as build indexes, drop databases and tables and even create new SQL logins and database users. The possibilities are pretty much endless!
I'd love to hear in the comments about what you have done using PowerShell to manage SQL either using SMO or even using some of the modules.
About the Author
Boe Prox is a Microsoft MVP in Windows PowerShell and a Senior Windows System Administrator. He has worked in the IT field since 2003, and he supports a variety of different platforms. He is a contributing author in PowerShell Deep Dives with chapters about WSUS and TCP communication. He is a moderator on the Hey, Scripting Guy! forum, and he has been a judge for the Scripting Games. He has presented talks on the topics of WSUS and PowerShell as well as runspaces to PowerShell user groups. He is an Honorary Scripting Guy, and he has submitted a number of posts as a to Microsoft's Hey, Scripting Guy! He also has a number of open source projects available on Codeplex and GitHub. His personal blog is at http://learn-powershell.net.