GO for it with SQLCMD: Introducing the Modern SQLcmd

Introduction

Today, we’re covering a new enhancement to SQLCMD, now called Go-SQLcmd. This tool allows you to easily spin up SQL Server containers, providing a more efficient way to manage SQL Server environments.

Lightweight Development Environment

Previously, you might have installed SQL Server Developer Edition on your computer to test features, write queries, and create a sandbox environment. While effective, this approach can be cumbersome and time-consuming. Go-SQLcmd offers a more lightweight and quicker development setup using Docker containers. This means you can get started with SQL Server in a matter of seconds, without the overhead of a full installation.

Prerequisites

To follow this example, you’ll need:

• Docker Desktop: Install Docker Desktop on Windows
• WSL (Windows Subsystem for Linux): Install with the command wsl --install -d Ubuntu Install WSL
• Winget: The Windows Package Manager

Capabilities of Go-SQLcmd

Go-SQLcmd allows you to query existing SQL Server and Azure SQL Database instances. It also introduces new features, such as:

• Creating containers with a single line of code
• Restoring databases from backup files when instances are created
• Opening Azure Data Studio for data exploration

Features of Go-SQLcmd

• Open Source: Available under the MIT license on GitHub.
• Cross-Platform: Compatible with Windows, macOS, and Linux for both X64 and ARM64 architectures.
• Authentication: Supports various Azure Active Directory authentication methods, including:
  • ActiveDirectoryDefault
  • ActiveDirectoryIntegrated
  • ActiveDirectoryPassword
  • ActiveDirectoryInteractive
  • ActiveDirectoryManagedIdentity
  • ActiveDirectoryServicePrincipal
• Enhanced Functionality: Includes all SQLCMD features and improvements, such as vertical result formatting.

Installation

To install Go-SQLcmd, use Winget:

winget install sqlcmd

Usage

Display Help

To see the help documentation for Go-SQLcmd, use the following command:

sqlcmd --help

Show Context

To view the current configuration context, use:

sqlcmd config view

Spinning Up a Container

To create a new SQL Server container, use the following command. This will spin up a container with SQL Server:

sqlcmd create mssql --accept-eula

To connect to the newly created container and query its version, use:

sqlcmd query "SELECT @@version"

To avoid having to accept the End User License Agreement (EULA) every time, set an environment variable:

[System.Environment]::SetEnvironmentVariable('SQLCMD_ACCEPT_EULA', 'YES', 'Machine')

Additional Commands

List Available Tags

To see all available SQL Server versions that can be created, use:

sqlcmd create mssql get-tags

Open in Azure Data Studio

To open Azure Data Studio and connect it to the SQL Server container, use:

sqlcmd open ads

Advanced Usage

Pause the Container

If you need to pause the SQL Server container, use the following command:

sqlcmd stop

Create a Container with a Specific Version and Restore Database

To create a SQL Server container with a specific version and restore a database from a backup file, use:

sqlcmd create mssql --accept-eula --tag 2019-latest --using AdventureWorksLT.bak

Clean Up

To stop and delete a SQL Server container, use the following commands:

sqlcmd stop
sqlcmd delete

Example Scenarios

Install Previous Version

To see all available SQL Server release tags and install a previous version, use:

sqlcmd create mssql get-tags
sqlcmd create mssql --tag 2019-latest

Create SQL Server with AdventureWorks Sample Database

To create a SQL Server container and attach the AdventureWorks sample database, use:

sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak

Create SQL Server with Custom Database Name

To create a SQL Server container and attach the AdventureWorks sample database with a custom name, use:

sqlcmd create mssql --using https://aka.ms/AdventureWorksLT.bak,adventureworks

Create SQL Server with Empty User Database

To create a SQL Server container with an empty user database, use:

sqlcmd create mssql --user-database db1

Install with Full Logging

To create a SQL Server container with full logging enabled, use:

sqlcmd create mssql --verbosity 4

Conclusion

Go-SQLcmd offers a practical and efficient way to manage SQL Server environments. By using this tool, developers and database administrators can quickly spin up SQL Server instances in a lightweight manner. The ability to create containers, restore databases from backups, and integrate seamlessly with Azure Data Studio makes Go-SQLcmd a useful addition to your toolkit.