How to reconcile Orphan stretch databases

Stretch databases where introduced in SQL server 2016 to allow store your cold data un Azure  and access them transparently and securely without any change in queries or applications.

https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/stretch-database?view=sql-server-ver15

Today I have been working on case were customer was disabled and enabled stretch on a table several times, and result had been that he had his cold data   distributed on several stretch databases and only one of them was replicating cold data.

Remember, If you need stop movement of cold data to your stretch database temporary , the option to use is “ Pause”.

The way to reconcile all of them on a single stretch database is not difficult but you will need download orphan data to your on-premises  using “Linked servers”  to orphan databases and using INSERT INTO  to merge all of them or your on-premises main database.

1.- If stretch is enable you will need identify stretch database that is active and receiving data.

Select Tasks | Stretch | Monitor for a database in SQL Server Management Studio to open  “Stretch Database Monitor”

The top portion of the monitor displays general information about both the Stretch-enabled SQL Server database and the remote Azure database.

The bottom portion of the monitor displays the status of data migration for each Stretch-enabled table in the database.

https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/monitor-and-troubleshoot-data-migration-stretch-database?view=sql-server-ver15

2.- Create one linked server per each orphan database.

Sample:

3.- Insert data from each Linked server on target on-premises table using INSERT INTO

  insert into [<target_database_name].[dbo].[<target_table_name>]

  select * from <linked_server_name>.dbo.<source_database_name>.dbo.[<table_name>]

4.- Let stretch replication upload data to the active stretch database.

See you soon!