MINUTE BY MINUITE PRODUCTION RUNBOOK FOR FULLY AUTOMATED MIGRATION FROM SAP ASE TO SQL Server Azure VM
---
OVERALL STRUCTURE
Breaking execution phase into controlled phases with automation hooks:
1. Pre-Cutover Lockdown
2. Final Sync
3. Validation
4. Cutover
5. Post-Go-Live Stabilization
Each step includes:
* Time
* Script
* What
* Why
---
PHASE 1 — PRE-CUTOVER LOCKDOWN
---
T-10 MIN — PRE-FLIGHT CHECK (AUTOMATED)
---
PowerShell Script
```powershell
Write-Host "Starting Pre-Flight Checks..."
#Check SQL Server connectivity
Invoke-Sqlcmd -ServerInstance "SQLServerVM" -Query "SELECT GETDATE()"
#Check disk space
Get-PSDrive -PSProvider FileSystem
#Check SQL services
Get-Service -Name MSSQLSERVER
```
# WHAT
* Verifies SQL Server is reachable
* Confirms disk space
* Confirms services are running
---
#WHY
If anything fails here, STOP immediately.This avoids starting a migration on an unstable system.
---
T-5 MIN — FORCE DISCONNECT USERS
---
#SQL Script
```sql
ALTER DATABASE BankingDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
```
# WHAT
* Kicks out all users
* Ensures no active transactions
---
# WHY
Open transactions can leads to data inconsistency during final sync.
---
PHASE 2 — FINAL SYNC (CRITICAL DATA CONSISTENCY STEP)
---
#T+0 MIN — FREEZE SOURCE (ASE)
---
# ASE Command
```sql
sp_dboption dbname, 'read only', true
go
```
---
# WHAT
* Stops all writes to ASE
---
# WHY
The above script guarantees no new data changes during final sync.
---
T+2 MIN — RUN FINAL DELTA SYNC (AUTOMATED)
PowerShell + BCP
```powershell
$tables = @("Transactions","Accounts")
foreach ($table in $tables) {
bcp "SELECT * FROM $table WHERE LastModified > '$LastSyncTime'" queryout "$table.dat" -S ASE_Server -T -c
bcp "BankingDB.dbo.$table" in "$table.dat" -S SQLServerVM -T -c -b 100000 -h "TABLOCK"
}
```
# WHAT
* Extracts only changed data
* Loads into SQL Server
---
# WHY
* Avoids full reload
* Minimizes downtime
---
PHASE 3 — VALIDATION (NO SHORTCUTS HERE)
---
T+10 MIN — ROW COUNT VALIDATION
---
# SQL Script
```sql
SELECT
(SELECT COUNT(*) FROM ASE_DB.dbo.Transactions) AS ASE_Count,
(SELECT COUNT(*) FROM BankingDB.dbo.Transactions) AS SQL_Count;
```
---
# WHAT
* Compares row counts
---
# WHY
Quick and effective first validation step
---
T+12 MIN — CHECKSUM VALIDATION
---
# SQL Script
```sql
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM BankingDB.dbo.Transactions;
```
---
# WHAT
* Validates data integrity
---
# WHY
Row counts can match but data can still differ — this catches that
---
CONDITIONAL LOGIC (AUTOMATED DECISION)
---
# PowerShell Logic
```powershell
if ($ASE_Count -ne $SQL_Count) {
Write-Host "Mismatch detected. Re-running sync..."
# trigger re-sync
}
else {
Write-Host "Validation passed"
}
```
---
# WHY
Automation removes human error in decision-making
---
PHASE 4 — CUTOVER (POINT OF NO RETURN)
---
T+15 MIN — SWITCH DATABASE MODE
---
# SQL Script
```sql
ALTER DATABASE BankingDB SET MULTI_USER;
```
---
# WHAT
* Allows users to connect
---
# WHY
System must be accessible before app connects
---
T+16 MIN — UPDATE CONNECTION STRING
---
#PowerShell (App Config Update)
```powershell
$config = "C:\App\config.xml"
(Get-Content $config) -replace "ASE_Server","SQLServerVM" | Set-Content $config
```
# WHAT
* Redirects application to SQL Server
---
# WHY
This is the **actual cutover moment**
---
# T+18 MIN — START APPLICATION
---
# PowerShell
```powershell
Start-Service -Name "BankingAppService"
```
---
# WHAT
* Brings application online
---
# WHY
Users can now access SQL Server instead of ASE
---
PHASE 5 — POST-GO-LIVE VALIDATION
---
# T+20 MIN — ACTIVE CONNECTION CHECK
---
# SQL Script
```sql
SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1;
```
---
# WHAT
* Confirms users are connecting
---
# WHY
Validates real-world usage
---
T+25 MIN — PERFORMANCE CHECK
---
# SQL Script
```sql
SELECT wait_type, wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
```
---
# WHAT
* Detects bottlenecks
---
# WHY
Early detection prevents production incidents
---
T+30 MIN — AUTOMATED BACKUP
---
# SQL Script
```sql
BACKUP DATABASE BankingDB
TO DISK = 'D:\Backup\post_cutover.bak'
WITH COMPRESSION;
```
---
# WHAT
* Takes immediate backup
---
# WHY
Creates a safe restore point after migration
---
FULL AUTOMATION MASTER SCRIPT (ORCHESTRATION)
---
## PowerShell MASTER RUNBOOK
```powershell
Write-Host "=== MIGRATION START ==="
# Step 1: Pre-check
Invoke-Sqlcmd -ServerInstance "SQLServerVM" -Query "SELECT GETDATE()"
# Step 2: Set single user
Invoke-Sqlcmd -Query "ALTER DATABASE BankingDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE"
# Step 3: Final sync
.\Run-DeltaSync.ps1
# Step 4: Validation
.\Run-Validation.ps1
# Step 5: Cutover
Invoke-Sqlcmd -Query "ALTER DATABASE BankingDB SET MULTI_USER"
# Step 6: Start app
Start-Service -Name "BankingAppService"
# Step 7: Backup
Invoke-Sqlcmd -Query "BACKUP DATABASE BankingDB TO DISK='D:\Backup\final.bak' WITH COMPRESSION"
Write-Host "=== MIGRATION COMPLETE ==="
```
---
FAILURE HANDLING (BUILT INTO RUNBOOK)
---
CASE 1 — DATA MISMATCH
# WHAT
* Stop process
* Re-run delta sync
# WHY
Ensures no corrupted data goes live
---
CASE 2 — APPLICATION FAILS
---
# Rollback Script
```powershell
Stop-Service "BankingAppService"
# Switch back config
(Get-Content $config) -replace "SQLServerVM","ASE_Server" | Set-Content $config
Start-Service "BankingAppService"
```
---
# WHY
Fast recovery = minimal business impact
---
CASE 3 — PERFORMANCE ISSUE
---
# WHAT
* Identify slow queries
* Scale Azure VM
---
# WHY
Cloud allows instant scaling
---
FINAL PRODUCTION CHECKLIST
---
Before declaring success:
* Data validated
* Application working
* Users connected
* Backup completed
* Performance stable
---
FINAL INSIGHT (IMPORTANT)
This runbook works because:
* It removes guesswork
* It uses automation over manual steps
* It includes decision logic
* It assumes things can fail
