“Productivity is being able to do things that you were never able to do before”

– Franz Kafka

What is SQLPSX?

Imagine that you are obliged to script out the T-SQL source code of all the stored procedures for all the databases on a particular SQL server instance in order, maybe, to audit any changes. You can do this by using the SSMS script dialogs of course. But, let’s make the situation a bit more complex. This process must be done weekly and without user input, that is, without the DBA sitting in front of the SSMS tool. There are drawbacks of attempting to use SSMS for this task.

The tool does not have the option to choose all databases. You have to do the process for each database in turn.

SSMS provides no way to automate the generation of the scripts. Each week you will have to do the whole process again, click by error-prone, tedious, click.

Now let’s provide some additional requirements to make it more like real life. You have fifty databases on the server that we’ll call QuiGonJinn. If the time for each database to generate scripts its five minutes, the total time to do this process will be two hundred and fifty minutes or, in other words, four hours. And remember that this process needs to happen every week. As a DBA skilled in PowerShell, you can accommodate these requirements by using a series of SQLPSX functions, in a command line of PowerShell, like this:

This script produces a .sql file for each database in the QuiGonJinn instance that contains the source TSQL of all the stored procedures, and stores it in the path c:\temp\scripts. To automate this process, you can simply set up a SQL Server Agent job to run the script on the required schedule or run it on Windows Scheduler on a server.

We used SQLPSX to do this. It saved a lot of effort,

SQLPSX can increase your day-to-day productivity. With it, you no longer need to lose at least four hours each week in mind-numbing tedium to perform this process. Because the process is automated, you’ve eliminated the possibility of user-error in running the SSMS tool, and you’ve increased the chances of getting home in the evening before your children go to bed.

The SQLPSX modules are compatible with all versions of SQL Server, starting with SQL Server 2000. According with its documentation, “SQLPSX consists of 13 modules with 163 advanced functions, 2 cmdlets and 7 scripts for working with ADO.NET, SMO, Agent, RMO, SSIS, SQL script files, PBM, Oracle and MySQL and using PowerShell ISE as a SQL and Oracle query tool. In addition optional backend databases and SQL Server Reporting Services 2008 reports are provided with SQL Server and PBM modules”. In other words, SQLPSX is the best and most complete library in PowerShell for SQL Server.

The libraries work directly with SMO objects (SQL Server Management Objects), so that each SQLPSX function returns an SMO object. For example, the Get-SQLServer returns an SMO object type Microsoft.SqlServer.­Management.Smo.Server. This means that you can use SQLPSX when you choose to , or interact directly with SMO if you need to, or want to. The distinction of SQLPSX is that frequently-used scripts are pre-packaged into PowerShell functions, allowing you to concentrate on the task on hand, rather than writing great gobs of mostly redundant SMO code for each new task, and then having to test it all.

SQLPSX allows you to reduce the amount of code you write in order to perform regular tasks such as scripting databases or setting up a complex, multi-machine task to, for example, configure replication. The original SQLPSX modules were created by a friend and mentor Chad Miller. It is now an active community project, and I am proud to be a developer participating in this ambitious project.

The SQLPSX cmdlets library today has over 15,000 downloads, and more than 160 people following the project. The forum is constantly monitored, questions are answered and bugs are fixed. The library is continuously updated, generating releases for bugs and improvements. You can download it from Codeplex-SQLPSX -> http://sqlpsx.codeplex.com/

Installing SQLPSX

In the article on setting up your environment, we provided instructions on how to install and configure SQLPSX in the profile, but now we’ll go a little deeper into this process.

From the http://sqlpsx.codeplex.com/ page, go to the download tab. There you will find two types of downloads; one that uses an MSI Installer and another with zipped modules as illustrated in Figure 1:

 

Figure 1 – The SQLPSX Site with the download options

For this example, we will download the.zip files and perform a manual setup. Another very important file in this process is the readme.txt, so be sure to also read it.

Click on the zip version in “other available downloads“, download the zip file and save it in a folder on your machine. By default the zip file is locked. In windows Explorer, Right click the file, pick ‘properties‘ in the context menu, and down right, click on unblock. I’m not referring to a specific file name because by the time you read this article a newer version may have been released.

Unzipping this file it will create a folder called “modules” and within this folder, 13 other folders with the name of each module within each folder is the individual module code as shown in Figure 2:

 

Figure 2 – Listing all SQLPSX modules

As we saw in my previous article, we need to put the modules’ code in one of the paths that the environment variable $ENV:PSModulePath points to. In the case we copy all these folders to the path of the user, or %WindowsUserProfile%\Documents\WindowsPowerShell\Modules. In my case all the folders were copied to C:\Users\Laerte.Junior\Documents\WindowsPowerShell\Modules

The SQLPSX libraries directly use the SQL Server Management Objects, or SMO, which is a collection of objects in a .NET library that provides you with the means to manage SQL Server programmatically: Therefore you need to have SMO installed on the machine that uses SQLPSX. If you’ve installed SQL Server Management Studio, SMO is already included: If not, then you can install it separately from the SMO NuGet Package. If you are installing SMO manually, we strongly recommend using the latest version of SMO because some features may not be accessible in very old versions of SMO.

Working with SQLPSX

The SQLPSX modules are fairly straightforward to understand even if you haven’t read the excellent documentation available from typing get-help followed by the name of the SQLPSX function. The function names, noun and verb, follow PowerShell naming conventions, and are therefore intuitive and consistent with each functions’ purpose. If you want to find the functions that refer to tables, for example, look for the Get-SQLTable cmdlet or for stored procedures select Get-SQLStoredProcedure. Because each function is abstracted into a PowerShell cmdlet/function, you don’t have to worry about which SQL Server system view to use, or find your T-SQL script that performs this operation. Also, you don’t need to worry about what runs on each version of SQL Server: SMO looks after this for you. Simply use the Get-SQLDatabase and this function will return all the necessary information. The next sections cover some often-used operations in SQL Server that can be easily done using SQLPSX.

General SQL Server – Server Settings

Some basic information that we may need to use frequently in our jobs administering SQL Server is stored in the server level settings of an SQL Server instance. The function Get-SQL Server returns all members you need for this process. For example, to list information such as name, collation, etc. of the ObiWan SQL Server instance, you can issue the following PowerShell statement:

We’ll also add the date that you gathered the information. Notice that we pass the date as custom property to the Select-Object because it is not a property of Get-SQLServer function:

We can also check for a specific set of SQL Server instances by SQL Server version. Regardless of the exact version number, you can use the starting number in the version number to identify each major version, as you can see in Table X:

SQL Server Version	Starts with
2000	8.
2005	9.
2008	10.
2008 R2	10.
2012	11.

Therefore, if we want to identify whether we have any instances of SQL Server 2005 amongst our servers, we can filter for “9.” in the property version:

Or to list all instances ordering by name and version properties:

Another very important set of instance level properties is returned by the T-SQL system procedure sp_configure. To have access to these using SQLPSX, we need to extract it from the configuration property returned by the function Get-SQLServer. The object returned by this function is a complex one in that it is an object that contains other objects. Thus Select-Object will return the specification of an internal object:

To access this object we have to then expand the two properties ‘Configuration’ and ‘Properties’ that are properties of the object returned by Get-SQLServer :

Another way is by directly accessing the property within the Configuration using custom properties. In the example we are using the Max Degree of Parallelism:

Database Settings and Objects

Now that we have the means to get the general information about the SQL Server instances, we will see how to access an instance’s databases. The function Get-SQLDatabase has this functionality. It returns generic information about one database or the collection of databases on a given instance. To return all the user databases in the QuiGonJinn SQL Server instance just type:

The function has two parameters. The -dbname parameter selects only the specified database. The parameter -force also includes the System Databases. So, to list the information in the database AdventureWorks2012 in the QuiGonJinn instance type:

…or to return all databases including the System ones:

We already have seen that we can pipe the output of a cmdlet to the Get-Member cmdlet to discover all the properties of any object in PowerShell. This is fine for many purposes, but when you do this, all members are listed including methods…etc. To list only the properties, we need to use the -membertype parameter:

The -membertype parameter will still return all the “property” properties, including those containing objects like the collection of tables. There is a “configuration” property at a database level, analogous to the instance-level configuration property returned by the Get-SQLServer function.

An interesting fact is that each SMO object also contains a specific property named “properties.” It contains all the other properties using simple data types, such as string or int. Listing all the properties returned by Get-SQLDatabase, which is a type SMO Microsoft.SqlServer.Management.Smo.Database, just expand the “properties” property and select Name and Value, as shown in the code below:

As you can see, with Get-SQLDatabase we have all of the database information, such as date of creation, autoshrink configuration option and last backup date.

Every database contains objects such as stored procedures, tables, views…etc. To access these objects, we use the specific functions to each one. As I said before, SQLPSX function names are quite intuitive, so to access the stored procedures collection and properties of each stored procedure of the ObiWan SQL Server instance and AdventureWorks2012 database we use the Get-SQLStoredProcedure:

The function has the parameter -name, and that means to access the properties of a particular stored procedure, say uspGetBillOfMaterials, and just pass its name as a parameter:

We can select the properties Name, DateLastModified, CreateDate, and whether a stored procedure is set for replication by just piping Get-SQLStoredProcedure output to the Select-Object cmdlet:

There are a number of database objects you can get this way. In this example, we’ll get information about tables using the function Get-SQLTable:

In the case of the tables we can go deeper and view all the properties of its indexes using the Get-SQLIndex function:

Figure 3 shows all the properties that the function Get-SQLIndex returns:

Figure 3 -All properties from Get-SQLIndex

Once we have the index objects, we can return fragmentation information using the Get-SQLIndexFragmentation function. To check how fragmented the indexes of the Person table are, just type:

We can see that the object returned from this function has several properties and that one of them is the AverageFragmentation. This means that we can filter by fragmentation, choosing only those indexes above 50% fragmentation, by using the code:

This way, we can use the formula to check whether fragmentation is greater than 10% and less than 30% and the page count> 1000, and if it is, we reorganize the index, otherwise we rebuild it using the functions Invoke-SQLIndexDefrag and Invoke-SQLIndexRebuild. The following listing shows the code to perform this operation for all tables and indexes in the database Adventureworks2012 and ObiWan SQL Server instance:

Listing – Performing Reorganize or Rebuild Indexes

As you can imagine from this listing, we can perform a complex operation with a few command lines that can be applied to all tables and indexes. There are many functions that SQLPSX have for Database and Server information, more than we can list here. For the full list type:

Scripting SQL Server Objects

As we saw at the beginning of the article, scripting SQL Server objects using SSMS can be a painful operation for a busy DBA with responsibility for many servers and databases, especially if you need to automate this process.

The function Get-SQLScripter fills this gap precisely. With it you have the flexibility needed because you can get the T-SQL script to CREATE or DELETE any SQL Server object. Let’s say you need to script the stored procedure uspGetWhereUsedProductID in the AdventureWorks2012 Database and QuiGonJinn SQL Server instance, the code is :

In this case the output is returned to the screen, but we can direct to a .sql file by just piping it to the Out-File cmdlet:

Similarly you can perform this operation with any other SQL Server object, such as tables, views or triggers.

Another very important group of options in the scripting process using SQLPSX are the ones that control the many ways that the scripting can be performed, such as adding "If Not Exists" at the beginning of the script to create the object only if it doesn’t already exist, or opting whether to specify the Collation clause when scripting Tables. In this case you have to set a variable with the New-SQLScriptingOptions function and select the options that you are interested in. The example below demonstrates how to add "IF Not Exists" in the script of the stored procedure uspGetWhereUsedProductID:

To add the “If Not exists”, but remove the “Collate” clause in the script in the Vendor table:

The Figure 4 illustrates the output:

Figure 4 -Vendor table with scripting options

As you can see, SQLPSX simplifies a very important task in the DBA’s day-to-day job. To return all scripting options used in New-SQLScriptingOptions function, type:

Deploy your first solution using SQLPSX

Anyone with many SQL Server instances to manage will need to automate the process of gathering information about the server-level configuration (sp_configure) of each one. For better organization, this process should be run monthly, and it is likely that you’ll need to store this information as a CSV file in a given path, with separate folders for each instance.

The work window you have for this process is not very large, so the process has to run as fast as possible, so synchronous mode is out of the question. One way to do this is to create a T-SQL script in each instance and run it with a SQL Agent job in each one. We can also think of creating an SSIS package to run this script on all servers, but both solutions have their limitations compared with using PowerShell.

If you were to use SSIS to create a flexible solution, you must create a separate step to gather instance information and configure the SSIS package for run each instance in turn asynchronously. You must have an advanced knowledge of the SSIS tool, and the means to maintain the package, so this may be difficult if you don’t have the SSIS prowess in-house.

if we’re serious about flexibility and minimizing the impact of maintenance, you definitely will not want to create or run a T-SQL script and SQL Agent job in each instance.

With PowerShell and SQLPSX, you have fewer lines of code, and you can run it on any SQL Server instances you can access. That’s because they are listed in a .txt file. If you want to add a new instance in the gathering or remove one, simply change the .txt file.

In addition, we have the PowerShell function Split-Job, which is part of the Functions.PSM1 module in the Toolkit file, which in turn to run the collection for each instance on a separate runspace and controlling it queue, or in other words, using asynchronous mode.

Before looking at the code, let’s look at and document the process flow. This is shown in Figure 5

Figure 5 -Flowchart for the process of gathering information from SQL Server instances

Now that we understand the process, let’s see the code. It is displayed in this Listing:

Listing – Gathering SQL Server instances information – Server level

The process (C), Split-Job, has some interesting parameters to check :

The -InitializeScript is the modules that I want to import and the -Variable parameter sends the variables chosen to the runspace. This is required because as the process inside Split-Job will run in another runspace, all the variables and functions from SQLPSX will be not visible.

The next step is create a path called C:\DocSQL and save this script as ListSQLInfo.ps1.Now it is just to navigate to the C:\DocSQL and run the script, typing:

The output will be the information from all SQL Server instances listed in the .txt file, in separated folders with the specific CSV for every instance inside them as shown by Figure 6

 

Figure 6 -Specific folders to each SQL Server instance

If we navigate to the folder ObiWan_Inst1 we will find the Obiwan_INST1_20121019_0913.CSV file, as shown in Figure 7:

 

Figure 7 – CSV File to the ObiWan\Inst1 SQL Server instance with the information gathered

And, by opening the ObIWan_Inst1_21021019_0913.csv file in Excel, we will have all the information gathered for the ObiWan\Inst1 SQL Server instance. The file has 133 lines of configurations options and Figure 8 illustrates some of them:

Figure 8 -First properties of the ObiWan\Inst1 CSV File

Now we need to automate this process. Let’s use the SQL Agent job for this. On the machine that will run the script, create a SQL Server Agent Job called GatheringInstanceInfo and add a step type CmdExec. Call it Step_1 and type in the command line:

Figure 9 illustrates the process:

Figure 9-Creating a SQL Server Agent Job to run the script

Okay, now we only need to schedule the job to run monthly and we have the process completely automated. After all, it is all about solutions. Just remember that the account that is running the SQL Agent job needs to have rights to create the files.

Summary

One of the tools that is designed to buy time for the busy DBA is SQLPSX. It works with PowerShell and SMO to make it all easier and quicker to use. We covered how to install SQLPSX and why to use it. The next step was driving into some day-to-day examples, as listing SQL Server instances information and Databases.

In order to show that its power went way beyond mere information-gathering, we also showed to perform a very important operation, to check a table’s fragmentation and how to fix it. To finish the article, we deployed our first solution using SQLPSX and learned how to schedule it in the SQL Server agent jobs.