SQL Server 2016 - How to archive historical tables in Azure with Stretch Database

A very simple way to implement Stretch Database and start storing your tables in the Microsoft Azure cloud is using the SQL Server Management Studio (SSMS) interface. I really like using the Wizard to implement the Stretch Database, especially in the first table, as it already allows you to create the databases and encryption keys (Always Encrypted) without even having to open the Azure Portal.

The first step is to select the table you would like to store in the cloud through the “Object Explorer”, right-click and select the “Stretch Database” menu and click on the “Enable” option.

The first screen of the Stretch Database Wizard displays a summary of how this feature will be configured.

If your table has any resource that is a Stretch Database limitation, you will come across this screen.

In the case of the SalesOrderHeader table, the limitations that are preventing the use of Stretch Database are check, default and foreign keys constraints. To get around this, I will create a new table (SalesOrderHeader2) with the contents of the original table (SalesOrderHeader), but without the contraints.

Now, let's try to activate Stretch Database on the newly created table. Note that the default option is “Entire Table”, that is, the entire table will be stored in the cloud. If you want to keep part of the data on the local infrastructure, even to have better performance in the most frequent queries, click on this link to open the cloud migration configuration screen.

If you want to customize the way of identifying “cold” data (little-used data – stored in the cloud) and “hot” data (frequently used data – stored on-site), you can select the “Choose Rows” option, define a name for this filter and choose the column and expression that will be used to filter the data, as per the example below:

This is what the Stretch Database configuration screen looks like after creating a filter to select the lines that will be migrated:

On this screen, we must configure the Azure account that will store the “cold” data from the source table.

On this screen, we will configure a strong password for the database master key (DMK), which will be used to encrypt data in the cloud (using Always Encrypted, all transparently for you)

And here, we can configure the Firewall for this database, either using your current IP or providing a range of IPs to allow access. This setting largely depends on your network, so it can be changed according to your scenario. If you would like to change these firewall settings after activating Stretch Database, this can be done through the Azure Portal.

Summary of the actions that will be carried out:

Data migration completed successfully! Now just wait for the data migration to finish in the background, in a completely transparent way. To monitor the progress of data transfer, use the DMV sys.dm_db_rda_migration_status:

Note that after creating the Stretch Database on at least one table, the database icon changes on the SSMS Object Explorer screen.

Additionally, 2 new objects are created: 1 External Data Source (Stretch Server) and 1 classification function (fncUltimos_4_Anos)

Video demonstration

After demonstrating how to implement Stretch Database using the SSMS interface, I will now share with you how to do the same thing, using only T-SQL commands.

The first step is to enable Stretch Database on the instance:

EXEC sp_configure 'remote data archive' , '1';  
GO

RECONFIGURE;  
GO

Before you can enable Stretch Database on individual tables, you need to enable it on the database. Enabling Stretch Database on a database or table requires db_owner and CONTROL DATABASE permissions.

You will need to manually log in to the Azure Portal to create a new Stretch Database server (if you don't already have one). Remember to configure the firewall rules to be able to access it.

Now is the time to configure the encryption of the data that will be sent to the Stretch Database:

USE [AdventureWorks]
GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD='minhasenha'; 
GO

CREATE DATABASE SCOPED CREDENTIAL MeuStretchDatabase  
    WITH IDENTITY = 'dirceuresende', SECRET = 'minha_senha';
GO

And let's enable Stretch Database on the database:

ALTER DATABASE AdventureWorks  
    SET REMOTE_DATA_ARCHIVE = ON  
        (  
            SERVER = 'dirceuresende_stretchdb.database.windows.net', 
            CREDENTIAL = MeuStretchDatabase
        );
GO

And let's enable Stretch Database for the table:

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) );
GO

If you want to filter the rows that will be uploaded to the Stretch Database instead of storing the entire table in Azure, you can use a predicate filter:

-- Criando a função de filtro
CREATE FUNCTION dbo.fn_stretch_by_date(@date datetime)  
RETURNS TABLE  
WITH SCHEMABINDING   
AS   
    RETURN SELECT 1 AS is_eligible WHERE @date < '2014-01-01'
GO

-- Iniciando o stretch database com o filtro
ALTER TABLE SalesHeaderOrder2 
  SET ( REMOTE_DATA_ARCHIVE = ON (
    FILTER_PREDICATE = dbo.fn_stretch_by_date(OrderDate),
    MIGRATION_STATE = OUTBOUND )
  )

It is worth noting that only deterministic functions can be used in the Stretch Database filter, that is, you will not be able to use GETDATE(), for example, to create a dynamic date filter. If you need this, you will have to create new functions, changing the filter, and applying an ALTER TABLE to change the filter function:

-- Criando uma nova função de filtro para aumentar 1 ano da função criada no exemplo anterior
CREATE FUNCTION dbo.fn_stretch_by_date_201501(@date datetime)  
RETURNS TABLE  
WITH SCHEMABINDING   
AS   
    RETURN SELECT 1 AS is_eligible WHERE @date < '2015-01-01'
GO

-- Iniciando o stretch database com o filtro
ALTER TABLE SalesHeaderOrder2 
  SET ( REMOTE_DATA_ARCHIVE = ON (
    FILTER_PREDICATE = dbo.fn_stretch_by_date_201501(OrderDate),
    MIGRATION_STATE = OUTBOUND )
  )

You can also create the table using Stretch Database:

CREATE TABLE SalesOrderHeader2
    ( ... )
    WITH ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) );
GO

If you analyze the execution plan of a query before activating Stretch on this table, you will see that the plan will not have the “Remote Query” operator when reading data from the tables (unless you use external data, such as Linked Server), only local data operators, such as Index Seek, Table Scan, Index Scan, etc..

However, when we activate Stretch on this table, we see that when querying the data on the remote server, the “Remote Query” operator becomes part of our execution plan. This occurs both when you query the entire table and when you query data that is only on the remote server.

If your query result is only stored locally, the “Remote Query” operator will not appear in the execution plan.

In the case of the example above, the “Discontinued” column was used as a filter to determine the data that should be migrated or not (1 = Migrates, 0 = Remains local), that is, if no filter is performed (bringing the entire table) or some other filter that returns data with this flag with both the values ​​0 and 1 or just the value 1, the “Remote Query” operator will be present, as part of the data will be on the remote server.

If a query only returns data with this flag = 0, the “Remote Query” operator will not be part of the execution plan, as only data that is in the local infrastructure will be returned.

To pause or resume data migration in Azure, choose Stretch for a table in SQL Server Management Studio and choose Pause to pause data migration or Resume to resume data migration. You can also use Transact-SQL to pause or resume data migration.

How to pause data migration

To pause data migration, use the command below:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) );
GO

How to resume/continue/remote data migration

To resume/resume/continue data migration, use the command below:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND ) );
GO

How to disable data migration for a table

To disable data migration and bring cloud-migrated data back to your on-premises infrastructure (copying remote data to the Azure table back to SQL Server incurs data transfer costs), use the command below:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) );
GO

To disable data migration and abandon data migrated to the cloud, use the command below:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY (
        MIGRATION_STATE = PAUSED
    )
GO

Remember to Pause data migration on individual tables when you want to troubleshoot local server issues or to maximize available network bandwidth. Furthermore, disabling Stretch Database for a table or database does not delete the remote object. If you want to delete the remote table or remote database, drop it using the Azure Management Portal. Remote objects continue to incur Azure costs until you delete them.

How to disable Stretch Database for a database

To disable Stretch Database for a database, you will first need to disable Stretch for all individual tables that are enabled in that database. Use the sys.remote_data_archive_tables view to find out what these tables are.

After disabling Stretch for all tables, use the command below to disable Stretch for the database:

ALTER DATABASE AdventureWorks
    SET REMOTE_DATA_ARCHIVE = OFF
GO

Remember that disabling Stretch Database for a database does not delete the remote database. If you want to delete the remote database, drop it using the Azure Management Portal. The remote database continues to incur Azure costs until you delete it.

A simple way to monitor the progress of data migration in the Stretch Database is using SQL Server Management Studio (SSMS), by clicking on the desired database, right-clicking, selecting the Tasks > Stretch > Monitor option, as shown in the image below:

This way, a report will open where you can view details of the number of lines eligible for migration and how many have already been migrated, as well as the loads (batches of up to 9,999 records) carried out:

One way to monitor the progress of data migration using Transact-SQL (T-SQL) is by consulting the sys.dm_db_rda_migration_status view, which will return information for each load batch:

To see information about which databases have Stretch Database enabled, see the view sys.remote_data_archive_databases

or the view sys.remote_data_archive_tables to consult the tables that have the Stretch Database:

If you activate Stretch Database in your environment, it is common to have questions about the backup/restore routines for these databases/tables that have local data and also in the cloud. Well, as for backup, the process doesn't change anything.

Database backup with Stretch Database enabled

The backup of local data (“hot” data), that is, that is not eligible for migration yet and is stored in your local infrastructure, continues to be done normally, using BACKUP DATABASE commands and automated through jobs, along with the bases/tables that are 100% local and in the same way that you back up the bases/tables that do not have the Stretch Database, including data that is eligible but has not yet been migrated at the time the backup is generated.

The data that has already been migrated and is in the Microsoft cloud is part of the automatic Azure backup routine, carried out every 8 hours (at least) in the Staging tables and with data retention of 7 days (point-in-time recovery), that is, Azure already backs up the “cold” data for you.

Restore bases with Stretch Database enabled

