rc-migration-tests/ef-migration/infrastructure/ReadMe.md

SQL Server Container Setup

This repository contains a containerized SQL Server setup with initialization scripts and configuration options.

Prerequisites

• Docker and Docker Compose installed on your system
• At least 2GB of available memory for SQL Server
• Port 1433 available on your host machine

Quick Start

Starting the Container

With environment variables:

docker-compose -f docker-compose-infra.yml up -d

Without environment variables (uses defaults):

ACCEPT_EULA=Y MSSQL_PID=Express SA_PASSWORD=YourStrong@Passw0rd docker-compose -f docker-compose-infra.yml up -d

Building the Container

docker-compose -f docker-compose-infra.yml build

Stopping and Removing the Container

Stop the container:

docker-compose -f docker-compose-infra.yml down

Remove container and persistent volume:

docker-compose -f docker-compose-infra.yml down -v

Configuration

Environment Variables

Variable	Description	Default Value
SA_PASSWORD	SQL Server SA user password	YourStrong@Passw0rd
DROP_DATABASE	Flag to drop existing database on startup	false
ACCEPT_EULA	Accept SQL Server EULA	Y
MSSQL_PID	SQL Server edition	Express

Ports

• 1433: SQL Server port (mapped to host machine)

Volumes

• sqlserver_data: Persistent storage for SQL Server data
• ./logs: Container logs directory

Setup Process

The container uses setup.sh as its entry point, which handles the complete initialization process:

Startup Sequence

1. Starts SQL Server in the background
2. Waits up to 50 seconds for SQL Server to be ready
3. Performs health checks using sqlcmd
4. Handles database initialization or reset based on environment variables

Database Management

• Checks for existing database (st-database)
• Drops database if DROP_DATABASE=true
• Verifies database initialization status by checking for key tables
• Runs initialization scripts only if needed

Script Execution Order

1. Runs init.sql to create the database and schemas
2. Executes all SQL scripts in the schemas directory
3. Handles GO statements automatically with proper spacing
4. Maintains container operation after setup

Error Handling

• Provides detailed logging for each step
• Continues despite non-critical script errors
• Exits with proper status codes for critical failures

Database Initialization

The container automatically:

1. Initializes SQL Server
2. Creates the database 'st-database' if it doesn't exist
3. Creates the following schemas:
   • fp
   • fw
   • int
   • ob

Database Reset

To reset the database:

DROP_DATABASE=true docker-compose -f docker-compose-infra.yml up -d

Extract Scripts

The container includes several utility scripts for managing database objects:

extract_tables.sh

• Extracts table definitions into separate files
• Usage: ./extract_tables.sh <sql_file>
• Output directory: sql_objects/tables

extract_constraints.sh

• Extracts table constraints and foreign keys
• Usage: ./extract_constraints.sh <sql_file>
• Output directories:
  • sql_objects/constraints
  • sql_objects/foreign_keys

extract_indexes.sh

• Extracts index definitions
• Usage: ./extract_indexes.sh <sql_file>
• Output directories:
  • sql_objects/indexes/clustered
  • sql_objects/indexes/nonclustered
  • sql_objects/indexes/unique_clustered

extract_views.sh

• Extracts view definitions
• Usage: ./extract_views.sh <sql_file>
• Output directory: sql_objects/views

extract_procedures.sh

• Extracts stored procedure definitions
• Usage: ./extract_procedures.sh <sql_file>
• Output directory: sql_objects/procedures

Resource Limits

The container is configured with the following resource constraints:

• Memory: 2GB minimum (required for SQL Server)
• CPU: Limited to 1 core, with 0.25 core reserved

Health Checks

The container includes a health check that:

• Runs every 10 seconds
• Verifies SQL Server connectivity
• Allows 10 retries before marking as unhealthy
• Has a 10-second startup grace period

Troubleshooting

If the container fails to start:

1. Ensure at least 2GB of memory is available
2. Check the logs directory for detailed error messages
3. Verify the port 1433 is not in use by another service
4. Check SQL Server logs: docker logs sql1
5. Verify SQL Server is running: docker exec sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P $SA_PASSWORD -Q "SELECT @@VERSION"

The container will automatically restart unless explicitly stopped.