abbgrade / pssmo

The PowerShell SQL Client module replaces the SQL Server utilities SQLCMD with native PowerShell commands.

PsSmo

The PowerShell SQL Client module replaces the SQL Server utilities SQLCMD with native PowerShell commands.

Installation

This module can be installed from PsGallery.

Install-Module -Name PsSmo -Scope CurrentUser

Alternatively it can be build and installed from source.

1. Install the development dependencies
2. Download or clone it from GitHub
3. Run the installation task:

Invoke-Build Install

Usage

TODO

Execute SQLCMD scripts like those created by DacFX.

Commands

Command	Description	Status
Connect-Instance	Create a new database connection.	☑
Disconnect-Instance	Close connection	☑
Invoke-Command	Execute SQLCMD scripts	☐
⮱ Batch support	Support GO statements	☑
⮱ File support	Script source from file	☑
⮱ Variable support	Support variables like $(variable)	☑
⮱ :setvar support	Support :setvar	☑
⮱ SQLCMD command support	Support SQLCMD commans like :on error	☐
Uninstall-Schema	Remove a database schema recursively	☐

Changelog

See the changelog file.

Development

• This is a Portable Module based on PowerShell Standard and .NET Standard.
• VSCode is recommended as IDE. VSCode Tasks are configured.
• Build automation is based on InvokeBuild
• Test automation is based on Pester
• Commands are named based on Approved Verbs for PowerShell Commands
• This project uses git-flow.
• This project uses keep a changelog.
• This project uses PsBuildTasks.

Status

Build

The build scripts require InvokeBuild. If it is not installed, install it with the command Install-Module InvokeBuild -Scope CurrentUser.

You can build the module using the VS Code build task or with the command Invoke-Build Build.

Testing

The tests scripts are based on Pester. If it is not installed, install it with the command Install-Module -Name Pester -Force -SkipPublisherCheck. Some tests require a SQL Server. The test creates a SQL Server in a Docker container. If needed, install Docker. The container are created using PSDocker, which can be installed using Install-Module PSDocker -Scope CurrentUser.

For local testing use the VSCode test tasks or execute the test scripts directly or with Invoke-Pester. The InvokeBuild test tasks are for CI and do not generate console output.

Release

1. Create a release branch using git-flow.
2. Update the version number in the module manifest.
3. Extend the changelog in this readme.
4. If you want to create a pre-release.
   1. Push the release branch to github, to publish the pre-release to PsGallery.
5. Finish release using git-flow.
6. Check if tags are not pushed to github.
7. Check if the release branch is deleted on github.
8. Create the release on github.

pssmo's Issues

Add Usage Examples to Readme

Tests fail on Linux

The tests depend on localdb, which is currently only available on windows.

Switch to PsSqlTestServer

Outdated target framework

netcoreapp2.1 is not longer supported and must be updated.

use dependabot

Connection timeout in windows build validation

Message:

Win32Exception: The wait operation timed out.
SqlException: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=80714; handshake=11;
at , D:\a\PsSmo\PsSmo\test\Connect-Instance.Tests.ps1:24

Change from System.Data.SqlClient to Microsoft.Data.SqlClient

https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

The Microsoft.Data.SqlClient package, now available in preview on NuGet, will be the flagship data access driver for SQL Server going forward.

This new package supports both .NET Core and .NET Framework.

Token Refresh

In authentication with Azure, during the connect a token is created but never refreshed.
For long running sessions this will not work unless token refresh is implemented.

Automated builds in build validation pipeline are failing for Linux

See failed build validation run

Release 1.0

Test Automation

Basic Documentation

Support the PowerShell help system

Delete schema

the should be a command to remove a SQL schema recursively with all it's tables etc.

Extract SQLCMD to own lib

The functions to interpret SQLCMD commands should be in a specialized library for reusability.
This may require a mapping from a .NET logging framework to the PowerShell logging approach.

Migrate PsSmo to PowerShell Assembly Load Context

Long-running SQL Scripts

Test and support long running scripts.
I looks like, scripts are terminated after 15 minutes.

Continuous Integration