未加星标

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

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2017 | 作者 红领巾 ] 0人收藏点击收藏

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 learningwork 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:

@julielerman @code I’ve been doing the same. SQL Server on a Mac was the missing link in being able to fully move away from windows.

― Liam Blizard (@LiamBlizard) April 7, 2017

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.


Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code
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.


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

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 volumecontainer”, then we will point the container that will run SQL Serverto 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 containerwill 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.


Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code
Notice 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 installedinside the container but that’s a messy way to use them. It’s easier to just install them on your computerdirectly. The command lines tools for Mac are sql-cli . You can install thatwith:

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 withthe MS SQL Connectcommand (C).The extension will prompt you for each parameter.

Server name: localhost
Database name: (enter)

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页:12
转载请注明
本文标题:Mashup: SQL Server on Linux in Docker on a Mac with Visual Studio Code
本站链接:http://www.codesec.net/view/558298.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(50)