+01 (414) 230 - 5550
Docker, SQL Server
Install and Run SQL Server Docker Container on Mac

Like most people, I use Mac , Windows as well Linux OS for development and testing purposes. Primarily I use Mac for Development purpose. I have few projects which uses SQL Server as Data Storage Layer. Setting up Docker Container on Mac and Opening up the ports was pretty easy and doesn’t take more than 10 Minutes.

 

Steps followed :
  • Install Docker
  • Pull SQL Server Docker Image
  • Run SQL Server Docker Image
  • Install mssql Client
  • Install Kitematic
  • Open the Ports to connect to SQL Server from the network
  • Setup port forwarding to enable access outside the network

 

Install Docker :

Get Docker dmg image and install. Just follow the prompts and its very straight forward. 
https://docs.docker.com/docker-for-mac/install/#download-docker-for-mac https://download.docker.com/mac/stable/Docker.dmg

 

Once you have installed docker , you can verify the installation and version.

 

Pull SQL Server Docker Image ( DEV Version )

 

Create SQL Server Container from the Image and Expose it on port 1433 ( Default Port )

-d: this launches the container in daemon mode, so it runs in the background


–name name_your_container (macsqlserver): give your Docker container a friendly name, which is useful for stopping and starting containers from the Terminal.


-e ‘ACCEPT_EULA=Y: this sets an environment variable in the container named ACCEPT_EULAto the value Y. This is required to run SQL Server for Linux.


-e ‘SA_PASSWORD=Passw1rd’: this sets an environment variable for the sa database password. Set this to your own strong password. Also required.


-e ‘MSSQL_PID=Developer’: this sets an environment variable to instruct SQL Server to run as the Developer Edition.


-p 1433:1433: this maps the local port 1433 to the container’s port 1433. SQL Server, by default, listens for connections on TCP port 1433.

microsoft/mssql-server-linux: this final parameter tells Docker which image to use

 

Install SQL Client for MAC

If you don’t have npm installed in Mac, install homebrew and node.

Connect to SQL Server Instance

 

Get External Tools to Manage Docker

Kitematic

https://kitematic.com/

 

Open Up the Firewall to connect to SQL Server from outside the Host

Ensure your firewall is configured to allow the connections to the SQL Server. I turned of “Block all incoming connections” and enabled “Automatically allow downloaded signed software to receive incoming connections”. Without proper firewall configurations, you won’t be able to connect to the SQL Server outside the host.

 

 

 

Connecting from the Internet ( Port forwarding Setup )

Lets say you want to connect to the SQL Server you setup from outside the network or from anywhere via internet,you can setup port forwarding.

Get your public facing IP and setup the port forwarding for Port 1433 ( SQL Server port you have setup your docker container ). If its setup correctly , you should be able to telnet into that port to verify the connectivity.

 Unless you absolutely require it , its very bad idea to expose the SQL Server to internet. It should be behind the network , only your webserver should be accessible via internet.

 

Troubleshooting :

While launching docker container , if you get the error saying that it doesn’t have enough memory to launch SQL Server Container, go ahead and modify the memory allocation for docker container.

  • This image requires Docker Engine 1.8+ in any of their supported platforms.
  • At least 3.25 GB of RAM. Make sure to assign enough memory to the Docker VM if you’re running on Docker for Mac or Windows.

I have setup this way.

 

If you don’t provision enough memory, you will error like this.

 

 

Look into Docker logs

Following command ( docker ps -a and docker logs mcsqlserver ) shows list of running processes and docker logs.

 

Security:

I highly recommend to create least privileged accounts and disable SA login. If you are exposing your SQL Server to internet, there are ton of hacking and pentest tools which uses sa login for brute force attack.

0

Docker, SQL Server

Windows Containers do not ship with Active Directory support and due to their nature can’t (yet) act as a full-fledged domain joined objects, but a certain level of Active Directory functionality can be supported through the use of Globally Managed Service Accounts (GMSA).

 

Windows Containers cannot be domain-joined, they can also take advantage of Active Directory domain identities similar to when a device is realm-joined. With Windows Server 2012 R2 domain controllers, we introduced a new domain account called a group Managed Service Account (GMSA) which was designed to be shared by services. 

 

https://blogs.technet.microsoft.com/askpfeplat/2012/12/16/windows-server-2012-group-managed-service-accounts/

https://technet.microsoft.com/en-us/library/hh831782(v=ws.11).aspx

 

We can authenticate to Active Directory resources from Windows container which is not part of your domain. For this to work certain prerequisites needs to be met.

 

For once your container hosts shall be part of Active Directory and you shall be able to utilize Group Managed Service Accounts.
https://technet.microsoft.com/en-us/library/hh831782%28v=ws.11%29.aspx?f=255&MSPPError=-2147217396

 

The following steps needed for communicate Windows container with on premise SQL server using GMSA.
Environments are used and described for this post.

  1. Active directory Domain Controller installed on server CloudIQDC1.
    • OS – Windows Server 2012/2016.
    • The domain name is cloudiq.local
  2. Below are the Domain members (Computers) joined in DC
    • CIQ-2012R2-DEV
    • CIQSQL2012
    • CIQ-WIN2016-DKR
    • cloud-2016
  3. SQL server installed on CIQSQL2012. This will be used for GMSA testing.
    • OS – Windows 2012
  4. cloud-2016 will be used to test GSMA connection.
    • This is the container host we are using to connect on premise SQL server using GMSA account.
  5. The GMSA account name is “container_gsma”. We will create this and configure it.

 

Step 1: Create the KDS Root Key
  1. We can generate this only once per domain.
  2. This is used by the KDS service on DCs (along with other information) to generate passwords.
  3. Login to domain controller.
  4. Open PowerShell and execute the below.
  5. Verify your key using the below command.

 

Step 2: Create GMSA account
  1. Create GSMA account using the below command.
  2. Use below command to verify the created GMSA account.
  3. If everything works as expected, you’ll notice a new gMSA object in your domain’s Managed Service Account.

 

Step 3: Add GMSA account to Servers where we are going to use.
  1. Open the Active directory Admin Center.
  2. Select the container_gmsa account and click on properties.
  3. Select the security and click on add.
  4. Select only Computers
  5. Select Computers you want to use GMSA. In our case we need to add CIQSQL2012 and cloud-2016.
  6. Reboot Domain controller first to these changes take effect.
  7. Reboot the computers who will be using GMSA. In our case we need to reboot CIQSQL2012 and cloud-2016.
  8. After reboots, login to Domain controller. Execute the below command.

 

Step 4: Install GMSA Account on Servers
  1. Login to the system where the GMSA account which will use it. In our case login to cloud-2016. This is the container host we are using to connect on premise SQL server using GMSA account.
  2. Execute the below command if AD features are not available.
  3. Execute the below commands
  4. If everything is working as expected then you need to create credential spec file which need passed to docker during container creation to utilize this service account. Run the below commands to downloads module which will create this file from Microsoft github account and will create a JSON file containing required data.

 

Step 5: SQL Server Configuration to allow GMSA
  1. On SQL server create login for GMSA account and add it to “Sysadmin” role. Based on your on premise DB access, you can add suitable roles.
0

AWS, AWS RDS, Azure, SQL Server
We are evaluating pros and cons of different hosting solutions for SQL Server which best suits our business needs.  
Our business needs
Our demand is very predictable seasonal demand. We are very small and can’t afford dedicated team for managing database infrastructure.( No DBA Team) Sky high expectation from Customers on availability and reliability for about 2 months in a year. Few minutes of downtown during peak period can cause havoc to our business . Fixed budget with very little wiggle room.   Our plan is to evaluate AWS SQL Server RDS, Azure RDS , Managed solutions from hosting provider. Evaluate each option in these categories.
  1. Performance and Reliability
  2. Ability to scale up during peak loads
  3. Cost ( Based on Network , Storage, Memory and CPU )
  4. Operations Efficiency
  5. Compliance
