Today, we talk about moving an on-premise SQL server database from your server to the cloud. Your boss might think that it’s easy to just pick up the old server data and upload it to the cloud. In reality, the project requires strategy, planning and technical experience.
To start, let’s do a quick overview on many of the benefits of moving to cloud. There are a wide range of benefits that provide a great overall ROI (Rate of Investment). Listed below are some.
- Maintenance: Database day to day management becomes much easier when a third-party is responsible to service it
- Advanced features: Today’s cloud service providers can provide the best database services already available on their platform
- Scalability: Easily scale up or down on storage based on data requirements
- Ubiquity: Replication is a thing from the past. The cloud is “everywhere” which eliminates the need to replicate data to reach users by location
For the project to be successful, organization is very important. Let’s plan the project timeline, infrastructure and requirements.
For this project, we will assume moving On Premise SQL Server to Azure cloud. The project planning for moving to AWS or another cloud provider will be similar but of course the technology tasks will be different.
The first task is to test compatibility to the target cloud platform. Compatibility issues are the main causes of database problems when migrating. There are various tools and methods to test for compatibility. This particular process is outside the scope of this blog but I encourage the developers to properly research this process before proceeding.
The next task is to plan the project. There are two main ways to migrate SQL Server to Azure. The first is simpler but may require significant downtime for the existing database.
Figure 1 shows a process document recommended by Microsoft for this procedure.
The 2nd option is more complex but is required if you can’t bring your production database down while performing the migration. This method uses transactional replication as a migration solution. The source database must meet the requirements for transactional replication and be compatible for Azure SQL Database. The typical workflow for this type of migration is following.
- Set up Distribution
- Create Publication
- Create Subscription
The Project plan
While many companies are adept at using Agile methodology for software delivery, sometimes the practice is lost when doing infrastructure and web operation projects similar to this one. We recommend combining this project with overall software Sprints. It helps with communication, planning and effort.
Sprint 1 (2 weeks)
- Prepare source environment
- Test compatibility
- Compile list of coding changes
- Backup database
Sprint 2 (2 weeks)
- Make coding changes
- Test and Test again
***more sprints maybe required for code changes, testing based on the application and the migration method as discussed above.
Have you been tasked to plan a migrate a on premise SQL server to Azure cloud? This blog provides a high-level summary of how to go about successfully delivering this project.
I hope this helps you get started in the project planning. Please do not hesitate to reach out to email@example.com for specific questions.