Disabling Stretch Database

-> 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

Blog5_1

-> 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.

Blog5_2.PNG

-> Make sure we have sufficient space on the data drive to bring back the data and then click on “Yes”.

Blog5_3.PNG

Blog5_4.PNG

-> 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

Blog5_5.PNG

-> 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

Blog5_6.PNG

-> Disabling stretch feature for Table2 by selecting option “Leave Data in Azure”.

Blog5_7.PNG

Blog5_8

Blog5_9

Blog5_10

-> 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

Blog5_11

-> 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

Blog5_13

Blog5_12

-> Now that the tables are done, I will disable the stretch feature for the database,

Blog5_14.PNG

Blog5_15

Blog5_16

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.

Leave a Reply