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
Share this
You May Also Like
These Related Stories
Use case for SQL server table partitioning
Use case for SQL server table partitioning
Jan 11, 2016
2
min read
Azure SQL managed instance: new member of the family!
Azure SQL managed instance: new member of the family!
Apr 19, 2017
3
min read
Azure Backup for SQL Server public preview
Azure Backup for SQL Server public preview
Jun 5, 2018
5
min read
No Comments Yet
Let us know what you think