When we talk about restoring data from banks/tables that have Stretch Database enabled, the situation is a little different. If the data that needs to be restored is ineligible data, that is, data that is in your local infrastructure (“hot” data), you will restore the data in the traditional way, using the RESTORE DATABASE command, in the same way as you restore data from databases without Stretch activated.

After completing the restore process, you will need to run the command below to reestablish the connection between the base and the remote base in Azure:

USE [AdventureWorks]
GO

EXEC sys.sp_rda_reauthorize_db
    @credential = N'MeuStretchDatabase',
    @with_copy = 1;
GO

Note: You can search for the credential name in the sys.database_scoped_credentials view.

To restore data that is in Azure, if the problem was with the data that was already migrated, you will need to perform the restore database in the Azure Portal. If you want to connect to a restored Azure database with a different name or in a different region, you can use SP sys.sp_rda_deauthorize_db (requires db_owner permissions) to remove the authenticated connection between a local Stretch-enabled database and the remote Azure database, also very useful when the remote server is inconsistent or unavailable.

After running sp_rda_deauthorize_db, all queries against Stretch-enabled tables and databases will fail because the query mode is set to DISABLED. To use these queries again, you must choose one of the 2 options below:

• Use the sys.sp_rda_reauthorize_db to reconnect to the remote Azure database. This operation automatically resets the query mode to LOCAL_AND_REMOTE, which is the default behavior for Stretch Database. That is, queries return results from local and remote data.
• Execute the sp_rda_set_query_mode with the LOCAL_ONLY argument to allow queries to continue running against only local data, ignoring remote data.

Recover an Azure dynamic database

The SQL Server Stretch Database service on Azure takes snapshots of all dynamic data at least every 8 hours using Azure Storage Snapshots. These snapshots are kept for 7 days. This allows you to restore data to at least one of 21 specific points from the last 7 days up to the time the last snapshot was taken.

To restore an Azure Dynamic Database to a specific point in time via the Azure portal, follow the procedure below:

• Log in to the Azure portal.
• On the left side of the screen, select “BROWSE” and “SQL Databases”.
• Navigate to the database and select it.
• At the top of the database sheet, click “Restore”.
• Specify a new Database Name, select a “Restore Point” and click “Create”.
• The database restoration process will begin and can be monitored with “NOTIFICATIONS”.

Recover a deleted Azure database

The SQL Server Stretch Database service in Azure takes a database snapshot before a database is removed and retains it for 7 days. After that, it no longer retains database snapshots. This allows you to restore a deleted database to the point where it was deleted.

To restore a deleted Azure database to the point it was deleted using the Azure portal, follow these procedures:

• Log in to the Azure portal.
• On the left side of the screen, select “BROWSE” and “SQL Servers”.
• Browse to the server and select it.
• Scroll down to “Operations” on the server blade and click on the “Deleted Databases” tile.
• Select the deleted database you want to restore.
• Specify a new Database Name and click “Create”.
• The database restoration process will begin and can be monitored with “NOTIFICATIONS”.

Limitations for Stretch-enabled tables

• Uniqueness is not enforced for UNIQUE constraints and PRIMARY KEY constraints on an Azure table that contains the migrated data.
• You cannot UPDATE or DELETE rows that have been migrated or rows that are eligible for migration in a Stretch-enabled table or in a view that includes Stretch-enabled tables.
• You cannot INSERT rows into a Stretch-enabled table on a Linked Server.
• You cannot create an index for a view that includes Stretch-enabled tables.
• Filters on indexes are not propagated to the remote table.

Limitations that currently prevent you from enabling Stretch for a table

• Tables that have more than 1,023 columns or more than 998 indexes
• FileTables or tables that contain FILESTREAM data
• Tables that are replicated or that are actively using Change Tracking or Change Data Capture (CDC)
• Memory Optimized Tables (In-Memory OLTP)
• Text, ntext and image data types, timestamp, sql_variant, XML, CLR data types, including geometry, geography, hierarchyid and user-defined CLR types
• Computed columns
• Default constraints and check constraints
• Foreign key constraints that reference the table. In a parent-child relationship (for example, Order and Order_Detail), you can enable Stretch for the child table (Order_Detail) but not for the parent table (Order).
• Full-text, XML or spatial indexes
• Indexed views that reference the table

When I started writing this article about Stretch Database, I didn't research much about the price of this resource, as in my opinion, I would only be charged for the data transferred and stored in Azure. When you are using the SSMS Wizard, it even shows you a cost estimate for your current use of the Stretch Database:

As you can see in the image, the estimated cost is 61 dollars per Terabyte, per month. However, this estimate is only for transferred data. When you create a Stretch Database server, you are charged for that server as well, and contrary to this estimate, it is not cheap at all. I realized this when I received an email, 4 days after my Stretch Database tests, saying that I had already consumed my 1,000 reais of credit in Azure (although I hadn't used anything yet).

After this scare (lol) I ended up researching more price of this resource, which is below so that you are aware of the cost of this service: