You don’t need a weatherman to know which way Windows blows.
– Bob Dylan, kind of

If you need to do a repetitive task in your day-to-day life, what’s the best way to go about it? You look for ways to simplify or even automate it. I love to wash dishes by hand, for example, but I enjoy the task even more with the help of a dishwashing machine, and it saves me time for other tasks, whilst listening to Bob Dylan and loud Rock music.

It’s no different at work, even in IT, doing Corporate Server Administration.

Many of my day-to-day tasks as a Database Administrator are as repetitive as washing up, and likewise could be easily automated with the right tool. An admin needs the means to perform complex tasks with few lines of code. What’s more, it must be flexible, able to integrate with all other Microsoft products and easily automated. That tool is Windows PowerShell.

What is Windows PowerShell?

Windows PowerShell was a turning-point in Windows Server Administration. It turned a Windows server from a point-and-click GUI device into a fully-programmable system. It allowed Windows Servers to be used in the same way that other servers were. At last, systems administrators were able to control and automate their server environments in a repeatable and efficient way. It created a new work lifestyle for Windows admins; the PowerShell Lifestyle.

Unix-based servers have always had shells, of course. A shell is a system program that facilitates the interaction between the user and the operating system, usually through having easy access to the kernel. Shells are essential for server environments. PowerShell inherits this characteristic, but in an unprecedented way. It passes data as objects. Imagine that you wish to retrieve CPU information. With a command line in PowerShell you can perform this operation accessing the Windows internal structure using WMI, as showing in the figure 1.1:

Figure 1.1- Displaying CPU information

Whereas previous shells invariably processed only data as text, Windows PowerShell is built on the .NET Framework and this allows it to use a common model for objects. This allows processes to pass objects between them as well as text. Because PowerShell has full access to all .NET classes and resources, easy object manipulation, and consistent design in its cmdlets, it becomes a powerful tool for the working Windows Server environment.

We’ll need to illustrate this with an example. If you want to retrieve information about a flat file called TempFile.txt in a folder called SQL. Using BASH to Linux the information received is what is showed in the figure 1.2:

Figure 1.2 – Information about TempFile.txt using Bash for Linux

NOTE Thank you Logan Destefani Merazzi who kindly provided me with the examples in BASH.

The Figure 1.3 displays the same operation in PowerShell:

And here is the result it gives:

Figure 1.3 – Information about TempFile.txt using PowerShell

You must be asking yourself what the difference is between them. In the figure 1.2 the BASH Shell processed a text command line and outputted a text result. In PowerShell, Figure 1.3, it processes and outputs an object, with several methods and properties. We can see this in figure 1.4:

Figure 1.4- Members from the object outputted in PowerShell

In the figure 1.5, we will see a simple count for each member:

Figure 1.5 – Grouping the object by member type.

We can see that we have forty-eight items, split into five member types. If we filter only methods, or the member type “Method”, we will find one called moveto() as the figure 1.6 shows :

Figure 1.6 – Filtering method MoveTo()

This means that we can use this method to move the file across the paths in the same command line that we get the information about the file. This only can happen because we are working with an object and not merely a text representation of it:

(gci tempfile.txt).moveto("c:\temp\tempfile.txt")

This characteristic of Windows PowerShell, that everything is an object performing complex operations very simply in a command line.

Although it was primarily designed for System Administrators, PowerShell is now extending its use to Database, SharePoint, Exchange and all Microsoft products.

In its first and second versions, PowerShell didn’t pretend to do more than provide a shell script language for Windows servers, but in the maturity of its third version, it has developed into a complete automation engine. So PowerShell is now more than the washing machine at home. As well as automatic washing and washing schedule, it would be clearing the dishes, washing the curtains and feeding the cat. Oh Boy, with PowerShell I am in heaven.

Why should DBAs know PowerShell?

Database Administrators love automation. You are surrounded by routine tasks. If you can automate such tasks as the morning checklists, into a concise, organized, repeatable and automated process, then it makes for a better, more interesting job. You will not need to spend hours checking on the health of the environment, because it will be detailed in your inbox, and you will be able to concentrate on other more challenging and interesting tasks.

