AWS

# This is the current folder structure sh-5.2$ tree . ├── Dockerfile ├── backups │ ├── APP-6.3.2-lab_Stage_2.bak │ ├── APP-6.3.2-lab_Stage_3.bak │ ├── APP-6.3.2-lab_Stage_4.bak │ ├── v9.1.23_APP_632_lab_Stage_3.bak │ └── v9.1.23_APP_632_lab_Stage_4.bak ├── certs │ ├── server-bundle.crt │ └── server.key ├── containers │ └── sql1 │ ├── data [error opening dir] │ ├── log [error opening dir] │ └── secrets [error opening dir] └── mssql.conf Create Dockerfile file FROM mcr.microsoft.com/mssql/server:2022-latest 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 default user USER mssql Create mssql.conf file [network] tlscert = /var/opt/mssql/secrets/server-bundle.crt tlskey = /var/opt/mssql/secrets/server.key tlsprotocols = 1.2 forceencryption = 1 Build an image # Build new image sudo docker build -t mssql-with-tools . Test locally # 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 \ -v /data/backups:/var/opt/mssql/backups \ --restart always \ --name sql1 \ -d mssql-with-tools Build a custom container and push into ECR in AWS. # The container URI is below ACCOUNTID.dkr.ecr.ap-southeast-2.amazonaws.com/gcs-sql-server:latest Then run the script to deploy a MS SQL Container #============================================================================= # The following approach successfully copy "server.key" #============================================================================= # Create a Docker volume for the certificates sudo docker volume create sql-certs # Copy the necessary certificate files into the volume sudo cp /data/certs/server-bundle.crt /var/lib/docker/volumes/sql-certs/_data/ sudo cp /data/certs/server.key /var/lib/docker/volumes/sql-certs/_data # Change the ownership sudo chown -R 10001:0 /var/lib/docker/volumes/sql-certs/_data/ sudo chmod -R 600 /var/lib/docker/volumes/sql-certs/_data/ # Retrieve an authentication token and authenticate your Docker client to your registry. Use the AWS CLI: aws ecr get-login-password --region ap-southeast-2 | sudo docker login --username AWS --password-stdin ACCOUNTID.dkr.ecr.ap-southeast-2.amazonaws.com # Deploy MS SQL Server 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 \ -v /data/backups:/var/opt/mssql/backups \ --restart always \ --name sql1 \ -d ACCOUNTID.dkr.ecr.ap-southeast-2.amazonaws.com/gcs-sql-server:latest After the deployment, check the status of the container # Check the login sudo docker exec -it sql1 /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'Password123' #Check the files sudo docker exec -it sql1 ls -l /var/opt/mssql/backups

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

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/

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

Select the right image to be updated Configure Image Builder Configure Network Review Confirmation

Delete all the items controlled by Group Policy (e.g., Certificates, Firewall Settings) Open “Amazon EC2Launch Settings” and click ”Shutdown with Sysprep”

Register AWS Accounts to the Terminal Set AWS Credential The command to check the Current AWS Credentials aws sts get-caller-identity The command to clear the AWS Account from the terminal unset AWS_ACCESS_KEY_ID unset AWS_SECRET_ACCESS_KEY unset AWS_SESSION_TOKEN

Deploy a Linux machine Update OS sudo yum update -y Update Hostname and check it sudo hostnamectl set-hostname DEV-VAR-OIDC2.apj.cloud hostnamectl Update TimeZone and check it sudo timedatectl set-timezone Australia/Sydney timedatectl DNS Settings - Make sure all the DNS servers are registered sudo vi /etc/resolv.conf Install some components for any Linux OS sudo yum install sssd-ad sssd-tools realmd adcli Install some components for Amazon Linux 2023. sudo yum install oddjob oddjob-mkhomedir Check the status of Active Directory realm discover apj.cloud ...

How to Enable GUI Access via Fleet Manager Ensure SSM Agent is Installed and Running Windows EC2 instances must have the “SSM Agent” installed and running. Check the status by the powershell command Get-Service AmazonSSMAgent Attach a Role with the following policies AmazonSSMManagedInstanceCore AmazonSSMFullAccess (This is required for GUI access via Fleet Manager) How to access to EC2 via Fleet Manager Go to “Systems Manager” → “Fleet Manager” ...

Copy certificate CloudShell Copy directory cp -r wildcard-v6 wildcard-v7 ZIP the directory zip -r wildcard-v7.zip wildcard-v7 Download from CloudShell See also: OpenSSL (3) - Server Certificate OpenSSL (4) - Client Certificate