PowerShell, SQL Server, and Linux Oh My!

The South Florida Code Camp 2017 is ON! Come and join me in this event on Saturday, March 11th 2017 all day event.

Check out the event speaking agenda: http://www.fladotnet.com/codecamp/Agenda.aspx

I will be presenting the following 3 sessions:

1. 09:50am – 11:00amWorking with SQL Server for Linux Cross-platform:
I will be showing the latest build of SQL Server vNext for Linux. Everything you need to install and connect to SQL Server. Then, what tools are available to use from either Linux and Windows. Sample on querying for database information using Python and PowerShell between two environments. This will be a demo intensive session.

2. 11:10am – 12:20pm Using Windows 10 Bash with PowerShell Open Source:
We’ll be taking a tour into Windows 10 Bash Linux subsystem. I’ll be sharing some tips on how to work with Bash, and the workaround to make PowerShell in side BASH. This is the perfect environment to start learning about Linux while you work in windows. We’ll be take advantage of how-to use cross-platform Open source product(s). All this and more from Windows 10.

3. 01:20pm – 02:30pm Using PowerShell Open Source across multiple Platforms:
PowerShell is Open Source now! Come see how you could use PowerShell cross-platform between Windows and Linux sharing scripts to assist in admin task automation. I’ll be walking on how to make this work with existing tools. Also, interacting with SQL Server for Linux.

To register go to the following link: http://www.fladotnet.com/codecamp/

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 – Exciting January 2017 New Releases

Here are a couple of interesting announcements back in January 2017:

SAPIEN 2017 all products released on 1/17/2017: https://www.sapien.com/software
Check out what’s new on PowerShell Studio 2017:
https://www.sapien.com/blog/2017/01/18/powershell-studio-2017-whats-new/

Windows Management Framework 5.1 RTM released on 1/19/2017.  More information at the following links:
https://blogs.msdn.microsoft.com/powershell/2017/01/19/windows-management-framework-wmf-5-1-released/
https://msdn.microsoft.com/en-us/powershell/wmf/5.1/install-configure

Download from: https://www.microsoft.com/en-us/download/details.aspx?id=54616

Also, don’t forget to check out Microsoft Virtual Academy (MVA) and look for existing “free” PowerShell training topics: https://mva.microsoft.com/

Current list of MVA PowerShell Topics:
https://mva.microsoft.com/search/SearchResults.aspx#!q=PowerShell&index=2&topic=PowerShell&lang=1033