You will very likely know that SQL Server 2008 / R2 end of support is on July 9th 2019. Not that long to start thinking about and assessing your options. For this post I just want to discuss couple of things that you can do and I specifically mention Azure technologies, think of this as a high level starter guide.
- Stay on-premises with SQL Server 2017 / 2019 (whatever your version strategy is).
- Use IaaS – Infrastructure as a service where you have SQL Server on Azure virtual machines.
- Use PaaS – Platform as a service which could be elastic pools to host databases all in one area, fully managed Azure SQL Database for specific databases or even Managed Instances.
- Do nothing. Let’s be honest, not really an option.
For example I know I have some databases that I can move to Azure SQL Database so what am I going to do? I follow A.M.T. = Assess , Migrate and Tune it.
Firstly you need to carry out an Assessment. I like to use the Data Migration Assistant (DMA) https://docs.microsoft.com/en-us/sql/dma/dma-overview?view=azuresqldb-current for this. This is a great tool to use because with this you will get to understand if you have unsupported features that will block your migration to Azure or possible compatibility issues. The great thing with version 4.0 is Azure SQL Database SKU recommendations feature, which allows you to identify the minimum recommended Azure SQL Database SKU based on performance counters collected from the machine hosting your databases. Previously I would have done this manually but having it wrapped within this tool just makes sense.
Once the assessment has been done and you know that nothing will prevent a successful move, start the migration. If you have read my blog for a while now you will know that there are many ways to do this, but typically DMA v 4.0 is a safe route. You will just need to create a new migration project rather than an assessment project.
Ok, so you have migrated but your job isn’t done yet. Yes you have moved the database to Azure SQL Database but this is the time to monitor it where you may very well tune / tweak settings as you see fit. This may well include a change to your service tier and no doubt hardening the services from a security POV. Think of setting up threat detection, auditing and firewall rules too. From a performance aspect you definitely will want to setup DTU usage alerts too.
I recommend the following webinars to really get up to speed with best practices and techniques.