Prashanth Jayaram

All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool

August 14, 2018 by

One of the major challenges we face today, in the software development lifecycle, is with respect to development and deployment. As applications are deployed by moving various pieces of the SQL code between several versions, configuration, different editions, and sometimes even different platforms, deployment becomes daunting. In my opinion, scripting helps a lot and can really “grease the wheels” with addressing many scenarios that involve such complexity.

The importance of database level scripting is high, but it is getting easier by the day. Scripting is a process of generating a text file that contains the data structures and data from the database objects.

So, do we have a SQL native tool which serves the various purposes and usage? In some cases, the answer’s a yes, and in others, it’s a no.

Database object scripting can be done in many several ways such as:

  1. Generate script wizard
  2. T-SQL
  3. PowerShell
  4. Mssql-scripter
  5. Third party tools

This article is all about using mssql-scripter for the task, and its simple capabilities to script the objects on a cross-platform SQL Servers.

In this article, we talk about:

  1. Mssql-scripter on Windows
  2. Mssql-scripter on Linux

Background

Microsoft recent announcement on SQL tool; a command line utility, DBFS and mssql-scripter, serves two different data management functions. In a previous article, Continuous Database Delivery (CD) using SQL Server Tools SqlPackage.exe, we saw how to use DBFS tools. This article is an effort to state how Python has become a more user-friendly DBA toolset, with emphasis on the next-generation database administrators. It’s all about thinking beyond the boundaries of traditional DBAs; one needs to know and understand the contemporary toolset to work on cross-platform systems.

Introduction

mssql-scripter is an open-source cross-platform command line tool. This command-line tool is used for scripting SQL Server database objects and data. It’s almost similar to the Generate Scripts Wizard in SSMS with a wide range of options.

mssql-scripter is a flexible tool which lets you generate .sql files that work anywhere including on-premises SQL Server, or on Azure. These SQL files can be T-SQL scripts; data definition language or data manipulation language. These files can also be piped to standard UNIX/Linux tools such as grep or awk or sed, to help with further manipulation.

These scripts can even be source-controlled. These scripts can be used with DevOps deployments as well, apart from SQL Database or Warehouse. That also means that the scripts are compatible with several other cross-platform CLI tools such as sqlcmd.

What’s notable here is that Python is necessary for the setup to work. Most SQL DBAs may have the question ‘Why this can’t be done using PowerShell?’ I had the same mindset as well. The reason I think, is that Python is growing in popularity under the umbrella of DevOps. Another reason is the strong language processing available in Unix/Linux commands; several powerful toolset provides various ways to get the required result.

Pre-requisites

Install Python – The recent versions of Python include pip by default. To Install Python on Windows

  1. Download the Python library from the download page.

  2. Right click the Python executable, and choose the Run as administrator option.

  3. Before selecting the Install now option, enable the Add Python 3.6 to PATH. This will update the environment variable. If you miss step, you may need it manually set the environment variable.

  4. Watch the status of the setup in the installation screen. The installation should go through without a glitch in most cases.

That’s all!

pip

This section discusses the installation and configuration of mssql-scripter. Before jumping into that, I would like to highlight some of the basics of the Python Package Management Programs.

What is pip?

pip is a package management system used to install and manage software packages, such as those found in the Python Package Index. A package installs the packages default under site-packages.

pip command has several options to install and validate the package installation process. Let’s see various pip command options:

Install the mssql-scripter package

List information about the installed packages


Show information about a package:


Show all information about a package:

Demo

Let us take a look at some scenarios on Windows and Linux

mssql-scripter on Windows

After performing all the above steps, the mssql-scripter is ready to use. The -h parameter of mssql-scripter provides a wide range of options. Please take a few minutes to understand the list of the parameters.

-h is the help switch that details all the available options used with the mssql-scripter command.

How to Connect to Linux SQL instance from Windows



How to Exclude database object from output file

For the demo, I’ve created two files: One with name test and the other, SQLShack, to generate DDL scripts of those objects that do not contain ‘test’ as their name. For this, use the –exclude-objects parameter with mssql-scripter.

Mssql-scripter on Linux

To configure mssql-scripter on a Linux distribution, CentOS/RHEL follow these steps.

To install Python-Pip using yum


Now, Pip is available for the mssql-scripter package management.

Install mssql-scripter package using pip:


Update the Python setup tools.

Run the mssql-scripter command.


How to Connect SQL Server instance on Windows from Linux server


How to project a subset of the table data using single input pattern

In the following example, the DSSPUSER data is projected into the SQL file.

How to project a subset of the table data using multiple patterns of input

In the following example, the DSSPUSER data is projected into the SQL file.


How to prepare the login script using search patterns

The grep command is used to filter out the objects that contain “create login” in the standard output.

Wrapping up

In this article, conducted a walk-through of the installation and configuration of the mssql-scripter tool on Windows and Linux platforms.

For a typical development, testing, and release management environment, scripting is a vital part of the job.

Not every solution works for every scenario, when it comes to programming techniques, scripting techniques, or tools. But mssql-scripter certainly seems promising in many cases since this tool is compatible with multiple platforms, and contains a wide range of parameters.

There may be some limitations to scripting in some cases, which need to be checked and tested on large databases.

Tell us what you think by posting your comments below!


Table of contents

Getting started building applications using SQL Server DevOps Tools
Overview of SQLCMD utility in SQL Server
The BCP (Bulk Copy Program) command in action
Continuous Deployment using SQL Server Tools SqlPackage.exe
All about MSSQL-Scripter, the SQL Server cross-platform scripting Tool
Getting started with SQL Operations Studio (SOS); initial installation and configuration

See more

The ApexSQL CI/CD toolkit, is a free automation toolkit for creating continuous database change integration and delivery pipelines to integrate database changes, test and deliver to production





Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.

View all posts by Prashanth Jayaram
Prashanth Jayaram

Latest posts by Prashanth Jayaram (see all)

DevOps, Importing, exporting

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB. View all posts by Prashanth Jayaram

264 Views