EXECUTION PHASE: Migrating from On-Premises SAP ASE (Sybase ASE to SQL Server running on Azure VM
PART 2 —EXECUTION PHASE: REAL MIGRATION STEPS YOU WILL FOLLOW
Now we move from planning to doing the migration.
This is where things can go wrong fast — so we’ll go step-by-step, simple, and safe, especially for very large (10TB–100TB) mission-critical databases.
---
1. OVERALL EXECUTION STRATEGY (VERY IMPORTANT)
You should NOT try to migrate everything at once.
Instead, follow this proven sequence:
This phase Flow
1. Prepare target SQL Server
2. Migrate schema
3. Perform initial bulk data load
4. Start incremental sync
5. Validate data
6. Cutover (final switch)
---
2. PREPARE TARGET SQL SERVER (FINAL CHECK)
Before moving data, confirm:
* SQL Server is installed and patched
* Storage is mounted correctly
* Enough space (at least 1.5x database size)
* TempDB optimized
* Network bandwidth tested
---
Quick Validation Script
```sql
SELECT name, size*8/1024 AS SizeMB
FROM sys.master_files
```
3. CREATE DATABASE STRUCTURE (ON SQL SERVER)
You already converted schema in Part 1 — now apply it.
---
Example: Create Database
```sql
CREATE DATABASE BankingDB
ON
(
NAME = BankingDB_Data,
FILENAME = 'D:\Data\BankingDB.mdf',
SIZE = 500GB,
FILEGROWTH = 10GB
)
LOG ON
(
NAME = BankingDB_Log,
FILENAME = 'E:\Logs\BankingDB.ldf',
SIZE = 100GB,
FILEGROWTH = 5GB
);
```
Important Best Practice
* Pre-size database (VERY IMPORTANT for large DBs)
* Avoid auto-growth during migration
---
4. INITIAL BULK DATA LOAD (MAIN STEP)
This is where 90% of the data moves.
---
4.1 Method 1 — BCP (BEST FOR LARGE DATA)
*BCP (Bulk Copy Program) is simple and powerful.
---
Step 1 — Export from ASE
```bash
bcp dbname.dbo.tablename out table.dat -c -U username -P password -S ASE_Server
```
---
Step 2 — Import into SQL Server
```bash
bcp BankingDB.dbo.tablename in table.dat -c -U username -P password -S SQLServerVM
```
---
For Very Large Tables (IMPORTANT)
Use:
```bash
bcp BankingDB.dbo.tablename in table.dat -b 100000 -a 32768 -h "TABLOCK"
```
---
Why This Works
* `-b` = batch size (reduces log pressure)
* `TABLOCK` = faster load
* Minimizes logging
---
4.2 Method 2 — BULK INSERT (Inside SQL Server)
---
```sql
BULK INSERT BankingDB.dbo.tablename
FROM 'D:\Data\table.dat'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
TABLOCK
);
```
---
4.3 Method 3 — SSIS (Optional but powerful)
Use if:
* Data transformation needed
* Complex mapping
---
5. PARALLEL DATA LOAD (CRITICAL FOR 10TB+)
If you load one table at a time, it will take days or weeks.
---
#Strategy
* Split large tables into chunks
* Load in parallel
---
# Example
Instead of:
```sql
SELECT * FROM Transactions
```
Do:
```sql
SELECT * FROM Transactions WHERE ID BETWEEN 1 AND 1000000
SELECT * FROM Transactions WHERE ID BETWEEN 1000001 AND 2000000
```
Run multiple BCP jobs simultaneously.
---
6. DISABLE INDEXES BEFORE LOAD (VERY IMPORTANT)
---
Why?
Indexes slow down bulk insert.
---
Disable Index
```sql
ALTER INDEX ALL ON tablename DISABLE;
```
---
Rebuild After Load
```sql
ALTER INDEX ALL ON tablename REBUILD;
```
---
7. HANDLE VERY LARGE DATABASES (10TB–100TB)
---
Key Techniques
7.1 Use Multiple Data Files
```sql
ALTER DATABASE BankingDB
ADD FILE (NAME = Data2, FILENAME = 'D:\Data2.ndf', SIZE = 500GB);
```
---
7.2 Use SIMPLE Recovery Mode (TEMPORARILY)
```sql
ALTER DATABASE BankingDB SET RECOVERY SIMPLE;
```
---
Why?
* Reduces log growth
* Faster inserts
---
After migration, switch back:
```sql
ALTER DATABASE BankingDB SET RECOVERY FULL;
```
---
8. INCREMENTAL DATA SYNC (LOW DOWNTIME)
After bulk load, system is still live on ASE.
You must sync changes.
---
Options
Option 1 — Timestamp-based Sync
---
```sql
SELECT * FROM Transactions
WHERE LastModified > 'last_sync_time'
```
---
Option 2 — Change Tracking (Manual)
* Add tracking column
* Capture inserts/updates
---
Option 3 — Custom Scripts (Common in ASE migrations)
---
9. AUTOMATION USING POWERSHELL
---
Example Script to Run Multiple BCP Jobs
```powershell
$tables = @("Customers", "Accounts", "Transactions")
foreach ($table in $tables) {
Start-Process -NoNewWindow -FilePath "bcp.exe" `
-ArgumentList "BankingDB.dbo.$table in $table.dat -S SQLServerVM -T -c"
}
```
---
10. DATA VALIDATION (CRITICAL STEP)
---
10.1 Row Count Check
```sql
SELECT COUNT(*) FROM tablename;
```
Compare ASE vs SQL Server.
---
10.2 Checksum Validation
```sql
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM tablename;
```
---
10.3 Spot Check
* Random records
* Critical tables
---
11. FINAL CUTOVER (GO-LIVE STEP)
---
Steps
1. Stop application
2. Stop ASE writes
3. Run final incremental sync
4. Validate data
5. Point application to SQL Server
---
12. CONNECTION STRING UPDATE
---
Old:
```text
ASE_Server
```
New:
```text
SQLServerVM.database.windows.net
```
---
13. ENABLE HADR (HIGH AVAILABILITY)
---
Use Always On Availability Groups
---
Step 1 — Enable Feature
```sql
EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
```
---
Step 2 — Create Availability Group
```sql
CREATE AVAILABILITY GROUP AG_Banking
FOR DATABASE BankingDB
REPLICA ON
'PrimaryServer' WITH (ENDPOINT_URL = 'TCP://Primary:5022'),
'SecondaryServer' WITH (ENDPOINT_URL = 'TCP://Secondary:5022');
```
---
14. BACKUP CONFIGURATION
---
Full Backup
```sql
BACKUP DATABASE BankingDB
TO DISK = 'D:\Backup\BankingDB.bak'
WITH COMPRESSION;
```
---
Log Backup
```sql
BACKUP LOG BankingDB
TO DISK = 'D:\Backup\BankingDB_log.trn';
```
---
15. COMMON FAILURES (AND FIXES)
---
Problem 1: Slow Migration
Fix:
* Use parallel BCP
* Increase batch size
---
Problem 2: Log File Full
Fix:
```sql
BACKUP LOG BankingDB;
```
---
Problemb3: Data Mismatch
Fix:
* Re-run incremental sync
* Validate again
No comments:
Post a Comment