Tag Archives: sqlserver

New Pluralsight Course: Interacting with SQL Server data in Visual Studio Code on Win, Mac, Linux

imageMy latest course on Pluralsight, Cross-platform SQL Server Management for Developers using VS Code, went live earlier this month (just as I was about to hop on a plane for 2 weeks of conference travel!)

This is a course on a Visual Studio Code extension that I enjoy using so much that I wanted to share it with you. It is the mssql extension which lets you interact with SQL Server in a fairly rich way that belies the lightness of the IDE which it extends. Because VS Code is cross-platform, so are all of its extensions. So you can use this while you are coding on Windows, Mac or Linux and want to do some basic interaction with a SQL Server database.

As SQL Server examples, I used SQL Server LocalDb on Windows, SQL Server for Linux in a Docker container on a Mac and Azure SQL in the cloud. The course starts not only y showing you how to install VS Code (and some VS Code basics) and the extension but also by walking you through how to set up each of the database servers. That means it also has a lesson on Docker , installing and running an image as well as a quick start on creating a new SQL database in the Azure portal.

Once everything is set up, I dig through the features and functionality of the mssql extension. And I turned over ever possible stone to make sure you don’t miss helpful features which is the norm if you just start using such a tool without any preparation.

The course is mostly demos and very light on Powerpoint slides and I do work in Windows, on my Macbook and even in a Linux virtual machine.

imageWhat I’m also proud about this course is that if you’ve never used VS Code before, you’ll learn how to get around this amazing editor. If you’ve never used Docker before, I provide a really helpful and gentle introduction and you’ll be able to work with it. I had some great support from the team responsible for this extension as they were so happy to have this kind of attention paid to it.

So whatever language you code in, whatever O/S you work on, if you are using SQL Server (or interested in using it), this course should be a great help in mastering this very handy extension!

Below is the Table of Contents for the course.

If you need a 30-day free trial to the Pluralsight library so that you can watch this, send me a note!


Module 1: Introducing and Installing VS Code and the mssql Extension

In this module you’ll get a short introduction to the cross-platform and free developer IDE, Visual Studio Code and its mssql extension, The extension allow you to perform some key interactions with a SQL Server database without leaving the IDE. You’ll also walk through installing both the IDE and the extension on Windows and macOS

  • Module and Course Overview
  • Introducing Visual Studio Code and the mssql Extension
  • Installing Visual Studio Code on Windows
  • Installing Visual Studio Code on macOS
  • Introducing Visual Studio Code’s Coding Super Powers
  • Installing the mssql Extension in VS Code

 

 

Module 2: Preparing SQL Server for Any Platform, Locally and in the Cloud

In this module, you’ll learn how to set up a variety of SQL Servers. All of them are quick to install. You’ll learn to install SQL Server LocalDb on Windows, create an Azure SQL Database in the cloud and use a Docker image of SQL Server for Linux to quickly spin up SQL Server in a container on macOS. This will ensure that you have a SQL Server database to interact with in the rest of the course.

  • SQL Server LocalDB: The Simplest SQL Server
  • Setting Up an Azure SQL Database in the Cloud
  • Verifying the New Azure SQL Database
  • Setting Up the Last Details of Your Azure SQL Database
  • Using a Docker Container to Host SQL Server for Linux on Any O/S
  • Installing Docker and Getting the SQL Server Container Running
  • Verifying the Containerized Database
  • Understanding Persistence and Lack of Persistence in Containers
  • Pulling a Custom Image with the Sample Database in Place

 

Module 3: Connecting to the Various SQL Servers From Various Platforms

In this module, you’ll learn the ins and outs of connecting to a variety of local, cloud and containerized SQL Servers with the mssql extension. You’ll learn how to use the commands and shortcuts, how connection profiles and passwords are stored and even how to create a handy shortcut for getting mssql started.

  • mssql’s Commands and Execution Engine
  • Connecting to LocalDB While Learning More About mssql Connections
  • Connecting to Azure SQL from Windows and macOS
  • Demonstrating How mssql Securely Stores Your Passwords
  • Using ADO.NET Connection Strings to Connect
  • Connecting to the Database in the Docker Container
  • Connection Keyboard and Status Bar Shortcuts
  • Creating a Keyboard Shortcut to Start up mssql and Connect

Module 4 : Learning the mssql Basics to Connect, Query and Create

In this module you’ll start interacting with the database, executing queries and commands, and exploring  result sets. You’ll learn about the snippets and also learn about attaching to existing database files and creating new databases from scratch. Most importantly  you’ll learn about the great SQL editor and result view support that the mssql extension brings to you.

  • Attaching an Existing Database File
  • Interacting with the Results of Your First Query
  • The Intelligent Editor Window
  • Using Snippets to Speed Up Command Building
  • Exploring Multiple Result Sets Further
  • Using Snippets to See Database Metadata
  • Creating Databases, Tables and Data

Module 5: Leveraging Advanced Tips & Tricks

This module will dig deeper into mssql and provide tips that take advantage not just of mssql features, but also capabilities of Visual Studio Code to make using mssql easier.

  • Exporting Results to CSV, JSON or Excel
  • Localization of mssql’s Messages
  • Controlling Behavior Through VS Code’s Settings
  • Formatting Code in the Editor Window
  • Results Window Tricks, Shortcuts & Settings
  • Checking Out the Last Few Settings
  • Creating Your Own SQL Snippets in VS Code
  • Looking Ahead to Integrated Authentication on Mac and Linux

What’s in that sqlservr.sh file on the mssql-sqlserver-linux docker image anyway?

Update June 3, 2017: The team has revised the docker image and the bash file is gone, presumably with its logic broken up in to various locations. Still I’m glad I grabbed this when I did to satisfy my curiosity!

Microsoft has created 4 official Docker images for SQL Server: SQL Server for Linux, SQL Server Developer Edition, SQL Server Express and (windows) SQL Server vNext) . They can be found on the Docker hub (e.g. https://hub.docker.com/r/microsoft/mssql-server-linux/) and there is also a Github repository for them at github.com/Microsoft/mssql-docker. Some of the files that go along with that image are not on Github. The Dockerfile files for each image run some type of startup script. The Windows images have a PowerShell script called start.ps1. You can see those in the Github repo. The Linux image runs a bash file called sqlservr.sh. That’s not included in the repo though and I was curious what it did.

Note: I wrote a blog post about using the SQL Server for Linux container (Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code and I’m also writing an article about using the containers for my July MSDN Magazine Data Points column (watch this space).

Still a bit of a bash noob, I learned how to read a file from a docker container on ..you guessed it…StackOverflow.  Following those instructions, I created a snapshot of my running container

MySqlServerLinuImage git:(master) docker commit juliesqllinux  mysnapshot

sha256:9b552a1e24df7652af0c6c265ae5e2d7cb7832586c431d4b480c30663ab713f0

and ran the snapshot with bash:

  MySqlServerLinuImage git:(master) docker run -t -i mysnapshot bin/bash

[email protected]:/# 

Then at the new prompt (#), used ls to get the listing

[email protected]:/# ls

SqlCmdScript.sql  SqlCmdStartup.sh  bin  boot  dev  entrypoint.sh  etc  home  install.sh  lib  lib64  media  mnt  opt  proc  root  run  sbin  srv  sys  tmp  usr  var

then navigated to  folder where the bash file is and listed its contents:

[email protected]:/opt/mssql/bin# ls

compress-dump.sh  generate-core.sh  mssql-conf  paldumper  sqlpackage  sqlservr  sqlservr.sh

Once I was there I used the cat command to list out the contents of the sqlservr.sh file and see what it does. Here is the secret sauce in case, like me, you NEED to know what’s going on under the covers!

[email protected]:/opt/mssql/bin# cat sqlservr.sh 

#!/bin/bash

#

# Microsoft(R) SQL Server(R) launch script for Docker

#

ACCEPT_EULA=${ACCEPT_EULA:-}

SA_PASSWORD=${SA_PASSWORD:-}

#COLLATION=${COLLATION:-SQL_Latin1_General_CP1_CI_AS}

have_sa_password=""

#have_collation=""

sqlservr_setup_prefix=""

configure=""

reconfigure=""

# Check system memory

#

let system_memory="$(awk '/MemTotal/ {print $2}' /proc/meminfo) / 1024"

if [ $system_memory -lt 3250 ]; then

    echo "ERROR: This machine must have at least 3.25 gigabytes of memory to install Microsoft(R) SQL Server(R)."

    exit 1

fi

# Create system directories

#

mkdir -p /var/opt/mssql/data

mkdir -p /var/opt/mssql/etc

mkdir -p /var/opt/mssql/log

# Check the EULA

#

if [ "$ACCEPT_EULA" != "Y" ] && [ "$ACCEPT_EULA" != "y" ]; then

 echo "ERROR: You must accept the End User License Agreement before this container" > /dev/stderr

 echo "can start. The End User License Agreement can be found at " > /dev/stderr

 echo "http://go.microsoft.com/fwlink/?LinkId=746388." > /dev/stderr

 echo ""

 echo "Set the environment variable ACCEPT_EULA to 'Y' if you accept the agreement." > /dev/stderr

 exit 1

fi

# Configure SQL engine

#

if [ ! -f /var/opt/mssql/data/master.mdf ]; then

 configure=1

 if [ ! -z "$SA_PASSWORD" ] || [ -f /var/opt/mssql/etc/sa_password ]; then

 have_sa_password=1

 fi

# if [ ! -z "$COLLATION" ] || [ -f /var/opt/mssql/etc/collation ]; then

# have_collation=1

# fi

 if [ -z "$have_sa_password" ]; then

        echo "ERROR: The system administrator password is not configured. You can set the" > /dev/stderr

        echo "password via environment variable (SA_PASSWORD) or configuration file" > /dev/stderr

        echo "(/var/opt/mssql/etc/sa_password)." > /dev/stderr

 exit 1

 fi

fi

# If user wants to reconfigure, set reconfigure flag

#

if [ -f /var/opt/mssql/etc/reconfigure ]; then

 reconfigure=1

fi

# If we need to configure or reconfigure, run through configuration

# logic

#

if [ "$configure" == "1" ] || [ "$reconfigure" == "1" ]; then

 sqlservr_setup_options=""

# if [ -f /var/opt/mssql/etc/collation ]; then

# sqlservr_setup_options+="-q $(cat /var/opt/mssql/etc/collation)"

# else

# if [ ! -z "$COLLATION" ]; then

# sqlservr_setup_options+="-q $COLLATION "

# fi

# fi

 set +e

 cd /var/opt/mssql

 echo 'Configuring Microsoft(R) SQL Server(R)...'

 if [ -f /var/opt/mssql/etc/sa_password ]; then

 SQLSERVR_SA_PASSWORD_FILE=/var/opt/mssql/etc/sa_password /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 elif [ ! -z "$SA_PASSWORD" ]; then

 SQLSERVR_SA_PASSWORD_FILE=<(echo -n "$SA_PASSWORD") /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 else

 if [ ! -z '$sqlservr_setup_options' ]; then

 /opt/mssql/bin/sqlservr --setup $sqlservr_setup_options 2>&1 > /var/opt/mssql/log/setup-$(date +%Y%m%d-%H%M%S).log

 fi

 fi

 retcode=$?

 if [ $retcode != 0 ]; then

 echo "Microsoft(R) SQL Server(R) setup failed with error code $retcode. Please check the setup log in /var/opt/mssql/log for more information." > /dev/stderr

 exit 1

 fi

 set -e

 rm -f /var/opt/mssql/etc/reconfigure

 rm -f /var/opt/mssql/etc/sa_password

 echo "Configuration complete."

fi

# Start SQL Server

#

exec /opt/mssql/bin/sqlservr $*

Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code

I’ve been having a lot of fun with the new mssql extension for Visual Studio Code. I have an article coming in MSDN Magazine and am planning more fun as well. My latest experiment was doing a big mashup taking advantage of the fact that there is now a Linux version of SQL Server. So we are no longer limited to hosting it on Windows or Azure. The most lightweight way to host SQL Server on Linux is in a Docker container. While I am sitting in front of a MacBook typing this I’m by no means working towards abandoning my Windows development or Windows machines. I’m just happy to have more options at my disposal as well as have the ability to share what I am learning work beyond the world of Windows developers.

Containers are not stateful. There are ways around that (I’ll show you below) but I only know enough to be dangerous here. This is a great way to use SQL Server at design time. Using this for production is a totally different story and you need to do a lot more research and soul-searching before using that option.

On the other hand, there are those who do have that particular goal:

I had to go through a number of documents to do this and of course I got stuck even with those resources at my disposal. So I will share the full path of how I got this setup working.

Pre-Requisite

I already have Docker for Mac installed on my MacBook. Here is the installation link if you need to perform that step. Keep in mind that you can’t do this on a VM. I tried as I wanted to repeat this with a clean setup.

Be sure that Docker is set to use at least 4GB of memory.

2017-04-07_13-55-27.jpg

Getting the SQL Server Docker Image

This is what makes the whole thing so easy! Microsoft has created an official docker image with SQL Server for Linux already on it.

In the terminal window, you can pull and install the official image with

 sudo docker pull microsoft/mssql-server-linux

Once it’s installed, the ‘docker images’ command will show you that the image is now available on your machine.

2017-04-07_14-10-55

Although I just installed it today, you can see that the image I’m using — which is the latest version — was created by Microsoft 3 weeks ago.

Spinning Up a Container (or Two) From the Image

Now that Docker is aware of the image, you can create a container from it — which is running instance of the image.

Because we’re on a Mac and awaiting for a “bug” to get fixed, we will actually create two containers.

Depending on your familiarity with Docker, you may or may not be aware that containers are not stateful. Once you delete a container, it’s all gone! If you have persisted data in that container, it, too, is all gone. However, Docker has a feature called “Volumes” which are a way to retain state between docker instances. So when one instance is shut down, the state is stored in a Volume. When another container instance is spun up, that volume provides the container with the state from the previous instance. This is how it’s possible to use containers for databases.

Here’s a great tutorial on volumes: https://rominirani.com/docker-tutorial-series-part-7-data-volumes-93073a1b5b72
And the official docker doc:  https://docs.docker.com/engine/tutorials/dockervolumes/#/creating-and-mounting-a-data-volume-container

However there’s an issue (which looks like the resolution is around the corner) with Docker on Mac hosting the sql-server-linux image. This prevents us from using a volume for persistence in the simple way. So instead, we’ll create a separate container that is a “data volume container”, then we will point the container that will run SQL Server to the data volume container.

Creating the volume container

I’ll name mine mssqldata. Here’s the command to create it. (Don’t miss the full length of the command!)

docker create -v /var/opt/mssql --name mssqldata  microsoft/mssql-server-linux /bin/true

This volume container still uses the image as its base. But we won’t be running SQL Server from this instance.

Creating the SQL Server container

Now you can create an container where you will run SQL Server and that container will use the data volume container for the persisted data.

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 --volumes-from mssqldata -d --name sql-server microsoft/mssql-server-linux

The two environment variables (accept_eula and sa_password) are required. The userid is (gulp) ‘sa’. The password requirements are: “At least 8 characters including uppercase, lowercase letters, base-10 digits and/or non-alphanumeric symbols.”. Mine’s really fancy!

Once these exist,

docker ps

will only show you the regular container. The volume container is hidden so you need

docker ps -a

to see it.

2017-04-07_16-07-49.jpgNotice that the container I will run SQL Server on is on a port whereas the data volume container has a different status “Created”, and is not exposed on a port.

Test the Connection From the SQL Command Line

You don’t have to do this but it made me happy and it was fun. Did you know that you can interact with SQL Server from the command line? The tools are installed inside the container but that’s a messy way to use them. It’s easier to just install them on your computer directly. The command lines tools for Mac are sql-cli.

Note: On May 16th, Microsoft released the macOS version of sqlcmd and bcp (bulk copy). Good to know if you are already familiar with sqlcmd. Here’s their blog post with more details.

You can install that with:

npm install -g sql-cli

Start it up with the mssql command.

To minimum you need connect is to identify the server (which is at localhost by default, you don’t need to specify the port) and the password. It will presume sa for userid.

mssql -s localhost -p Passw0rd

If it’s successful, you’ll get some information followed by a new prompt, mssql.

Connecting to localhost...done

sql-cli version 0.4.14
Enter ".help" for usage hints.
mssql> 

 Enough of that. Now I get to use my new favorite tool. The mssql extension in Visual Studio Code.

Connect in Visual Studio Code

Once you have mssql installed in VS Code, you can begin by creating a new sql file. Either via the commands (F1 for the command pallete, MS SQL to see the commands and then New Query. This will prompt you for a connection – one parameter at a time. YOu can also start with the MS SQL Connect command (⇧⌘C). The extension will prompt you for each parameter.

Server name:  localhost
Database name: (enter)
User name: sa
Password: Passw0rd
Save Password (yes or no)
Profile name: [your choice]

If you enter everything correctly, not only will it connect, but the details will get stored as a connection profile in the VS Code settings and be available for subsequent connections.

You can see the connection status as it is connecting and then when connected in the lower right hand corner of the IDE.

2017-04-07_18-19-37.jpg

In the SQL file open in the editor, you can type SQL and see some existing snippets as well as get help from Intellisense whicih has read the schema of the data on the server. So far there’s not much.

Selecting the  sqlListDatabases snippet and then executing it (right click for Execute Query on the context menu or ⇧⌘E) displays the databases:

2017-04-07_18-22-45.jpg

Now you can use TSQL to create databases, database objects, query data. In the results pane you will see data as a grid similar to what you might see in SSMS. You can also export results to CSV or JSON. I’ve recently written an article about all of the cool things you can do with mssql which will be in teh June 2017 MSDN Magazine. But that connects to a SQL Azure database. In the meantime you can just go to the docs for the extension (aka.ms/mssql-marketplace).

Creating a Database and a Table

I let some more snippets help me to create a database and a table.

The first was the sqlCreateDatabase where I changed the snippet’s database name placholder to create a new database called LinuxReally then executed that with ⇧⌘E.

Re-running the select name from sys.databases command showed that the new database was now in the list.

Next I leveraged the sqlCreateTable snippet to help me create a new table. I named teh table DatabasesIKnow and gave it three columns

CREATE TABLE dbo.DatabasesIKnow
(
  Id INT NOT NULL PRIMARY KEY, -- primary key column
  DatabaseName [NVARCHAR](50) NOT NULL,
  KnowIt [Bit]
);

For some reason, the intellisense cache did not automatically refresh when I created this. Possibly becuse it was a new database. Even the mssql extension’s Refresh Intellisense Cache command did not kick it in. I got it working by disconnecting and reconnecting this time choosing the LinuxReally database rather than letting the extension connect to master by default. When I did that, I could see the message “Updating Intellisense…” in the status bar. After I had done this, the intellisense did auto refresh any time I modified the database schema.

Once i had the new database, I could execute “select * from dbo.DatabasesIKnow” and see the proper results. In my case, since I haven’t added data, there were no rows. But clearly it was reading from my table.

2017-04-08_09-04-55.jpg

Taking Down the sql-server Container

Now come the big docker volume tests. I first disconnected from the database inside of VS Code with⇧⌘E. Then I stopped and removed the sql-server container with the two commands:

docker stop sql-server
docker rm sql-server

But I left the data volume container (mssqldata) running.

I then created a new container instance using the same command as earlier:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 --volumes-from mssqldata -d --name sql-server microsoft/mssql-server-linux

In VS Code, I think reconnect to the server and my new database which was easy since it had been stored in the connection profiles . It’s the first one, localhost: LinuxReally.

2017-04-08_09-11-50.jpg

The connection was successful and listing databases showed my new DatabasesIKnow database . Select * from that database showed the schema. So my database was persisted in the data volume container even though I had killed and recreated the sql server container.

Next test: take down both containers.

Now it was time to see what happens when I stop and remove both containers!

docker stop sql-server
docker rm sql-server 
docker stop mssqldata
docker rm mssqldata

Next I use the same command I used previously to restart the sql-server container with the parameter to use the (not running) mssqldata volume container.

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=Passw0rd' -p 1433:1433 --volumes-from mssqldata -d --name sql-server microsoft/mssql-server-linux

That fails. I need to first run the data volume container. Unfortunately, I re-ran the create command for that and overwrote the existing container, so all was lost. GOOD LESSON THERE! 🙂

So I started from scratch. In the brand new data volume container I recreated my database and table.

After more experiments, I realized that I had misunderstood the Docker documentation on volumes. You can create copies of data volume containers and remove those. But you need to be more of a docker ninja. While you can make all kinds of copies of the container, once you RM all of the related volumes, it is gone gone. This blog post does clarify some of the info I was still confused about wrt creating/backing up/restoring data volume containers: tricksofthetrades.net/2016/03/14/docker-data-volumes/.

So the bottom line is you need to leave the Data Volume Container running in some format (the original or some flavor of copy). (I’m still finding it hard to believe that it doesn’t somehow get stored as a file you can re-run so I will update this as soon as someone corrects me!)

Windows, Mac, Linux, Azure and Anwhere You Can Host a Docker Container

And if you think this is only somehting to do on OS X (because that’s where I’m doing it) .. no no no! Did you know there is now Docker for Windows? And that VS Code is cross platform? This is such a great way to quickly get SQL Server up and running in your development environment.

2017-04-08_10-49-59.jpg

And as I said on twitter, comparing the experience of pulling the docker image and spinning up a container to the experience of installing SQL Server  on windows is something like this: