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
"@;
 $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!

Microsoft SQL PowerShell Continnium

Yes! By now you all heard the news. SQL Server 2016 July update will include 25 new cmdlets for SQL PowerShell under the new module named SQLServer.
Now, you need to know that this only with the installation of the “Microsoft Sql Server Management Studio” (MSSMS) available as an option on the SQL Server 2016 installation disk. Its a separate download.
SQLPosh_00_2016-07-04_17-50-29
This link will take you to the following web page: https://msdn.microsoft.com/library/mt238290.aspx
SQLPosh_06_2016-07-04_17-50-29
Interesting to know that the SQLPS module hasn’t been renamed (just changed), as its still loaded on the “C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS” folder.
SQLPosh_01_2016-07-04_17-50-29
But, don’t worry about having SQLPS conflicts with the SqlServer cmdlets. Aparently, SqlServer module will be loaded and somehow the SQLPS is disabled. You’ll find the SQLServer module at “C:\Program Files\WindowsPowerShell\Modules\SqlServer” folder.
SQLPosh_07_2016-07-04_17-50-29
SQLPosh_02_2016-07-04_17-50-29
Now, during my SQLServer 2016 new instance installation (No MSSMS) on a new image, I notice that SQLPS module get installed. So make sure to test SQL PowerShell if you are not going to install MSSMS.
You can verify all the modules you have installed on your machine by typing this simple oneliner:
get-module -ListAvailable | Select name,path | sort name
or
get-module -ListAvailable | Select name,path | sort name
See sample results from my machine:
SQLPosh_03_2016-07-04_17-50-29
Also, you notice that I got various copy of SQL PowerShell modules including the new one.  Its obvious that I got 3 version of SQL Server installed.  But, in order to use only the newer module, I got to make sure my PSModule path have the proper folder location names loaded.
To check the values of your PSModulePath type: $env:PSModulePath
SQLPosh_04_2016-07-04_17-50-29
Now, if you ever have the need to do some editing to any of these Path values, then you need to open the “Environment Variables” under your “This PC” system properties using File Explorer.  (See image below)
SQLPosh_05_2016-07-04_17-50-29
I can do the Happy Dance now!!  The SQL Server Team has done a GREAT JOB!!!

List of SQLServer module cmdlets (*NEW) – SQLServer MSSMS July Update

Add-SqlAvailabilityDatabase
Add-SqlAvailabilityGroupListenerStaticIp
*Add-SqlAzureAuthenticationContext
*Add-SqlColumnEncryptionKeyValue
Add-SqlFirewallRule
Backup-SqlDatabase
*Complete-SqlColumnMasterKeyRotation
ConvertFrom-EncodedSqlName
ConvertTo-EncodedSqlName
Convert-UrnToPath
Decode-SqlName
Disable-SqlAlwaysOn
Enable-SqlAlwaysOn
Encode-SqlName
*Get-SqlAgent
*Get-SqlAgentJob
*Get-SqlAgentJobHistory
*Get-SqlAgentJobSchedule
*Get-SqlAgentJobStep
*Get-SqlAgentSchedule
*Get-SqlColumnEncryptionKey
*Get-SqlColumnMasterKey
Get-SqlCredential
Get-SqlDatabase
*Get-SqlErrorLog
Get-SqlInstance
Get-SqlSmartAdmin
Invoke-PolicyEvaluation
Invoke-Sqlcmd
*Invoke-SqlColumnMasterKeyRotation
Join-SqlAvailabilityGroup
New-SqlAvailabilityGroup
New-SqlAvailabilityGroupListener
New-SqlAvailabilityReplica
*New-SqlAzureKeyVaultColumnMasterKeySettings
New-SqlBackupEncryptionOption
*New-SqlCertificateStoreColumnMasterKeySettings
*New-SqlCngColumnMasterKeySettings
*New-SqlColumnEncryptionKey
*New-SqlColumnEncryptionKeyEncryptedValue
*New-SqlColumnEncryptionSettings
*New-SqlColumnMasterKey
New-SqlCredential
*New-SqlCspColumnMasterKeySettings
New-SqlHADREndpoint
Remove-SqlAvailabilityDatabase
Remove-SqlAvailabilityGroup
Remove-SqlAvailabilityReplica
*Remove-SqlColumnEncryptionKey
*Remove-SqlColumnEncryptionKeyValue
*Remove-SqlColumnMasterKey
Remove-SqlCredential
Remove-SqlFirewallRule
Restore-SqlDatabase
Resume-SqlAvailabilityDatabase
Save-SqlMigrationReport
Set-SqlAuthenticationMode
Set-SqlAvailabilityGroup
Set-SqlAvailabilityGroupListener
Set-SqlAvailabilityReplica
*Set-SqlColumnEncryption
Set-SqlCredential
*Set-SqlErrorLog
Set-SqlHADREndpoint
Set-SqlNetworkConfiguration
Set-SqlSmartAdmin
SQLSERVER:
Start-SqlInstance
Stop-SqlInstance
Suspend-SqlAvailabilityDatabase
Switch-SqlAvailabilityGroup
Test-SqlAvailabilityGroup
Test-SqlAvailabilityReplica
Test-SqlDatabaseReplicaState
Test-SqlSmartAdmin

Additional Information:

1. PowerShell Team Blog on feedback and/or bugs using UserVoice: https://blogs.msdn.microsoft.com/powershell/2016/05/09/understanding-the-powershell-uservoice/
2. To log any feedback and/or bugs using UserVoice under Windows: https://windowsserver.uservoice.com/forums/301869-powershell/