Hi
We've been forced to use "Stretch" for some history tables where our existing storage capacity is being pushed to it's limits... hooray , new hardware (something that can attach to our new greenlake/nimble SAN 🙂 )
however for licencing reasons and server consolidation i'm testing a lot of our apps for the move (lots of code changes)
one problem - I've restored the stretched database to my laptop - I want to disable stretching on my laptop (while bringing the data back to my laptop but not losing data that is stretched) and not affecting the live stretch
so - the sql documentation is as follows
To disable Stretch Database for a table, select Stretch for a table in SQL Server Management Studio. Then select one of the following options.
Disable | Bring data back from Azure. Copy the remote data for the table from Azure back to SQL Server, then disable Stretch Database for the table. This operation incurs data transfer costs, and it can't be canceled.
Disable | Leave data in Azure. Disable Stretch Database for the table. Abandon the remote data for the table in Azure.
You can also use Transact-SQL to disable Stretch Database for a table or for a database.
After you disable Stretch Database for a table, data migration stops and query results no longer include results from the remote table.
if I disable stretch on a restored database on my laptop but I use the "bring back" option, will I affect live? will the stretch data still be on azure?
MVDBA
Going by the documentation, when you restore a stretch database, you have to reauthenticate / reauthorise the access to the stretched data. One of the parameters to the command that does this is to make a copy of the database.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 27, 2020 at 2:19 pm
Hmmmmm….. looks like that is the correct fix as i'm getting the following error on my commands
Cannot query table xxx.dbo.historic_xxxx' because this operation is currently disabled for this object.
if I understand correctly then it takes a clone of the old stretch and links it to my restored database, allowing me to not effect live.
MVDBA
January 27, 2020 at 2:22 pm
Yup, as long as you pass @with_copy = 1 as one of the parameters, it should do just that.
As always, though - test it before you break anything!
Good luck.
Thomas Rushton
blog: https://thelonedba.wordpress.com
January 28, 2020 at 11:47 pm
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply