Can you run PowerShell in SQL Server Management Studio? YES!

Just to be clear!! You can run PowerShell in SQL Server Management Studio.

Not only you can run PowerShell, but you can create scheduled jobs in SQL Agent to run PowerShell scripts. This has been available since SQL Server 2008 (before R2). And, this is a lot better now, as each version are finally providing more “SQL PowerShell cmdlets” to manage your SQL Server in their *”SQLPS” PowerShell module.

*Note: SQLPS Module was introduce with SQL Server 2008.

Nowadays, Thanks to both Aaron Nelson, Christy LeMaire, and Rob Sewell who have contribute to the success of provide new enhancements to SQL Server PowerShell (SQLPS) cmdlets. Check out their tools:

DBA Tools “best practices and instance migration module” link: https://dbatools.io/
DBA Reports “free, fun” link: https://dbareports.io/

By the way, DBA Reports is owned by Rob Sewell – @sqldbawithabeard. Great Work!!

So, YES! You can run PowerShell from SQL Server Management Studio.

How to run PowerShell?

You can Right-Click on most of the SQL Server objects under “Object Explorer” and look for “Start PowerShell“.

This will open the PowerShell prompt and you are ready to start your adhoc scripting.

Keep in mind, on the latest version of SQL Server (< 2012), the SQL PowerShell module (SQLPS) is loaded and already available. This will create a SQL Server Drive connecting (in this case) to your local instance installation or whichever instance you’re connecting to.

Notice, in my case, the above image will open a PowerShell prompt and is using PowerShell version 5.1 which is part of my Windows 10. The same will be true on earlier OS version of PowerShell. The “Start PowerShell” will open the current PowerShell version installed on that machine.

Another thing to understand, although you already have a set of available cmdlets to manage your SQL Server, you can still expand and build more script with the use SMO (SQL Server Management Objects). So, the possibilities to build your own solutions are endless.

All SMO .NET assemblies are loaded into your system when installing SSMS.

About SQLPS been removed

To be clear! Documentation states that SQLPS “Utility” (sqlps.exe) will be removed in the future. But, the SQLPS PowerShell module will still be available. (See reference link)
https://technet.microsoft.com/en-us/library/cc280450(v=sql.130).aspx

This is why you rather use the normal PowerShell console and start using the SQLPS module. Keep in mind, that since PowerShell 3.0, all existing installed modules are automatically loaded and ready to use in your PowerShell session.

How do I get SQL PowerShell?

Simple! SQL PowerShell comes included when SQL Server Management Studio(SSMS) is installed. For sometime ago SSMS (SSMS 2012) has been available to install separately (free-of-charge). As a matter of fact, you could install three separate SQL Server Features components without the need of installing SSMS and start scripting against your SQL engine.

The following link shows both latest version of SSMS (16.5.3) and the preview SSMS for SQL Server vNext (RC 17) can be found here:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Bonus – No need for SSMS GUI nor the SQL Engine

Sometimes there’s no need to install a SQL instance, nor SSMS GUI but only the necessary components installed in order to run and scheduled some SQL PowerShell scripts in Windows Server Task Scheduler. I had this scenario on a **server with no SQL engine but needed to run some scheduled SQL PowerShell scripts. Only 3 components are needed:

