Mark
#1
Microsoft SQL Server (MSSQL) is a powerful relational database that integrates well with Spring Boot when properly configured. Whether you're building enterprise-level apps or local projects, Docker helps you run MSSQL without a complex setup. Combined with Spring Boot’s application.yml, you can manage your database configuration in a clean, profile-based way for both development and production environments.
This guide walks you through running MSSQL with Docker and setting up your Spring Boot configuration using application.yml files for both dev and Kubernetes-based prod environments.

Step 1: Why Use Docker for SQL Server?

Instead of manually installing and configuring SQL Server on your local machine, Docker allows you to run a self-contained SQL Server environment with minimal effort. This makes your development setup:
  • Faster to spin up
  • Consistent across teams
  • Easily disposable or resettable
  • Aligned with production environments

Step 2: Running SQL Server in Docker (Development)

To run SQL Server locally using Docker, use this command: 
docker run -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=YourStrong!Passw0rd" \  -p 1433:1433 --name mssql-dev -d mcr.microsoft.com/mssql/server:2019-latest
  • ACCEPT_EULA=Y: Accepts Microsoft’s end-user license agreement.
  • SA_PASSWORD: Sets the admin password (must meet complexity requirements).
  • 1433:1433: Exposes the default SQL Server port.
After a short wait, your MSSQL instance is up and running locally.

Step 3: Add the MSSQL JDBC Driver Dependency

To connect your Spring Boot application with SQL Server, add this dependency to your pom.xml: 
<dependency>
  <groupId>com.microsoft.sqlserver</groupId>
  <artifactId>mssql-jdbc</artifactId>
  <version>12.2.0.jre11</version> <!-- Adjust for your Java version -->
</dependency>
his allows Spring Boot to understand and communicate with MSSQL databases.

Step 4: Configure application-dev.yml for Local Setup

In your src/main/resources folder, create a file named application-dev.yml: 
spring:
  datasource:
    url: jdbc:sqlserver://localhost:1433;databaseName=testdb
    username: sa
    password: YourStrong!Passw0rd
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    hibernate:
      ddl-auto: update
Replace testdb with your actual database name. If the database doesn’t exist yet, you can create it manually using a SQL client like DBeaver or Azure Data Studio.
To activate the development profile: 
SPRING_PROFILES_ACTIVE=dev

Step 5: Kubernetes Production Setup

In production, particularly in Kubernetes environments, hardcoding database credentials or service names isn’t a good idea. Instead, use ConfigMap and Secret resources to manage your configuration.

Step 5.1: Create a Kubernetes ConfigMap and Secret

ConfigMap:

apiVersion: v1
kind: ConfigMap
metadata:
  name: mssql-config
  namespace: your-namespace
data:
  mssql_host: mssql-service
  mssql_port: "1433"
  mssql_database: prod_db

Secret (for sensitive credentials):

apiVersion: v1
kind: Secret
metadata:
  name: mssql-secret
  namespace: your-namespace
type: Opaque
data:
  mssql_user: c2E=                  # base64 for 'sa'
  mssql_password: WW91clN0cm9uZyFQYXNzdzByZA==  # base64 for 'YourStrong!Passw0rd'

Step 5.2: Inject Variables in Deployment YAML

Update your app’s Kubernetes deployment YAML to use these variables: 
containers:
  - name: your-app
    image: your-app-image
    env:
      - name: MSSQL_HOST
        valueFrom:
          configMapKeyRef:
            name: mssql-config
            key: mssql_host
      - name: MSSQL_PORT
        valueFrom:
          configMapKeyRef:
            name: mssql-config
            key: mssql_port
      - name: MSSQL_DATABASE
        valueFrom:
          configMapKeyRef:
            name: mssql-config
            key: mssql_database
      - name: MSSQL_USER
        valueFrom:
          secretKeyRef:
            name: mssql-secret
            key: mssql_user
      - name: MSSQL_PASSWORD
        valueFrom:
          secretKeyRef:
            name: mssql-secret
            key: mssql_password
      - name: SPRING_PROFILES_ACTIVE
        value: prod

Step 6: Configure application-prod.yml

Now, create your application-prod.yml file: 
spring:
  datasource:
    url: jdbc:sqlserver://${MSSQL_HOST}:${MSSQL_PORT};databaseName=${MSSQL_DATABASE}
    username: ${MSSQL_USER}
    password: ${MSSQL_PASSWORD}
    driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
  jpa:
    hibernate:
      ddl-auto: validate
This configuration uses environment variables to dynamically inject values at runtime, making your app safe and environment-agnostic.

Step 7: Managing Profiles

To keep everything clean and maintainable, use Spring profiles:
  • application-dev.yml: for local development
  • application-prod.yml: for cloud or Kubernetes production
Spring Boot will automatically pick the right file when you set SPRING_PROFILES_ACTIVE.

Example: 

SPRING_PROFILES_ACTIVE=prod
Or via Kubernetes YAML, as shown earlier.


image quote pre code
Share: