Why should you migrate your on-prem database to cloud:
Here are following factors, you would consider when migrating an on-prem database to cloud:
- You want to modernize current IT asset base.
- You want to prepare your database platform for future needs.
- You want to lower your database infrastructure costs.
- You want to increase Business Agility.
Migration Phases:
If you are migrating a mission critical database to cloud, you would need to plan it carefully. Following plan covers most of the different migration phases:
Server/Database Build on Azure:
Every organisation has their own standards, however you can consider following best practices to build your server/database on Azure:
- Provision Azure SQL as per performance baseline.
- Use following link to calculate the DTU required on Azure managed Instance. https://dtucalculator.azurewebsites.net/
- New provisioning option, vCore can be used to map CPU with on-prem servers.
- Restrict SQL Logins and database hardening for better security.
- Setup maintenance jobs as per Microsoft best practices using Azure Automation.
- Improve database performance with optimal configuration.
- Migrate the database swiftly with minimal business impact (within agreed downtime).
- Setup proactive monitoring on servers for efficient Event Management.
- Environment failure testing (Acceptance Testing) : Execute all possible environment failure scenarios to simulate the failure/disaster situations in controlled way and document their fixes and behavior for operational maturity.
Different Database Migration methods:
Here are different database migration methods. You can choose most suitable option as per your database availability and business requirements.
- With Downtime
- Migration using Data Migration Assistant
- Migration with Backup and Restore using Azure Blob
- Migration with Import and Export Data Tier using SSMS
- Deploy Database from On-prem to Azure SQL Directly through SSMS
- Without Downtime
- Migration using Azure DMS (Data Migration Service)
Brief summary of each Database migration method:
Migration using Data Migration Assistant:
- Evaluate if database is ready to migrate and produce a readiness report
- Provide recommendations for how to remediate migration blocking issues
- Recommend the minimum Azure SQL Database SKU based on performance counter data of existing database
- Perform the actual migration of schema, data and objects (server roles, logins, etc.)
- Useful for small and less critical databases
- Downtime is required
- SQL Database 2012+ supported.
Migration with Backup and Restore using Azure Blob:
- Use Database Migration Assistant tool “Assessment” for following reasons:
- Detects compatibilities issues
- Recommends performance and reliability improvements
- Then, use Backup and Restore database using Azure Blob-
- Ease of Use -Traditional way to migrate the databases using native backups and restores
- Migration of schema, data and objects
- Useful for multiple small/big databases
- Downtime is required
- Direct migration from SQL Server 2005+ databases
Migration with Export and Import Data Tier using SSMS:
- First, use Database Migration Assistant tool for “Assessment” for following reasons:
- Detects compatibilities issues
- Recommends performance and reliability improvements
- Then, use Import and Export Data Tier using SSMS:
- Migration using bacpac files
- Migration of schema, data and objects
- Useful for small databases and less critical databases
- Downtime is required
Deploy Database from On-prem to Azure SQL Directly through SSMS:
- First, use Database Migration Assistant tool for “Assessment” for following reasons:
- Detects compatibility issues
- Recommends performance and reliability improvements
- Then, use Deploy Database from On-prem to Azure SQL Directly through SSMS:
- Direct migration from On premise server to Azure SQL
- Migration of schema, data and objects
- Useful for small databases and less critical databases
- Downtime is required
Migration using Azure DMS (Data Migration Service):
- Fully Managed Service for Seamless Migrations
- Leverages Data Migration Assessment tool for following assessment
- Detects compatibility issues
- Recommends performance and reliability improvements
- Fire and Forget Migration
- Migration of schema, data and objects
- Useful for big databases and business critical databases
- No or less downtime is required
- Multiple Database engine supported
Benefits of Database Migration on Azure:
Here are the following benefits of migrating an on-prem database to Cloud:
- Flexibility and scalability.
- Improved supportability.
- Increased up-time.
- Enabler for improved RPO (Recovery point objective) & RTO (Recovery time objective).
- Excellent Performance in latest version of SQL Server.
- Better Security.
- Improved Index Management.
Potential challenges of Database Migration on Azure:
While your specific environment will determine the challenges that apply to you, there are some general drawbacks associated with database migrations on cloud that you will want to consider.
- Compliance requirements migrating your sensitive data on cloud.
- Latency when using database on clouds.
- If your hardware is controlled by someone else, you might lose some transparency and control when debugging performance issues.
- Shared resources which might impact database performance.
- Uncontrolled downtime.
I tried to cover all the important factors involved in a database migration, please leave a comment if you have any feedback or question.
Nice article..a must read for those who are looking to move away from traditional expensive on-prim infrastructure.👍
LikeLike
Very detailed and meaningful 👍
LikeLike
Really nice one Sumit.
Its very great to see a brief and quick description of all these different migration approaches on one page. It can really help people to compare and choose the one best suited for them.
LikeLike
A must read for those who are looking to learn about Database migration from on-prem to Azure SQl..
LikeLike
Quite informative and very well written article. Are you planning to write one for Migrating to AWS as well?
LikeLike
Thanks Anubha. Please see my new blog post on database migration from Microsoft SQL to AWS Aurora.
LikeLike
Good detailed information for data migration from on-prem to Azure SQL.
LikeLike
Aw, this was an incrediƅly nice рost. Ⴝpending some timе and actual effort to create a good article…
but what can I ѕay… I hesitate a lot and don’t seem to
get anything done.
LikeLike
Good replies in return of this difficulty
with firm arguments and describing all concerning that.
LikeLike
Very well explained.
Looking forward for more article on migration.
LikeLike
Thanks guys, I hope you find it useful. I have tried to cover all aspect of moving your on-premise database to cloud.
LikeLike
Great Stuff !!
LikeLike
nice article
LikeLike