There are usually three ways to automate any task in SQL Server administration:

1. Using DDL and system stored procedures
2. Using the graphical user interface programs, SQL Server Management Studio (SSMS) and SQL Server Configuration Manager
3. Using PowerShell scripting

In Windows, the conventional way of doing administration is by pointing and clicking within the GUI. The problem with using the graphic user interface, besides the time it takes, is the difficulty in reproducing the results on many machines without human error. Although you may able to type the correct responses into a graphic Wizard program once or twice, it’s not only tedious but error-prone to attempt to do this on one hundred SQL Server instances. Your chances of achieving perfect configuration every time are slim. If you have to do exactly the same thing on each instance, you can script what you’ve just done (or even what you about to do are) using the scripting capabilities built into SSMS. There is a script button on almost every graphic user interface window and most database objects can be scripted from the SSMS Object Explorer pane. The figure 1.7 shows a scripting options in a backup dialog window and the figure 1.8 the options to the database objects:

Figure 1.7 – Scripting options in a backup dialog window

Figure 1.8 The Script wizard in the SSMS Explorer Pane

There is even the facility to script the definition of an entire database and its objects, though this does not exist for SQL Server Configuration Manager.

One of the reasons why PowerShell automation has taken a while to catch on for administering SQL Server is that SQL Server comes with a great, and for the most part standardized, scripting language of its own: Transact-SQL (T-SQL). Database administrators are usually very skilled and comfortable with T-SQL, and prefer to use it for everything. In addition, using T-SQL gives DBAs a perfect built-in repository for storing performance and other historical information for recording and trending purposes: databases and tables. Using T-SQL the same script used for collecting information can also store that information in a single additional line of code. And SQL Server also includes:

• Database mail, to email the DBA with any information or notifications required
• A SQL Server-specific job scheduling and execution system, SQL Server Agent
• A command-line utility, SQLCMD (and OSQL, popular in earlier versions) with a Macro-like capability.
• The Management Data Warehouse, a SQL Server system data collection facility with some built-in reports
• The SQL Server Utility and Data Tier Applications, SQL Server-specific application and multi-server management (AMM) features
• A data-centric workflow system, SQL Server Integration Services (SSIS)
• The ability to “shell-out” to the operating system in T-SQL scripts: xp_cmdshell.

When..When..When That is the question.

Why not just use SSIS or TSQL?

A database administrator might justifiably ask “When would I use PowerShell scripting in preference to SQL Server scripting?” and “When would I use PowerShell in place of the build-in tools that SQL Server provides?”

SQL Server Integration Services (SSIS) is usually a tool used by programmers, rather than DBAs. It does include an interface to operating system capabilities but it’s also programmed almost exclusively using a graphic user interface, limiting the scripting and reproducibility factor. The xp_cmdshell capability, although useful, has its limitations when it comes to retrieving information and executing commands against multiple machines. It is also considered to be a security attack vector in SQL Server and is turned off by default in modern SQL Server versions.

Just as SQL is a database industry standard, PowerShell is a Windows administration standard. PowerShell is part of the Windows Common Engineering Criteria (footnote to CEC here) and therefore integrated with almost all Windows products and features. In addition to parts of the Windows operating systems, third-parties such as virtualization and SAN vendors are coming out with PowerShell integration libraries at a fast pace. DBAs can extend their reach by knowing PowerShell as well as SQL.

PowerShell uses a very elegant means of automating any number of SQL Server processes on any number of instances and servers. Its integration with the operating system as well as all SQL Server resources (using, for example, SMO and WMI) provides an inexhaustible source of possibilities to create automated solutions.

NOTE SMO, or, SQL Server Management Objects is an object collection created to work with all SQL Server Resources. Reference: http://bit.ly/17nhda1.

The same code written for one server is easily scaled to many servers just adding another server to a list. Scaling to the corporate level is exactly what this series of articles covers, because even a simple process to check which SQL Server Agent jobs have failed becomes a pain when required on several SQL Servers instances. The Database Administrators do not need to create a process in each server to report its information: They just need a central repository and from it, apply the solution in all the servers, centralizing the information and reducing the code maintenance.

PowerShell scripts also excel at DBA tasks that require coordination between multiple SQL Server instances where each SQL Server instance is configured differently. PowerShell is also great for tasks that involve both SQL Server configuration and operating system configuration. Examples of this sort of task might include:

• Replication topologies
• Automating SQL Server-related cluster resources
• Always On availability group configuration and management
• Log shipping configuration and monitoring
• Coordination Network setup and firewall changes
• SQL Server operations involving external certificates
• Using SQL Server in a virtualized environment
• SQL Server client configuration

Because PowerShell is also the language spoken by systems administrators, it helps to smooth over the interaction between systems administrators and DBAs when configuring a SQL Server instance to take advantage of features of newer modern operating system versions. The configuration can be automated with seamless integration.

There are processes involving DDL operations that should always be done in T-SQL. There is no sense in using 50 lines of SMO code to define a database table that can be defined in 5 lines of standard T-SQL DDL. But T-SQL’s main focus is on internal database operations, such as creation of databases and tables, querying data and the like. It is painful, for example, to retrieve the disk space with mount points in a stored procedure. The solution probably could be based on CLR or a stored procedure that manipulates COM objects and would consist of a considerable number of lines of code. By using Windows PowerShell, however, you can perform this operation and scale to many servers in a single line of code.

Time Savings

Another very important point is the time saving. Professionals that know how to create clever solutions to maintain their environment are automatically saving time, and generally providing control and management of their resources, saving money for them and their company.

This is easy to illustrate. If, for example, you want to retrieve all information about an SQL Server instance, then you’re faced with the prospect of using a script with approximately five hundred T-SQL lines. Then you’d have to work out a way, possibly best done with SQLCMD, to replicate this query to the other servers. The same process can be done in three lines of PowerShell, as you can see:

#A – Load the SMO Assembly
#B- Create an SMO Server object referencing the QuiGonJinn Server and assign it to the $Server variable
#C – Display the SQL Server object information

With more two lines you can use it in several servers and, the script can be run from the central repository. PowerShell should be used for operations like this when T-SQL dos not provide the same ease-of-use.

T-SQL vs PowerShell: Making the choice.

PowerShell doesn’t always provide the best solution. Using PowerShell, for example, is the last thing you should think about using in order to create a table.

I follow some general rules and call them the four commandments for DBAs who are deciding when to use PowerShell:

• Know when not to use PowerShell
• If you wish to code PowerShell as you would code C#, then use C# instead. To code in PowerShell you must do it the PowerShell way, using PowerShell conventions.
• Whenever possible, write functions, not scripts. A function should do one thing well.
• Always use a good IDE. Database Administrators are definitely slightly deviant, but they aren’t masochists.

A DBA will generally use PowerShell for administrative and repetitive tasks. An example of this is to search an IP range looking for a SQL Server service, or in other words, tracking SQL Server instances across the network. The code is pretty simple, considering the complex process it does:

#A – Set the verbosepreference environment variable to display the messages in Write-Verbose cmdlet
#B- Define the range to the Ip´s
#C – Exclude the machine that is running the script
#D – Set the variable $IP with the current IP
#F – Test if the IP is responding to PING, otherwise display verbose message
#G – Get the SQL Server Services in the current IP and store in the Service variable
#H – If found any SQL Server Service display the output, otherwise display a verbose message.

I am using WMI to test the connection, but you easily can switch to the cmdlet Test-Connection

Consistency

One of the great characteristics of Windows PowerShell is its consistency. Everything is an object and, generally speaking, an output from a cmdlet is the input to another one. This means that the output can be saved in a txt file just by “piping” Out-File cmdlet.

NOTE The concept of pipeline and the “piping” word has already been discussed in another POSH DBA article.

The output now is in a .txt file called SQLServerIP.txt in the c:\PowerShellForSQLServer2012 Folder like the figure 1.9 shows:

Figure 1.9 – Txt file with the IP where an SQL Server Service was found.

