POST PHASE: Migrating from On-Premises SAP ASE (Sybase ASE to SQL Server running on Azure VM
PART 3 — POST PHASE (AFTER GO-LIVE): STABILIZE, SECURE, AND OPTIMIZE
This phase decides whether your system runs smoothly for years or slowly turns into a problem.
---
1. WHAT “POST PHASE” REALLY MEANS
After go-live, your job is to:
* Make sure the system is stable
* Make sure performance is better or equal
* Protect data with backup + HADR
* Secure the system (especially for banking)
* Monitor everything
* Optimize cost in Azure
---
2. FIRST 24–72 HOURS (CRITICAL STABILIZATION WINDOW)
This is your highest risk period.
---
What You Must Watch Continuously ?
* CPU usage
* Disk latency
* Blocking / deadlocks
* Failed queries
* Application errors
---
Quick Health Check Script
```sql
SELECT
GETDATE() AS CurrentTime,
cpu_count,
physical_memory_kb/1024 AS MemoryMB
FROM sys.dm_os_sys_info;
```
---
Check Active Queries
```sql
SELECT
session_id, status, blocking_session_id, wait_type, wait_time
FROM sys.dm_exec_requests;
```
If you see:
* Long waits
* Blocking chains→ You must act immediately
---
3. PERFORMANCE TUNING (MOST IMPORTANT SKILL)
Migration success is performance success
---
3.1 Identify Slow Queries
```sql
SELECT TOP 10
total_elapsed_time / execution_count AS avg_time,
execution_count,
query_hash
FROM sys.dm_exec_query_stats
ORDER BY avg_time DESC;
```
---
3.2 Enable Query Store (MUST DO)
```sql
ALTER DATABASE BankingDB
SET QUERY_STORE = ON;
```
---
Why Query Store?
* Tracks query performance
* Helps detect regressions
* Allows plan forcing
---
3.3 Fix Missing Indexes
```sql
SELECT *
FROM sys.dm_db_missing_index_details;
```
---
3.4 Rebuild Fragmented Indexes
```sql
ALTER INDEX ALL ON tablename REBUILD;
```
---
3.5 Update Statistics
```sql
UPDATE STATISTICS tablename;
```
---
4. MEMORY AND CPU OPTIMIZATION
---
Set Max Memory Properly
```sql
EXEC sp_configure 'max server memory (MB)', 60000;
RECONFIGURE;
```
---
Check Wait Stats
```sql
SELECT * FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;
```
---
Common Wait Types
* PAGEIOLATCH → disk slow
* CXPACKET → parallelism issue
---
5. BACKUP STRATEGY (PRODUCTION READY)
You must have a reliable backup plan immediately after go-live.
---
5.1 Full Backup (Daily)
```sql
BACKUP DATABASE BankingDB
TO DISK = 'D:\Backup\BankingDB_full.bak'
WITH COMPRESSION;
```
---
5.2 Differential Backup
```sql
BACKUP DATABASE BankingDB
TO DISK = 'D:\Backup\BankingDB_diff.bak'
WITH DIFFERENTIAL;
```
---
5.3 Transaction Log Backup (Every 5–15 min)
```sql
BACKUP LOG BankingDB
TO DISK = 'D:\Backup\BankingDB_log.trn';
```
---
5.4 Automate Using SQL Agent
Create jobs for:
* Full backup
* Log backup
* Cleanup
---
6. HADR (HIGH AVAILABILITY + DISASTER RECOVERY)
---
6.1 Validate Availability Group
```sql
SELECT * FROM sys.dm_hadr_availability_replica_states;
```
---
6.2 Failover Test (VERY IMPORTANT)
```sql
ALTER AVAILABILITY GROUP AG_Banking FAILOVER;
```
---
6.3 Backup on Secondary (Best Practice)
```sql
BACKUP DATABASE BankingDB
TO DISK = 'D:\Backup\BankingDB_secondary.bak';
```
---
7. SECURITY (BANKING LEVEL REQUIREMENTS)
For a US bank, security is NOT optional.
---
7.1 Enable Transparent Data Encryption (TDE)
```sql
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123';
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate';
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;
ALTER DATABASE BankingDB SET ENCRYPTION ON;
```
---
7.2 Create Logins and Roles
```sql
CREATE LOGIN app_user WITH PASSWORD = 'StrongPass!';
CREATE USER app_user FOR LOGIN app_user;
ALTER ROLE db_owner ADD MEMBER app_user;
```
---
7.3 Enable Auditing
```sql
CREATE SERVER AUDIT Audit_Banking
TO FILE (FILEPATH = 'D:\Audit\');
ALTER SERVER AUDIT Audit_Banking WITH (STATE = ON);
```
---
8. MONITORING AND ALERTING
---
8.1 Built-in Monitoring
* SQL Server Agent Alerts
* Performance Monitor
---
8.2 Key Metrics to Track
* CPU %
* Disk latency
* Deadlocks
* Failed logins
---
8.3 Simple Alert Example
```sql
EXEC msdb.dbo.sp_add_alert
@name = 'High CPU',
@message_id = 0,
@severity = 0,
@enabled = 1;
```
---
9. AZURE COST OPTIMIZATION
---
Key Rules
* Don’t over-provision VM size
* Use reserved instances
* Monitor disk usage
---
Resize VM if needed
* Scale up for performance
* Scale down for cost
---
10. DATA VALIDATION (POST MIGRATION)
---
Final Row Count Check
```sql
SELECT COUNT(*) FROM tablename;
```
---
Business Validation
* Reports match?
* Transactions correct?
* No missing data?
---
11. POST PHASE CHECKLIST
Follow this strictly:
* Monitor system 24–72 hours
* Enable Query Store
* Tune slow queries
* Rebuild indexes
* Update statistics
* Configure backups
* Test restore
* Validate HADR
* Enable security (TDE, audit)
* Set alerts
* Optimize cost
* Validate data with business users
---
12. FAILURE SCENARIOS + RECOVERY
---
Scenario 1 — Database Corruption
Restore:
```sql
RESTORE DATABASE BankingDB
FROM DISK = 'D:\Backup\BankingDB_full.bak'
WITH REPLACE;
```
---
#Scenario 2 — Server Crash
* Failover to secondary
* Redirect application
---
Scenario 3 — Slow Performance
* Identify slow queries
* Add indexes
* scale VM
---
13. REAL-WORLD DBA ROUTINE (DAILY TASKS)
---
Every Day
* Check alerts
* Check backups
* Monitor performance
---
Every Week
* Rebuild indexes
* Review slow queries
---
## Every Month
* Test restore
* Review capacity
---
14. SIMPLE “WAR ROOM” SCRIPT (GO-LIVE SUPPORT)
During production issues, communication matters.
---
Roles
* DBA (you)
* App team
* Network team
---
What to Say
* “Database is online and accessible”
* “Monitoring performance baseline”
* “No blocking detected”
* “Backup completed successfully”
No comments:
Post a Comment