EazyTMS AO nightly daily database update

Introduction

EazyTMS development needs fresh AO data.

We have implemented and deployed daily database restore from Ahola AO Live to EasyTMS AO nightly. * EasyTMS AO nightly has own environment which includes Daily updated database, client, server and service. * Developers can access to database with Rest Servers and Client. * Environment is on bckendrsvtst-0:

\\bckendsrvtst-0\EazyTMS\

Steps

Daily updated database copy from Live 1. Scheduled export from Live 2. backup.bak transfer form stable bucket to nightly bucket 3. Delete old EazyTMS_AO_nightly database 4. import to nightly SQL Instance 5. set db user with correct permissions → SQL Agent 6. deletion processes to bucket and instance databases 7. Zabbix monitoring atleast for logs to Grafana 1. Database Export - Import logs

~~Database can be same version as live, so we dont need to ran evolution to db on every deployment~~ We have automated evolution_main.sql with SQL Agent.

---

Scheduling

timing is different between summer | winter time. So utc+3 and utc+2
scheduling is marked as summer time utc+3

1. klo 03:00 stop servers, service, start db export 2. klo 03:00, after successfull export → transfer .bak between buckets 3. klo 04:00 delete existing eazytms db 4. klo after successfull databse deletion process → import db 5. klo 04:45 add db user to new db 6. klo 04:47 evolution_main.sql 7. klo 04:48 Upgrade compatibility level 7. klo 04:49 start service 8. klo 4:55 start servers

---

STABLE:

We will run the job on Teleport server with scheduled bash scripts.
Teleport -server (10.170.0.67) is in common-servers-01 -project

Bash script to export and transfer backup scheduled cron job: * Export db as a backup from production (Ahola_AO_Live) * Moving backup from stable bucket to nightly bucket /home/jaani/script/export_db.sh

#!/bin/bash

# Define variables
INSTANCE_NAME="mssql-stable"
BUCKET_PATH="gs://ao_db_live/daily-backups/AholaLiveBackupToTest.bak"
DATABASE_NAME="Ahola_AO_Live"
PROJECT_ID="stable-databases-01"

# Define variables for Cloud Storage Transfer
SOURCE_BUCKET_PATH="gs://ao_db_live/daily-backups/AholaLiveBackupToTest.bak"
DESTINATION_BUCKET_PATH="gs://scheduled-db-export/daily-backups/" # Note: Trailing slash for directory

# Define log file path and current timestamp
LOG_DIR="${HOME}/logs/db_exports" # Changed log directory to user's home
LOG_FILE="${LOG_DIR}/mssql_stable_export_$(date +'%Y%m%d_%H%M%S').log"
TIMESTAMP=$(date +'%Y-%m-%d %H:%M:%S')

# Ensure the log directory exists
mkdir -p "${LOG_DIR}"

# Redirect all subsequent output (stdout and stderr) to the log file
exec > >(tee -a "${LOG_FILE}") 2>&1

echo "--- Starting database export and transfer process at ${TIMESTAMP} ---"
echo "Project: ${PROJECT_ID}"
echo "-----------------------------------------------------------------"

# --- STEP 1: GCLOUD SQL EXPORT ---
echo "--- Step 1: Initiating Cloud SQL database export ---"
echo "Instance: ${INSTANCE_NAME}"
echo "Database: ${DATABASE_NAME}"
echo "Export Destination: ${BUCKET_PATH}"
echo "----------------------------------------------------"

gcloud sql export bak "${INSTANCE_NAME}" "${BUCKET_PATH}" \
    --database="${DATABASE_NAME}" \
    --project="${PROJECT_ID}"

# Check the exit status of the SQL export command
if [ $? -eq 0 ]; then
    echo "--- Cloud SQL database export completed successfully. ---"
else
    echo "--- ERROR: Cloud SQL database export failed. Aborting transfer. ---"
    echo "Please check the log file for details: ${LOG_FILE}"
    exit 1 # Exit with an error status if SQL export failed
fi

echo "----------------------------------------------------"

# --- STEP 2: GCLOUD STORAGE TRANSFER ---
echo "--- Step 2: Initiating Cloud Storage bucket transfer ---"
echo "Source: ${SOURCE_BUCKET_PATH}"
echo "Destination: ${DESTINATION_BUCKET_PATH}"
echo "--------------------------------------------------------"

gcloud storage cp "${SOURCE_BUCKET_PATH}" "${DESTINATION_BUCKET_PATH}"

# Check the exit status of the storage transfer command
if [ $? -eq 0 ]; then
    echo "--- Cloud Storage transfer completed successfully. ---"
else
    echo "--- ERROR: Cloud Storage transfer failed. ---"
    echo "Please check the log file for details: ${LOG_FILE}"
    exit 1 # Exit with an error status if storage transfer failed
fi

echo "--------------------------------------------------------"
echo "--- Script finished at ${TIMESTAMP} ---"
echo "All operations completed successfully."

Windows Task Scheduler jobs: * Stop AttracsService

\\bckendsrvtst-0\EazyTMS_nightly\Scripts\Manage-AttracsService.ps1 stop

* Stop AO Servers

\\bckendsrvtst-0\EazyTMS_nightly\Scripts\control_processes_eazytms_nightly.ps1 -action stop

---

NIGHTLY:

Bash script to delete old database and import new with cron job: * first delete old (Ahola_AO_EazyTMS_nightly) DB * Import latest Backup file as a new DB

#!/bin/bash

# Define variables
INSTANCE_NAME="mssql-nightly"
# This is the path to the .bak file you want to import FROM.
# Ensure this file exists in your Cloud Storage bucket.
IMPORT_BAK_PATH="gs://scheduled-db-export/daily-backups/AholaLiveBackupToTest.bak"
# This is the name of the database that will be deleted from the instance.
OLD_DATABASE_TO_DELETE="Ahola_AO_EazyTMS_nightly"
# This is the name of the database that will be created/restored to from the BAK file.
# Often, this is the same name as the deleted one to replace it.
NEW_DATABASE_TO_IMPORT="Ahola_AO_EazyTMS_nightly"

PROJECT_ID="nightly-databases-01"

# Define log file path and current timestamp
LOG_DIR="${HOME}/logs/db_operations" # Changed log directory
LOG_FILE="${LOG_DIR}/mssql_nightly_replace_db_$(date +'%Y%m%d_%H%M%S').log"
TIMESTAMP=$(date +'%Y-%m-%d %H:%M:%S')

# Ensure the log directory exists
mkdir -p "${LOG_DIR}"

# Redirect all subsequent output (stdout and stderr) to the log file
exec > >(tee -a "${LOG_FILE}") 2>&1

echo "--- Starting database replacement process at ${TIMESTAMP} ---"
echo "Project: ${PROJECT_ID}"
echo "-----------------------------------------------------------------"

# --- STEP 1: DELETE EXISTING NIGHTLY DATABASE ---
echo "--- Step 1: Deleting existing database: ${OLD_DATABASE_TO_DELETE} ---"
echo "Instance: ${INSTANCE_NAME}"
echo "----------------------------------------------------------"

# Before deleting, check if the database actually exists.
# This prevents errors if the script is re-run and the DB is already gone.
if gcloud sql databases list --instance="${INSTANCE_NAME}" --project="${PROJECT_ID}" --format="value(name)" | grep -q "^${OLD_DATABASE_TO_DELETE}$"; then
    echo "Database '${OLD_DATABASE_TO_DELETE}' found. Proceeding with deletion."
    gcloud sql databases delete "${OLD_DATABASE_TO_DELETE}" \
        --instance="${INSTANCE_NAME}" \
        --project="${PROJECT_ID}" \
        --quiet

    if [ $? -eq 0 ]; then
        echo "--- Database ${OLD_DATABASE_TO_DELETE} deleted successfully. ---"
    else
        echo "--- ERROR: Failed to delete database ${OLD_DATABASE_TO_DELETE}. Aborting. ---"
        echo "Please check the log file for details: ${LOG_FILE}"
        exit 1
    fi
else
    echo "Database '${OLD_DATABASE_TO_DELETE}' does not exist on instance '${INSTANCE_NAME}'. Skipping deletion."
fi
echo "----------------------------------------------------------"

# --- STEP 2: IMPORT NEW DATABASE ---
echo "--- Step 2: Initiating Cloud SQL database import ---"
echo "Instance: ${INSTANCE_NAME}"
echo "Import Source: ${IMPORT_BAK_PATH}"
echo "Target Database (within BAK): ${NEW_DATABASE_TO_IMPORT}"
echo "----------------------------------------------------"

# Start the import operation asynchronously and capture the operation ID
IMPORT_OPERATION_ID=$(gcloud sql import bak "${INSTANCE_NAME}" "${IMPORT_BAK_PATH}" \
    --database="${NEW_DATABASE_TO_IMPORT}" \
    --project="${PROJECT_ID}" \
    --quiet \
    --async --format="value(name)")

if [ -z "${IMPORT_OPERATION_ID}" ]; then
    echo "--- ERROR: Failed to initiate Cloud SQL database import or retrieve operation ID. Aborting. ---"
    echo "Please check the log file for details: ${LOG_FILE}"
    exit 1
fi

echo "--- Import initiated with operation ID: ${IMPORT_OPERATION_ID} ---"
echo "--- Waiting for import operation to complete... ---"

gcloud sql operations wait "${IMPORT_OPERATION_ID}" --project="${PROJECT_ID}" --timeout=unlimited

# Check the status of the import operation
IMPORT_OPERATION_STATUS=$(gcloud sql operations describe "${IMPORT_OPERATION_ID}" --project="${PROJECT_ID}" --format="value(status)")

if [ "${IMPORT_OPERATION_STATUS}" == "DONE" ]; then
    echo "--- Cloud SQL database import completed successfully. ---"
else
    echo "--- ERROR: Cloud SQL database import failed with status: ${IMPORT_OPERATION_STATUS}. ---"
    echo "Please check the log file and Google Cloud Console for operation ${IMPORT_OPERATION_ID} details."
    exit 1 # Exit with an error status if SQL import failed
fi
echo "----------------------------------------------------"

echo "--- Script finished at ${TIMESTAMP} ---"
echo "Database replacement process completed."

Windows Task Scheduler jobs: * Start AttracsService

\\bckendsrvtst-0\EazyTMS_nightly\Scripts\Manage-AttracsService.ps1 start

* Start AO Servers

\\bckendsrvtst-0\EazyTMS_nightly\Scripts\control_processes_eazytms_nightly.ps1 -action start

---

SQL Agent

Note that SQL Agent work in UTC 0 time!

Add database user to new EazyTMS_AO_Nightly database.

-- AddUserToDatabase.sql

-- This part will be skipped if 'YourLoginName' already exists.
-- If it's a Windows Login, ensure the correct format (e.g., 'YourDomain\YourWindowsUser').
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'ao_eazytms_nightly')
BEGIN
    -- For SQL Server Authentication (if you ever need to create a new one):
    -- CREATE LOGIN [YourLoginName] WITH PASSWORD = 'YourStrongPassword!', CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
    -- For Windows Authentication (if you ever need to create a new one):
    -- CREATE LOGIN [YourDomain\YourWindowsUser] FROM WINDOWS WITH DEFAULT_DATABASE = [master];
    PRINT 'Login [ao_eazytms_nightly] already exists or will be created if not found.';
END
GO

-- Use the target database where you want to add the user
USE [Ahola_AO_EazyTMS_nightly]; -- **IMPORTANT: Replace YourDatabaseName with your actual database name**
GO

-- Create the User in the database and map it to the existing Login
-- Using the provided username: ao_eazytms_nightly
IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = 'ao_eazytms_nightly')
BEGIN
    CREATE USER [ao_eazytms_nightly] FOR LOGIN [ao_eazytms_nightly]; -- **IMPORTANT: Replace YourLoginName with the actual existing login name**
    PRINT 'User [ao_eazytms_nightly] created in database [Ahola_AO_EazyTMS_nightly] for login [ao_eazytms_nightly].';
END
ELSE
BEGIN
    PRINT 'User [ao_eazytms_nightly] already exists in database [Ahola_AO_EazyTMS_nightly].';
END
GO

-- Grant permissions to the user: db_datareader and db_datawriter
ALTER ROLE [db_datareader] ADD MEMBER [ao_eazytms_nightly];
ALTER ROLE [db_datawriter] ADD MEMBER [ao_eazytms_nightly];
PRINT 'User [ao_eazytms_nightly] granted db_datareader and db_datawriter permissions in database [Ahola_AO_EazyTMS_nightly].';

****

Next Step: SQL Agent will run job: evolution-to-eazytms-nightly * evolution_main.sql from latest version (need to update manually, when there is changes to script)

Next: * SQL Agent will execute compatibility level upgrade with these commands:

-- Apply on the eazytms-nightly database (substitute the real DB name):
ALTER DATABASE [<eazytms-nightly DB>] SET COMPATIBILITY_LEVEL = 150;

-- Safety valve — get IQP features but keep the old estimator until CE is verified:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;

---

GCLOUD COMMANDS:

EXPORT DATABASE

gcloud sql export bak mssql-stable gs://ao_db_live/daily-backups/AholaLiveBackupToTest.bak \
    --database=Ahola_AO_Live \
    --project=stable-databases-01

BUCKET TRANSFER

gcloud storage cp gs://ao_db_live/daily-backups/BackupToTest.bak gs://scheduled-db-export/daily-backups/

DELETE DATABASE

gcloud sql databases delete Ahola_AO_EazyTMS_nightly --instance=mssql-nightly --project=nightly-databases-01 --quiet

IMPORT DATABASE

gcloud sql import bak mssql-nightly gs://scheduled-db-export/daily-backups/BackupToTest.bak \
    --database=Ahola_AO_EazyTMS_nightly \
    --project=nightly-databases-01 \
    --quiet