If you find this article valuable and you decide that you want to get your learn on with PowerShell and SQL Server, there is a SQLSkills class that I am teaching this year, please join me to get a great start in how to get your toolset growing by adding PowerShell to your toolbelt. Early Bird discount still available until March 10, 2017.
I have heard many times from many people that it is not worth learning PowerShell as a DBA. I have been using PowerShell since v1.0 and have found it to be amazingly rewarding. I will illustrate why this is the case in this entry and hopefully some of it will resonate with you and you will find some worth in learning at least a little PowerShell for your DBA job.
First, PowerShell was built to manage Windows Servers and soon manage some part of Linux servers as DotNet Core comes into play. Back in the day, if you were a DBA and wanted to manage SQL Servers, you used a lot of SMO (Shared Management Objects) and that meant programming in C# or pseudo C# in PowerShell. Creating objects, managing properties, etc.. That was not for everyone I know. I have a developer background and an IT background so managing servers and Active Directory and building programs to do things was part of my world a long time ago. But what I found was that there were not a lot of tools in the marketplace for doing things against SQL Server with PowerShell then so it was not widely adopted by DBAs.
In the SQL Server 2008 days, Microsoft wrote some PowerShell Snapins that would give us access to some cmdlets (Commands) in PowerShell to manage some things and introduced a PowerShell Provider for SQL Server that turned your SQL Server into a series of Paths (SQLSERVER:\sql\servername\instancename) and that was pretty powerful, at least to a guy like me, looking to automate many repetitive tasks. With those cmdlets and the provider there began to be a way to now use PowerShell to get at SQL Server without writing .NET code. It certainly did not cover the breadth of the product like Exchange had done with their cmdlets, but it was a start of some love. This is where the dawn of becoming a PowerShell DBA began.
Now Why Investigate
For a DBA there are many times you want to know information about your server, and there is plenty of TSQL to get that information. You need to know the DMVs or the system tables to get at that information and for a fairly new DBA, that can be a real challenge. Here is where the paradigm begins. In SMO there are objects (methods, properties) that encapsulate the properties of the objects in SQL Server into a class (Object Oriented term). These classes represent the objects by having the appropriate methods and properties to act on those objects. Let me illustrate.
We all know that there is a Database object in SQL Server and there are many TSQL commands that we can use to find out information such as…
SELECT * FROM sys.databases
SELECT * FROM sys.database_files
and many more….
It is a common practice to create a set of TSQL scripts and take them with you as you go. That is one of the values of longevity in the industry is what you have created and built up. There is no argument from me for this mentality. This is where you parallel with the PowerShell mentality in that you can reuse the scripts later on and not have to remember or rebuild them on the fly. But is there a better way? Maybe, but at least there is “another” way.
Enter PowerShell
PowerShell in and of itself is a mechanism to do many things. I advocate DBAs learn it because of the way you can change your job by using Automation, Scripting and Information Gathering that is much different than running TSQL Scripts and copy-pasting them into documents or tables. (or even Excel) I will take you through a couple of scenarios to give you a flavor of an additional tool (not advocating replacing SSMS or any other tool with PowerShell, only making you more efficient) with examples.
Say I want to have a list of databases with their name and recovery model.
TSQL
SELECT name, recovery_model FROM sys.databases;
PowerShell
Get-SqlDatabase –ServerInstance servername | Select Name, RecoveryModel | Format-Table * –AutoSize
We won’t really go into how you get to be able to run these commands, just to compare the commands. They are not much different than each other, one is run in SSMS or sqlcmd and one is run in PowerShell console or ISE or another tool that will execute PowerShell. The results are pretty similar as well.
Now that doesn’t look too scary. You get the same results. Here is how you would get it with the provider.
Notice that I used a Path-Like structure to get to the Databases in the Provider. I used the –Force parameter to ensure that I got the System Databases. Now that seems the same, other than the tool. So you ask the question, why do I do it? Why do I learn PowerShell to be able to get the same information? Here is the answer.
Objects are the answer
In TSQL you get results in a Grid or Text, you copy and paste or you just look at it and read it. You just get some text back either in PowerShell or TSQL or so it seems. In PowerShell everything is an object and they all have (potentially) methods and properties. Let me illustrate something that is an interesting part of PowerShell and objects.
Say I want to change the recovery model to Full on my DBA database. in TSQL I could do it in a couple of ways and it would look something like this.
ALTER DATABASE databasename SET RECOVERY FULL
Let’s take a look at how you do it with PowerShell and why it would be valuable to get this tool in your toolbelt.
What you see is the retrieving of the Database object in an SMO object (the provider always returns an SMO object) and there is a Property called RecoveryModel that tells me which recovery mode it is. To set it, the property is read/write so I can change it with the words “FULL”, “SIMPLE” or “BULKLOGGED” and then I use the method Alter() as above. The methods have the double parenthesis () after the method name and I could have inserted ($true) in the parenthesis to do a WITH ROLLBACK IMMEDIATE like I would do if I were changing a property that required full access. Now that looks like it may not be very advantageous, but imagine the next scenario.
I create a function to change my recovery model. I can use parameters for my SQL Server instance and the database and the RecoveryModel. Now I have a tool that does not require SSMS to be installed and allows me to call a function to change my recovery model. Once this is written and saved (and even put in a module for easy loading), and loaded into my environment, I can use it pretty easily for any database on any server.
Like I said before, this is contrived and works because I have SMO loaded, which is not hard to do, but this entry is all about why, not how to get things loaded. I have a SQL PowerShell Stairway at SqlServerCentral.com that you can go and read to help you get that part worked out. Not hard but not the reason for this post.
Join me in the quest to become a PowerShell DBA. Stay tuned for more on this site and join me in a class soon.