When you’re programming SQL Server, you wouldn’t use PowerShell instead of TSQL or SSIS. You’d use it in addition to these other tools. It is an extra string to your bow. There are built-in PowerShell cmdlets for sending mail, as an adjunct or alternative to using database mail. PowerShell integrates with SQL Agent through the inclusion of a PowerShell SQL Agent subsystem, and, additionally, you can run PowerShell scripts through the SQL Agent Command Shell subsystem. SQL Server includes a cmdlet, Invoke-SqlCmd that can run T-SQL scripts and return DataTable objects or XML objects. PowerShell has built-in support for WMI, using either native WMI cmdlets or SMO/WMI libraries for multi-machine configuration tasks and SQL Server event support. Exploring the integration features, as well as presenting solutions that use PowerShell and SQL Server together, in the main point of this series of articles.

Real World PowerShell Solutions

Imagine that you have to manage a system that monitors disk space for to 300+ Servers and covers mount points as well. For doing this, the decentralized method is clearly out of the question because you do not want to create a monitoring system in each server.

We must think in two approaches: centralized/synchronous and centralized/asynchronous. Which is the best? As everything in SQL Server is “It depends”

Before I show you the sort of asynchronous system that I’d use, we will see the difference between them. Why? Just because I am advising, strongly, to use a particular method based on my field experience but I am not saying to you must “Do it this Way”. Both the two approaches work perfectly, and it is just a question of selecting the one that fits your particular requirements.

Synchronously working

I remember a long time ago, in one of my physics classes, my teacher drew five candles on the blackboard and asked us: “Each candle takes 10 minutes to burn completely. If I light one at a time, and wait until it has burned, how long will take the whole process? “The answer is simple, the whole process will take 50 minutes, because the next candle will be lit only when the previous one finishes burning.

Using the same idea of candles in the process of monitoring disk space, Figure 1.10 illustrates the centralized/synchronous approach:

Figure 1.10 – Process using synchronous approach

We can see that the process starts on Server1 and only after the output from it the process starts on Server2. As the candles, this means if the Server1 take 10 minutes to finish, the process two only will start after this 10 minutes and this scenario is the same for all the servers you have in your list to monitoring. In this case it took at least 20 minutes. The whole process is serialized and if you have just a few servers then that would probably be OK.

Asynchronously working

If we now use the asynchronous approach, then all the requisitions are started at the same time (or at least with a little delay between them to start the necessary background process), but the main point is that the subsequent process does not need to wait for the previous to finish. The whole task is started and ran in background, using PowerShell background jobs or Runspaces.

NOTE The concept of PowerShell background Jobs and Runspaces are very interesting and I strongly recommend to study.

If we think in terms of the candles example, if each candle takes 10 minutes to burn completely and I light all at the same time, then it isn’t going to take 50 minutes in all. It will be 10 minutes for each candle and all candles, the entire process.

Figure 1.11 illustrates the same monitoring disk space process asynchronously, you can see there isn’t a queue. Does not matter how much time Server1 takes to finish, server 2 is also started and as they finish, they will send their output to the repository.

Figure 1.11 – Process using asynchronous approach

If the job involves both Server1 and Server2, it will take just 10 minutes, because the monitoring processes were started at the same time. Obviously, there are other variables to take into account, such as the control of the job or the runspace queue, but even with this overhead, it will be faster to manage several servers asynchronously than synchronously.

The maintenance of a centralized system is much simpler because all the effort of monitoring the jobs and storing all the information of the other servers is concentrated in only one spot. By making the process asynchronous it is not only faster but less complex. The alternative of creating, in each instance, a monitoring system to list the free disk space is definitely not something that you would like to write, let alone maintain.

PowerShell: The power of the command line

Windows PowerShell has established itself as an automation engine that provides system administrators, database and all of the Microsoft Server line a means to not only automate routine jobs, but to go further and create professional solutions for managing servers.

I strongly believe that after you take off your fear of command line and try PowerShell you will have the same feeling that I have. Pure passion.

Many thanks to my good friend and Jedi Bob Beauchemin who was the technical reviewer of this article.