{"id":1735,"date":"2013-12-10T00:00:00","date_gmt":"2013-12-10T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/the-posh-dba-sqlpsx-sql-server-powershell-extensions\/"},"modified":"2021-08-24T13:39:49","modified_gmt":"2021-08-24T13:39:49","slug":"the-posh-dba-sqlpsx-sql-server-powershell-extensions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/the-posh-dba-sqlpsx-sql-server-powershell-extensions\/","title":{"rendered":"The PoSH DBA – SQLPSX: SQL Server PowerShell Extensions"},"content":{"rendered":"
\n

\t“Productivity is being able to do things that you were never able to do before”<\/p>\n

– Franz Kafka<\/p>\n

What is SQLPSX?<\/h2>\n

\tImagine 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. <\/p>\n

\tThe tool does not have the option to choose all databases. You have to do the process for each database in turn. <\/p>\n

\tSSMS 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. <\/p>\n

\tNow 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<\/strong>. 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: <\/p>\n

Get-SqlDatabase -sqlserver QuiGonJinn | #A\r\nGet-SqlStoredProcedure | #B\r\nForEach-Object { #C\r\n   $Database = $_.dbname #D\r\n   Get-SqlScripter -smo $_ | #E\r\n   Out-File -FilPath \"c:\\temp\\Script\\$($Database).sql\" -Append #F\r\n}\r\n\r\n#A - Connecting to the QuiGonJinn SQL Server instance and return all the user databases\r\n#B - Listing all stored procedures for each database\r\n#C - Starting a loop to each stored procedure\r\n#D - Storing the database name\r\n#E - Scripting the currently stored procedure\r\n#F - Outputting to the .sql file split by Database Name\r\n<\/pre>\n
 \tThis script produces a .sql file for each database in the QuiGonJinn<\/strong> instance that contains the source TSQL of all the stored procedures, and stores it in the path c:\\temp\\scripts<\/code>. 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. <\/p>\n
 \tWe used SQLPSX to do this. It saved a lot of effort, <\/p>\n
 \tSQLPSX 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. <\/p>\n
 \tThe 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. <\/p>\n
 \tThe libraries work directly with SMO objects (SQL Server Management Objects), so that each SQLPSX function returns an SMO object. For example, the Get-SQLServer<\/code> returns an SMO object type Microsoft.SqlServer.­Management.Smo.Server<\/code>. 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. <\/p>\n
\n
 \tNote<\/strong>: For a full explanation about SMO see SQL Server Management Objects Reference at Microsoft Developer Network (MSDN) http:\/\/bit.ly\/U0leMX<\/a> <\/p>\n<\/p><\/div>\n
 \tSQLPSX 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. <\/p>\n
 \tThe 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\/<\/a> <\/p>\n
Installing SQLPSX<\/h2>\n
 \tIn 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. <\/p>\n
 \tFrom the http:\/\/sqlpsx.codeplex.com\/<\/a> 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 <\/strong>1: <\/p>\n
 \t <\/p>\n
 \t\"1911-clip_image001-340x446.jpg\"<\/p>\n
 \tFigure 1 – The SQLPSX Site with the download options <\/p>\n
 \tFor 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. <\/p>\n
 \t Click on the zip version in “other available downloads<\/em>“, 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<\/em>‘ in the context menu, and down right, click on unblock<\/em>. I’m not referring to a specific file name because by the time you read this article a newer version may have been released. <\/p>\n
 \tUnzipping 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 <\/strong>2: <\/p>\n
 \t \"1911-clip_image002-420x409.jpg\"<\/p>\n
 \tFigure 2 – Listing all SQLPSX modules <\/p>\n
 \tAs 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<\/code>. In my case all the folders were copied to C:\\Users\\Laerte.Junior\\Documents\\WindowsPowerShell\\Modules<\/code> <\/p>\n
\n
 \tNote<\/strong>: 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. <\/p>\n<\/p><\/div>\n
 \tThe 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<\/a>. 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. <\/p>\n
\n
 \tNote This entire procedure is documented in the Documentation tab on the SQLPSX website <\/p>\n<\/p><\/div>\n
Working with SQLPSX<\/h2>\n
 \tThe 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<\/code> cmdlet or for stored procedures select Get-SQLStoredProcedure<\/code>. 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<\/code> 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. <\/p>\n
General SQL Server – Server Settings <\/h3>\n
 \tSome 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<\/code> returns all members you need for this process. For example, to list information such as name, collation, etc. of the ObiWan<\/strong> SQL Server instance, you can issue the following PowerShell statement: <\/p>\n
Get-SqlServer -sqlserver Obiwan |\r\nSelect-Object     Name ,\r\n                  Collation ,\r\n                  Edition,\r\n                  Language,\r\n                  Product,\r\n                  ProductLevel,\r\n                  ServiceAccount,\r\n                  version\r\n  \r\nName            : obiwan\r\nCollation       : SQL_Latin1_General_CP1_CI_AS\r\nEdition         : Enterprise Edition (64-bit)\r\nLanguage        : English (United States)\r\nProduct         : Microsoft SQL Server\r\nProductLevel    : RTM\r\nServiceAccount  : starwars\\SQLExecutive\r\nVersion         : 11.0.2100\r\n<\/pre>\n
\n
 \tNote<\/strong>: We are only bringing the server information from ObiWan<\/strong> instance. But you can also use this function to get information about a series of instances stored in the c:\\ servers\\SQLInstances.txt<\/code> file, that looks like this: <\/p>\n<\/p><\/div>\n
Obiwan\r\nObiwan\\INST1\r\nObiwan\\INST2\r\nQuiGonJinn\\INST1\r\nQuiGonJinn\\INST2\r\nQuiGonJinn\\INST3\r\n......\r\n<\/pre>\n
 \tWe’ll also add the date that you gathered the information. Notice that we pass the date as custom property to the Select-Object<\/code> because it is not a property of Get-SQLServer<\/code> function: <\/p>\n
$DateTime = Get-Date #A\r\nGet-Content c:\\servers\\SQLInstances.txt | #B\r\nForEach-Object { #C\r\n   Get-SqlServer $_ | #D\r\n   Select-Object @{N='Date';E={$DateTime}},\r\n         Name ,\r\n         Collation ,\r\n         Edition,\r\n         Language,\r\n         Product,\r\n         ProductLevel,\r\n         ServiceAccount,\r\n         Version #E\r\n}\r\n\r\n#A - Store the currently date and time to $datetime variable\r\n#B - Get the SQL Server instances in the SQLinstances.txt and pipe to Foreach-Object cmdlet\r\n#C - Starts a loop to perform operation to each server in process #B\r\n#D - Access the currently SQL Server instance in the loop\r\n#E - Add the currently Date as custom property and Selects only a few properties from Get-SQLServer function\r\n<\/pre>\n
 \tWe 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: <\/p>\n\n\n\n\n\n\n\n\n
\n
SQL Server Version<\/em><\/strong><\/p>\n<\/td>\n
\n
Starts with<\/em><\/strong><\/p>\n<\/td>\n<\/tr>\n
\n
2000<\/strong><\/p>\n<\/td>\n
\n
8.<\/p>\n<\/td>\n<\/tr>\n
\n
2005<\/strong><\/p>\n<\/td>\n
\n
9.<\/p>\n<\/td>\n<\/tr>\n
\n
2008<\/strong><\/p>\n<\/td>\n
\n
10.<\/p>\n<\/td>\n<\/tr>\n
\n
2008 R2<\/strong><\/p>\n<\/td>\n
\n
10.<\/p>\n<\/td>\n<\/tr>\n
\n
2012<\/strong><\/p>\n<\/td>\n
\n
11.<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 \tTherefore, 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: <\/p>\n
...\r\nGet-SqlServer $_ |\r\nwhere {$_.version -like '9.*' } | ...\r\n<\/pre>\n
 \tOr to list all instances ordering by name and version properties: <\/p>\n
...\r\nGet-SqlServer $_ |\r\nSort-Object Name,Version | ...\r\n<\/pre>\n
 \tAnother very important set of instance level properties is returned by the T-SQL system procedure sp_configure<\/code>. To have access to these using SQLPSX, we need to extract it from the configuration property returned by the function Get-SQLServer<\/code>. The object returned by this function is a complex one in that it is an object that contains other objects. Thus Select-Object<\/code> will return the specification of an internal object: <\/p>\n
Get-SqlServer Obiwan | Select-Object configuration\r\n\r\nConfiguration\r\n-------------\r\nMicrosoft.SqlServer.Management.Smo.Configuration\r\n<\/pre>\n
 \tTo access this object we have to then expand the two properties ‘Configuration’ and ‘Properties’ that are properties of the object returned by Get-SQLServer : <\/p>\n
Get-SqlServer -sqlserver ObiWan |\r\nSelect-Object -ExpandProperty Configuration |\r\nSelect-Object -ExpandProperty Properties\r\n<\/pre>\n
 \tAnother way is by directly accessing the property within the Configuration using custom properties. In the example we are using the Max Degree of Parallelism: <\/p>\n
Get-SqlServer -sqlserver Obiwan |\r\nSelect-Object name,@{n='max degree of \r\nParallelism';e={$_.configuration.MaxDegreeOfParallelism.RunValue}}\r\n<\/pre>\n
\n
 \tNote<\/strong>: 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:<\/p>\n
Get-SQLServer <SQLServerInstance> | Get-Member<\/code> <\/p>\n<\/p><\/div>\n
Database Settings and Objects<\/h3>\n
 \tNow 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<\/strong> SQL Server instance just type: <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn\r\n<\/pre>\n
 \tThe 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<\/strong> instance type: <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn -dbname AdvetureWorks\r\n<\/pre>\n
 \t…or to return all databases including the System ones: <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn -force\r\n<\/pre>\n
 \tWe already have seen that we can pipe the output of a cmdlet to the Get-Member<\/code> 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<\/code> parameter: <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn -dbname AdventureWorks |\r\nGet-Member -membertype property\r\n<\/pre>\n
 \tThe -membertype<\/code> parameter will still return all the “property<\/em>” 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<\/code> function. <\/p>\n
 \tAn 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<\/code>, which is a type SMO Microsoft.SqlServer.Management.Smo.Database<\/code>, just expand the “p<\/em>roperties<\/em>” property and select Name and Value, as shown in the code below: <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn -dbname adventureworks2012 |\r\nSelect-Object -ExpandProperty properties |\r\nselect name,value\r\n\r\nName                                        Value\r\n----                                        -----\r\nActiveConnections                               4     \r\nAutoClose                                   False\r\nAutoShrink                                  False\r\nCompatibilityLevel                            110\r\nCreateDate                   8\/8\/2012 10:04:21 PM\r\nDataSpaceUsage                             115176\r\nDboLogin                                     True\r\nDefaultFileGroup                          PRIMARY\r\nDefaultSchema                                 dbo\r\nID                                              7\r\nIndexSpaceUsage                             69936\r\nIsAccessible                                 True\r\nIsDbAccessAdmin                              True\r\nIsDbBackupOperator                           True\r\nIsDbDatareader                               True\r\nIsDbDatawriter                               True\r\nIsDbDdlAdmin                                 True\r\nIsDbDenyDatareader                          False\r\nIsDbDenyDatawriter                          False\r\nIsDbOwner                                    True\r\nIsDbSecurityAdmin                            True\r\nIsFullTextEnabled                            True\r\nIsSystemObject                              False\r\nLastBackupDate               1\/1\/0001 12:00:00 AM\r\nLastDifferentialBackupDate   1\/1\/0001 12:00:00 AM\r\nLastLogBackupDate            1\/1\/0001 12:00:00 AM\r\n.....\r\n.....\r\n.....\r\n<\/pre>\n
 \tAs you can see, with Get-SQLDatabase<\/code> we have all of the database information, such as date of creation, autoshrink configuration option and last backup date. <\/p>\n
 \tEvery 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<\/strong> SQL Server instance and AdventureWorks2012<\/code> database we use the Get-SQLStoredProcedure<\/code>: <\/p>\n
Get-SQLDatabase -sqlserver Obiwan -dbname Adventureworks2012 |\r\nGet-SQLStoredProcedure\r\n<\/pre>\n
 \tThe function has the parameter -name, and that means to access the properties of a particular stored procedure, say uspGetBillOfMaterials<\/code>, and just pass its name as a parameter: <\/p>\n
Get-SQLdatabase -sqlserver Obiwan -dbname Adventureworks2012 |\r\nGet-SQLStoredProcedure -name uspGetBillOfMaterials\r\n<\/pre>\n
 \tWe can select the properties Name, DateLastModified<\/code>, CreateDate<\/code>, and whether a stored procedure is set for replication by just piping Get-SQLStoredProcedure<\/code> output to the Select-Object<\/code> cmdlet: <\/p>\n
Get-SQLdatabase -sqlserver Obiwan -dbname Adventureworks2012 |\r\nGet-SQLStoredProcedure |\r\nSelect-Object      Name,\r\n                   DateLastModified ,\r\n                   CreateDate ,\r\n                   ForReplication\r\n<\/pre>\n
 \tThere 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: <\/p>\n
Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 |\r\nGet-SqlTable\r\n<\/pre>\n
\n
Note<\/strong>: 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 <\/p>\n<\/p><\/div>\n
 \tIn the case of the tables we can go deeper and view all the properties of its indexes using the Get-SQLIndex<\/code> function: <\/p>\n
Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 |\r\nGet-SqlTable -name Person |\r\nGet-SQLIndex\r\n<\/pre>\n
 \tFigure <\/strong>3 shows all the properties that the function Get-SQLIndex returns: <\/p>\n
 \t\"1911-Figure4.3-ab24f0b4-7df1-44ae-8057-1\"<\/p>\n
 \tFigure 3 -All properties from Get-SQLIndex <\/p>\n
 \tOnce 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: <\/p>\n
Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 |\r\nGet-SqlTable -name Person |\r\nGet-SQLIndex |\r\nGet-SQLIndexFragmentation\r\n\r\nIndex_Name                 : IX_Person_LastName_FirstName_MiddleName\r\nIndex_ID                   : 2\r\nDepth                      : 2\r\nPages                      : 106\r\nRows                       :\r\nMinimumRecordSize          :\r\nMaximumRecordSize          :\r\nAverageRecordSize          :\r\nForwardedRecords           :\r\nAveragePageDensity         :\r\nIndexType                  : NONCLUSTERED INDEX\r\nPartitionNumber            : 1\r\nGhostRows                  :\r\nVersionGhostRows           :\r\nAverageFragmentation       : 6.60377358490566\r\n<\/pre>\n
 \tWe can see that the object returned from this function has several properties and that one of them is the AverageFragmentation<\/code>. This means that we can filter by fragmentation, choosing only those indexes above 50% fragmentation, by using the code: <\/p>\n
Get-SqlDatabase -sqlserver ObiWan -dbname AdventureWorks2012 | #A\r\nGet-SqlTable -name Person | #B\r\nGet-SQLIndex | #C\r\nGet-SQLIndexFragmentation | #D\r\nWhere-Object {$_.AverageFragmentation -ge 50} | #E\r\nSelect-Object      index_Name,\r\n                   Pages,\r\n                   Rows,\r\n                   IndexType,\r\n                   AverageFragmentation #F\r\n\r\n#A - Accessing the Database AdventureWorks2012 in the ObiWan SQL Server instance\r\n#B - Returning the information from the table Person\r\n#C - Accessing its indexes\r\n#D - Listing the fragmentation properties\r\n#E - Filtering only ones that has The property AverageFragmentation above 50\r\n#F - Selection the properties Index_name, Pages,Rows, IndexType, AverageFragmentation\r\n<\/pre>\n
 \tThis way, we can use the formula to check whether fragmentation is greater than 10% and less than 30% and the page count> 1000<\/code>, and if it is, we reorganize the index, otherwise we rebuild it using the functions Invoke-SQLIndexDefrag<\/code> and Invoke-SQLIndexRebuild<\/code>. The following listing shows the code to perform this operation for all tables and indexes in the database Adventureworks2012<\/code> and ObiWan<\/strong> SQL Server instance: <\/p>\n
Get-SqlDatabase -sqlserver ObiWan -dbname adventureworks2012 | #A\r\nGet-SqlTable | #B\r\nGet-SqlIndex | #C\r\nforeach-object { #C\r\n   $IndexObject = $_ #D\r\n   $SQLinstanceName = $_.server #E\r\n   $Database = $_.dbname #E\r\n   $SQLTable = $_.table #E\r\n   Get-SqlIndexFragmentation -index $IndexObject | #F\r\n   ForEach-Object { #F\r\n       if (($_.AverageFragmentation -ge 1 -and $_.AverageFragmentation -le 30 -and $_.Pages -ge 0)) { #G\r\n          Write-Host -ForegroundColor Yellow \"Performing Defrag - SQL Server : $($SQLInstanceName) Database : $($Database) Table : $($Table) Index : $($_.Index_name)\" #H\r\n          Invoke-SqlIndexDefrag -index $IndexName -Verbose #I\r\n       } elseif (($_.AverageFragmentation -gt 30 -and $_.Pages -ge 0)) { #J\r\n          Write-Host -ForegroundColor Yellow \"Performing Rebuild - SQL Server : $($SQLInstanceName) Database : $($Database) Table : $($SQLTable) index : $($_.Index_name)\" #K\r\n          Invoke-SqlIndexRebuild -index $IndexObject -Verbose #L\r\n       }\r\n   }\r\n}\r\n\r\nPerforming Rebuild - SQL Server : ObiWan Database : adventureworks2012 \r\nTable : Employee index : AK_Employee_LoginID\r\nVERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics\r\nPerforming Rebuild - SQL Server : ObiWan Database : adventureworks2012 \r\nTable : Employee index : AK_Employee_NationalIDNumber\r\nVERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics\r\nPerforming Rebuild - SQL Server : ObiWan Database : adventureworks2012 \r\nTable : EmployeeDepartmentHistory index : \r\nPK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID\r\nVERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics\r\nPerforming Rebuild - SQL Server : ObiWan Database : adventureworks2012 \r\nTable : EmployeePayHistory index : \r\nPK_EmployeePayHistory_BusinessEntityID_RateChangeDate\r\nVERBOSE: Invoke-SqlIndexRebuild XMLVALUE_Person_Demographics\r\n\r\n#A - Connect to Database AdventureWorks2012 in the SQL instance ObiWan\r\n#B - Return the Tables properties\r\n#C - Return Information about the indexes and starts a loop for each one\r\n#D - Store the Object Index in the $IndexObject Variable\r\n#E - Store the name of SQL Server instance, Database and Table in the variables $SQLInstanceName ,$Database and $SQLTable\r\n#F - Return the information about the fragmentation of the Index and starts a loop for each one\r\n#G - Test if the fragmentation is less than 10 and greater than 30 and page counts greater than 1000\r\n#H- Display in the screen information about what SQL Server instance, database, Table and Index will perform the Index Reorganize\r\n#I - Perform the Index Reorganize\r\n#J - Test the fragmentation is above 30 and page count greater than 1000\r\n#K - Display in the screen information about what SQL Server instance, database, Table and Index will perform the rebuild\r\n#L - Perform the Index Rebuild\r\n<\/pre>\n
 \tListing – Performing Reorganize or Rebuild Indexes <\/p>\n
 \tAs 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: <\/p>\n
Get-Command Get-SQL*\r\n<\/pre>\n
Scripting SQL Server Objects<\/h3>\n
 \tAs 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. <\/p>\n
 \tThe function Get-SQLScripter<\/code> 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<\/code> in the AdventureWorks2012<\/code> Database and QuiGonJinn<\/strong> SQL Server instance, the code is : <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |\r\nGet-SqlStoredProcedure -Name uspGetWhereUsedProductID |\r\nGet-SqlScripter\r\n<\/pre>\n
 \tIn 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<\/code> cmdlet: <\/p>\n
Get-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |\r\nGet-SqlStoredProcedure -Name uspGetWhereUsedProductID |\r\nGet-SqlScripter |\r\nOut-File c:\\temp\\scripts\\ uspGetWhereUsedProductID.sql\r\n<\/pre>\n
 \tSimilarly you can perform this operation with any other SQL Server object, such as tables, views or triggers. <\/p>\n
 \tAnother 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\"<\/code> 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<\/code> function and select the options that you are interested in. The example below demonstrates how to add \"IF Not Exists\"<\/code> in the script of the stored procedure uspGetWhereUsedProductID<\/code>: <\/p>\n
$ScriptingOptions = New-SqlScriptingOptions\r\n$ScriptingOptions.IncludeIfNotExists = $true\r\nGet-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |\r\nGet-SqlStoredProcedure -Name uspGetWhereUsedProductID |\r\nGet-SqlScripter -scriptingOptions $ScriptingOptions\r\n<\/pre>\n
 \tTo add the “If Not exists”, but remove the “Collate” clause in the script in the Vendor<\/code> table: <\/p>\n
$ScriptingOptions = New-SqlScriptingOptions\r\n$ScriptingOptions.IncludeIfNotExists = $true\r\n$ScriptingOptions.NoCollation = $true\r\nGet-SqlDatabase -sqlserver QuiGonJinn -dbname Adventureworks2012 |\r\nGet-SqlTable -Name Vendor |\r\nGet-SqlScripter -scriptingOptions $ScriptingOptions\r\n<\/pre>\n
 \tThe Figure <\/strong>4 illustrates the output: <\/p>\n
 \t\"1911-Figure4.4-9b2a8f77-5c55-4b12-8d7f-c\"<\/p>\n
 \tFigure 4 -Vendor table with scripting options <\/p>\n
 \tAs 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<\/code> function, type: <\/p>\n
New-SQLScriptingOptions | Get-Member\r\n<\/pre>\n
 Deploy your first solution using SQLPSX<\/h2>\n
 \tAnyone 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. <\/p>\n
 \t 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. <\/p>\n
 \t 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. <\/p>\n
 \tif 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. <\/p>\n
 \tWith 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. <\/p>\n
 \tIn addition, we have the PowerShell function Split-Job<\/code>, which is part of the Functions.PSM1<\/code> 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. <\/p>\n
 \tBefore looking at the code, let’s look at and document the process flow. This is shown in Figure <\/strong>5 <\/p>\n
\n
 \tNote<\/strong>: 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<\/a>. <\/p>\n<\/p><\/div>\n
 \t\"1911-poshdbaimage.PNG\"<\/p>\n
Figure 5 -Flowchart for the process of gathering information from SQL Server instances <\/p>\n
 \tNow that we understand the process, let’s see the code. It is displayed in this Listing: <\/p>\n
$global:PathExportFile = \"c:\\DOCSQL\" #A\r\n$global:timestamp = Get-Date -Format \"yyyyMMdd_hhmm\" #B\r\nGet-Content c:\\PoshForSQLServer2012\\SQLInstances.txt | #C\r\nSplit-job { #C\r\n   ForEach-Object { #D\r\n      if ((Test-SQL -ServerInstance $_).result) { #E\r\n         try {\r\n            $Server = Get-SqlServer $_ #F\r\n            $ServerNameToPath = ($server.Name -replace '\\\\','_') #G\r\n            $CSVFIleName = \"$($ServerNameToPath)_$($timestamp).csv\" #H\r\n            $ServerPath = (Join-Path -Path $PathExportFile -ChildPath $ServerNameToPath) #I\r\n            if ( -not (Test-Path -path $ServerPath -PathType Container) ) { #J\r\n               New-Item -Path $ServerPath -ItemType Directory -Force | Out-Null #K\r\n            }\r\n            $FullPathFile = Join-Path -Path $ServerPath -ChildPath $CSVFIleName #L\r\n            $PropertiesHash = @{} #M\r\n            $server.Properties | foreach {$PropertiesHash.add($_.Name,$_.Value) } #N\r\n            $server.Configuration.Properties | foreach { $PropertiesHash.add($_.DisplayName,$_.RunValue) } #O\r\n            $PropertiesHash.GetEnumerator() | #P\r\n            foreach {$_} | #Q\r\n            select @{E={$_.name};N='Configuration'},\r\n               @{E={$_.value};N='Value'} | #R\r\n            Export-Csv $FullPathFile -NoTypeInformation #S\r\n         } catch {\r\n            $ErrorMessage = \"$(Get-date) $($Error[0])\" #T\r\n            Write-Error $ErrorMessage #U\r\n            $ErrorMessage |\r\n            Out-File (join-path $PWD -ChildPath \"DocError.txt\") -Append #V\r\n         }\r\n      } else {\r\n         $ErrorMessage = \"$(Get-date) - SQL Server Instance $($_) unreachable\" #X\r\n         Write-Warning $ErrorMessage #Y\r\n         $ErrorMessage |\r\n         Out-File (join-path $PWD -ChildPath \"DocError.txt\") -Append #V\r\n      }\r\n   }\r\n} -InitializeScript {Import-Module functions -Force;Import-Module SQLServer -Force } -variable PathExportFile,timestamp -noprogress\r\n\r\nRemove-variable PathExportFile -ErrorAction SilentlyContinue #Z\r\nRemove-variable Timestamp -ErrorAction SilentlyContinue #Z\r\n\r\n#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\r\n#C and #D - Read the SQLInstances text file with the name of all SQL Server instances and start a runspace for each one\r\n#E- Test the connection for the current SQL Server instance\r\n#F - Stores the object returned by Get-SQLServer into a variable\r\n#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.\r\n#H - Create the .CSV file name by joining the SQL Server instance Name variable (G) with the Timestamp\r\n#I - Join the root path with the SQL Server instance name variable (G) to create a full path to the CSV File\r\n#J and #K - Check to see if the full path exists (I). If not, create it.\r\n#L - Join the full path with the CSV File Name (G)\r\n#M - Initialize a hashtable to store the SQL Server instance information\r\n#N - Return the properties from the SQL Server instance and add to the hashtable\r\n#O -Return the properties from the Configuration property from the SQL Server Server object and add to the hashtable\r\n#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\n#R -Select only the Name and Value properties from the hashtable and changing the \"Name\" property name to \"Configuration\"\r\n#S - Export to the CSV file in the specific folder to its name.\r\n#T - Store the error message and the currently date\r\n#U - Write the PowerShell error message in the error stream\r\n#V - Outputting the error message to a text file called \"DocError.txt\" in the currently path ($PWD environment variable)\r\n#X - Storing the custom error message and the currently date\r\n#Z - Removing the global variables.\r\n<\/pre>\n
 \tListing – Gathering SQL Server instances information – Server level <\/p>\n
The process (C), Split-Job, has some interesting parameters to check :<\/p>\n
} -InitializeScript {Import-Module functions -Force; Import-Module Sqlserver -Force} -Variable PathExportFile,timestamp -noprogress\r\n<\/pre>\n
 The -InitializeScript<\/code> is the modules that I want to import and the -Variable <\/code>parameter sends the variables chosen to the runspace. This is required because as the process inside  Split-Job<\/code> will run in another runspace, all the variables and functions from SQLPSX will be not visible. <\/p>\n
 \tThe next step is create a path called  C:\\DocSQL<\/code> and save this script as  \tListSQLInfo.ps1<\/code>.Now it is just to navigate to the C:\\DocSQL<\/code> and run the script, typing: <\/p>\n
