How to reconcile Orphan stretch databases

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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

 

Palomag_MSFT_0-1603091014622.png

 

 

 

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.

 

 

Palomag_MSFT_1-1603091014667.png

 

 

 

 

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.

 

Palomag_MSFT_2-1603091014674.png

 

 

Sample:

Palomag_MSFT_0-1603094545059.png

 

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!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.