One of the new options in SQL Server 2016 is to store cold data in Microsoft Azure. The idea behind it is that doing so could be far less expensive than storing the data locally. It sounds great but the reality is a bit different. in this blog post I will share my experiences with this feature.
Stretch database or the first steps versus "Distributed database"
Looking closer at the feature of Stretch Database it incorporates actually the separation of data in a table over multiple servers. There is a main server and a back-end server that both contain a part of the data in your table. The back end server has currently to be in the Azure cloud. The front end can be in the Azure cloud to. When a query is fired on the table both front end and back end servers work together to get the data.
The MSDN Library shares an interesting picture:
What this picture shows is that in the Azure cloud a SQL Server Database is created that stores all or a part of the cold data. The data in a table can be split using a filter like "if a date column is before 1/1/2016 move the data to the cold data server in the Azure cloud".
This process looks a bit like a user friendly version of hashing distributed tables on the Microsoft Analytics Platform System. The difference is that this solution uses Enterprise SQL Server versions and it is limited by 2 servers and that the reference tables are not stored distributed in the Azure cloud too. This means that when a query using fields that are not indexed the Azure Cloud version has to collect all the data to let the master decide if a row should be in the query results or not. Moving data means that a connection between the 2 servers becomes a bottle neck.
There are also some limitations:
At the end the SQL Server Wizard had created a Azure SQL Server Database with a performance level of 400 DTU. The wizard does not allow you to chose your performance level but it chooses a performance level close to your local environment which was in my case a 4 core VM with 8 GB of memory and multiple disks on Premium Storage. The total size of part of the table that was moved into the Azure cloud was 4,19 GB. As I stated before it was just a test.
In my opinion the option to chose a performance level should be added to the Stretch Database Wizard as soon as possible.Why? Because the 400 DTU VM consumed in one night € 179,22 of my 200 € budget doing nothing and storing 4,19 GB of data which was not queried at all. Oeps!
That was also the moment that I started to look for the pricing of a Stretch Database solution in Azure (too late of cause) and I was blown of my chair.
Holy crap! Check out the current prices:
Stretch database or the first steps versus "Distributed database"
Looking closer at the feature of Stretch Database it incorporates actually the separation of data in a table over multiple servers. There is a main server and a back-end server that both contain a part of the data in your table. The back end server has currently to be in the Azure cloud. The front end can be in the Azure cloud to. When a query is fired on the table both front end and back end servers work together to get the data.
The MSDN Library shares an interesting picture:
This process looks a bit like a user friendly version of hashing distributed tables on the Microsoft Analytics Platform System. The difference is that this solution uses Enterprise SQL Server versions and it is limited by 2 servers and that the reference tables are not stored distributed in the Azure cloud too. This means that when a query using fields that are not indexed the Azure Cloud version has to collect all the data to let the master decide if a row should be in the query results or not. Moving data means that a connection between the 2 servers becomes a bottle neck.
There are also some limitations:
- Uniqueness can not be enforced on stretched tables in the Azure cloud
- INSERT, UPDATE and DELETE operations are not allowed (Read-Only data)
- Filters in indexes are not propagated into the Azure cloud table
- No tables that contain FileStream data
- Maximum of 1023 columns and 998 indexes
- No Memory optimized tables
- No replicated tables
- No ntext, text, images, timestamp, sql variant, XML and CLR data types including geometry, geography, hierarchyid, and CLR user-defined types
- No default and check constraints
- No computed columns
- No Foreign Keys constraints to a reference table
- No full text indexes or indexed views
At the end the SQL Server Wizard had created a Azure SQL Server Database with a performance level of 400 DTU. The wizard does not allow you to chose your performance level but it chooses a performance level close to your local environment which was in my case a 4 core VM with 8 GB of memory and multiple disks on Premium Storage. The total size of part of the table that was moved into the Azure cloud was 4,19 GB. As I stated before it was just a test.
In my opinion the option to chose a performance level should be added to the Stretch Database Wizard as soon as possible.Why? Because the 400 DTU VM consumed in one night € 179,22 of my 200 € budget doing nothing and storing 4,19 GB of data which was not queried at all. Oeps!
Holy crap! Check out the current prices:
SQL Server Stretch Database Wizard has chosen for me a VM that will cost me €6.274,15 per month. And that is for only 400 DTU.
Chose 2000 DTU and it will cost you €31.370,76 per month.
And that is only the server.You have to add also the Outbound Bandwidth for the data going to your local server if someone fires a query on your cold data in Azure.
Oeps again !
My advice: if you really have a problem with a large database and no disk more space available consider an on premises SQL Server Reference architecture made by ATOS. It is a very performing and scalable SQL Server architecture that will cost far less than choosing a SQL Server Stretch Database in the Azure cloud.
Comments
Post a Comment