SSMS Version 17.4 no more SQLPS Module

It was just a matter of time, as it was already mention in previous SSMS (SQL Server Management Studio) documentation that SQLPS module was going to be deprecated and replace with the new SQLServer module.

See SSMS download information at: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

After SSMS Version 17.4 was release back in December, SQLPS module is no longer available. So, if you try to use the “Start PowerShell” from any of the database object, you’ll get the message “No SQL Server cmdlets found…” popup message.

New SQLServer PowerShell Module

But, no worries! Both the SSMS link and the popup message tell you where to get the new *SQLServer PowerShell module as is a separate installation from the PowerShell Gallery.

PowerShell Gallery SQLServer PowerShell Module, Get here: https://www.powershellgallery.com/packages/SqlServer/21.0.17199

One thing to point out, this module is only meant to be use on Windows PowerShell.

In other words, it will not work in PSCore6.

Housekeeping Needed

Now, there’s the chance previous SSMS installations have left the older SQLPS PowerShell Module in the system.

As is shown in the previous image, the variable $env:PSModulePath contains the path to the existing SQLPS module(s).

Either, remove the path manually using PowerShell, or thru the GUI System “Environment Variable“.

Or better yet, if you’re using SAPIEN Technologies, Inc. “PowerShell Studioproduct, the n use the Cache Editor feature to manage your existing PowerShell Modules. Check out the blog post and video about this feature at:
https://www.sapien.com/blog/2017/12/07/powershell-studio-feature-nugget-refreshing-local-cache-powershell-cmdlets-and-modules/

Video featuring PowerShell Studio Cache Editor

Option for PSCore

The only way to use PSCore6 to work with SQLServer cross-platform, is using the SMO (SQLServer Management Objects) for .NETCore, which is available in NuGet. For more information in how to install it, check my blog post at:
http://www.maxtblog.com/2017/11/streamlining-sql-server-management-objects-smo-in-powershell-core/

The only downside, you need to create the script from scratch. There’s plenty of documentation about SMO to overcome this hurdle. Most important, you are  sharpen your PowerShell scripting skills.

Don’t forget that before install any artifacts from PowerShell Gallery, NuGet, or Chocolatey the console shell need to be open “as an Administrator“.

Be Bold!! Learn PowerShell Core!!

PSCore6 – Nuget Microsoft.SqlServer.SqlManagementObjects latest Package (v140.17218.0) Broken

This is the SMO (SqlServer Management Objects) package use to create PSCore6 scripts to connect and manage SQL Server on Windows, Linux, and Azure.

But today, I found out the latest version “140.17218.0″ is broken. I had to rolled back to use an older version “140.17199.0” to get it to work again.

You can find the information about this package in this link:
https://www.nuget.org/packages/Microsoft.SqlServer.SqlManagementObjects

This NuGet SMO package version is built on .NETCore 2.0 for PSCore6, and will not install in Windows PowerShell.

Installing SMO Package

To *install the previous SMO package version “140.17199.0“, use the following command:

Install-Package Microsoft.SqlServer.SqlManagementObjects -RequiredVersion '140.17199.0'

*Note: Need to install as an Administrator.

If  the newer SMO version “140.17218.0” is installed then it will not connect. There are no errors, or failures displayed.  (See image)

This issue has been reported to NuGet SMO owners and hopefully will be resolved soon.

Testing SMO in PSCore6

Here’s the PSCore6 script for SMO testing. The script will work in both Windows and Linux.

## - Help find and save the location of the SMO dll's in a PowerShell variable:
$smopath = `
Join-Path ((Get-Package Microsoft.SqlServer.SqlManagementObjects).Source `
| Split-Path) (Join-Path lib netcoreapp2.0)

# Add types to load SMO Assemblies only:
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.Smo.dll)
Add-Type -Path (Join-Path $smopath Microsoft.SqlServer.ConnectionInfo.dll)

## - Prepare connection and credential strings for SQL Server:
## - (Connection to Windows SQL Server multi-instance sample)
$SQLServerInstanceName = 'System01,1451'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Turn ON below for Linux:
## - (Connection to Linux SQL Server multi-instance sample)
# $SQLServerInstanceName = 'LinuxSystem02'; $SQLUserName = 'sa'; $sqlPwd = '$Mypwd01!';

## - Prepare connection passing credentials to SQL Server:
$SQLSrvConn = New-Object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd);
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn);

## - SMO Get SQL Server Info:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;

## - End of Code

Most Important

In order for this to work, NuGet needs to be installed first. The following *code block will help to check if it’s already installed. And, if not, then it will install NuGet in PSCore6.

