Stretch Database in SQL Server 2016: Notes from the Field #127

Stretch Database is the SQL Server 2016 feature that attracted me most. SQL Server 2016 was released last week, with many features for performance and stability. As the name says, it stretches your database by keeping part of it in the Azure cloud. Microsoft has since discontinued Stretch Database.

In the 127th Notes from the Field episode, database expert Kenneth Urena shows how to use Stretch Database. Kenneth is a partner at Linchpin People.

SQL Server 2016 is being released, so we all ask how to use it to improve our database procedures. One of the features the product team added is the ability to recover huge databases quickly. That is where Stretch Database comes in.

What Is SQL Server Stretch Database?

Stretch is a technology introduced in SQL Server 2016. It lets you select tables, especially huge ones. You keep part of the data in the Azure cloud and part on premises. It is one of the first steps from Microsoft toward a truly integrated hybrid database. The idea is to keep cold data in the cloud and hot data on premises.

Are All Tables Eligible to Be Stretched?

This is the first release of the technology, so it comes with many restrictions. I recommend you use the Microsoft SQL Server 2016 Upgrade Advisor and follow these steps:

1. Open the Stretch Database Advisor.
2. Select the database to analyze.

   

3. Read the analysis report.

   

As the report shows, the sales table is not a candidate to be migrated to Stretch Database. The nice thing about the wizard is that it shows what to change to make the table eligible. In this case, you remove a constraint.

How to Start Using Stretch Database

By default, SQL Server 2016 (or at least RC3) does not come with Stretch Database enabled. To enable it, follow these steps:

1. Enable Stretch Database at the instance level. Don’t worry: this only tells SQL Server that you are likely to stretch some tables.

use master
-- Enable stretch database at the instance level
exec sp_configure 'remote data archive', '1';
GO
RECONFIGURE

2. Select the database you want to enable for Stretch Database.

   

3. Select the tables you want to stretch.

   

4. Once the table is selected, you can stretch the entire table. Or click the “Entire table” link and define the criteria for which rows get migrated.

   

5. Sign in to the Azure account that will host the cold data.

   

6. Create a new server or use an existing one in Azure.

   

7. Create a database master key.

   

8. Select the address from which this database is accessed. It can also be a network subnet range.

   

9. Review the summary, including the pricing in Azure.

   

10. Finally, the Stretch Database relationship is established.

    

How to Monitor, Add Tables or Pause the Migration

You can monitor the status of the migration in two ways.

1. Using T-SQL:

-- monitor synchronization status
select * from sys.dm_db_rda_migration_status

2. With the wizard: select the Monitor item in the Stretch menu.

   

Change the Stretch Status of a Table

You can do this by altering MIGRATION_STATE to OUTBOUND or PAUSED, as follows.

alter table T1 set (REMOTE_DATA_ARCHIVE = ON(MIGRATION_STATE=OUTBOUND));

Restore a Stretched Database

Restore the database with the normal backup and restore procedure. Most of the data lives in the cloud. So at the end of the restore, you must re-establish the link to the Azure database. You can do that with these steps:

1. Make sure you save the credential name. You can get it with this T-SQL:

select name from sys.database_scoped_credentials

2. Re-establish the link as follows:

exec sp_rda_reauthorize_db @credential = N'credential name', @with_copy = 0

How to Quickly See What Is Stretched

Some changes were made to system views such as sys.databases and sys.tables. To find the databases that are stretched, run this T-SQL:

select name
from sys.databases
where is_remote_data_archive_enabled = 1

What about the tables? To find the tables that are being stretched, this query is handy:

select object_id,
name
, is_remote_data_archive_enabled
from sys.tables
where is_remote_data_archive_enabled = 1

Conclusions

1. It is a great way to decrease the recovery time of a huge database when you restore from backups.
2. It is a great way to decrease storage billing for historical data. The data is in Azure, and you pay only for what you use.
3. It is the first release of the technology, so it comes with many restrictions. For example, no updates are allowed (up to RC3), no deletes are allowed, and the list goes on.
4. Setting up a staging or pre-production environment is complicated. You cannot restore the database in staging and link it to a different Azure database.
5. ALTER on stretched tables is not allowed. To modify a table, migrate the data to a new structure.

Stretch Database is not a bigger server, it is cold data in Azure and hot data on premises.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.