How to reconcile Orphan stretch databases

Published 10-19-2020 01:03 AM 1,002 Views
Microsoft

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

 

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

 

 

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!

%3CLINGO-SUB%20id%3D%22lingo-sub-1794324%22%20slang%3D%22en-US%22%3EHow%20to%20reconcile%20Orphan%20stretch%20databases%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1794324%22%20slang%3D%22en-US%22%3E%3CP%3EStretch%20databases%20where%20introduced%20in%20SQL%20server%202016%20to%20allow%20store%20your%20cold%20data%20un%20Azure%20%26nbsp%3Band%20access%20them%20transparently%20and%20securely%20without%20any%20change%20in%20queries%20or%20applications.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Fstretch-database%2Fstretch-database%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Fstretch-database%2Fstretch-database%3Fview%3Dsql-server-ver15%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EToday%20I%20have%20been%20working%20on%20case%20were%20customer%20was%20%3CSTRONG%3Edisabled%3C%2FSTRONG%3E%20and%20%3CSTRONG%3Eenabled%3C%2FSTRONG%3E%20stretch%20on%20a%20table%20several%20times%2C%20and%20result%20had%20been%20that%20he%20had%20his%20cold%20data%20%26nbsp%3B%26nbsp%3Bdistributed%20on%20several%20stretch%20databases%20and%20only%20one%20of%20them%20was%20replicating%20cold%20data.%3C%2FP%3E%0A%3CP%3ERemember%2C%20If%20you%20need%20stop%20movement%20of%20cold%20data%20to%20your%20stretch%20database%20temporary%20%2C%20the%20option%20to%20use%20is%20%E2%80%9C%20%3CSTRONG%3EPause%3C%2FSTRONG%3E%E2%80%9D.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_0-1603091014622.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227602i85166A83BD863742%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_0-1603091014622.png%22%20alt%3D%22Palomag_MSFT_0-1603091014622.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20way%20to%20reconcile%20all%20of%20them%20on%20a%20single%20stretch%20database%20is%20not%20difficult%20but%20you%20will%20need%20download%20orphan%20data%20to%20your%20on-premises%26nbsp%3B%20using%20%E2%80%9CLinked%20servers%E2%80%9D%20%26nbsp%3Bto%20orphan%20databases%20and%20using%20INSERT%20INTO%26nbsp%3B%20to%20merge%20all%20of%20them%20or%20your%20on-premises%20main%20database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1.-%20If%20stretch%20is%20enable%20you%20will%20need%20identify%20stretch%20database%20that%20is%20active%20and%20receiving%20data.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20%3CSTRONG%3ETasks%20%7C%20Stretch%20%7C%20Monitor%3C%2FSTRONG%3E%20for%20a%20database%20in%20SQL%20Server%20Management%20Studio%20to%20open%20%26nbsp%3B%E2%80%9CStretch%20Database%20Monitor%E2%80%9D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20top%20portion%20of%20the%20monitor%20displays%20general%20information%20about%20both%20the%20Stretch-enabled%20SQL%20Server%20database%20and%20the%20remote%20Azure%20database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20bottom%20portion%20of%20the%20monitor%20displays%20the%20status%20of%20data%20migration%20for%20each%20Stretch-enabled%20table%20in%20the%20database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_1-1603091014667.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227603i77D4C3BD9CC85790%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_1-1603091014667.png%22%20alt%3D%22Palomag_MSFT_1-1603091014667.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Fstretch-database%2Fmonitor-and-troubleshoot-data-migration-stretch-database%3Fview%3Dsql-server-ver15%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fsql-server%2Fstretch-database%2Fmonitor-and-troubleshoot-data-migration-stretch-database%3Fview%3Dsql-server-ver15%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2.-%20Create%20one%20linked%20server%20per%20each%20orphan%20database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_2-1603091014674.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227601iF2E449376E871322%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_2-1603091014674.png%22%20alt%3D%22Palomag_MSFT_2-1603091014674.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESample%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Palomag_MSFT_0-1603094545059.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227616iFB7FB780659E56CD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Palomag_MSFT_0-1603094545059.png%22%20alt%3D%22Palomag_MSFT_0-1603094545059.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3.-%20Insert%20data%20from%20each%20Linked%20server%20on%20target%20on-premises%20table%20using%20INSERT%20INTO%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20insert%20into%20%5B%3CTARGET_DATABASE_NAME%3E%5D%3C%2FTARGET_DATABASE_NAME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%20select%20*%20from%20%3CLINKED_SERVER_NAME%3E.dbo.%3CSOURCE_DATABASE_NAME%3E.dbo.%5B%3CTABLE_NAME%3E%5D%3C%2FTABLE_NAME%3E%3C%2FSOURCE_DATABASE_NAME%3E%3C%2FLINKED_SERVER_NAME%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4.-%20Let%20stretch%20replication%20upload%20data%20to%20the%20active%20stretch%20database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20you%20soon!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Version history
Last update:
‎Oct 19 2020 01:02 AM
Updated by: