{"id":1908,"date":"2016-04-19T16:30:39","date_gmt":"2016-04-19T14:30:39","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=1908"},"modified":"2016-04-21T21:32:47","modified_gmt":"2016-04-21T19:32:47","slug":"sql-2016-features-stretch-database","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2016\/04\/19\/sql-2016-features-stretch-database\/","title":{"rendered":"SQL 2016 features: Stretch Database"},"content":{"rendered":"

Stretch database allows for a table to span an \u2018earthed\u2019 SQL Server instance and an Azure SQL Database. It allows for parts (or all) of a table, presumably older, less used parts, to be stored in Azure instead of on local servers. This could be very valuable for companies that are obliged to retain transactional data for long periods of time, but don\u2019t want that data filling up the SAN\/flash array.<\/p>\n

After having played with it, as it is in RC2, I have some misgivings. It\u2019s still a useful feature, but probably not as useful as I initially assumed when it was announced.<\/p>\n

To start with, the price. Stretch is advertised as an alternative to expensive enterprise-grade storage. The storage part is cheap, it\u2019s costed as \u2018Read-Access Geographically Redundant Storage\u2019 blob storage.<\/p>\n

\"PriceStorage\"<\/a><\/p>\n

Then there\u2019s the compute costs<\/p>\n

\"PriceCompute\"<\/a><\/p>\n

The highest tier is 2000 DSU at $25\/hour. To compare the costs to SQL Database, a P2 has the same compute costs as the lowest tier of Stretch, and that\u2019s with a preview discount applied to Stretch. It\u2019s going to be a hard sell to my clients at that price (though that may be partially because of the R15=$1 exchange rate).<\/p>\n

The restrictions on what tables are eligible are limiting too. The documented forbidden data types aren\u2019t too much of a problem. This feature\u2019s intended for transactional tables, maybe audit tables and the disallowed data types are complex ones. HierarchyID, Geography, XML, SQL_Variant.<\/p>\n

A bigger concern are the disallowed features. No computed columns, no defaults, no check constraints, can\u2019t be referenced by a foreign key. I can\u2019t think of too many transactional tables I\u2019ve seen that don\u2019t have one or more of those.<\/p>\n

It\u2019s looking more like an archive table, specifically designed to be stretchable will be needed, rather than stretching the transactional table itself. I haven\u2019t tested whether it\u2019s possible to stretch a partitioned table (or partition a stretched table) in order to partition switch into a stretched table. If it is, that may be the way to go.<\/p>\n

I have another concern about stretch that\u2019s related to debugging it. When I tested in RC2, my table was listed as valid by the stretch wizard, but when I tried, the ALTER TABLE succeeded but no data was moved. It turned out that the Numeric data type wasn\u2019t allowed (A bug in RC2 I suspect, not an intentional limitation), but the problem wasn\u2019t clear from the stretch-related DMVs. The problem is still present in RC3<\/p>\n

\"StretchDMV\"<\/a><\/p>\n

The actual error message was no where to be found. The new built-in extended event session specifically for stretch tables was of no additional help.<\/p>\n

\"StretchXE\"<\/a><\/p>\n

The error log contained a different message, but still not one that pinpointed the problem.<\/p>\n

This blog post was based on RC2 and written before the release of RC3, however post RC3 testing has shown no change yet. I hope at least the DMVs are expanded before RTM to include actual error messages and more details. We don\u2019t need new features that are hard to diagnose.<\/p>\n

As for the other limitations, I\u2019m hoping that Stretch will be like Hekaton, very limited in its first version and expanded out in the next major version. It\u2019s an interesting feature with potential, I\u2019d hate to see that potential go to waste.<\/p>\n","protected":false},"excerpt":{"rendered":"

Stretch database allows for a table to span an \u2018earthed\u2019 SQL Server instance and an Azure SQL Database. It allows for parts (or all) of a table, presumably older, less used parts, to be stored in Azure instead of on… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"New Blog Post: SQL 2016 features: Stretch Database","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,16],"tags":[],"class_list":["post-1908","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-syndication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-uM","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1908","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=1908"}],"version-history":[{"count":9,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1908\/revisions"}],"predecessor-version":[{"id":1919,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/1908\/revisions\/1919"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=1908"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=1908"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=1908"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}