Blog Barista: Jim Rasche & Greg Antrim | Sept. 12, 2018 | Developer Tools | Brew time: 8 min

You could use one of the relational database management systems (RDBMS) that is more OS agnostic such as SQLite or MySQL, but, if you are working with an existing database or with strict requirements you may not have a choice. If you need to use Microsoft SQL Server, you do have an option: you can run SQL Server in a Docker container. This provides us with all the benefits of Docker (ease of setup, deploy, and versioning) while allowing us to develop on our preferred platform.

What we will cover:

1. Installing Docker
2. Setting Up the SQL Server Docker Container
3. Connect to Your Database with a SQL Client
4. Minimum .NET Core Project Setup
5. Initializing the Database
6. Putting It All Together

Install Docker

Nothing too difficult here. Docker provides a DMG file so install it like you would any other OSX app. Once installed, open the Docker Application and enter your password to give it privileged access. If you run into issues you can take a look at the official Docker installation instructions.

Set Up the SQL Server Docker Container

I used this SQL Server Docker tutorial initially, but it didn’t really tell the full story about how to interact with Docker after setup, so I’ll expand on the process here. We will be running the following commands:

Command:

Explanation:

The command above pulls down the 2017-latest version of the microsoft/mssql-server-linux Docker image. You chose from the available versions or leave the version tag off to automatically pull the latest image.

Command:

Explanation:

• docker run –name sql
  • Create and start a Docker container named sql
• -e ‘ACCEPT_EULA=Y’
  • Set the ACCEPT_EULA environment variable to yes to confirm your acceptance of the license agreement
• -e ‘SA_PASSWORD=<StrongPasswordYouSet>
  • Set the SA_PASSWORD environment variable to a password that you choose. Remember to replace <StrongPasswordYouSet> with a password of your choosing. This will set the password for the SA database user.
• -p 1433:1433
  • Expose port 1433 inside of the Docker container to port 1433 on the host machine.
• -d microsoft/mssql-server-linux:2017-latest
  • Use the mssql-server-linux image and start in detached mode.

At this point, your database is up and running. I’d recommend that you read through the Docker run command docs or use docker —-help to get familiar with the commands. Here are a couple that you may find helpful:

Command:

Explanation:

• docker ps -a
  • Lists all containers. The status field shows if a container is up and running
• docker image list
  • Shows all of the images you’ve pulled down
• docker start/stop <CONTAINER_NAME>
  • Start or stop a container named CONTAINER_NAME after it has been created via the docker run command

Connect to Your Database with a SQL Client

Now that your database is up and running, I’d suggest downloading a client to interact with your new database. I’m using SQLPro for MSSQL and have been relatively happy with it. After opening SQLPro you should be able to connect using these settings:

• Server Host: localhost
• Port: 1433
• Authentication: SQL Server Authentication
• Login: SA
• Password: <StrongPasswordYouSet>

If the connection is successful, create an empty database named <YourDatabaseName>. We will be running our migration on that database.

Minimum .NET Core Project Setup

These steps represent the minimum setup you need to do in order to connect to the database and run migrations. Your project setup will likely be more complex and may only use some of these steps. If you don’t want to do the project setup yourself, you can clone the GitHub Repo for this tutorial.

1. Create a new ASP.NET Core Empty App

2. Add Entity Framework Packages

6. Add an appsettings.json file to your project

Initialize the Database

Next, we are going to run our migrations to initialize the database. It took some trial and error to get the backend talking to the database. I will document what I tried as well as the results of each attempt. All of these connection strings should go in your appsettings.json file. The command dotnet ef database update was ran after changing the connection string in each attempt.

Attempt #1

Connection String:

Result:

Migrations were not run.

Command Output:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403] Entity Framework Core 2.1.1-rtm-30846 initialized ‘TestingContext’ using provider ‘Microsoft.EntityFrameworkCore.SqlServer’ with options: None System.Exception: Cannot connect to SQL Server Browser. Ensure SQL Server Browser has been started. —> System.Net.Internals.SocketExceptionFactory+ExtendedSocketException: Device not configured

Attempt #2 – Specify sqlExpress as the database

Connection String:

Result:

Migrations were not run.

Command Output:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403] Entity Framework Core 2.1.1-rtm-30846 initialized ‘TestingContext’ using provider ‘Microsoft.EntityFrameworkCore.SqlServer’ with options: None System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 25 – Connection string is not valid) —> System.Net.Sockets.SocketException (0x80004005): Undefined error: 0

Attempt #3 – Specify port with a comma instead of a colon

Connection String:

Result:

While the console showed success, no tables were created under <YourDatabaseName>. Running the following sql revealed the tables were created in the master database.

Command Output:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]

Entity Framework Core 2.1.1-rtm-30846 initialized ‘TutorialDatabaseContext’ using provider ‘Microsoft.EntityFrameworkCore.SqlServer’ with options: None

info: Microsoft.EntityFrameworkCore.Database.Command[20101]

Executed DbCommand (6ms) [Parameters=[], CommandType=’Text’, CommandTimeout=’30’]

SELECT OBJECT_ID(N'[__EFMigrationsHistory]’);

info: Microsoft.EntityFrameworkCore.Database.Command[20101]

Executed DbCommand (4ms) [Parameters=[], CommandType=’Text’, CommandTimeout=’30’]

CREATE TABLE [__EFMigrationsHistory] (

[MigrationId] nvarchar(150) NOT NULL,

[ProductVersion] nvarchar(32) NOT NULL,

CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])

);

Attempt #4 – Use both Catalog and Database arguments to specify the database

Connection String:

Result:

The migrations ran and the tables showed up in <YourDatabaseName>.

Put It All Together

Now that we have our migrations working, we just need to put everything together.

1. Start SQL Server Docker Container

docker start sql

2. Build your project

cd <Your_Solution_Directory>

dotnet publish

3. Run your project

dotnet <Your_Solution_Directory>/ <Your_Project_Directory>/bin/Debug/<sdk-version>/<projectName>.dll

Your database and application should now be running and you are free to develop on the platform of your choosing.

Other recent posts: