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:
- Installing Docker
- Setting Up the SQL Server Docker Container
- Connect to Your Database with a SQL Client
- Minimum .NET Core Project Setup
- Initializing the Database
- Putting It All Together
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:
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.
- 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:
- 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.
Migrations were not run.
info: Microsoft.EntityFrameworkCore.Infrastructure 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
Migrations were not run.
info: Microsoft.EntityFrameworkCore.Infrastructure 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
While the console showed success, no tables were created under <YourDatabaseName>. Running the following sql revealed the tables were created in the master database.
Entity Framework Core 2.1.1-rtm-30846 initialized ‘TutorialDatabaseContext’ using provider ‘Microsoft.EntityFrameworkCore.SqlServer’ with options: None
Executed DbCommand (6ms) [Parameters=, CommandType=’Text’, CommandTimeout=’30’]
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
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.
- Start SQL Server Docker Container
docker start sql
- Build your project
- 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:
OKEMOS, MI, April 11, 2023 – Kunz, Leigh and Associates (KL&A) announces the recent retirement of KL&A Co-Founder John Leigh as of March 31, 2023. Mr. Leigh began his career as a developer working on large mainframe systems before moving…
Blog Barista: Dana Graham | June 15th, 2022 | Culture | Brew time: 5 min
Let me start by saying I don’t care for the term “work family.” I have a family I love, and they have absolutely nothing to do with my career. I want my work life to be its own entity. I like boundaries (and the George Costanza Worlds Theory). Certainly, I want to enjoy and trust my coworkers, and I want to feel supported and cared for…