Monitor Stretch in SQL Server 2016

ByHariharan Rajendran

Monitor Stretch in SQL Server 2016

Go through my previous article to enable the stretch for your database and table. This article is about how to monitor the stretch. You can monitor the stretch through GUI or DMVs.

We have below DMVs available to monitor the status of the stretch.

select * from sys.remote_data_archive_databases

 

The above script will provide the details about what is the database name in Azure used for stretch.

image

 

select * from sys.remote_data_archive_tables

 

This will tell you, what is the table name in azure used for stretch.

image

The above result has column called “filter_predicate”. It means, if apply any predicate function while configure the stretch then you can see that predicate function here. unfortunately, we don’t have option to apply the predicate function in GUI method. It is available when we enable stretch through T-SQL Script.

select * from sys.dm_db_rda_migration_status

This script will tell you about data migration. Data will start migrate from the table once stretch is enabled. Migration will start  from on-premises SQL Server to Azure. It is using batch process so it will migrate maximum of  4999 records in a batch. The batch id will be available in Azure table.

image

To see the live migration, we can use “SP_SPACEUSED”. By seeing the rows and size we can identify the data migration.

image

 

You can monitor the stretch using GUI,

image

image

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a MVP in Data Platform and Microsoft Certified Trainer with 10+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!