Infrastructure Requirements :

SQL Server Enterprise Edition since we use enterprise features AlwaysOn Availability group for High Availability Geo Replication or Multi Availability zone implementation for Cloud based databases Ability to route Read/Write workloads 128 Gig RAM – Minimum 1 – 2 TB Storage with 500 Gigs of SSD for TempDB Database and High Volume Tables Memory Optimized OLTP Support which needs SQL Server 2016 Edition Ability to handle ~ 30 K IOPS during peak load.

Amazon AWS SQL Server RDS
RDS Pricing Link :    AWS SQL Server RDS Pricing http://www.ec2instances.info/rds/?selected=db.r3.8xlarge
Enterprise Edition  Single-AZ Deployment
  Price Per Hour
Memory Optimized Instances – Current Generation
db.r3.2xlarge $5.810
db.r3.4xlarge $11.404
db.r3.8xlarge $19.271
 
Multi-AZ Deployment
  Price Per Hour
Memory Optimized Instances – Current Generation
db.r3.2xlarge $11.620
db.r3.4xlarge $22.808
db.r3.8xlarge $38.542

AWS SQL Server RDS Configurations On-Demand for SQL Server (License Included) Multi-AZ Deployment Region:  US East (N. Virginia) Memory Optimized Instances – Current Generation Price Per Hour RAM : 244 GB 10 Gigabit 32 vCPU 20,000 Provisioned IOPS  
db.r3.8xlarge 244 GB 2 x 320 SSD Intel Xeon E5-2670 v2 (Ivy Bridge) 32 vCPUs 10 Gigabit

https://aws.amazon.com/rds/sqlserver/pricing/
Azure Pricing Calculator

Azure performance is measured in DTU. We have been collecting our performance metrics during load test. The following link provides lightweight utility to convert perfmon counters to Azure DTU’s.


Perfmon Counters to Azure DTU Conversion Utility Link:
Perfmon to Azure DTU calculator

Understanding DTUs Based on Microsoft definition :https://azure.microsoft.com/en-us/documentation/articles/sql-database-service-tiers/  

The Database Transaction Unit (DTU) is the unit of measure in SQL Database that represents the relative power of databases based on a real-world measure: the database transaction. We took a set of operations that are typical for an online transaction processing (OLTP) request, and then measured how many transactions could be completed per second under fully loaded.


Azure RDS Pricing Calculator Link
 : Azure RDS Pricing Calculator

Azure SQL Server Pricing Calculator
Azure Options for SQL Server

  https://azure.microsoft.com/en-us/pricing/details/sql-database/

Basic
eDTUs PER POOL MAX STORAGE PER POOL 1 MAX DBs PER POOL MAX eDTUs PER DATABASE PRICE 2
100 10 GB 200 5 ~$149/mo
200 20 GB 400 5 ~$298/mo
400 39 GB 400 5 ~$595/mo
800 78 GB 400 5 ~$1,198/mo
1200 117 GB 400 5 ~$1,800/mo
Standard
eDTUs PER POOL MAX STORAGE PER POOL 1 MAX DBs PER POOL MAX eDTUs PER DATABASE PRICE 2
100 100 GB 200 100 ~$223/mo
200 200 GB 400 100 ~$446/mo
400 400 GB 400 100 ~$900/mo
800 800 GB 400 100 ~$1,800/mo
1200 1.2 TB 400 100 ~$2,701/mo
Premium
eDTUs PER POOL MAX STORAGE PER POOL 1 MAX DBs PER POOL MAX eDTUs PER DATABASE PRICE 2
125 250 GB 50 125 ~$697/mo
250 500 GB 50 250 ~$1,399/mo
500 750 GB 50 500 ~$2,790/mo
1000 750 GB 50 1000 ~$5,580/mo
1500 750 GB 50 1000 ~$8,370/mo
0