SQL 2016 – 5 (Real) reasons to upgrade, part 2


SQL 2016 – 5 (Real) reasons to upgrade, part 2


My personal favorite reason to upgrade to SQL2016: StretchDB

StretchDb provides cost-effective availability for cold data Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query. You can provide longer data retention timelines without breaking the bank for large tables like Customer Order History. Benefit from the low cost of Azure rather than scaling expensive, on-premises storage. You choose the pricing tier and configure settings in the Azure Portal to maintain control over price and data access speeds.


What is it/what it does:

  • The ultimate archive solution
  • Provides cost-efficient availability for cold data
  • Scales up or down as needed
  • Doesn’t require changes to queries or applications
  • Streamlines on-premise data maintenance
  • Keeps your data secure during migration


After you enable Stretch Database for a SQL Server instance, a database, and at least one table, it silently begins to migrate your historical data to an Azure SQL Database. If you store historical data in a separate table, you can migrate the entire table. If your table contains both historical and current data, you can specify a filter predicate to select the rows to migrate.

Stretch Database ensures that no data is lost if a failure occurs during migration. It also has retry logic to handle connection issues that may occur during migration. A dynamic management view provides the status of migration. You can pause data migration to troubleshoot problems on the local server or to maximize the available network bandwidth.

You don’t have to change existing queries and client apps. You continue to have seamless access to both local and remote data, even during data migration. There is a small amount of latency for remote queries, but you only encounter this latency when you query the historical data.


Is it for you?


Limitations – Table

  • More than 1,023 columns
  • More than 998 indexes
  • Tables that contain FILESTREAM data
  • FileTables
  • Replicated tables
  • Tables that are actively using Change Tracking or Change Data Capture
  • Memory-optimized tables


Limitations – Datatypes/Column properties

  • timestamp
  • sql_variant
  • XML
  • geometry
  • geography
  • hierarchyid
  • CLR user-defined types (UDTs)


Damn nasty

Column types

  • Computed columns


  • Check constraints
  • Default constraints
  • Foreign key constraints that reference the table


  • Full text indexes
  • XML indexes
  • Spatial indexes
  • Indexed views that reference the table


How to get started

Before you configure a database for Stretch, we recommend that you run the Stretch Database Advisor to identify databases and tables that are eligible for Stretch. The Stretch Database Advisor also identifies blocking issues. Stretch Database migrates data to an Azure SQL Database. Therefore you have to have an Azure account and a subscription for billing.

Have the info you need to create a new Azure SQL Database or to select an existing SQL Database, and to create a firewall rule that lets your local server communicate with the remote server.


Enable Database for Stretch

  • Choose an (existing) Azure SQL Database server for the data that Stretch Database migrates
  • The database has to have a database master key
  • Provide a credential for Stretch Database to use for communication
  • Enable Stretch for a table
  • Replicate the entire table
  • Write an Inline Table-Valued Function to Select Rows to Migrate


The Stretch Function

Creating a Stretch Function

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datatype1, @column2 datatype2 [, ...n])
RETURN  SELECT 1 AS is_eligible

What can you do with a Stretch Function

  • Create it
  • Alter it
  • Remove it
  • Replace it (sort of)
  • Pauze migration
  • Remove a filter predicate from a table

To migrate the entire table instead of selected rows, remove the existing FILTER_PREDICATE by setting it to null. After you remove the filter predicate, all rows in the table are eligible for migration. After you bind the function to the table as a predicate, the following things are true.

The next time data migration occurs, only the rows for which the function returns a non-empty value are migrated. The columns used by the function are schema bound. You can’t alter these columns as long as a table is using the function as its filter predicate.

You can replace a previously specified filter predicate by running the ALTER TABLE statement again and specifying a new value for the FILTER_PREDICATE parameter. The new function has to be less restrictive than the previous function. All the operators that existed in the old function must exist in the new function.

The new function can’t contain operators that don’t exist in the old function and the order of operator arguments can’t change. Only constant values that are part of a <, <=, >, >= comparison can be changed in a way that makes the predicate less restrictive.


Reverting from StretchDB

To disable Stretch Database for a table, select Stretch for a table in SQL Server Management Studio. Then select one of the following options.

  • Disable | Bring data back from Azure. Copy the remote data for the table from Azure SQL Database back to SQL Server, then disable Stretch Database for the table. (Be carefull because unmigrating your data incurs Azure data transfer charges)
  • Disable | Leave data in Azure. Disable Stretch Database for the table. Abandon the remote data for the table in Azure SQL Database.

After you disable Stretch Database for a table, data migration stops and query results no longer include results from the remote table. Before you can disable Stretch Database for a database, you have to disable Stretch Database on all the individual Stretch-enabled tables in the database.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...