Click here to monitor SSC
  • Av rating:
  • Total votes: 17
  • Total comments: 9
Laerte Junior

The PoSH DBA - SQLPSX: SQL Server PowerShell Extensions

10 December 2013

Every DBA needs to know about SQLPSX, the PowerShell module library that is built by DBAs for DBAs, and designed to provide intuitive functions around the SMO objects. It makes the automation of database administration easier across all versions of SQL Server since SQL Server 2000. Laerte Junior, who is one of the developers on the open-source project, describes how to use it.

“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:

Get-SqlDatabase -sqlserver QuiGonJinn | #A
Get-SqlStoredProcedure | #B
ForEach-Object { #C
   $Database = $_.dbname #D
   Get-SqlScripter -smo $_ | #E
   Out-File -FilPath "c:\temp\Script\$($Database).sql" –Append #F
}

#A – Connecting to the QuiGonJinn SQL Server instance and return all the user databases
#B – Listing all stored procedures for each database
#C – Starting a loop to each stored procedure
#D – Storing the database name
#E – Scripting the currently stored procedure
#F – Outputting to the .sql file split by Database Name

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.

Note: For a full explanation about SMO see SQL Server Management Objects Reference at Microsoft Developer Network (MSDN) http://bit.ly/U0leMX

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

Note: In several examples in this book, we'll use SQL Server Agent jobs to run a script. In this case you will have to do the same process to make the modules available for the account that will run SQL Agent Job, whether or not it is a proxy account. Another option is to use a machine-global PowerShell profile that applies to all sessions. My previous article covered this.

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 SQL Server Feature Pack http://bit.ly/SIFalD.. 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.

Note This entire procedure is documented in the Documentation tab on the SQLPSX website

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:

Get-SqlServer -sqlserver Obiwan |
Select-Object     Name ,
                  Collation ,
                  Edition,
                  Language,
                  Product,
                  ProductLevel,
                  ServiceAccount,
                  version
  
Name            : obiwan
Collation       : SQL_Latin1_General_CP1_CI_AS
Edition         : Enterprise Edition (64-bit)
Language        : English (United States)
Product         : Microsoft SQL Server
ProductLevel    : RTM
ServiceAccount  : starwars\SQLExecutive
Version         : 11.0.2100

Note: We are only bringing the server information from ObiWan instance. But you can also use this function to get information about a series of instances stored in the c:\ servers\SQLInstances.txt file, that looks like this:

Obiwan
Obiwan\INST1
Obiwan\INST2
QuiGonJinn\INST1
QuiGonJinn\INST2
QuiGonJinn\INST3
......

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:

$DateTime = Get-Date #A
Get-Content c:\servers\SQLInstances.txt | #B
ForEach-Object { #C
   Get-SqlServer $_ | #D
   Select-Object @{N='Date';E={$DateTime}},
         Name ,
         Collation ,
         Edition,
         Language,
         Product,
         ProductLevel,
         ServiceAccount,
         Version #E
}

#A - Store the currently date and time to $datetime variable
#B – Get the SQL Server instances in the SQLinstances.txt and pipe to Foreach-Object cmdlet
#C – Starts a loop to perform operation to each server in process #B
#D – Access the currently SQL Server instance in the loop
#E – Add the currently Date as custom property and Selects only a few properties from 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:

...
Get-SqlServer $_ |
where {$_.version -like '9.*' } | ...

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

...
Get-SqlServer $_ |
Sort-Object Name,Version | ...

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:

Get-SqlServer Obiwan | Select-Object configuration

Configuration
-------------
Microsoft.SqlServer.Management.Smo.Configuration

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 :

Get-SqlServer –sqlserver ObiWan |
Select-Object -ExpandProperty Configuration |
Select-Object -ExpandProperty Properties

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:

Get-SqlServer –sqlserver Obiwan |
Select-Object name,@{n='max degree of 
Parallelism';e={$_.configuration.MaxDegreeOfParallelism.RunValue}}

Note: In the above examples, we display only certain information such as Name, Collation, Edition, Language, Product, Product Level, Version and Service Account. But there is much more to be displayed. Just type the command below to get the full list of members returned by the function:

Get-SQLServer <SQLServerInstance> | Get-Member

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:

Get-SqlDatabase -sqlserver QuiGonJinn

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:

Get-SqlDatabase -sqlserver QuiGonJinn –dbname AdvetureWorks

…or to return all databases including the System ones:

Get-SqlDatabase -sqlserver QuiGonJinn -force

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:

Get-SqlDatabase -sqlserver QuiGonJinn –dbname AdventureWorks |
Get-Member –membertype property

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:

Get-SqlDatabase -sqlserver QuiGonJinn -dbname adventureworks2012 |
Select-Object -ExpandProperty properties |
select name,value

Name                                        Value
----                                        -----
ActiveConnections                               4     
AutoClose                                   False
AutoShrink                                  False
CompatibilityLevel                            110
CreateDate                   8/8/2012 10:04:21 PM
DataSpaceUsage                             115176
DboLogin                                     True
DefaultFileGroup                          PRIMARY
DefaultSchema                                 dbo
ID                                              7
IndexSpaceUsage                             69936
IsAccessible                                 True
IsDbAccessAdmin                              True
IsDbBackupOperator                           True
IsDbDatareader                               True
IsDbDatawriter                               True
IsDbDdlAdmin                                 True
IsDbDenyDatareader                          False
IsDbDenyDatawriter                          False
IsDbOwner                                    True
IsDbSecurityAdmin                            True
IsFullTextEnabled                            True
IsSystemObject                              False
LastBackupDate               1/1/0001 12:00:00 AM
LastDifferentialBackupDate   1/1/0001 12:00:00 AM
LastLogBackupDate            1/1/0001 12:00:00 AM
.....
.....
.....

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:

Get-SQLDatabase –sqlserver Obiwan –dbname Adventureworks2012 |
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:

Get-SQLdatabase –sqlserver Obiwan –dbname Adventureworks2012 |
Get-SQLStoredProcedure –name uspGetBillOfMaterials

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:

Get-SQLdatabase –sqlserver Obiwan –dbname Adventureworks2012 |
Get-SQLStoredProcedure |
Select-Object      Name,
                   DateLastModified ,
                   CreateDate ,
                   ForReplication

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:

Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 |
Get-SqlTable

Note: The function Get-SQLTable has some parameters to filter the table name and schema. For a complete explanation of the function type Get-Help Get-SQLTable -full

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

Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 |
Get-SqlTable –name Person |
Get-SQLIndex

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:

Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 |
Get-SqlTable –name Person |
Get-SQLIndex |
Get-SQLIndexFragmentation

Index_Name                 : IX_Person_LastName_FirstName_MiddleName
Index_ID                   : 2
Depth                      : 2
Pages                      : 106
Rows                       :
MinimumRecordSize          :
MaximumRecordSize          :
AverageRecordSize          :
ForwardedRecords           :
AveragePageDensity         :
IndexType                  : NONCLUSTERED INDEX
PartitionNumber            : 1
GhostRows                  :
VersionGhostRows           :
AverageFragmentation       : 6.60377358490566

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:

Get-SqlDatabase -sqlserver ObiWan -dbname AdventureWorks2012 | #A
Get-SqlTable –name Person | #B
Get-SQLIndex | #C
Get-SQLIndexFragmentation | #D
Where-Object {$_.AverageFragmentation –ge 50} | #E
Select-Object      index_Name,
                   Pages,
                   Rows,
                   IndexType,
                   AverageFragmentation #F

#A – Accessing the Database AdventureWorks2012 in the ObiWan SQL Server instance
#B – Returning the information from the table Person
#C – Accessing its indexes
#D – Listing the fragmentation properties
#E – Filtering only ones that has The property AverageFragmentation above 50
#F – Selection the properties Index_name, Pages,Rows, IndexType, AverageFragmentation

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:

Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 | #A
Get-SqlTable | #B
Get-SqlIndex | #C
foreach-object { #C
   $IndexObject = $_ #D
   $SQLinstanceName = $_.server #E
   $Database = $_.dbname #E
   $SQLTable = $_.table #E
   Get-SqlIndexFragmentation -index $IndexObject | #F
   ForEach-Object { #F
       if (($_.AverageFragmentation -ge 1 -and $_.AverageFragmentation -le 30 -and $_.Pages -ge 0)) { #G
          Write-Host -ForegroundColor Yellow "Performing Defrag - SQL Server : $($SQLInstanceName) Database : $($Database) Table : $($Table) Index : $($_.Index_name)" #H
          Invoke-SqlIndexDefrag -index $IndexName –Verbose #I
       } elseif (($_.AverageFragmentation -gt 30 -and $_.Pages -ge 0)) { #J
          Write-Host -ForegroundColor Yellow "Performing Rebuild - SQL Server : $($SQLInstanceName) Database : $($Database) Table : $($SQLTable) index : $($_.Index_name)" #K
          Invoke-SqlIndexRebuild -index $IndexObject –Verbose #L
       }
   }
}

Performing Rebuild - SQL Server : ObiWan Database : adventureworks2012 
Table : Employee index : AK_Employee_LoginID
VERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics
Performing Rebuild - SQL Server : ObiWan Database : adventureworks2012 
Table : Employee index : AK_Employee_NationalIDNumber
VERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics
Performing Rebuild - SQL Server : ObiWan Database : adventureworks2012 
Table : EmployeeDepartmentHistory index : 
PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID
VERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics
Performing Rebuild - SQL Server : ObiWan Database : adventureworks2012 
Table : EmployeePayHistory index : 
PK_EmployeePayHistory_BusinessEntityID_RateChangeDate
VERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics

#A – Connect to Database AdventureWorks2012 in the SQL instance ObiWan
#B – Return the Tables properties
#C – Return Information about the indexes and starts a loop for each one
#D – Store the Object Index in the $IndexObject Variable
#E – Store the name of SQL Server instance, Database and Table in the variables $SQLInstanceName ,$Database and $SQLTable
#F – Return the information about the fragmentation of the Index and starts a loop for each one
#G – Test if the fragmentation is less than 10 and greater than 30 and page counts greater than 1000
#H- Display in the screen information about what SQL Server instance, database, Table and Index will perform the Index Reorganize
#I – Perform the Index Reorganize
#J – Test the fragmentation is above 30 and page count greater than 1000
#K - Display in the screen information about what SQL Server instance, database, Table and Index will perform the rebuild
#L – Perform the Index Rebuild

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:

Get-Command Get-SQL*

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 :

Get-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |
Get-SqlStoredProcedure -Name uspGetWhereUsedProductID |
Get-SqlScripter

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:

Get-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |
Get-SqlStoredProcedure -Name uspGetWhereUsedProductID |
Get-SqlScripter |
Out-File c:\temp\scripts\ uspGetWhereUsedProductID.sql

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:

$ScriptingOptions = New-SqlScriptingOptions
$ScriptingOptions.IncludeIfNotExists = $true
Get-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |
Get-SqlStoredProcedure -Name uspGetWhereUsedProductID |
Get-SqlScripter -scriptingOptions $ScriptingOptions

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

$ScriptingOptions = New-SqlScriptingOptions
$ScriptingOptions.IncludeIfNotExists = $true
$ScriptingOptions.NoCollation = $true
Get-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |
Get-SqlTable -Name Vendor |
Get-SqlScripter -scriptingOptions $ScriptingOptions

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:

New-SQLScriptingOptions | Get-Member

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

Note: When using the term “Runspaces” I’m referring to the method of creating an instance of PowerShell from the command line, which you can interact with, by creating and modifying its elements, as providers, commands or functions. It is something like you open a new PowerShell session, but using command line. For a further reader, look at MSDN Link http://bit.ly/SrtrFV.

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:

$global:PathExportFile = "c:\DOCSQL" #A
$global:timestamp = Get-Date -Format "yyyyMMdd_hhmm" #B
Get-Content c:\PoshForSQLServer2012\SQLInstances.txt | #C
Split-job { #C
   ForEach-Object { #D
      if ((Test-SQL -ServerInstance $_).result) { #E
         try {
            $Server = Get-SqlServer $_ #F
            $ServerNameToPath = ($server.Name -replace '\\','_') #G
            $CSVFIleName = "$($ServerNameToPath)_$($timestamp).csv" #H
            $ServerPath = (Join-Path -Path $PathExportFile -ChildPath $ServerNameToPath) #I
            if ( -not (Test-Path -path $ServerPath -PathType Container) ) { #J
               New-Item -Path $ServerPath -ItemType Directory -Force | Out-Null #K
            }
            $FullPathFile = Join-Path -Path $ServerPath -ChildPath $CSVFIleName #L
            $PropertiesHash = @{} #M
            $server.Properties | foreach {$PropertiesHash.add($_.Name,$_.Value) } #N
            $server.Configuration.Properties | foreach { $PropertiesHash.add($_.DisplayName,$_.RunValue) } #O
            $PropertiesHash.GetEnumerator() | #P
            foreach {$_} | #Q
            select @{E={$_.name};N='Configuration'},
               @{E={$_.value};N='Value'} | #R
            Export-Csv $FullPathFile -NoTypeInformation #S
         } catch {
            $ErrorMessage = "$(Get-date) $($Error[0])" #T
            Write-Error $ErrorMessage #U
            $ErrorMessage |
            Out-File (join-path $PWD -ChildPath "DocError.txt") –Append #V
         }
      } else {
         $ErrorMessage = "$(Get-date) - SQL Server Instance $($_) unreachable" #X
         Write-Warning $ErrorMessage #Y
         $ErrorMessage |
         Out-File (join-path $PWD -ChildPath "DocError.txt") –Append #V
      }
   }
} -InitializeScript {Import-Module functions -Force;Import-Module SQLServer -Force } -variable PathExportFile,timestamp -noprogress

Remove-variable PathExportFile -ErrorAction SilentlyContinue #Z
Remove-variable Timestamp -ErrorAction SilentlyContinue #Z

#A and #B – Define the root path to the .CSV files and the timestamp variables as global. These variables will be passed to the Split-Job function. The PathExportFile variable stores the root path to the ,CSV files and Timestamp to build their names
#C and #D – Read the SQLInstances text file with the name of all SQL Server instances and start a runspace for each one
#E- Test the connection for the current SQL Server instance
#F – Stores the object returned by Get-SQLServer into a variable
#G – Replace the “\” characters to ” _” to create the .CSV file name and create a folder to the currently SQL Server instance. The “\” character is a invalid character in file and folder names.
#H – Create the .CSV file name by joining the SQL Server instance Name variable (G) with the Timestamp
#I – Join the root path with the SQL Server instance name variable (G) to create a full path to the CSV File
#J and #K – Check to see if the full path exists (I). If not, create it.
#L – Join the full path with the CSV File Name (G)
#M – Initialize a hashtable to store the SQL Server instance information
#N – Return the properties from the SQL Server instance and add to the hashtable
#O –Return the properties from the Configuration property from the SQL Server Server object and add to the hashtable
#P and #Q – Get the enumerator from the hashtable and starts a loop on it. This action is performed to create the CSV file with all properties in rows, otherwise it would be created on columns, hampering visibility, as there would be many columns.
#R –Select only the Name and Value properties from the hashtable and changing the “Name” property name to “Configuration”
#S – Export to the CSV file in the specific folder to its name.
#T – Store the error message and the currently date
#U – Write the PowerShell error message in the error stream
#V – Outputting the error message to a text file called “DocError.txt” in the currently path ($PWD environment variable)
#X - Storing the custom error message and the currently date
#Z – Removing the global variables.

Listing - Gathering SQL Server instances information - Server level

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

} -InitializeScript {Import-Module functions -Force; Import-Module Sqlserver -Force} -Variable PathExportFile,timestamp -noprogress

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:

CD \DOCSQL

.\ListSQLInfo.ps1

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:

PowerShell.exe "c:\DocSQL\ListSQLInfo.ps1"

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.

Laerte Junior

Author profile:

Laerte Junior is a PowerShell MVP and, through his technology blog and simple-talk articles, an active member of the Microsoft community in Brasil. He is a skilled Principal Database Architect, Developer, and Administrator, specializing in SQL Server and Powershell Programming with over 8 years of hands-on experience. He holds a degree in Computer Science, has been awarded a number of certifications (including MCDBA), and is an expert in SQL Server 2000 / SQL Server 2005 / SQL Server 2008 technologies. He also organizes, and is a speaker at microsoft community events, attracting hundreds of attendees. Laerte has also recently become a Friend of Redgate in Brasil, has taught classes at universities, and produced webcasts for the community.

You should follow him on Twitter as @LaerteSQLDBA

Search for other articles by Laerte Junior

Rate this article:   Avg rating: from a total of 17 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Defrag part:Get-SqlDatabase
Posted by: Jorge Serres (not signed in)
Posted on: Saturday, December 21, 2013 at 2:41 PM
Message: Hi Laerte,
good article but it's got some errors! pls review it b4 posting otherwise makes us doubt quality....I do like your articles and trust the code. However, I usually catch some errors...the index defrag does not work smoothly...It'll miss out on some tables

