Category: Windows 2012

Updating ActiveDirectory module in Windows 10

Posted on by admin

Do you want to use “ActiveDirectory” module in PowerShell 7 RC.1 in Windows 10? For those who haven’t notice yet, seem like one of the recent updates to Windows 10 RTM Build 1909 will includes the latest version of this module.

“ActiveDirectory” module version 1.0.0.0 will not work in PowerShell 7 RC.1. It will give the following error during the import module process:

“ActiveDirectory module imports, but the PSDrive AD: is not created.
Set-Location: Cannot find drive. A drive with the name ‘AD’ does not exist.”

To correct the issue, you will need to update this module to version 1.0.1.0.

How to install the updated version?

First, make sure you have installed all of the latest Windows updates. Previous ActiveDirectory module will be on version 1.0.0.0.

To install, look in the “App or remove programs | Optional Features” then look under “Add a feature” for the “RSAT: Active Directory Domain Services and Lightweight Directory Services Tools.”

It will replace the previous version with the newer one and will work with PowerShell 7 RC.1.

Remember

To use this module the system need to be a member of a domain, or you’ll get the following error message:

“WARNING: Error initializing default drive: ‘Unable to find a default server with Active Directory Web Services
running.’.”

Also, it’s only available for Windows 10 RTM Build 1909, Windows 10 Insider Edition, and Windows Server 2019.

SSMS Version 17.4 no more SQLPS Module

Posted on by admin

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 Studio” product, 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!!

Installing PowerShell Core in Server Core

Posted on by admin

In the following Virtual machine scenario, I got one Server Core with Active Directory (Build 16299) and Windows 10 (Build 16299) joined to my new domain. Both Build 16299.
On my Windows client I create a shared folder named “SharedFiles”, where I copy over the latest MSI version of PowerShell Core “PowerShell-6.0.0-win-x64.msi”.

Then, on the Server Core I’m going to create a map drive to my Windows client shared folder to then run the MSI installation from Windows PowerShell Console.

Install Steps

Here are the steps on Server Core Command Prompt:

1. Open Windows PowerShell:

[sourcecode language=”powershell”]
## – Start PowerShell:
PowerShell

[/sourcecode]

2. Map network drive to Windows Client shared folder, then change drive:

[sourcecode language=”powershell”]
## – Map network drive:
New-PSDrive -Name “m” -PSProvider “FileSystem” -Root “\\systemname\sharedfolder”;

## – Change to drive to “m:” and verify the MSI file is there:
m:
dir

[/sourcecode]

3. At the “m:” drive, execute the installation with the following command:

[sourcecode language=”powershell”]
## – Start installing PowerShell Core:
msiexec /i .\PowerShell-6.0.0-win-x64.msi /q

[/sourcecode]

4. As this is a silent installation, so give it a few minutes, then exit to the command prompt
[sourcecode language=”powershell”]
## – type “exit” to get back to the command prompt
exit

## – Change directory to PowerShell Core, and execute pwsh.exe:
cd “C:\Program Files\PowerShell\6.0.0”
pwsh

[/sourcecode]

Setting ExecutionPolicy and Update Help

It has become a routine that after first time openning Powershell, that we need to: 1) set the ExecutionPolicy, and update the help documentation:

So, setting the execution policy, pick the one you need. The most common ones are: “RemoteSigned”, “ByPass”, “AllSigned”, “Unrestricted”.

[sourcecode language=”powershell”]
## – Example setting ExecutionPolicy as “RemoteSigned”
Set-ExecutionPolicy -ExecutionPolicy “RemoteSigned”;

[/sourcecode]

Then, its very important to update the help documentation. I had the tendency to always include to parameter “-Force“:

[sourcecode language=”powershell”]
## – Updating Help documentation:
Update-Help -Force

[/sourcecode]

And, you’re ready to work with PowerShell Core on Server Core.

Oh! How to uninstall PowerShell Core

Let go a little further. Here are the steps to successfully uninstall PowerShell Core on Server Core.
Open Windows PowerShell, make a list of all installed products using Get-WMIObject command:

[sourcecode language=”powershell”]
## – Listing installed products
get-wmiobject Win32_Product `
Select IdentifyingNumber, Name, LocalPackage `
| Format-Table -AutoSize;

[/sourcecode]

This will confirmed PowerShell Core is installed. Pay close attention to the “IdentifyingNumber” property which is really the product GUID.

Now, we can proceed to uninstall PowerShell Core. There are two way to execute a product MSI uninstall:

1. Providing the installation path with filename:

[sourcecode language=”powershell”]
## – Executing Uninstall MSI silently and write to log file: (Using filename)
msiexec.exe /x “c:\filename.msi” /qn /lv “C:\msilogfile.log”

[/sourcecode]

2. Using the IdentifyingNumber (Product GUID) listed when executing the Get-WMIObject:

[sourcecode language=”powershell”]
## – Executing Uninstall MSI silently and write to log file: (Using Product GUID)
msiexec.exe /x ‘{11111111-1111-1111-1111-11111111111X}’dir /qn /lv “C:\msilogfile.log”

[/sourcecode]

Image below doing MSI uninstall using GUID:

Both examples, include the use to write to a log file “C:\msilogfile.log“, along with the necessary switches to uninstall and execute silently. (Above switches are lowercase)

Additional Information

Check the following links on:

1. Installing MSI files with PowerShell: https://kevinmarquette.github.io/2016-10-21-powershell-installing-msi-files/

2. Report or check any issues with PowerShell Core: https://github.com/PowerShell/PowerShell/issues

3. PowerShell Core Reach GA (Generally Available) status: https://blogs.msdn.microsoft.com/powershell/2018/01/10/powershell-core-6-0-generally-available-ga-and-supported/

Be Bold!! Learn PowerShell Core!!

PSCore 6.0.0 – Upgrading/Uninstalling OpenSSH in Windows

Posted on by admin

This is all done with Chocolatey Package Manager for Windows. The following commands are important to know:

1. Information about the package:
choco info OpenSSH

2. Upgrade installed package:
choco upgrade OpenSSH

3. Remove installed package:
choco uninstall OpenSSH

4. Seach for a package:
choco search OpenSSH

Now, this is very important to know as these packages gets update without any notice and avoiding upgrades could impact the system.

As of the writing of this post, the latest version of Win32 OpenSSH is v0.0.24.0.

[sourcecode language=”powershell”]
## – Chocolatey package information command:
choco info OpenSSH

[/sourcecode]

Keep in mind, all these steps need to be executed with administrator privileges.

Upgrading Win32 OpenSSH

The upgrade process should be enough to get the package to the latest build. Now, I notice my latest upgrade step, I found files that shouldn’t be in the folder.

[sourcecode language=”powershell”]
## – Chocolatey upgrade package command:
choco upgrade OpenSSH

[/sourcecode]

So, take the time to verify and confirm everything is installed as expected. Don’t forget to document and/or save any file, such as: sshd_config.

Uninstalling/Installing Win32 OpenSSH

In the other hand, it doesn’t hurt to uninstall everything, check there are no files left in the folder, and that event the folder was removed. In other words, a clean installation will be perfect.

Before uninstalling, I would recommend a few extra steps, such as:

1. Stop SSH services: sshd and ssh-agent

[sourcecode language=”powershell”]
## – Stopping SSH Services:
Stop-Service sshd
Stop-Service ssh-agent

[/sourcecode]

2. Proceed to uninstall SSH services and change directory:

[sourcecode language=”powershell”]
## – Execute uninstall ssh services script from the OpenSSH folder:
.\uninstall-sshd.ps1
cd c:\
[/sourcecode]

3. Before, doing the uninstall step, make sure there are no process using OpenSSH that may have open file(s).

[sourcecode language=”powershell”]
## – Execute uninstall command:
choco uninstall OpenSSH

[/sourcecode]


Sample of the uninstall error due to an open file:

In this case, just rerun the uninstall step. The goal is to have everything removed including folders.

Now, you’re ready to do a clean installation of Win32 OpenSSH.
Please refer to the following blog post which I have recently added few thing I missed last time: http://www.maxtblog.com/2017/12/powershell-core-updated-setup-openssh-in-windows-and-linux/

For information about Chocolatey The package manager for Windows:
https://chocolatey.org/

Be Bold!! Learn PowerShell Core!!

Welcome PowerShell Core 6.0.0 GA

Posted on by admin

Yes! Today the Microsoft PowerShell Team with the help of the community has made PowerShell Core “Generally Available” and “Supported“. Feel free to read their blog post at:
https://blogs.msdn.microsoft.com/powershell/2018/01/10/powershell-core-6-0-generally-available-ga-and-supported/

Get It Now

Of course, there are little housekeeping to do, such as download links still pointing to the older version. But, while they fix the links, you go straight to the “Release” page to individually download the file: https://github.com/PowerShell/PowerShell/releases

Also, on the Linux distribution side, I found out that the auto upgrade stop working. So the following steps will help you install PowerShell Core 6.0.0 in Linux Ubuntu found in the Github “How To Install” link.

Ubuntu Power

Windows 10 WSL

If you have Windows 10 WSL (Ubuntu) install use the same instruction for Ubuntu 16.04:

[sourcecode language=”bash”]
# Import the public repository GPG keys
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add –

# Register the Microsoft Ubuntu repository
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/microsoft.list

# Update the list of products
sudo apt-get update

# Install PowerShell
sudo apt-get install -y powershell

# Start PowerShell
pwsh

[/sourcecode]

So, in either Windows or Linux, must important is to remove/uninstall the previous version. Then, install PowerShell Core 6.0.0.

Remember

Please file any PowerShell Core issues (not Windows PowerShell) in Github at: https://github.com/PowerShell/PowerShell/issues

So, in order to properly address any Windows PowerShell issues, has to be reported on UserVoice at: https://windowsserver.uservoice.com/forums/301869-powershell

As stated in the landing page under “Windows PowerShell vs PowerShell Core” section.

Follow PowerShell Core in Tweeter using #PSCore6 tag.

FLPSUG – Next Online meeting July 26th 2017

Posted on by admin

I’m working on getting a meeting with Keiser University to allow me to host my Florida PowerShell User Group Monthly meetings at their Port St. Lucie Campus location.  But, in the meantime, I setup July’s Online meeting for Wednesday 26th at 6:30pm (EST).

This month topic:

“Working with SQL Server for Linux Cross-Platform”

You’re welcome to explore the latest build of SQL Server for Linux, including everything you need to install and connect to SQL Server. He will also look into the tools that are available to use from Linux and / or Windows. Maximo will provide samples on querying for database information using Python/Java and PowerShell between two environments. This will be a demo intensive session you will not want to miss!

To register, click on the following Eventbrite link: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-july-2017-tickets-36113308879?ref=estw

I hope you can joined me in this exciting session!

South Florida SQLSaturday – Working with SQL Server in Linux session

Posted on by admin

First! Thanks to the organizers, and specially the attendees as they waited patiently to the attend my last session of the day “Working with SQL Server for Linux Cross-Platform”. It proves to be good. Love their interaction and the will to embrace technology.

As, I almost didn’t make it to the event, due to car problem, I one my coworker gave me ride to the event at Nova Southeastern University. I missed giving the early session “SQL Server working with PowerShell and Python” so I ended up merging both sessions into one.

For my surprise, the last session went better than I expected. I ran everything from Azure Cloud which work like a charm, and the attendee were awesome.

Both presentation and all demo scripts were uploaded to the SQLSaturday #627 event site. I hope you all take advantage of the resource link I provided.

In the demo it’s interesting we covered the following on PowerShell Core, Python 3.6 (Anaconda), and SQL Server 2017 (Linux):

* In Window 10, using SSMS v17.1 connecting to SQL Server 2017 in Linux
* In Linux, connect to a Windows Shared folders
* In Windows, using SSMS to restore a Windows Database into Linux SQL Server.
* Sample script using Python tk (Gui) w/pyodbc (SQL connector), and PowerShell displaying PowerShell object in a Gridview.
* Using SMO in Linux with PowerShell.

And, we did covered a lot in a short time.

By the way, I will be giving the same session at IDERA’s Geek Synch webinar, on July 12th, at 11:00am CT/12:00pm ET:

Geek Sync – I Working with SQL Server for Linux Cross-Platform
https://register.gotowebinar.com/register/9138484624537412611

Once again Thanks to everyone!

I’ll see you all in Orlando SQLSaturday #678 on October 7th!

Using Linux SQL Server SMO in PowerShell Core

Posted on by admin

Yes! It’s possible. Here’s the information in how to set it up and start doing some PowerShell scripting. But, first understand that everything posted here is still a Work-In-Progress. And, the good news, it’s all Open Source.

I hope you find the following information essential as there’s no really any instruction in how to install these components. So, let’s get started!

Where To Get It!

The Microsoft SQL Tools Service is a set of API that provided SQL Server Data Management capabilities on all system cross-platforms. It provide a small set for SMO dll’s enough to get started.

You can download the file from following Github link: https://github.com/Microsoft/sqltoolsservice 

Here’s the list of available SMO DLL’s currently include in the “SqlToolsService – ServiceLayer” file:

[sourcecode language=”text”]
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Dmf.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Management.SmoMetadataProvider.dll
Microsoft.SqlServer.Management.SqlScriptPublishModel.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoExtended.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.SqlParser.dll
[/sourcecode]

Keep in mind, this list will continue to grow and we hopefully expect more SMO DLL’s added.

Installation pre-requisites

In my case, I got various systems setup: Windows and Ubuntu 16.04. So, I make sure I download correct *zip or *tar.gz file

As, pre-requisite, you will needed to have already installed *”.NET Core 2.0 Preview 1” for the SQL Service Tools to work and remember this need to be installed in all systems.

Just in case, here’s the link to download “.NET Core 2.0 Preview 1“: https://www.microsoft.com/net/core/preview#windowscmd
https://www.microsoft.com/net/core/preview#linuxubuntu

Now, because we are working with PowerShell Core, don’t forget to install the latest build found at:
https://github.com/PowerShell/PowerShell/releases

Windows Installation

You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

To make *”Sql Tools Services” to work in PowerShell Core, I had to extract all content in the file into the “C:\Program Files\PowerShell\6.0.0-Beta.x” folder. Remember, this will replace any existing DLL’s on that folder.

*Caution: This steps should be done on a test machine as there’s always a possibility that it could PowerShell Core DLL’s.

Don’t forget that all these components are still in development but this should stopped us from trying and even contributing.

The file you’ll need to download for Windows is: microsoft.sqltools.servicelayer-win-x64-netcoreapp2.0.zip

Please, for now ignore the *microsoft.sqltools.credentials*.  If you install the Credentials DLL’s in the PowerShell Beta folder, PowerShell will not work.

Linux Installation

Now, for Linux is a different story as there’s no need to add the DLL’s in the PowerShell Core folder. You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

I would recommend doing the following steps in the Bash Console:

1. At your /home/user-name location, create the sqltoolsservice folder:

[sourcecode language=”bash”]
maxt@MyUbuntu01:~$ mkdir sqltoolsservice
[/sourcecode]

2. Change directory and Download the file for Ubuntu:

[sourcecode language=”bash”]
maxt@MyUbuntu01:~$ cd sqltoolsservice/
maxt@MyUbuntu01:~/sqltoolsservice$ wget https://github.com/Microsoft/sqltoolsservice/releases/download/v1.0.0-alpha.34/microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz
[/sourcecode]

3. Continue extract the *tar.gz into the folder:

[sourcecode language=”bash”]
maxt@MyUbuntu01:~$ tar -xzvf microsoft.sqltools.credentials-ubuntu16-x64-netcoreapp2.0.tar.gz
[/sourcecode]

That’s it for Linux. Now, you are ready to work with SMO and PowerShell.

Testing SMO in PowerShell Core

This is changing my way I script SMO in PowerShell. As my normal way I’ve been scripting SMO in PowerShell doesn’t work in PowerShell Core. Basically, a few more lines need to be added and now I will use the Add-Type to get the SMO assemblies loaded.

Loading SMO Assemblies

The first step is to load the SMO assemblies needed to start working with SQL Server. So, the following line is finally depricated and won’t work:

[sourcecode language=”powershell”]
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”)
[/sourcecode]

The old method I’ve been using for a long time will failed because is expecting the “Property Login …” to be set.

The updated way, has been replaced by the Add-Type with the following essential three assemblies:

[sourcecode language=”powershell”]
## – Loadind SQL Server SMO assemblied needed:
$Assem = (
“Microsoft.SqlServer.Management.Sdk.Sfc”,
“Microsoft.SqlServer.Smo”,
“Microsoft.SqlServer.ConnectionInfo”
); Add-Type -AssemblyName $Assem;
[/sourcecode]

The above assemblies are required in order to work since SQL Server SMO 2012 and greater. You can have limited use when connecting to SQL Servers version 2005, and possibly 2000.

Prepare connection parameters for Windows Systems

In Windows systems, we use ‘Integrated Authentication‘. But, here’s where things change a bit since SQL Server 2012 SMO. You will need to prepare the connection parameters, and set the *.UseIntegratedSecurity property to ‘true‘ (the default is ‘false‘). At the same time, you’ll need to set the password to ‘null’ in order to connect successfull.

[sourcecode language=”powershell”]
## – Prepare connection strings and connect to a Windows SQL Server:
$SQLServerInstanceName = ‘sqlsvrinst01,1439’;
$SQLUserName = ‘winUsername’;
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $null);
$SQLSrvConn.UseIntegratedSecurity = $true;
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)
[/sourcecode]

Now, you can query the PowerShell Object $SQLSrvObj.

[sourcecode language=”powershell”]
## – Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;
[/sourcecode]

Prepare connection parameters for Linux Systems

For Linux systems, we use ‘SQL Authentication’. Here we add the SQL User password, then passing the value to the SqlConnectionInfo class.  And, the *.UseIntegratedSecurity property by the default is ‘false‘.

[sourcecode language=”powershell”]
## – Prepare connection strings and connect to a Linux SQL Server:
$SQLServerInstanceName = ‘sqlsvrinst01,1439’;
$SQLUserName = ‘sqluser01’; $sqlPwd = ‘$usrpwd01!’;
$SQLSrvConn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo($SQLServerInstanceName, $SQLUserName, $SqlPwd)
$SQLSrvObj = new-object Microsoft.SqlServer.Management.Smo.Server($SQLSrvConn)
[/sourcecode]

Again, you can proceed to query the PowerShell Object $SQLSrvObj.

[sourcecode language=”powershell”]
## – Query PowerShell Object:
$SQLSrvObj.Information `
| Select-Object parent, platform, product, productlevel, `
OSVersion, Edition, version, HostPlatform, HostDistribution `
| Format-List;
[/sourcecode]

Please notice in the above image, the Windows 10 Insider Build 16215 Bash Console is running PowerShell Core. This list insider release made it possible for PowerShell Core to be functional again.

Conclusion

As we can see, this opens new opportunities to build cross-platform PowerShell scripts solutions working with SQL Servers in Linux, Windows, and others.

This is very exciting to start experiencing first hand these upcoming changes. I can’t deny that’s it’s challenging but you can’t turn down an opportunity to gain more skills.

Please, take advantage and subscribe to Microsoft Azure. Build, test, and start deploying solutions. Don’t be afraid to be creative. We all learn thru trial and errors!

This is a good time to keep up with what’s going on with technology.

Additional References:

Microsoft Azure: https://azure.microsoft.com/en-us/
Github: https://github.com/
Ubuntu: https://www.ubuntu.com/
Microsoft Windows Bash Shell: https://msdn.microsoft.com/en-us/commandline/wsl/about
Microsoft Academy: https://mva.microsoft.com/
Microsoft Channel 9: https://channel9.msdn.com/
Microsoft MVP Blog: https://blogs.msdn.microsoft.com/mvpawardprogram/
Microsoft SQL Server Docs: https://docs.microsoft.com/en-us/sql/sql-server/sql-server-technical-documentation
Microsoft PowerShell Blog: https://blogs.msdn.microsoft.com/powershell/

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

Posted on by admin

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.

PowerShell – SQL Server 2014 SMO TruncateData() Workaround

Posted on by admin

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:

[sourcecode language=”sql”]

Truncate Table databasename.schema.tablename
GO

[/sourcecode]

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:

[sourcecode language=”powershell”]
## —————————————– ##
## – 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++;
};
[/sourcecode]

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.