# Register NuGet package source, if needed
# The package source may not be available on some systems (e.g. Linux)
if (-not (Get-PackageSource | Where-Object{$_.Name -eq 'Nuget'}))
{
Register-PackageSource -Name Nuget -ProviderName NuGet -Location https://www.nuget.org/api/v2
}else{
Write-Host "NuGet Already Exist! No Need to install."
}

*Note: Thanks to the SMO guys for providing this code block to get me started testing.

Also, if you already installed the buggy NuGet SMO version, remember to use the following command to uninstall the package:

uninstall-package Microsoft.SqlServer.SqlManagementObjects

I’m hoping this blog post will help in any way.

Be Bold!! Learn PowerShell Core!!

PowerShell – SQL Server Management Studio v17 is Ready!

Yes! The SQL Server Management Studio  Version 17 is available for download for the purpose of managing the new SQL Server 2017, Azure SQL Databases and Azure SQL Data Warehouse. At the same time this version won’t prevent you from working with older SQL Server such as SQL Server 2000 and SQL Server 2005 but there will be some features that won’t work. Please know the limitations!

To download the new SQL Server Management Studio V17, click on the following link:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

Now there are a few things, you should be aware. This version depends on Visual Studio 2015 with the update KB3165756 installed. I ended up crashing SSMS when I made the mistake of uninstalling Visual Studio 2015 to make some room for the Visual Studio Preview.

Here’s the SSMS error message that will popup and made my application useless.

But, no worries! In order to correct the issue, I did the following steps:

1. Uninstall SSMS
2. Restart machine (Windows 10 Insider Build 16179)
3. In my case, I installed the Visual Studio 2015 Shell only.
4. Search and installed the Visual Studio 2015 Update – KB3165756.
5. Restart machine.
6. Just to make sure, open Visual Studio 2015 and verify the update was installed.
7. Download SQL Server Management Studio V17: https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms
8. Install and restart system.
9. Open SSMS v17 and verify there are no errors.

Apparently, there are strong dependencies in SSMS with Visual Studio 2015.

Now, there are a few changes from previous SSMS Release Candidates in regards with PowerShell SQL Server commands. As the new PowerShell “SQLServer” module is no longer included with SSMS v17. But, you still get the SQLPS module.

The following PowerShell one-liners to check for the modules installed:

gcm -Module SQLServer -CommandType Cmdlet | Select-object -first 10 name
gcm -Module SQLPS -CommandType Cmdlet | Select -first 10 name

In order to installed the PowerShell “SQLServer” module, you’ll need to downloaded from “PowerShell Gallery”. Follow the instructions from the following link:
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-ps-module

One requirement for using PowerShell “SQLServer” module is to have PowerShell Version 5.0 or greater.

Install-module -Name SqlServer -Scope CurrentUser -AllowClobber

Make sure that after you complete the installation, close and open another PowerShell session.

Now, you are ready to work with both SSMS v17 and the new PowerShell “SQLServer” module.

 

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.

Azure PowerShell Preview 1.0 is here

Welcome to PowerShell Azure Resource Manager (RM)!

Microsoft has introduce and made available Azure PowerShell Preview 1.0.  Please read all about it in the following Azure Blog site:

Also, feel free to search for it at usign the following link:

Some Interesting links 

Well, within the search there are more interesting blog articles to start doing some PowerShell scripting:

How to install and configure Azure PowerShell: https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Using Azure PowerShell with Azure Resource Manager: https://azure.microsoft.com/en-us/documentation/articles/powershell-azure-resource-manager/

Manage Azure SQL Database with PowerShell: https://azure.microsoft.com/en-us/documentation/articles/sql-database-command-line-tools/

Integrating SQL AlwaysOn with Azure Site Recovery: https://azure.microsoft.com/en-gb/blog/integrating-sql-alwayson-with-azure-site-recovery/

Get it at PowerShell Gallery

Now, my question here is, did you found Azure PowerShell 1.0 Preview? Probably not, but in fact, it’s AzureRM 1.0.1 to be installed. Although, I did found Azure version 0.9.11 and, Yes! I did download that one just in case.

To download these new bits from the PowerShell Gallery, check out the “Get Started with the PowerShell Gallery” link: http://www.powershellgallery.com/pages/GettingStarted

AzureRM_01_10-16-2015

Search for both “Azure” and AzureRM”.

AzureRM_02_10-16-2015

AzureRM_03_10-16-2015

During my quest to download Azure PowerShell 1.0, I realized that its AzureRM the one I need to download using: “Install-Module AzureRM” followed by “Install-AzureRM -force“.

AzureRM_04_10-16-2015

To start using the AzureRM Cmdlets, just run the “Import-AzureRM” command.

AzureRM_07_10-16-2015

Now, we are ready to play with Azure Resource Manager and Azure SQL databases.   There’s a total of 653 *AzureRM* cmdlets.

AzureRM_08_10-16-2015

AzureRM_09_10-16-2015

Run the following command to list all *AzureRM* commands:

Import-AzureRM; Get-Command *AzureRM*;

 

In my next blog article I’ll be converting my classic Azure SQL script to use the Azure RM new paradigm.

 

FLPSUG Last meeting May 15th on “PowerShell Azure SQL Database”

FLPSUG Lync meeting Thursday May 15 2014 – Session: “PowerShell Working w/Microsoft Azure SQL Database” Speaker Maximo Trinidad (MVP) and Co-hosting Adnan Cartwright (MVP).

In this presentation I will be covering how to subscribe to Azure, setup PowerShell to connect to your subscription, use scripting to create a SQL Database Server and then use SMO with PowerShell push data to your SQL Azure tables. All this using PowerShell scripting plus showing some editor, scripting techniques, and tips to avoid issues when working and setting Azure with PowerShell. (live demo with Windows Azure). Thanks to Adnan for assisting me in this meeting.

Here’s the Powerpoint presentation, demo scripts, and link to the recorded video. The video is the full and unedited meeting: https://onedrive.live.com/redir?resid=7FD7082276C66197!31206&authkey=!AIbxWUhyiUth7Dg&ithint=file%2c.zip

Video link: http://youtu.be/9-fMxXY7DcQ

Co-host Adnan Cartwright link: http://www.fisg.us/

 

Quick Rundown – Microsoft Azure SQL Database Server and PowerShell

Azure SQL Database 
1. Web and Business editions are no longer available. Now there’s Basic, Standard, and Enterprise editions. (New)
2. There is a limit of 6 SQL Database Servers and up to 150 databases per subscription.
3. Create database from 1 GB up to 500GB of storage.
4. Database Throughput Unit(DTU) Service performance levels available: (New)

  • DTU Service Level:
    1  – Basic
    5 – S1
    25 – S2
    100 – P1
    200 – P2
    800 – P3

AzureSQLdb_DTU

For more information about Azure SQL Database Throughout Units Service, check out Scott Kline and Tobias Ternstrom on this link: http://channel9.msdn.com/Series/Windows-Azure-Storage-SQL-Database-Tutorials/Scott-Klein-Video-02

Quick tips when testing Azure SQL Database Server:
1. There’s no need to specify a storageaccount.
2. Start with “Basic” or “Standard” Service level.
3. Start with  1GB in size for testing.
4. Current Azure Portal version will create random database server names.

Observations on the “Preview” Azure Portal on Azure SQL Datbases:
1. It show the ability to group databases.
2. You can provide a SQL Database Server name (not random).

Upcoming Azure Portal (Preview)

Assign a Group and Name your Database Server
Assign a Group and Name your Database Server

Windows Management Framework 5.0 May 2014

On PowerShell  v5.0 Preview just released on May 14th.  This version has no problem with the Azure PowerShell cmdlets installation.

Blog: http://blogs.msdn.com/b/powershell/archive/2014/05/14/windows-management-framework-5-0-preview-may-2014-is-now-available.aspx

Download at: http://www.microsoft.com/en-us/download/details.aspx?id=42936

Azure PowerShell cmdlets updated

Azure PowerShell cmdlet was updated to version 0.8.2 on 05/12/2014 with 390 commands.AzurePosh_082_05152014

 

 

QuickBlog: PowerShell Working with Windows Azure

As I venture into the realm of learning some PowerShell Automation in Windows Azure, its interesting the things you learn by just trying things out.  On my previuos blogs I mention, in order to use PowerShell, you need to create and install the certificate in the portal.  After that, you can use following commands to connect to Azure:

1. Import-Module Azure (*Optional – Autoload module is already set to “On”)
2. Set-AzureSubscription
3. Select-AzureSubscription

*Note: The ‘Import-Module Azure’ is more of a habit to do it.  Powershell 3.0/4.0 will search and automatically load a module the first time the cmdlet is been executed.

I just realized, after the Certificates Keys are installed in Azure, then you don’t need to execute the above commands Set-AzureSubscription and Select-AzureSubscription everytime I open the PowerShell Console.  Yes! I can start typing away and work with Azure commands.

Just try it!  If you already loaded the certificate keys, then Open a PowerShell console session and type “Get-AzureVMimage” to display the list of available Azure VM images:

WindowsAzrureValidAccess

If there’s no certificates installed, the you’ll get the following message: (on another PC)

WindowsAzureInvalidAccess

So that you know, when working with Windows Azure SQL Database Server(s), you don’t need to set up a Storage (Container) Account nor a Azure Cloud Service. Definitely you will need them when working with Windows Azure VM’s.

Next, I will be blogging on “PowerShell working with Windows Azure VM’s”.

That’s it for now,

Maximo Trinidad (MVP Windows PowerShell)
AKA Mr. PowerShell

Trap missing IP Address for SQL Database Server Firewall Rule

As I work on my second blog piece for the “Getting Ready with Windows Azure SQL Database Server PowerShell and SMO Part – 2/2“, I came up with a way to trap the current IP Address with PowerShell scripting.  When using the Portal for creating your SQL Database Server, it will ask you if you want to create the Firewall rule for you.  But you may want to automate this step using PowerShell and there’s no cmdlet to identify  the “current” IP Address of your Windows Azure connection.

Here’s an example of how the Portal message when is asking for the current IP Address to be added to the Firewall rules:

WindowsAzure2SQLdb

WindowsAzureMissingIPrule

I’m going right to the point with this small blog piece.  Basically,  I’m trapping the error message from the “New-AzureSqlDatabaseServerContext” which will fail to connect to your Azure SQL Database. Then, I’m dissecting the string to get the IP Address in error.  This is my way of trapping the IP address.  I know there might be a better way but for now it works.

I’m assuming the connection to Windows Azure has already been established and you are trying to use the “New-AzureSqlDatabaseServerContext” for connecting to the database.  If you haven’t created the rule then it won’t connect.

Note: Again, stay tuned for the next “Windows Azure SQL Database Server with PowerShell and SMO” blog part 2/2.

In the “New-AzureSqlDatabaseServerContext” I’m including the following two parameters: -ErrorAction ‘SilentlyContinue’ and -ErrorVariable errConn.  The “ErrorAction” results in not displaying the message.  The “ErrorVariable” define the PowerShell variable you will be storing the error message.  Notice the “ErrorVariable” name doesn’t include a “$” but its needed to view it (ie. $errConn).

## - Storing error value:
$azConn = New-AzureSqlDatabaseServerContext  `
-ServerName $azServerName -Credential $azCredential `
-ErrorAction 'SilentlyContinue' -ErrorVariable errConn;

The additional script code shown next will dissect the error message string from $errConn variable. It will take the string to create an array which will help identify the element position where the IP Address is stored.  In this case I’m assuming the error message will not change so the IP Address will always be located in the same place (Right!).  So the first time this code execute, it will find the IP Address in element #18.

Note: Please run first the code to identify the element position in case the “Culture” settings might change the location of the IP Address.

## - Extract information from the ErrorVariable:
$getIPforFW = ([string] $ErrConn[0]).split(" '");

## - Display all variable stored in the array and identify where the IP address is stored:
$global:x = 0;
$getIPforFW | Select-Object @{label='element';Expression={"[$($Global:x)]"; $Global:x++}}, `
@{label = 'Array';Expression={[string] $($_);}};

## - Run once to confirm IP value is stored in element #18:
$getIPforFW[18].Trim();

TrappingIPfromError

The rest is easy.  After extracting the IP value then you can use the “New-AzureSqlDatabaseServerFirewallRule” to create the firewall rule to include the current IP Address.

## - Get current IP and added it to the SQL Database Firewall Rule:
New-AzureSqlDatabaseServerFirewallRule `
-ServerName "YourServerName" -RuleName "YourClientIPAddressRule" `
-StartIPAddress $getIPforFW[18].Trim() -EndIPAddress $getIPforFW[18].Trim();

CreateFilewallfromIPfound

You can refine this script code to suit your need.  Just make sure to test a few times and verify you are getting the results you need.

Here’s a few more commands you could use to work with these rules.  The “Remove-AzureSqlDatabaseServerFirewallRule” to remove any existing rule(s) and the “Get-AzureSqlDatabaseServerFirewallRule” to list them all.

## - List all your SQL Database Firewall Rules:
Get-AzureSqlDatabaseServerFirewallRule -ServerName "YourServerName";

## - Removing existing SQL Database Firewall Rule:
Remove-AzureSqlDatabaseServerFirewallRule `
-ServerName "YourServerName" -RuleName "YourClientIPAddressRule"";

So, at the end, you will have the ability to automate the process without the need of using the Portal.  Most can be done using PowerShell scripting.

Stay tuned for more Windows Azure SQL Database Server.

That’s it for now!

Maximo Trinidad
Mr. PowerShell
🙂