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.
ALTER TABLE TRIP SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) ) ;
SELECT * INTO TRIP_temp FROM TRIP
SET IDENTITY_INSERT TRIP ON INSERT INTO TRIP SELECT * FROM TRIP_temp GO DROP TABLE TRIP_temp
ALTER TABLE TRIP SET ( REMOTE_DATA_ARCHIVE = OFF ( MIGRATION_STATE = PAUSED ) )
ALTER DATABASE SampleDB SET REMOTE_DATA_ARCHIVE = OFF
IF( SELECT value_in_use FROM sys.configurations WHERE name = 'remote data archive' ) = 1 BEGIN EXEC sp_configure 'remote data archive', 0; RECONFIGURE; END;
DROP DATABASE [RDASampleDB6D511ED3-2041-45F3-8AEC-1CF9D858CC89]