Migrating an On-Premises SQL Server Cluster to Azure Platform as a Service
Many Enterprise Environments run SQL Server in Clustered Configurations using Windows Server Failover Clustering to achieve High Availability. These Clusters require careful Management of Storage, Networking, Patching, and Failover Configuration.
Azure Platform as a Service removes most of this Operational Overhead while maintaining SQL Server Compatibility. The recommended PaaS destination for clustered SQL Server Workloads is Azure SQL Managed Instance because it supports features such as SQL Agent, Linked Servers, Cross Database Queries, CLR, Service Broker, Near full SQL Server Compatibility
Azure SQL Managed Instance includes Built In High Availability and Automatic Failover.
Architecture Overview
On-Premises Environment:
- Windows Server Failover Cluster
- Shared Storage (SAN or Storage Spaces)
- Two or more SQL Nodes
- Listener Name used by Applications
- Azure PaaS Environment
- Azure SQL Managed Instance
- Managed Storage and High Availability
- Virtual Network Integration
- Automatic Failover Replicas
- Application connections are redirected from the On-Premises Cluster Listener to the Azure SQL – Managed Instance Endpoint.
Prerequisites
Before Migration begins the following Prerequisites must be Satisfied.
SQL Server Requirements
SQL Server Version must be supported by Azure SQL Managed Instance.
Supported Versions typically include:
SQL Server 2008 and later
Database Requirements
Database Compatibility Level must be supported.
Unsupported Features Must Be Removed such as:
FILESTREAM
Cross Instance Dependencies
Unsupported CLR Components
Infrastructure Requirements
Azure Subscription
Virtual Network
Subnet Dedicated for SQL Managed Instance
Azure Database Migration Service
Azure Storage Account
Permissions Required
SQL Sysadmin Permissions
Azure Contributor or Owner Permissions
Step 1 Create Azure Virtual Network
Azure SQL Managed Instance requires a Dedicated Subnet.
Azure Portal Steps
(1) Navigate to Azure Portal
(2) Select Virtual Networks
(3) Select Create
(4) Configure settings
Name: SQL-MI-VNET
Address Space: 10.10.0.0/16
Subnet Name: SQLMI-Subnet
Subnet Range: 10.10.1.0/24
Important Configuration
Disable Subnet Delegation Conflicts
Ensure No other Services exist in this Subnet
Step 2 Create Network Security Group
Create a Security Group to Control Inbound and Outbound Traffic.
Portal Steps
(1) Navigate to Network Security Groups
(2) Select Create
(3) Configure
Name: SQLMI-NSG
Region: Same Region as Virtual Network
Attach: NSG to SQLMI Subnet.
Step 3 Create Azure SQL Managed Instance
Azure SQL Managed Instance is the target PaaS Platform.
Portal Steps
(1) Navigate to Azure Portal
(2) Select SQL Managed Instance
(3) Select Create
Configuration
Instance Name: Prod-Sql-Mi
Admin Username: SQLAdmin
Compute Tier: General Purpose or Business Critical
vCore Count: Based on Workload
Storage: Start with 512 GB or Higher
Networking: Virtual Network SQL-MI-VNET
Subnet: SQLMI-Subnet
Security: Enable Azure Defender if required.
Deployment Time: Managed Instance deployment may take 3 to 6 hours.
Step 4 Configure Connectivity
Applications must connect to the Managed Instance endpoint.
Typical endpoint format
mi-name.public.database.windows.net
Configure DNS or Application Connection Strings accordingly.
Example SQL Connection String
Server=tcp:prod-sql-mi.public.database.windows.net,1433;Initial Catalog=SalesDB;User ID=sqladmin;Password=StrongPassword
Step 5 Assess SQL Cluster Environment
Use Data Migration Assistant to Assess Compatibility.
Install Data Migration Assistant on a Workstation.
Steps:
(1) Open Data Migration Assistant
(2) Select New Assessment
(3) Choose SQL Server to Azure SQL Managed Instance
(4) Connect to source SQL Cluster
(5) Run compatibility Assessment
Review results and Resolve Issues.
Step 6 Create Azure Database Migration Service
Azure Database Migration Service Performs the Actual Migration.
Portal Steps
(1) Navigate to Azure Portal
(2) Search for Database Migration Service
(3) Select Create
Configuration:
Service Name: Sql-Dms-Prod
Region: Same Region as Managed Instance
Pricing Tier: Premium for Online Migrations
Step 7 Create Migration Project
Within Database Migration Service:
(1) Create new Migration Project
(2) Source Server Type: SQL Server
(3) Target Server Type: Azure SQL Managed Instance
(4) Migration Type: Online Migration Preferred
Step 8 Configure Source Connection
Enter the On-Premises SQL Cluster Listener.
Example:
Server Name: Sqlcluster.Company.Local
Authentication: SQL Authentication or Windows Authentication
Validate Connection.
Step 9 Configure Target Connection
Connect to Azure SQL Managed Instance.
Example:
Server Name: Prod-Sql-Mi.Public.Database.Windows.Net
Authentication: Sql Authentication
Verify Connectivity.
Step 10 Select Databases
Choose the Databases that will be Migrated.
Migration Options Include
Single Database Migration
Bulk Database Migration
Step 11 Configure Migration Settings
Configure Backup Storage.
Create an Azure Storage Account.
Portal Steps:
(1) Create Storage Account
(2) Create container Named Migration-Backups
Provide Storage Access Key in Migration Configuration.
Step 12 Start Migration
Begin Migration.
Azure Database Migration Service will
Backup database from on Premises Cluster
Upload to Azure Storage
Restore to Azure SQL Managed Instance
Step 13 Monitor Migration
Migration Status can be Monitored on the Portal.
Stages include:
Backup
Transfer
Restore
Synchronization
Step 14 Cutover Process
During Final Cutover:
(1) Stop Application Writes
(2) Synchronize Final Transactions
(3) Complete Migration
(4) Update Application Connection Strings
Applications now connect to Azure SQL Managed Instance.
Step 15 Post Migration Validation
Perform Validation Tasks:
- Verify Database Integrity
- DBCC CHECKDB
- Verify Application Connectivity.
Test:
- Queries
- Stored Procedures
- SQL Agent Jobs
Step 16 Configure Security
Enable Advanced Security Features.
Enable Azure Defender for SQL
Enable Transparent Data Encryption
Configure Firewall Rules.
Step 17 Configure Backup and Monitoring
Azure SQL Managed Instance Automatically Handles Backups.
Retention Policies: Short term Retention and Long term Retention
Monitoring Tools:
Azure Monitor
Log Analytics
SQL Insights
Best Practices
Perform Migration Testing in Staging Environment First.
Use Azure Database Migration Service for Minimal Downtime.
Choose Business Critical tier for high I/O Workloads.
Implement Private Endpoints to Secure Connectivity.
Monitor Performance after Migration.
Validate compatibility using Data Migration Assistant.
Enable Auditing and threat Detection.
Security Best Practices
Use Azure Private Link for Database Connectivity.
Enable Transparent Data Encryption.
Enable Azure Defender for SQL.
Use Azure Active Directory Authentication.
Restrict SQL Firewall Rules.
Implement Role Based Access Control.
Monitor Database Activity.
Do's
Perform Full Compatibility Assessment.
Use Azure Database Migration Service.
Migrate during Maintenance Windows.
Test Application Connectivity.
Implement Monitoring and Alerting.
Don'ts
Do not Migrate without Compatibility Testing.
Do not expose SQL Managed Instance Publicly without Proper Firewall Configuration.
Do not Ignore Performance Benchmarking.
Do not Disable Encryption.
Do not Migrate Production Databases without Staging Validation.
Conclusion
Migrating SQL Server Clusters from on Premises Infrastructure to Azure SQL Managed Instance enables Organizations to Modernize their Database Platforms while eliminating the Operational Complexity of Managing Failover Clusters and Storage Systems.
By following a Structured Migration Process using Tools such as Data Migration Assistant and Azure Database Migration Service, Enterprises can successfully Transition their Clustered SQL Environments to Azure Platform as a Service with Minimal Downtime and Improved Scalability.
0 comments