Recently I was asked by a client if the Stretch table feature will work with SQL Express and if so what the limitations are. This seemed like an interesting use case so we explored further and here is what we found.
Can SQL EXPRESS edition in SQL 2016 be used with stretch tables feature in the first place?
The answer is yes as shown in the MS documentation and the screenshot below. And yes it works with the Vanilla flavor as well.
What is the Max size of the database in SQL Express?
The max size of the database in SQL Express is 10 GB. The details are in the link mentioned previously.
Does this include data moved into Stretch table as well?
So I created a VM in Azure running SQL Express and then created a database with a single table with a char(8000) data type column and populated 1310154 rows in it which includes an identity column and a datetime column.
I then tried to use stretch table feature in SQL 2016 and move some of the data into an Azure SQL database.
Almost immediately I came across some serious drawbacks of the feature.
- A significant drawback of the feature is that it is very expensive considering the data being stored is not part of the frequently used OLTP workload.
- In addition when provisioning the database you don’t get to choose the database version in Azure such as basic standard etc.
- If the remote database is unavailable even queries referencing the existing data doesn’t work
- The remote database is not queryable directly since tables etc. do not show up in SSMS directly.
- Performance of the queries is not so great compared to local table.
- It is possible to delete the archive database in Azure without any checks from local instance, this will result in local queries also failing since it looks for the remote table during query execution. This is even after stopping stretch table feature and choosing to leave the data in Azure.
However I am also able to bypass the 10GB limit of the SQL express edition so that’s a plus but is it enough? I don’t think so.
References
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016
Please Consider Subscribing
