SQL Server and Azure - hybrid partitioning - a low-cost solution to retain your data
It's already a fact: cloud is here to stay. As time goes by, we have a better understanding of how it really works as well as its real pros and cons. But here is the most important lesson: We don't need to completely move to the cloud in one shot. The complete "cloud-shift" may never happen, depending on the business requirements, future plans and business requirements. The cloud may act as a support for specific on-premises needs, creating a hybrid system which takes the best from both on-premises and from the cloud. This mix results in a system where you have control over the resources, and the advantage of cloud scalability. The cost constraint is the same as maintaining your systems on-premises. Microsoft SQL Server and Microsoft Azure offer different options to integrate an instance on-premises. In this article I will look at the "Hybrid Partitioning" capability.
What is hybrid partitioning? Hybrid partitioning is not an official Azure service or even a SQL Server new feature. This is just a creative way to take advantage of some capabilities from both SQL Server and Azure:
- Azure storage
- SQL Server table partitioning
- SQL Server support for data & log files in Azure
- Historical data must be retained for X years, but this is not frequently used.
- Because of disk space constraints, old data is being purged to free up space.
- Create an Azure Storage account and container (more info here)
- Create a new database Filegroup (or multiple ones)
- Create one or more files inside of the created Filegroup, pointing to Azure Storage
- Create a partition scheme and function
- Enable the partitioning on the table
- Create an Azure Storage account and container (more info here)
- Create a new database Filegroup
- Create a table referring the new Filegroup - the default is the PRIMARY Filegroup
- You can also move the table from the current filegroup to the new one