(Below content extracted from Microsoft link (Install section) : https://www.microsoft.com/en-us/download/details.aspx?id=52676 )

Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016
The Microsoft Windows PowerShell Extensions for SQL Server includes a provider and a set of cmdlets that enable administrators and developers to build PowerShell scripts for managing instances of SQL Server. The SQL Server PowerShell Provider delivers a simple mechanism for navigating SQL Server instances that is similar to file system paths. PowerShell scripts can then use the SQL Server Management Objects to administer the instances. The SQL Server cmdlets support operations such as executing Transact-SQL scripts or evaluating SQL Server policies.

Filename: X86 and x64 Package (PowerShellTools.msi)

– Microsoft® SQL Server® 2016 Shared Management Objects
SQL Server Management Objects (SMO) is a .NET Framework object model that enables software developers to create client-side applications to manage and administer SQL Server objects and services.

Note: Microsoft SQL Server Management Objects requires – Microsoft SQL Server System CLR Types, that is available on this page.
Filename: X86 and x64 Package (SharedManagementObjects.msi)

– Microsoft® System CLR Types for Microsoft SQL Server® 2016
The SQL Server System CLR Types package contains the components implementing the geometry, geography, and hierarchy id types in SQL Server. This component can be installed separately from the server to allow client applications to use these types outside of the server.

Filename: X86 and x64 Package (SQLSysClrTypes.msi)

**Note: This can apply to desktop/laptop is you don’t want to install the whole SQL Server CD. As long as, you have remote connection to a SQL Server system, then you just start building scripts. You will save some disk space too.

IDERA Geek Synch Webinar – Wednesday February 22nd, 2017

Topic: Using PowerShell with Python and SQL Server


Description: Just as PowerShell is argued as the main technology for automation in Windows Datacenters Infrastructure, it is equally important in other non-Windows Operating Systems. According to Maximo Trinidad, Windows Administrators have an advantage working with PowerShell just as Linux Administrators have an advantage with Bash / PHP / Python.

Webinar starts at: 11:00AM – 12:00PM (CDT) / 12:00pm – 01:00pm (EST)

Register at: https://www.idera.com/events/geeksync

Florida PowerShell User Group Online Meeting – Thursday February 23rd, 2017

Topic:  Understanding Bash On Windows 10


Description: Come and learn how to setup and use Bash On Windows 10. Learn the tips and tricks to use PowerShell and Python together. At the same time, there are some differences in working with script files between Bash and Windows systems. We’ll be seen how to use efficiently use Windows apps with Python scripts on Bash. Lots of demo!

Meeting starts at: 6:30pm(EST)

Register at: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-february-2017-tickets-31689059831

PowerShell Open Source Alpha14 and SQL Server for Linux CTP 1.1 are out!

This is Great! Microsoft keep delivering updates before Christmas just to keep us busy and excited.

To download the latest PowerShell Open Source just go to the link below:

https://github.com/PowerShell/PowerShell

Just remember to remove the previous version, and any existing folders as this will be resolved later.

To download the latest SQL Server vNext just check the following Microsoft blog post as the new CTP 1.1 includes version both Windows and Linux:

SQL Server next version Community Technology Preview 1.1 now available

And, don’t forget the check out the release notes as Microsoft SQL Server team has done an excellent job in providing documentation:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes

For those interested in how SQL Server became to Linux, check the following Microsoft Blog posts:

SQL Server on Linux: How? Introduction

http://arstechnica.com/information-technology/2016/12/how-an-old-drawbridge-helped-microsoft-bring-sql-server-to-linux/

Additional Tools in the Horizon

Just an FYI on some tools that recently caught my attention from Jet Brain:

1. PyCharm Community Edition (free) – Python Develoment IDE Tool available for both Windows and Linux.
2. DataGrip – SQL Developer IDE Tools for working with Databases (including SQL Server). Also available for both Windows and Linux.

And, of course, we can’t forget of Microsoft VS Code. This lightweight coding IDE works great in both Windows and Linux:

https://code.visualstudio.com/

 

My IDERA’s PowerShell Webcast not only for DBA’s

I decided to compiled all of the webcast I did for IDERA Geek Synch from the last two years(2015 – 2016). Free registration and view the webcast: https://www.idera.com/events/geeksync

iderageeksynch

08/10/2016 – Geek Sync Webcast : Learn the PowerShell Essentials on Error Trapping

Join IDERA and Maximo Trinidad as he walks you through how to find and identify errors in your PowerShell script. Do you want to learn how to trap and document error while running PowerShell scripts? This session will cover the use and how to trap errors in your PowerShell script. We’ll be creating simple script providing some scenarios in trapping errors. At the same time, we are going to end up creating an error report.

https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/powershell-error-trapping

06/01/2016 – Geek Sync Webcast : The Essential PowerShell Tools for the Database Administrator

In this session Maximo will be showing some available tools the DBA can use along with PowerShell. We’ll be integrating Visual Studio with PowerShell and at the same time using IDERA’s PowerShellPlus editor. At the end, we’ll build an SSIS package solution to execute our PowerShell script. It will be packed with interesting thing to do as well as help you accomplish your automation tasks.

https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/powershell-tools-dba

02/25/2016 – Geek Sync Webcast : The Essentials in Building Basic PowerShell Modules

In this session you’ll learn the basics on how-to build PowerShell Modules. Previously, we’ve learned how to create functions with Maximo. This is the next step to collect and group together all your existing functions to build a module. Most important, Maximo will show you where the modules should be stored and how to load them.

https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/basic-powershell-module

11/14/2015 – Geek Sync Webcast : The Essentials to Tackling Basic PowerShell Functions

In this session Maximo will demonstrate the creation of a PowerShell function from a one-liner and/or an existing script file in its basic form. This will show the scripting evolution you’ll experience while building your PowerShell scripting skills. At the same time he will be showing you some existing resources already available at your fingertip.

https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/powershell-basics

2015 – Geek Sync Webcast : Creating a SQL Server Database Report with PowerShell

This webinar is a deep dive on how to create a SQL Server report using PowerShell and SMO. At the same time, you will learn how to create and work with PowerShell objects, scriptblocks, formatting properties, and generating output results. We’ll be looking into creating a report to identify database properties irregularities. This will be a good start to help begin documenting your SQL Server on the network.

https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/creating-sql-server-db-powershell

2015 – Geek Sync Webcast : PowerShell Essentials using SQL Server SMO

This session will provide the PowerShell essential skills to work with SQL Server Management Objects (well known as SMO). Maximo Trinidad will talk about how-to connect to SQL Server (both Windows and SQL Authentication), working with SMO objects, and showing some PowerShell cmdlets that can assist when building the script(s).

https://www.idera.com/resourcecentral/webcasts/sqlserver/geeksync/powershell-essentials-using-sql-server

It’s never late to learn PowerShell!!

VS Code working with SQL Server

VS Code editor works great and specially when using it in cross-platform environment. So, I decided to try using it connect to one of my SQL Server instance. To configure VS Code editor is simple. Make sure to check my previous blog post for information.

http://www.maxtblog.com/2016/09/vs-code-running-powershell-debug-13/
http://www.maxtblog.com/2016/09/vs-code-running-powershell-code-runner-extensions-23/
http://www.maxtblog.com/2016/09/vs-code-running-powershell-terminal-session-33/

Next we need to look which SQL extension are available. In this case I decided to use “vscode-mssql” extension:

vscodesql_01_2016-09-30

After installing, we need to customized their setting by creating connection(s) to our SQL Server. We do this by opening VS Code “User Preferences” and under “Default Settings.json” we search for the “vscode-mssql” settings to be copied over to our working folder “settings.json” file.

vscodesql_02_2016-09-30

I configure two database connections, which you can choose by pressing Ctrl-Shift-E before it execute the SQL Script.

vscodesql_03_2016-09-30

The result from the SQL Script will be displayed on the right side of the editor.

vscodesql_04_2016-09-30

One thing to notice, on VS Code version 1.5.3, there’s an extension called “vscode-icons” which gives nice file icons to the working files.

vscodesql_04a_2016-09-30

vscodesql_05_2016-09-30

PowerShell – SQL Server 2014 SMO TruncateData() Workaround

As I was still puzzle why the SMO *.TruncateData() was missing in SQL Server 2014, I needed to find a quick workaround to continue with my data migration.

Of course, here comes T-SQL scripting to the rescue:


Truncate Table databasename.schema.tablename
GO

Basically, subtitute the SMO .TruncateData() with few lines of T-SQL statement, and have PowerShell run the code against SQL Server. The script is shown below:

## ----------------------------------------- ##
## - Using SMO steps to work with tables:
$SQLServerInstanceName = 'TSQLDSP01'; $global:SQLServerDatabasename = 'devMaxText';
$global:DbSchema = 'dbo

[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$SQLSrvObj = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstanceName;
$tables = $SQLSrvObj.Databases[$global:SQLServerDatabaseName].tables;

$global:cnt = 1;
foreach ($t in $tables.name)
{
$tsqry = @"
Truncate Table $($global:SQLServerDatabasename).$($global:DbSchema).$($t)
Go
"@;
#$SQLSrvObj.Databases[$SQLServerDatabaseName].ExecuteNonQuery($tsqry);
Write-Verbose "[$($global:cnt.ToString("0000"))]Truncate Table dgSAP_Old.dbo.$($t) Process" -Verbose;
$global:cnt++;
};

This *script will assist in the third-party application to load date back to the tables for our migration.

*Note: Just in case, I commented out the line having the .ExecuteNonQuery() method.

Important PowerShell Links To Remember

Powershell_2016-07-28_12-37-40

Just a reminder!  Here are some PowerShell resource link to save.

Microsoft just recently announce the PowerShell “Windows Management Framework 5.1 Preview“. Check the PowerShell Team Blog:

Announcing Windows Management Framework (WMF) 5.1 Preview

In addition, if you encounter any PowerShell bugs and want to provide any suggestions, below is the link you can use:

*note: Keep in mind. WMF 5.1 Preview is not supported in production environments.

More resource links:

Windows PowerShell Home Page
https://msdn.microsoft.com/powershell

Windows Management Framework 5.1 Preview
https://www.microsoft.com/en-us/download/details.aspx?id=53347

Don’t forget to check WMF 5.1 Release Notes
https://msdn.microsoft.com/en-us/powershell/wmf/5.1/release-notes

SAPIEN Technologies ‘PowerShell Studio 2016’ – The premier editor and tool-making environment for Windows PowerShell
https://www.sapien.com/software/powershell_studio

And forgot!!  For everyone who love to work with PowerShell and SQL Server:

Go and keep learning about PowerShell!!

Microsoft SSMS July 2016 Hotfix is Available now

Go and get it.  Of course, No SQLServer PowerShell update on this hotfix. But you’ll need this update.

MSSMS_02_2016-07-15_14-32-33

Read more on this link for more information: https://msdn.microsoft.com/en-us/library/mt238290.aspx

If you are still interested of see what’s new in this July Edition of SSMS, check the following Microsoft Blog site:
https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

MSSMS_01_2016-07-15_14-32-33

 

Create PowerShell Top-Down ETL Process III

On previous blog we have collect some data and built our .NET PowerShell object which is going to be loaded into our SQL Server.

In this final section we are completing the process of loading the data into SQL Server. We need to make sure we got our Database and table previously created and ready to receive our data.

For this process we’ll need to create 3 functions:
1. ConnectTo-SQLServer
2. Load-LogRecord
3. Process-PSObjectToSQL

Of course we could do everything in one long script file.  But, by breaking out into it will make it much easier to handle and maintain.

In order to connect to SQL Server, we’ll be using straight SMO classes to load the data. Also, we’ll  integrate some basic error catching using “try-catch” code block structure.

I’m keeping the functions in a basic level hardcoding some the PSObject variables instead of using parameter names. Keep in mind, the use of parameter name(s) in a function brings a lot flexibility and function reusability.

Function – ConnectTo-SQLServer

Using SMO with a few lines of code we connect to a SQL Server engine. Here’s the basic script code to will allow us to initiate a connection to SQL Server:

function ConnectTo-SQLServer
{
 [CmdletBinding()]
 param ()
 ## - Select SQLServer:
 $global:SQLServerInstanceName = "MTRINIDADLT2";
 $global:SQLServerDatabaseName = "LogEntries";
 $global:logServerName = "tcgsvvm04";
 
 ## - Loading SMO .NET Assembly: (Required)
 [system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
 
 ## - Connecting to SQL Server (Windows Authentication) and building you table object:
 $global:SQLSvrObj = new-object('Microsoft.SqlServer.Management.Smo.Server') $SQLServerInstanceName;
 $global:SQLSvrObj.ConnectionContext.StatementTimeout = 0;
};

You will notice the use the of “$global:variablename” with some of the variable objects created. This way we won’t loose the value stored in my PSObject(s) like “$global:SQLSvrObj” and have it available to other PowerShell function(s).

Function – Load-logRecord

This function show a Kind-Of template for using T-SQL insert statement to an existing table and at the same time subtitute the PSObject variable(s) constructing full qualify database table name. We construct the T-SQL Insert string and then to execute the query using SMO database *method “.ExecuteNonQuery($global:insertqry)”.

function Load-logRecord
{
CmdletBinding()]
Param ()
$global:insertQry = @"
INSERT INTO [$($global:SQLServerDatabaseName)].[dbo].[$($global:logServerName)_Log]
([Column1]
, [Column2]
, [Column3]
, [Column4]
, [Column5]
, [Column6]
, [Column7]
, [Column8]
, [Column9]
, [Column10]
, [Column11]
, [Column12]
, [Column13]
, [Column14]
, [Column15]
, [ServerName])
VALUES
( '$($global:l.Column1)'
, '$($global:l.Column2)'
, '$($global:l.Column3)'
, '$($global:l.Column4)'
, '$($global:l.Column5)'
, '$($global:l.Column6)'
, '$($global:l.Column7)'
, '$($global:l.Column8)'
, '$($global:l.Column9)'
, '$($global:l.Column10)'
, '$($global:l.Column11)'
, '$($global:l.Column12)'
, '$($global:l.Column13)'
, '$($global:l.Column14)'
, '$($global:l.Column15)'
, '$($global:LogServerName)')
GO
"@;
&amp;amp;amp;amp;nbsp;$global:SQLSvrObj.Databases["Master"].ExecuteNonQuery($global:insertQry);
};

*Note: Keep in mind, by saving the PSObject variable with a $global: scope, you will access to its value after the function has been executed or the value will be dispose (null).

Function – Process-PSObjectToSQL

This is the heart of loading the data.  Here we use both previous functions: 1. ConnectTo-SQLServer and
2. Load-LogRecord.

This a simple code block using the ForEach() block to read thru the PSObject variable to load the data into SQL Server.  All accomplished with a few code block.

Now, here’s where we’ll include our error catching code block in case we encounter any data load issue during this process.

function Process-PSObjectToSQL
{
 [CmdletBinding()]
 param (
  [array]$SQLDataObj
 )
 ## Start process
 Try
 {
  ConnectTo-SQLServer;
  $global:cnt = 0
  Write-Verbose "Start loading Data to SQL Server" -Verbose;
  foreach ($global:l in $SQLDataObj)
  {
   Load-logRecord;
   $global:cnt++;
  }
 }
 catch
 {
  $global:ShowErr = @"
Insert SQL failed:  Record #[ $($global:cnt) ]`n`r-----
Table: [$($global:SQLServerDatabaseName)].[dbo].[$($global:logServerName)_Log]`n`r-----
$($global:insertQry)`n`r-----`n`r$($error[0].Exception)
"@
  [system.reflection.assembly]::LoadWithPartialName("System.Windows.Forms") | Out-Null;
  [System.Windows.Forms.MessageBox]::Show("$global:ShowErr", "SQLInsertQry Exception") | Out-Null;
  
  $errfound = $true
 }
 finally
 {
  ## - Cleanup connection: 
  $global:SQLSvrObj.ConnectionContext.Disconnect();
  $global:SQLSvrObj = $null;
  if ($errfound -eq $true)
  {
   Write-Verbose "Failure loading Data to SQL Server!" -Verbose;
   $global:ShowErr | Out-File -FilePath c:\Temp\SQLDataLoadfailure.txt;
   Invoke-Item c:\Temp\SQLDataLoadfailure.txt;
  }else{
   Write-Verbose "Completed loading Data to SQL Server" -Verbose;
  }
 }
};

This function has a ParameterName $SQLDataObj.  This will take the previously created $SQLData PSObject to be loaded to SQL Server.

In Error catching code block, the try{..} contains all the logic code to process the data. Then, the catch{..} has a custom string with enough information to trouble the issue the process may have encountered, such as: Fully qualified Database name, Insert Query T-SQL script, and the actual Exception error message.

At the end of the process, error or not, the finally{..} block will always execute the code. In this case, to disconnect and cleanup the connection to SQL Server.

Executing the process

After all the previous functions has been loaded, just type the following one-liner:

Process-PSObjectToSQL -SQLDataObj $SQLData;

This sample script code can serve as a startup Template to load data into SQL Server.

This sample SQL data load will fail. Here’s when the Try/Catch/Finally will work for you in trapping what went wrong. Adding the necessary code to provide that additional information to troubleshoot and fix the problem.

Be Creative!  Check out the results.

  1. Data exception when inserting a record which was a transformation error in one of the fields.SampleSQLDataLoad_01
  2. Results generated from the data exception can be use to find what went wrong with this T-SQL insert script. SampleSQLDataLoad_02
  3. Error exception code could generate an output file. SampleSQLDataLoad_03
  4. Then, after fixing the issue, the data was completely loaded to SQL Server. SampleSQLDataLoad_04

Happy PowerShell!