So you have an old SQL Server. It's working, but it's a bit slow, and you can't use all of those cool features you read about, such as Memory-Optimized Tables or Query Store. You spend too much time troubleshooting problems instead of developing new features. And, most important, the end of support for your SQL Server version is just around the corner.
It's time to make a change. But where do you start?
Modernizing your SQL Server is, in most cases, a critical project for the business. If you do it right, you can get many benefits, improve performance, availability, and security, and even save costs. But there are a lot of risks and decisions to make along the way, and if you do it wrong, then you might just lose money, but it can also be a disaster.
You should start with asking some fundamental questions...
Why?
It might sound obvious, but this is a very important question that you should ask yourself and other stakeholders in your organization. Why do we want to modernize our SQL Server? Do we have to do it? Is it really so important? What are our main pain points and how this modernization project is going to solve them?
It's important to communicate the reasons across the organization. Everyone should be aligned, agree to the importance of this project, and understand how it's going to solve the problems, which are the reasons for modernizing.
I have recently published an article called Five Reasons to Upgrade Your SQL Server. This article talks about upgrading your SQL Server, but it is relevant just the same for any other type of modernization (i.e. migrating to the cloud). I recommend that you read that article for some good reasons and motivations to make this move.
Once you answered the question "why?", it is also important to define the goals and the KPIs for the project. For example, if the main reason for modernizing your SQL Server is slow performance, then the goal of the project should be to improve performance. But this is not enough. The goal should be specific and measurable. For example: "Increase Batch Requests per Second by at least 30%".
If you set clear goals and communicate them to all the relevant stakeholders, then now everyone is aligned and focused, and it's easy to measure the success of the project.
If you are going to modernize your SQL Server just because it's old or just because Microsoft says you should move to the cloud, without answering the question "why?", then you might not get the support, commitment, and budget that you need in order to run the project successfully, and the project just might fail.
Where?
This question is actually composed of two questions: where are we today? and where do we want to be tomorrow?
The first question is about the current state. You should map your SQL Server(s), their current versions, build numbers, and editions. You should identify and document all the objects and properties in the current environment, such as linked servers and instance configurations. This is very important to make sure that you don't forget anything. Imagine that there is a SQL Server Agent job that runs some business process once a day, and you forgot to migrate it to the new environment. This can be quite unpleasant.
You can use various tools for mapping the current state, depending on your environment and your preferences. Some examples are: Microsoft Assessment and Planning Toolkit, Data Migration Assistant, and dbachecks.
The second question is even more important. It involves more specific questions, such as: "do we want to stay on-premises or to migrate to the cloud?", "do we want to migrate to the IaaS or PaaS model?", "do we want to consolidate all databases from our existing SQL Servers to a single elastic pool or to keep them in separate managed instances?"...
Remember the goals and KPIs you defined in the previous step? These will help you answer these questions. For example, if one of the main goals is to reduce the maintenance and administration overhead from your DBAs, then migrating to the PaaS model is probably the right choice for you. Of course, you should also consider any constraints your business has. For example, if the database is used by a 3rd-party application which requires CLR Integration, then Azure SQL Database is not an option.
How?
Now that you have a clear understanding of why we are doing it and where we want to go, it is time to plan the actual migration. During this step you should consider some more requirements and constraints, such as the amount of downtime allowed during the migration. This will narrow down the possible options.
Here are just a few migration methods you can choose from: backup & restore, export and import using the SqlPackage utility, Transactional Replication, and Azure Database Migration Service.
If your SQL Server is mission-critical, and you want to avoid failures as much as possible, then you should spend some time in this step. Create a detailed plan and document it. Send it to your peers for review. Communicate it to all the relevant stakeholders, and make sure that everyone understands the plan. Include a risk analysis in your plan, and plan how you are going to mitigate each risk, and how you will perform a rollback in case you have to.
The more time you spend on the "how?" - the more confidence you are going to have in your plan, and the less risks you are going to face later.
We Can Help
Our team has a lot of experience in SQL Server modernization projects. We have done it dozens of times with many different customers with different environments, pains, and constraints. We've seen it all. Well, probably not all, but we've seen a lot.
Based on our experience, we developed a methodology that allows us to run successful projects for our customers. We begin with asking a lot of questions, identifying the pain points, the reasons, the goals, and the KPIs.
We map your current environment and document everything. We then ask more questions and decide, together with you, what the target platform should be.
Once we have all the answers, we write a comprehensive plan, taking into account the goals, the existing and target platforms, and any additional requirements and constraints. We present the plan to you so that you can understand and approve it.
Once we have an approved plan, we start the implementation phase. We set up a test environment, we monitor performance and create a benchmark, we develop all the required scripts and processes, we test everything, including the risk analysis, and finally, we run the migration in the production environment.
We have a lot of customer success stories. Whether you want to upgrade your SQL Server or move to the cloud, if you would like to join our family of happy customers, then contact us. We would love to help.