-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. In this article, I will be disabling stretch database on the SQL server instance.
-> Executing the below query to see what all tables are part of stretch database,
select object_name (object_id),* from sys.remote_data_archive_tables
-> We must first disable the stretch for each individual tables before disabling it for the database.
-> Disabling it for Table1 by migrating the data back from Azure database to On-Premise database. Please note that migrating the data from azure will involve extra cost.
-> Make sure we have sufficient space on the data drive to bring back the data and then click on “Yes”.
-> Thats quick. Usually it takes time for migrating, if the data involved is too huge.
-> Executing the below query to see what all tables are part of stretch database. We dont see Table1 anymore,
select object_name (object_id),* from sys.remote_data_archive_tables
-> Checking the execution plan after executing the below query. The “Remote Query” is no longer present and the “actual number of rows” now is 50000.
select sname, year, count(*)
from Table1
group by sname,year
-> Disabling stretch feature for Table2 by selecting option “Leave Data in Azure”.
-> Executing the below query to see what all tables are part of stretch database. We dont see any tables now,
select object_name (object_id),* from sys.remote_data_archive_tables
-> Checking the execution plan after executing the below query. The “Remote Query” is no longer present and the “actual number of rows” now is 10000. It is clear that all the rows in azure is not migrated back to On-Premise database.
select sname, year, count(*)
from Table2
group by sname,year
-> Now that the tables are done, I will disable the stretch feature for the database,
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.