USE OF AUTOMATION IN THE MIGRATING OnPrem SQL Server to AWS EC2 With VLDBs
:
* PowerShell → orchestration, file movement, scheduling
* SQL scripts → backup, restore, validation, HADR
Automate:
1. Full backup (multi-file, compressed)
2. Transaction log backups (loop)
3. File transfer (robocopy)
4. Restore (full + logs)
5. Continuous sync loop
6. Final cutover
7. Post-migration validation
8. Optional HADR setup (Always On)
BEFORE YOU RUN ANYTHING, Please Customize/Edit these variables everywhere:
```Sample powershell Script
$SourceServer = "ONPREM-SQL"
$TargetServer = "EC2-SQL"
$Database = "YourDB"
$BackupPath = "G:\Backup"
$TargetBackupPath = "\\EC2-SQL\Backup"
$DataPath = "D:\Data"
$LogPath = "E:\Logs"
```
PART 1: FULL BACKUP AUTOMATION (SQL)
Run on source (on-prem SQL Server)
1.1 Full Backup Script (Multi-file + Compression)
```Sample SQL Script
DECLARE @DBName NVARCHAR(100) = 'YourDB';
DECLARE @Path NVARCHAR(200) = 'G:\Backup\';
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '
BACKUP DATABASE [' + @DBName + ']
TO
DISK = ''' + @Path + @DBName + '_1.bak'',
DISK = ''' + @Path + @DBName + '_2.bak'',
DISK = ''' + @Path + @DBName + '_3.bak'',
DISK = ''' + @Path + @DBName + '_4.bak''
WITH COMPRESSION, STATS = 5;
';
EXEC(@SQL);
```
PART 2: FILE TRANSFER AUTOMATION (PowerShell)
Run on **source server**
2.1 Copy Backup Files to EC2
```Sample powershell Script
$Source = "G:\Backup"
$Destination = "\\EC2-SQL\Backup"
robocopy $Source $Destination *.bak /E /Z /MT:16 /R:3 /W:5 /LOG:C:\copy.log
```
PART 3: RESTORE FULL BACKUP (SQL)
Run on target (EC2 SQL Server)
3.1 Restore with NORECOVERY
```Sample SQL Script
RESTORE DATABASE [YourDB]
FROM
DISK = 'G:\Backup\YourDB_1.bak',
DISK = 'G:\Backup\YourDB_2.bak',
DISK = 'G:\Backup\YourDB_3.bak',
DISK = 'G:\Backup\YourDB_4.bak'
WITH
MOVE 'YourDB_Data' TO 'D:\Data\YourDB.mdf',
MOVE 'YourDB_Log' TO 'E:\Logs\YourDB.ldf',
NORECOVERY,
STATS = 5;
```
PART 4: LOG SHIPPING AUTOMATION (CONTINUOUS SYNC)
This is the core automation loop
4.1 SQL: Transaction Log Backup (Source)
```Sample SQL Script
DECLARE @DBName NVARCHAR(100) = 'YourDB';
DECLARE @Path NVARCHAR(200) = 'G:\Backup\';
DECLARE @FileName NVARCHAR(200);
SET @FileName = @Path + @DBName + '_log_'
+ REPLACE(CONVERT(VARCHAR, GETDATE(), 120), ':', '-') + '.trn';
BACKUP LOG [YourDB]
TO DISK = @FileName
WITH COMPRESSION, STATS = 5;
```
4.2 PowerShell: Continuous Copy + Restore Loop
Run this on a utility server or EC2
```Sample powershell Script
while ($true)
{
Write-Host "Starting sync cycle..."
# Step 1: Backup log (remote execution)
Invoke-Sqlcmd -ServerInstance $SourceServer -Query "
BACKUP LOG [$Database]
TO DISK = '$BackupPath\$Database\_log.trn'
WITH COMPRESSION;
"
# Step 2: Copy logs
---Sample powershell Script
robocopy $BackupPath $TargetBackupPath *.trn /MT:16 /R:2 /W:2
# Step 3: Restore logs
---Sample powershell Script
$files = Get-ChildItem "$TargetBackupPath\*.trn"
foreach ($file in $files)
{
$query = "
RESTORE LOG [$Database]
FROM DISK = '$($file.FullName)'
WITH NORECOVERY;
"
Invoke-Sqlcmd -ServerInstance $TargetServer -Query $query
# Move processed file
Move-Item $file.FullName "$TargetBackupPath\processed\" -Force
}
Start-Sleep -Seconds 300 # 5 minutes
}
```
PART 5: FINAL CUTOVER AUTOMATION
5.1 Final Log Backup (Source)
```Sample SQL Script
BACKUP LOG [YourDB]
TO DISK = 'G:\Backup\YourDB_final.trn'
WITH NORECOVERY;
```
5.2 PowerShell: Final Copy + Restore
```Sample powershell Script
# Copy final log
---Sample powershell Script
robocopy $BackupPath $TargetBackupPath YourDB_final.trn
# Restore final log WITH RECOVERY
---Sample powershell Script
Invoke-Sqlcmd -ServerInstance $TargetServer -Query "
RESTORE LOG [$Database]
FROM DISK = '$TargetBackupPath\YourDB_final.trn'
WITH RECOVERY;
"
```
PART 6: POST-MIGRATION VALIDATION AUTOMATION
6.1 Check Database Status
```Sample powershell Script
Invoke-Sqlcmd -ServerInstance $TargetServer -Query "
SELECT name, state_desc FROM sys.databases WHERE name = '$Database';
"
```
6.2 Run Integrity Check
```Sample powershell Script
Invoke-Sqlcmd -ServerInstance $TargetServer -Query "
DBCC CHECKDB('$Database') WITH NO_INFOMSGS;
"
```
6.3 Update Statistics
```Sample powershell Script
Invoke-Sqlcmd -ServerInstance $TargetServer -Query "
USE [$Database];
EXEC sp_updatestats;
"
```
PART 7: AUTOMATED BACKUP JOB (POST-MIGRATION)
7.1 SQL Agent Job Script
```Sample SQL Script
USE msdb;
EXEC sp_add_job
@job_name = 'FullBackupJob';
EXEC sp_add_jobstep
@job_name = 'FullBackupJob',
@step_name = 'BackupStep',
@subsystem = 'TSQL',
@command = '
BACKUP DATABASE YourDB
TO DISK = ''G:\Backup\YourDB_full.bak''
WITH COMPRESSION;
';
EXEC sp_add_schedule
@schedule_name = 'DailySchedule',
@freq_type = 4,
@freq_interval = 1;
EXEC sp_attach_schedule
@job_name = 'FullBackupJob',
@schedule_name = 'DailySchedule';
EXEC sp_add_jobserver
@job_name = 'FullBackupJob';
```
PART 8: ALWAYS ON AUTOMATION (HADR)
8.1 Enable Always On
```Sample SQL Script
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Always On Availability Groups', 1;
RECONFIGURE;
```
8.2 Create Availability Group
```Sample SQL Script
CREATE AVAILABILITY GROUP YourAG
FOR DATABASE YourDB
REPLICA ON
'N1' WITH (
ENDPOINT_URL = 'TCP://N1:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
),
'N2' WITH (
ENDPOINT_URL = 'TCP://N2:5022',
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = AUTOMATIC
);
```
PART 9: ERROR HANDLING (IMPORTANT)
Add logging in PowerShell:
```Sample powershell Script
try {
Invoke-Sqlcmd -ServerInstance $TargetServer -Query "SELECT 1"
}
catch {
Write-Host "Error occurred: $_"
Out-File -FilePath "C:\error.log" -Append
}
```
PART 10: FULL AUTOMATION FLOW
Here’s the mental automation pipeline:
STEP 1: Run full backup (SQL)
STEP 2: Copy backup (PowerShell)
STEP 3: Restore (SQL)
STEP 4: Start log loop (PowerShell + SQL)
STEP 5: Final log + recovery
STEP 6: Switch application
STEP 7: Run validation + monitoring
No comments:
Post a Comment