Running Two Instances of SQL Server Simultaneously on Windows Server 2016 Standard Using Docker Containers

Is it possible to run two instances of SQL Server simultaneously on a Windows Server 2016 Standard Edition? The answer, in a way, is yesbut not without a bit of a workaround. Let's explore the reasons why, the setup process, and what benefits this might offer.

Why Would You Even Need Two Instances?

First, let's address the question: why would you need or want to run two instances of SQL Server on a single Windows Server 2016 Standard Edition? There are several reasons:

To provide a high level of database isolation, ensuring that different databases do not interfere with each other. To enforce strict access control, ensuring that users have access only to the databases for which they are authorized. To adhere to organizational policies that require the separation of concerns between different business units or departments. To enhance security by limiting the exposure of sensitive information.

By running two instances of SQL Server, you can achieve these goals more effectively than by running one instance. Each instance can be configured to handle a specific set of databases and users, ensuring that each set operates independently and securely.

How to Set Up Two Instances Using Docker Containers

While Windows Server 2016 Standard Edition does not natively support running multiple instances of SQL Server simultaneously, you can achieve this through containerization using Docker. Docker allows you to deploy and run applications in lightweight, standalone containers. Here's a step-by-step guide on how to set up two instances of SQL Server in Docker containers:

Prerequisites

Before you begin, make sure you have the following:

Windows Server 2016 Standard Edition installed on your server. The SQL Server Docker images available. You can find the official Microsoft SQL Server Docker images on Docker Hub. Access to the command line or PowerShell on your Windows server.

Step-by-Step Setup Guide

Step 1: Install Docker

To install Docker, follow these steps:

Go to the Docker website and download the Windows Desktop installer. Run the installer and follow the on-screen instructions to complete the installation. After installation, launch Docker and check the version number to ensure it's installed correctly.

Step 2: Create a Network for the Containers

It's crucial to create a network in Docker to manage the communication between your SQL Server containers. Run the following command in your command line:

docker network create sql-network

Step 3: Start the First SQL Server Instance

To start the first SQL Server instance, use the following command:

docker run --name sql-instance1 -e SA_PASSWORDYourStrong!Passw0rd -e ACCEPT_EULAY -p 1433:1433 -d 

This command specifies the instance name (sql-instance1), the SA password, the EULA acceptance, the port mappings, and the Docker image used.

Step 4: Start the Second SQL Server Instance

Repeat the same procedure to start the second instance, with a different name (e.g., sql-instance2) and ensure the IP address is available for both:

docker run --name sql-instance2 -e SA_PASSWORDYourStrong!Passw0rd -e ACCEPT_EULAY -p 1434:1433 -d 

Note the port mapping for the second instance (1434:1433), which maps port 1433 on the second instance to port 1434 on the host.

Step 5: Verify the Instances

After running these commands, both instances should be running. You can verify this by checking the Docker container logs and listening on the specified ports:

docker ps

Use a tool like SQL Server Management Studio to connect to each instance on the respective ports (1433 and 1434) and confirm that both instances are functioning as expected.

Configuring Access Permissions

Configuring access permissions for users in each instance is straightforward. Here’s an example to demonstrate how you can set up user permissions in one of the SQL Server instances:

User1 Full Access to Database1

USE [Database1];GOCREATE USER [User1] FOR LOGIN [User1];EXEC sp_addrolemember 'db_owner', 'User1';

User2 Full Access to Database2

USE [Database2];GOCREATE USER [User2] FOR LOGIN [User2];EXEC sp_addrolemember 'db_owner', 'User2';

User3 Read Access to Both Databases

USE [Database1];GOCREATE USER [User3] FOR LOGIN [User3];GRANT SELECT ON DATABASE::[Database1] TO [User3];USE [Database2];GOGRANT SELECT ON DATABASE::[Database2] TO [User3];

User4 Read Access to Database1 Only

USE [Database1];GOCREATE USER [User4] FOR LOGIN [User4];GRANT SELECT ON DATABASE::[Database1] TO [User4];

For User0, you can assign full access to both databases in the same way:

USE [Database1];GOGRANT db_owner TO [User0];USE [Database2];GOGRANT db_owner TO [User0];

Conclusion

While it's not straightforward to run two instances of SQL Server on Windows Server 2016 Standard Edition, using Docker containers can provide a flexible and efficient way to achieve this. By carefully configuring each container, you can ensure optimal performance and security for your databases. This setup also allows for easy replication, scaling, and maintenance of your database environment.

If you are considering this approach, make sure to thoroughly test your configuration to ensure that everything works as expected. Additionally, monitor your server usage and adjust your approach based on your specific needs and requirements.