kohera-logo-regular.svg

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?

Stretch DB - 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

  • COLUMN_SET
  • Computed columns

Constraints

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

Indexes

  • 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])
RETURNS TABLE
WITH SCHEMABINDING 
AS
RETURN  SELECT 1 AS is_eligible
        WHERE 

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.

Parameter sniffing solved with new Parameter Sensitive Plan Optimization feature

If you’re someone that works a lot with Microsoft SQL Server, there’s no doubt that you’ve had issues with an issue called “Parameter sniffing” before. Up until now, you had...

Creating maps with R and Power BI

The possibilities are infinite when it comes to creating custom visuals in Power BI. As long as you have creativity and knowledge about the right programming language, you can let...

Sending monitoring alerts through Telegram

What if you could get the ease of phone notifications for whatever monitoring alerts you need? Then we have a solution for you with the app Telegram. Some of you...

Send mails with Azure Elastic Database Jobs

The DatabaseMail feature in SQL Server and Managed Instance is widely used by many professionals. But what if you want a similar functionality in Azure SQL Database? There are options,...

Sorting matrices in Power BI

Recently I worked on a Power BI project for a client. They had a SharePoint site where they regularly published articles and wanted to pour view data into a report...

The world of data is evolving

The data landscape has changed dramatically over recent years. In the past, we mainly heard that we needed to do as much as possible “cloud-only”—but this trend has become more...