CD \\DOCSQL\r\n\r\n.\\ListSQLInfo.ps1\r\n<\/pre>\n
 \tThe 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 <\/strong>6 <\/p>\n
 \t \"1911-clip_image006-300x175.jpg\"<\/p>\n
 Figure 6 -Specific folders to each SQL Server instance <\/p>\n
 \tIf we navigate to the folder ObiWan_Inst1<\/code> we will find the Obiwan_INST1_20121019_0913.CSV file, as shown in Figure <\/strong>7: <\/p>\n
 \t \"1911-clip_image007-380x151.jpg\"<\/p>\n
 Figure 7 – CSV File to the ObiWan\\Inst1 SQL Server instance with the information gathered <\/p>\n
 \tAnd, 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 <\/strong>8 illustrates some of them: <\/p>\n
 \t\"1911-Figure4.9-4c0d3053-f870-4f9e-b6cf-0\"<\/p>\n
 Figure 8 -First properties of the ObiWan\\Inst1 CSV File <\/p>\n
 \tNow 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<\/code> and add a step type CmdExec. Call it Step_1 and type in the command line: <\/p>\n
PowerShell.exe \"c:\\DocSQL\\ListSQLInfo.ps1\"\r\n<\/pre>\n
 \tFigure <\/strong>9 illustrates the process: <\/p>\n
 \t\"1911-Figure4.10-38a2f850-da9d-467a-b8e6-\"<\/p>\n
 \tFigure 9-Creating a SQL Server Agent Job to run the script <\/p>\n
 \tOkay, 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. <\/p>\n
Summary<\/h2>\n
 \tOne 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. <\/p>\n
 \tIn 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. <\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
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.…<\/p>\n","protected":false},"author":221715,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[4168,4170,4635,4336,4179,4150,4151,5651],"coauthors":[6819],"class_list":["post-1735","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","tag-database","tag-database-administration","tag-powershell","tag-smo","tag-source-control","tag-sql","tag-sql-server","tag-the-posh-dba"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1735","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221715"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1735"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1735\/revisions"}],"predecessor-version":[{"id":72793,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1735\/revisions\/72793"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1735"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1735"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1735"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1735"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}