When I was at SQL Bits this week someone was asking me about SQL Server 2016’s new stretch database features. The attendee in question was asking about backing up and restoring the production database for development purposes. In their case the development team like to work with up-to-date production data.
The question was: “If I restore the production database to development, and the developers add data to a stretch table, will development data be stretched out into Azure and get mixed with production data that has already been stretched?” It was a great question, and wasn’t something I knew the answer to straight away.
The answer is, that when you restore the production database into development you need to re-authorise the stretch features to Azure. This is done by executing a new system stored procedure called: sys.sp_rda_reauthorize_db. You can find information on it here.
This stored procedure has a parameter called @with_copy, which is a BIT value. If you specify 1 then a copy of the remote data is made on the same Azure SQL DB server and created as a new Azure SQL DB. The on premises restored database will then be connected to the copy. This means that any data added to the newly restored database doesn’t get stretched and mixed with production data. I’m sure this is good news for anyone that was wondering the same thing! If there are any follow-up questions, then please email me at simon@coeo.com