To monitor Stretch-enabled databases and data migration, use the Stretch Database Monitor feature. It is part of SQL Server Management Studio and you open it when you select your database and then choose Tasks/Stretch/Monitor:
The top portion of the monitor displays general information about both the stretch-enabled SQL Server database and the remote Azure database, while the status of data migration for each stretch-enabled table in the database is shown in the bottom part of the screen:
You can also use the dynamic management view sys.dm_db_rda_migration_status to check the status of migrated data (how many batches and rows of data have been migrated). It contains one row for each batch of migrated data from each stretch-enabled table on the local instance of SQL Server. Here is the result generated by executing this view:
The sys.remote_data_archive_databases and sys.remote_data_archive_tables catalog views give information about the status of migration at the table and database level.
This query provides archive database information:
USE Mila; SELECT * FROM sys.remote_data_archive_databases;
Here is the output:
By using the following command, you can get information about archive tables on the Azure side :
USE Mila; SELECT * FROM sys.remote_data_archive_tables;
And here is the output:
Finally, to see how much space a stretch-enabled table is using in Azure, run the following statement:
USE Mila; EXEC sp_spaceused 'dbo.T1', 'true', 'REMOTE_ONLY';
Here is the result of this command:
name rows reserved data index_size unused
------ ------- ----------- ------ ---------- ------------
dbo.T1 2 288 KB 16 KB 48 KB 224 KB
In the next sections, you will see how to pause or disable the Stretch DB feature.