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:
Factors You Should Be Aware of to Ensure Project Success
Blog Barista: Bob Marquis, CPA, PMP | Oct 16, 2019 | Project Management | Brew time: 5 min
Project managers tend to be structured, organized, and process oriented. This is a good thing. Unfortunately for project managers, not everyone on a project is this way. Usually, people aren’t as manageable as a well-documented project plan…
Blog Barista: Jessica Davis | October 9, 2019 | Productivity | Brew time: 6 min
Welcome to Part 2 of the Harnessing the Power of Microsoft Word series. In case you missed it, I covered Microsoft Word’s editing symbols, ruler, and navigation pane features in Part 1. Today, I’m going to talk about templates…