1. Taking Full Backups with sqlcmd # Run the commands when you reach an important point in the database configuration sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "BACKUP DATABASE [v7.3.1_HUB_511_lab] TO DISK = '/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak' WITH FORMAT, INIT, NAME = 'Stage3';" sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "BACKUP DATABASE [HUB-5.1.1-lab] TO DISK = '/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_3.bak' WITH FORMAT, INIT, NAME = 'Stage3';" # Check the result sudo docker exec -it sql1 ls -l /var/opt/mssql/backups/ 2. Restoring a Specific Backup # Restore databases sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "RESTORE DATABASE [v7.3.1_HUB_511_lab] FROM DISK = '/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak' WITH REPLACE, RECOVERY;" sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'Password123' -Q "RESTORE DATABASE [HUB-5.1.1-lab] FROM DISK = '/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_3.bak' WITH REPLACE, RECOVERY;" 3. Restoring a Specific Backup via SSM # Restore database via SSM aws ssm send-command \ --instance-ids "i-0e0df3af14a11b3d1" \ --document-name "AWS-RunShellScript" \ --parameters 'commands=[ "sudo docker exec sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '\''Password123'\'' -Q \"RESTORE DATABASE [v7.3.1_HUB_511_lab] FROM DISK = '\''/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak'\'' WITH REPLACE, RECOVERY;\"" ]' \ --region "ap-southeast-2" # Check the Log in case of failure aws ssm list-command-invocations --command-id abab87ca-7abb-4746-8666-fa6ebbe67b51 --details
File and Folder Structure at the end Create mssql.conf [network] tlscert = /var/opt/mssql/secrets/server-bundle.crt tlskey = /var/opt/mssql/secrets/server.key tlsprotocols = 1.2 forceencryption = 1 Create Dockerfile: # Use the official Microsoft SQL Server 2022 image as base FROM mcr.microsoft.com/mssql/server:2022-latest # Switch to root to install packages USER root # Install required dependencies RUN apt-get update && \ apt-get install -y curl apt-transport-https gnupg2 && \ mkdir -p /etc/apt/keyrings && \ curl -sSL https://packages.microsoft.com/keys/microsoft.asc | gpg --dearmor > /etc/apt/keyrings/microsoft.gpg && \ chmod 644 /etc/apt/keyrings/microsoft.gpg && \ echo "deb [signed-by=/etc/apt/keyrings/microsoft.gpg] https://packages.microsoft.com/ubuntu/22.04/prod jammy main" > /etc/apt/sources.list.d/mssql-release.list && \ apt-get update && \ ACCEPT_EULA=Y apt-get install -y mssql-tools unixodbc-dev && \ ln -s /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd && \ ln -s /opt/mssql-tools/bin/bcp /usr/bin/bcp && \ apt-get clean && \ rm -rf /var/lib/apt/lists/* # Switch back to mssql user USER mssql Build an image # Build new image sudo docker build -t mssql-with-tools . Run commands # Run new container sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=Password123' \ -p 1433:1433 \ -v /data/containers/sql1/data:/var/opt/mssql/data \ -v /data/containers/sql1/log:/var/opt/mssql/log \ -v sql-certs:/var/opt/mssql/secrets:ro \ -v /data/mssql.conf:/var/opt/mssql/mssql.conf:ro \ --restart always \ --name sql1 \ -d mssql-with-tools Verify installation: # Test sqlcmd sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -?
Backup files from Docker Container Login to the machine running the Docker Container Copy back files in Docker container to the current directory sudo docker cp sql1:/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_2.bak ./HUB-5.1.1-lab_Stage_2.bak sudo docker cp sql1:/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_3.bak ./HUB-5.1.1-lab_Stage_3.bak sudo docker cp sql1:/var/opt/mssql/backups/HUB-5.1.1-lab_Stage_4.bak ./HUB-5.1.1-lab_Stage_4.bak sudo docker cp sql1:/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_3.bak ./v7.3.1_HUB_511_lab_Stage_3.bak sudo docker cp sql1:/var/opt/mssql/backups/v7.3.1_HUB_511_lab_Stage_4.bak ./v7.3.1_HUB_511_lab_Stage_4.bak Upload them to S3 bucket # Change the ownership of the files: sudo chown ssm-user:ssm-user *.bak # Create a timestamp variable TIMESTAMP=$(date +%Y%m%d-%H%M%S) # Upload both files to the timestamped folder aws s3 cp HUB-5.1.1-lab_Stage_2.bak s3://gcs-share/db-backup/$TIMESTAMP/ aws s3 cp HUB-5.1.1-lab_Stage_3.bak s3://gcs-share/db-backup/$TIMESTAMP/ aws s3 cp HUB-5.1.1-lab_Stage_4.bak s3://gcs-share/db-backup/$TIMESTAMP/ aws s3 cp v7.3.1_HUB_511_lab_Stage_3.bak s3://gcs-share/db-backup/$TIMESTAMP/ aws s3 cp v7.3.1_HUB_511_lab_Stage_4.bak s3://gcs-share/db-backup/$TIMESTAMP/
Create a container (SSL) First, create a new working directory and prepare your certificate files: mkdir gcs-rabbit-ssl cd gcs-secure-rabbit mkdir certs # Copy your certificates to gcs-secure-rabbit/certs: # - ca.crt # - mid-ca.crt # - server-001.crt # - server-001.key Set 644 to these certificate chmod 644 certs/* Create a rabbitmq.conf (gcs-secure-rabbit/rabbitmq.conf): # RabbitMQ Configuration File # Disable non-SSL listeners listeners.tcp = none listeners.ssl.default = 5671 # SSL configuration ssl_options.cacertfile = /etc/rabbitmq/ssl/ca-bundle.crt ssl_options.certfile = /etc/rabbitmq/ssl/server.crt ssl_options.keyfile = /etc/rabbitmq/ssl/server.key ssl_options.verify = verify_peer ssl_options.depth = 2 ssl_options.fail_if_no_peer_cert = true # Management SSL configuration management.ssl.port = 15671 management.ssl.cacertfile = /etc/rabbitmq/ssl/ca-bundle.crt management.ssl.certfile = /etc/rabbitmq/ssl/server.crt management.ssl.keyfile = /etc/rabbitmq/ssl/server.key Create a Dockerfile (e.g., gcs-secure-rabbit/DockerFile): FROM rabbitmq:3.11.10-management # Create SSL directory RUN mkdir -p /etc/rabbitmq/ssl # Copy certificates COPY ca.crt mid-ca.crt /etc/rabbitmq/ssl/ COPY server-001.crt /etc/rabbitmq/ssl/server.crt COPY server-001.key /etc/rabbitmq/ssl/server.key # Create bundle certificate RUN cat /etc/rabbitmq/ssl/mid-ca.crt /etc/rabbitmq/ssl/ca.crt > /etc/rabbitmq/ssl/ca-bundle.crt # Copy config file COPY rabbitmq.conf /etc/rabbitmq/rabbitmq.conf # Expose SSL ports EXPOSE 5671 15671 CMD ["rabbitmq-server"] Build and run the container: # Build the image sudo docker build -t gcs-secure-rabbit:latest . # Run the container sudo docker run -d --hostname secure-rabbit --name secure-rabbit \ -p 15671:15671 \ -p 5671:5671 \ --restart always \ gcs-secure-rabbit:latest Check the container logs after running it: sudo docker logs secure-rabbit See also: RabbitMQ Container - HTTP ...
Configure in AWS management console Stay in the working directory where Dockerfile is located (e.g., ~/gcs-rabbit) Open Repository page in Amazon ECR Create a repository by the code below aws ecr create-repository --repository-name gcs-normal-rabbit --region ap-southeast-2 Click “View push command” and follow the instruction with sudo command See also: RabbitMQ Container - HTTP RabbitMQ Container - SSL
Create a container (HTTP) Install Docker sudo yum install docker -y sudo systemctl start docker sudo systemctl enable docker docker --version sudo docker info Create a workiing directory mkdir gcs-rabbit cd gcs-rabbit Create “Dockerfile” # Use the official RabbitMQ image from the Docker Hub FROM rabbitmq:3.11.10-management # Set the default RabbitMQ environment variables ENV RABBITMQ_DEFAULT_USER=guest ENV RABBITMQ_DEFAULT_PASS=guest # Expose ports for RabbitMQ and the management UI EXPOSE 5672 15672 # Copy rabbitmq.conf if you have additional configurations COPY rabbitmq.conf /etc/rabbitmq/rabbitmq.conf # Start RabbitMQ server CMD ["rabbitmq-server"] Create “rabbitmq.conf” # RabbitMQ Configuration File # Listeners for AMQP (5672) and HTTP management (15672) listeners.tcp.default = 5672 management.tcp.port = 15672 # Optional: Define a specific IP address to bind to # (Uncomment the next line to specify a specific IP) # listeners.tcp.default = 0.0.0.0 # Disable SSL (since you're focusing on HTTP only) ssl_options.verify = verify_none ssl_options.fail_if_no_peer_cert = false Build a Docker Image sudo docker build -t gcs-normal-rabbit:latest . sudo docker images Test the Docker Image locall sudo docker run -d --name brown -p 5672:5672 -p 15672:15672 gcs-normal-rabbit sudo docker logs brown See also: RabbitMQ Container - SSL ...
Select the right image to be updated Configure Image Builder Configure Network Review Confirmation
sqlcmd -S d11-sql-db001.gcs.cloud -U sa -P Password123 1 > 2 > 3 < exit sqlcmd -S d11-sql-db001.gcs.cloud -U sa -P Password123 -Q "SELECT session_id, encrypt_option FROM sys.dm_exec_connections WHERE session_id = @@SPID;" session_id encrypt_option 53 FALSE
Delete all the items controlled by Group Policy (e.g., Certificates, Firewall Settings) Open “Amazon EC2Launch Settings” and click ”Shutdown with Sysprep”
OpenSSL Initial Setup Create a folder structure mkdir -p certs/{ca,mid-ca,server}/{private,certs,newcerts,crl,csr} Change the permissions chmod -v 700 certs/{ca,mid-ca,server}/private Create index files touch certs/{ca,mid-ca}/index Set a serial number openssl rand -hex 16 > certs/ca/serial openssl rand -hex 16 > certs/mid-ca/serial Copy and place the configuration files ca.conf - mid-ca.conf