SQL Server 2016 – Drop \ Disable Database \ Table Stretch Feature

It’s been a week I was playing around this new feature. Now I’m going to drop\disable this feature so save some $ in my Azure subscription 🙂

Disabling this is a tricky one as we knew we moved our data from in house server to the cloud. Now when you disable it the data will be partially in cloud and the rest will be in our server. So first step is to get the data from the cloud to our inhouse server. Time taken depends on the volume of data and the network speed between inhouse and cloud server.

There are lot of methods but I prefer my method because of less down time or you can switch over in no time.

InHouse Server

• Pause database stretching

ALTER TABLE TRIP SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) ) ;

• Bring back data to a temp table

SELECT * INTO TRIP_temp FROM TRIP

• Move the data from temp table to the actual table

SET IDENTITY_INSERT TRIP ON
INSERT INTO TRIP
SELECT * FROM TRIP_temp
GO
DROP TABLE TRIP_temp

• Ensure you got all the data into the actual table before you disable it or else drop the temp table some point later. Now disable streching at table level. When you do the same step via GUI it will warn you that the table and database in Azure wont be dropped and it will be there so we have to manually clear it. Just right click on the table, go to Stretch and then Disable

• However when you do it via below TSQL query it wont warn you anything. It will be good if Microsoft add a small piece of information.

ALTER TABLE TRIP SET ( REMOTE_DATA_ARCHIVE = OFF ( MIGRATION_STATE = PAUSED ) )

• Now let’s disable at streching at database level

ALTER DATABASE SampleDB
SET REMOTE_DATA_ARCHIVE = OFF

• Now let’s disable at server level. If you enabled stretch for any other database then dont disable it server level.

IF( SELECT value_in_use
FROM sys.configurations
WHERE name = 'remote data archive' ) = 1
BEGIN
EXEC sp_configure 'remote data archive', 0;
RECONFIGURE;
END;

In Azure (Cloud)

• Connect to the Azure server
• Run the below command to drop the database created for streching. It’s easy to identify the db name, it will be unique.

DROP DATABASE [RDASampleDB6D511ED3-2041-45F3-8AEC-1CF9D858CC89]