This level will demonstrate simple techniques to perform a few important SQL Server maintenance tasks using PowerShell. We'll barely scratch the surface of what's possible but I hope it will whet your appetite. We'll cover how to:

• Document and monitor Server properties and instance-levels configuration settings.
• Monitor important database properties, and alter them if necessary
• Perform basic index maintenance

Server Information

We will start with the Server object. There are situations that you may want to get information about the server itself. The server object contains two objects that can provide you with most of the information you would get when right clicking on the server object in Management Studio and selecting Properties. The two objects are Configuration and Information. Table 11.1 shows an example of some of the properties from the Information object and Table 11.2 shows an example of the properties in the Configuration object. One thing to note about the Configuration object is that each item in the object is actually an object as well as you will see in Figure 11.1. Also note that the Configuration object contains the items that you would find when executing the sp_configure procedure.

Table 11.1 Properties in the Server.Information object

Table 11.2 Properties in the Server.Configuration object

Figure 11.1 Properties in a configuration item

You won't be able to type in the code in Figure 11.1 until later in this level, but this figure shows you that there are properties of a Configuration item so it is an object that holds an object. You might ask where you would use this in your day-to-day operations. The data can be used to capture a snapshot of the configuration of server information for later trending analysis or to detect changes made to the environment. Listing 11.1 shows how you would gather some of the current settings of sp_configure using PowerShell and display the name, current and running values. Figure 11.2 shows the results.

# Load the Assemblies
Import-Module SQLPS –DisableNameChecking
$server = Get-Item SQLSERVER:\sql\localhost\default
$server.Configuration.Properties | 
      Select –First 10 DisplayName, ConfigValue, RunValue, IsDynamic | 
              FT * -AutoSize

Listing 11.1 Getting SQL Server Configuration properties list

Figure 11.2 Server Configuration properties results

Gathering Data from the Server Configuration

One thing that you will do as a database administrator is to configure or monitor these and other settings to ensure that they are what you want them to be. The example in Listing 11.1 illustrates getting the configuration properties all at once. This may or may not be useful to you in this format, due to the fact that it is display only. If you use the methods in the previous level to output data to SQL Server tables, you will then have a monitoring component that can store a snapshot of these properties in a table every week to ensure that they have not changed or that they contain the values you expect.

The scripts in this section are those that we used in level 10 of this stairway. First we will dot-source the Out-DataTable.ps1 script, which can be used like Write-DataTable.ps1 below and is found here (https://gallery.technet.microsoft.com/scriptcenter/4208a159-a52e-4b99-83d4-8048468d29dd). This script basically takes tabular-like data from PowerShell and turns it into a System.Data.DataTable so that we can import this data into a SQL table. We then dot-source the Write-DataTable.ps1 script so that we can put the data into the SQL table in our monitoring database. Listing 11.2 has the table definition and Listing 11.3 contains the code to put the results into our data table. Below are the steps to take to follow along. If you don't have a database already you can create a database for this purpose. For this example we will use MyDB which we created in level 9.

1. If you have not created your MyDB database, you should create it now.
2. Make sure Out-DataTable.ps1 and Write-DataTable.ps1 are in c:\scripts. If this is not where you want them, you will need to change the path in Listing 11.3 to reflect the location of those scripts.
3. Ensure that you have loaded the SQLPS module as shown in Listing 11.1.
4. If you have not previously created the table in Listing 11.2, you should create it to continue the examples in this level.
5. Run the code in Listing 11.3 to get the configuration items and output them to a DataTable and write the data to the Table SqlConfigMonitor.

Notice that the data that you retrieved from the Server.Configuration properties is now in the table with their values.

CREATE TABLE dbo.SqlConfigMonitor (
DisplayName varchar(128) NOT NULL,
ConfigValue varchar(10) NULL,
RunValue varchar(10) NULL,
IsDynamic bit NULL,
GatherDate datetime NOT NULL DEFAULT(getdate())
)

Listing 11.2 SqlConfigMonitor table for storing snapshots of configuration

. c:\scripts\Out-DataTable.ps1
. c:\scripts\Write-DataTable.ps1
$server = Get-Item SQLSERVER:\sql\localhost\default
$config = $server.Configuration.Properties | 
      Select DisplayName, ConfigValue, RunValue, IsDynamic | 
              Out-DataTable
Write-DataTable -ServerInstance localhost `
    -Database MyDB -TableName SqlConfigMonitor `
    -Data $config
$query = "SELECT * FROM dbo.SqlConfigMonitor"
Invoke-Sqlcmd –ServerInstance localhost –Query $query –Database MyDB

Listing 11.3 Insert snapshot of configuration into table

Using the information

After a few iterations of gathering the data, you will see that the GatherDate column will contain the date that you inserted the data into the table. You will be able to then get the newest set of configuration items and see what the values are. You can also compare snapshots to see that a configuration item changed from one day to the next. This is a simple example of how you could use PowerShell and SQL Server to monitor configuration changes. This applies to many other things such as database options, table or index counts and more.

Maintaining a Database using PowerShell

In the life of a DBA you have many opportunities to maintain databases, whether they are restored into a QA or Development environment or they are in production. One example of this maintenance is in managing the settings of a database. Some settings that you may care about are the compatibility level or page verification of a database. Listing 11.4 shows an example of retrieving these settings from the MyDB database object and Listing 11.5 shows changing them to be what we want them to be. Figure 11.3 and 11.4 show the results of each. We are assuming that the database would be set to TORN_PAGE_DETECTION mode in the database options as well as having the compatibility level preset to 100. With this in mind, you can run the scripts below to see how you would see these values and change them.

$database = Get-Item SQLSERVER:\sql\localhost\default\Databases\MyDB
$database | Select Name, PageVerify, CompatibilityLevel |
    ft * -AutoSize

Listing 11.4 Retrieve serveral database properties

Figure 11.3 Results of current properties of Name, PageVerify and CompatibilityLevel of the MyDB database

$database = Get-Item SQLSERVER:\sql\localhost\default\Databases\MyDB
$database.PageVerify = "CHECKSUM"
$database.CompatibilityLevel = "120"
$database.Alter()
$database.Refresh()
$database | Select Name, PageVerify, CompatibilityLevel |
    ft * -AutoSize

Listing 11.5 Changing database properties

Figure 11.4 Results of the change

In Listing 11.5, we are setting two of the properties. To make the changes effective I then executed the $database.Alter() method. Essentially this generated the TSQL necessary to make the changes and sent the TSQL to the SQL Server instance for execution. After the Alter() method, we issued a Refresh() to retrieve a refreshed copy of the properties, which were then displayed. In using PowerShell with SQL Server the process is a send/receive process. I set properties to values (they must be valid, or nothing happens) and then issue the Alter() command to make the changes. This applies to most all the objects in SMO as does the Refresh() method.

Index Maintenance with PowerShell

In most DBA roles there is a recurring activity that involves index maintenance. Indexes need care and feeding. While some indexes need to be rebuilt, some only require a reorganize to keep them maintained. In preparation for this section, the script in Listing 11.6 shows the table definition for TestTable and the definition of the index on that table. If you do not have the table and index in your MyDB database, you can create them first with this script. In Listing 11.6 you will also see a typical rebuild command and a typical reorganize command. In Listing 11.7 you will see the PowerShell and SMO code that does the same thing.

CREATE TABLE [dbo].[TestTable](
[id] [int] NULL,
[test] [varchar](30) NULL,
[test2] [varchar](30) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_TestTable_ID] ON [dbo].[TestTable]
(
[id] ASC
)
ALTER INDEX IX_TestTable_ID ON dbo.TestTable REBUILD
ALTER INDEX IX_TestTable_ID ON dbo.TestTable REORGANIZE

Listing 11.6 TSQL to Rebuild or Reorganize the index

# get the database
$database = Get-Item SQLSERVER:\sql\localhost\default\Databases\MyDB
# get the index, in this case very specific
$index = $database.Tables["TestTable"].Indexes["IX_TestTable_ID"]
# Rebuild the index
$index.Rebuild()
# Reorganize the index
$index.Reorganize()

Listing 11.7 PowerShell and SMO used to Rebuild and Reorganize Indexes

As you can see the code is different and the context is determined differently too. In Listing 11.6 the context is in the current database you are in. In Listing 11.7 the context is set by retrieving the database object from the server and then getting the table inside the database then the index inside the table collection. Then with that object in a variable, execute the method on the object, since the object is the context, it becomes simple to know what to expect on execution of the method. As you have seen in other levels, the objects methods and properties try to help you understand what you are getting by using the object.

To conclude this level I want to offer a suggestion. Sometimes you may get curious about what is actually sent to the engine from SMO and PowerShell. It is usually insightful to see what TSQL is generated by the library. SQL Server Profiler is a great tool is use, even though it has been deprecated. It is not recommended to use Profiler on a production system, but you can still get useful information in a test environment. If you start a trace to look at Batch:Started events, and run the example in Listing 11.7, you can see what gets sent to SQL Server in TSQL. Figure 11.5 shows the TSQL output, retrieved from Profiler, of the two statements that SQL Server executes when the methods are used.

Figure 11.5 Profiler output from SMO statements

As you have seen from the examples in this level, there are many things that you can do with SQL PowerShell. This stairway just scratches the surface in what you can do when administering a database. I hope this has given you a taste of the possibilities. There are many other things you can do with SMO in PowerShell and I give you a challenge to go find something that you do regularly in TSQL or in Management Studio and see if you can do it in SMO with PowerShell.

Wrapping Up – Where can you go from here

Having completed the SQL Server PowerShell Stairway, I want to help you chart a course for continued success in your PowerShell for SQL Server journey. My best advice is keep playing and experimenting with PowerShell and SQL Server scripts. When you start learning, you'll find quickly that your Internet searches seems to reveal PowerShell scripts to solve instantly every problem that plagues you. You end up trying to digest the entire space in a month, or even a week, and end up with a huge number of potentially useful scripts that you barely understand. I advise you to resist the temptation, and try to write scripts of your own so that your learning can proceed at a steady pace. Over the years, I have amassed a large number of scripts that I use and adapt to each new situation.

Listed below are resources, divided into various types, which will give you a start to your journey and includes both links to books as well as online material. You will quickly get to know the people that are talking about PowerShell and SQL Server so you can keep up with them for future ideas

Scripting in General

The resources below are general resources that will help you become more familiar with scripting in general and will have scripts that you can try out or learn more.

• SQL Server Central - http://www.sqlservercentral.com/Search/?q=powershell&s=10&t=afbs&sort=relevance
• Hey Scripting Guy! http://blogs.technet.com/b/heyscriptingguy/
• Powershell.org - http://powershell.org/wp/
• Learn Scripting - https://technet.microsoft.com/en-us/scriptcenter/dd742419

There are more resources out there, but these sites will help you get a good feel for scripting in general and may give you some ideas of how to better tailor PowerShell scripting for your environments.

Tool Building

Tool building will be a very important skill for you to acquire. It is one thing to have scripts that you have built and use, but it is quite another to build your arsenal of scripts into a tool that is more portable and accessible with a single command. Some would just call these tools ‘Modules', but whether they are modules or a collection of functions or cmdlets that you have created, it will be an asset to you wherever you go.

Some tools I have found very useful and that I have used and still use in my every day PowerShell with SQL Server are listed below:

• SQLPSX - http://sqlpsx.codeplex.com/
• PSCX - http://pscx.codeplex.com/
• Out-DataTable on the TechNet script gallery
• Write-DataTable on the TechNet script gallery
• Invoke-Sqlcmd2 on the TechNet script gallery
• Tools on PowerShellDBA.com – http://powershelldba.com

Below are sites that will help you to get started in Tool Building:

• Mike Fal blog on SQL Server Central - http://www.sqlservercentral.com/Authors/Articles/Mike__Fal/1408632
• Don Jones - http://powershell.com/cs/blogs/donjones/default.aspx
• PowerShell Team blog - http://blogs.msdn.com/b/powershell/
• Bruce Payette - http://powershell.org/wp/tag/bruce-payette/
• Ben Miller – http://powershelldba.com

Books

If you're interested in books, take a look at the following:

• Pro PowerShell for Database Developers http://www.apress.com/9781484205426
• Windows PowerShell Cookbook http://shop.oreilly.com/product/0636920024132.do
• https://www.manning.com/books/windows-powershell-in-action-second-edition
• https://www.manning.com/books/powershell-deep-dives
• https://www.manning.com/books/powershell-in-depth-second-edition
• https://www.manning.com/books/powershell-in-practice
• https://www.manning.com/books/learn-powershell-toolmaking-in-a-month-of-lunches
• https://www.packtpub.com/big-data-and-business-intelligence/sql-server-2014-powershell-v5-cookbook

Reference Sites

One thing you can never get enough of is references. This section will give you reference material to refer to for help in anything you are doing.  Remember that one of the best references is Get-Help and Get-Command. These cmdlets will help you get the most out of the modules and scripts you have installed on your machine. Also in the list below are the script repositories that you will be able to use regularly and some codeplex projects that will be useful in your future with PowerShell.

• TechNet Script Center –  https://technet.microsoft.com/en-us/scriptcenter/bb410849.aspx
• TechNet Script Repository - https://gallery.technet.microsoft.com/scriptcenter
• CodePlex site for Source Projects – http://www.codeplex.com
• GitHub site for Source Projects – http://www.github.com
• Powershell.com – http://powershell.com
• Master PowerShell - http://powershell.com/cs/blogs/ebookv2/default.aspx
• PowerShell Tips - http://powershell.com/cs/blogs/tips/
• PowerShell DBA – http://powershelldba.com
• Chad Miller - http://www.sqlservercentral.com/blogs/chadmiller/
• Laerte Junior - https://www.simple-talk.com/author/laerte-junior/
• Allen White - https://www.simple-talk.com/author/allen-white/
• Adam Bertram - http://www.adamtheautomator.com/

Help Sites – Forums

I have learned a great deal by answering questions in the public help forums. You can learn a lot from people who have already been doing what you want to do. There is a great ecosystem out there for asking questions. Below are links (and this is not an exhaustive list) to forums and sites that let you register and ask questions of the experts and get some real help.  All forums are not created equal, but these are some that you can rely on and I am sure there are many more that I have not visited.

• SQL Server Central Forums – http://www.sqlservercentral.com/Forums/Forum1351-1.aspx
• PowerShell.com forums – http://powershell.com/cs/forums/
• TechNet Scripting Guys forum – https://social.technet.microsoft.com/Forums/windowsserver/en-US/home?forum=ITCG
• Scripting Center forums - https://social.technet.microsoft.com/Forums/scriptcenter/en-US/home
• List of Forums for PowerShell – http://learn-powershell.net/2013/04/27/what-forums-can-i-go-to-for-powershell-help/
• Twitter – #PoshHelp - https://twitter.com/search?q=%23poshhelp&src=typd
• Another list – http://learn-powershell.net/powershell-forum-directory/

Thanks for joining me on the journey of SQL Server PowerShell. It has been fun and educational to put it in this format. Please stay in touch out there and don't be afraid to share, share, share.