Subject: Get-sqldatabse
Posted by: Laerte (not signed in)
Posted on: Saturday, December 21, 2013 at 3:38 PM
Message: Sure Jorge, but can you be more specific my friend ? I dont get any bugs doing here. Error message it wiould be good. Also, you can ping me at laertesqldba@outlook.com

Subject: Running .\ListSQLInfo.ps1
Posted by: demisco (not signed in)
Posted on: Monday, December 23, 2013 at 7:18 AM
Message: Hello,

Very nice article. I have a strange issue though. Trying to run the ListSQLInfo.ps1 and it gives me the error below:

PS C:\DBA\POSHScripts> .\ListSQLInfo.ps1
Missing closing '}' in statement block.
At C:\DBA\POSHScripts\ListSQLInfo.ps1:40 char:59
+ Remove-variable Timestamp -ErrorAction SilentlyContinue #Z <<<<
+ CategoryInfo : ParserError: (CloseBraceToken:TokenId) [], ParseException
+ FullyQualifiedErrorId : MissingEndCurlyBrace

I run out of ides :P

Thanks a lot
demisco

Subject: Laerte
Posted by: Running .\ListSQLInfo.ps1 (not signed in)
Posted on: Friday, December 27, 2013 at 2:09 AM
Message: Demisco, I am running without problems. Do you have the functions.psm1 and sqlpsx in some of the $psmodulepath variable, or in case of the posh2.0 in your profile ? what is posh and sql version ? I am running 3.0 and SQL 2012. Are you sure that you copy the full code in the page ? Tkx man

Subject: Running .\ListSQLInfo.ps1
Posted by: demisco (not signed in)
Posted on: Friday, December 27, 2013 at 3:05 AM
Message: I have functions.psm1 and sqlpsx in C:\Windows\System32\WindowsPowerShell\v1.0\Modules
The $ENV:PSModulePath points to C:\Users\myusername\Documents\WindowsPowerShell\Modules;C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
The version of powershell is 2.0
SQL Version is 2008 R1
I do not have any profiles........i was using the command import-module to load the required modules (Functions and SQLPSX) into my powershell command line.
I copy/pasted the code and verified may times that it was correct :)

Thanks a lot for your time and reply.


Subject: Running .\ListSQLInfo.ps1
Posted by: Laerte (not signed in)
Posted on: Friday, December 27, 2013 at 4:07 AM
Message: My friend, can you ping me at laertesqldba@outlook.com ? lets see what is happeing . Thanks :)

Subject: Error Message
Posted by: Laerte (not signed in)
Posted on: Friday, December 27, 2013 at 7:34 AM
Message: Guys, already sent the ListSQLInfo.ps1 to the Editor Team to download, please do not copy and paste the code :)

Subject: Error Message
Posted by: Laerte (not signed in)
Posted on: Friday, December 27, 2013 at 7:36 AM
Message: Or if you do it, check for wild chars in the end of these linesand remove it :)

Out-File (join-path $PWD -ChildPath "DocError.txt") –Append #V
}
} else {
$ErrorMessage = "$(Get-date) - SQL Server Instance $($_) unreachable" #X
Write-Warning $ErrorMessage #Y
$ErrorMessage |
Out-File (join-path $PWD -ChildPath "DocError.txt") –Append #V

Subject: re: Running .\ListSQLInfo.ps1
Posted by: Andrew Clarke (view profile)
Posted on: Saturday, December 28, 2013 at 10:44 AM
Message: The file is now in the head of the article, in the grey box next to the title

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.