SQL Server 2016 – Configure Database Stretching

In the last two days we saw whats stretch database and how to identify the tables. Today we will see how to stretch your database to Azure cloud. Before we start I would like mention it again that the tables you choose for streching will migrate all the data to Azure which means we can stretch only the archieve tables so dont just blindly add all the tables. Just include only the required archieve tables.

Configuring Stretch Dataabse
  • Connect to SQL Server 2016
  • By default remote data archieve is turned off so use the query below to turn it on. This will enable the feature at server level

IF( SELECT value_in_use
FROM sys.configurations
WHERE name = 'remote data archive' ) = 0
BEGIN
EXEC sp_configure 'remote data archive', 1;
RECONFIGURE;
END;

sql_server_2016_stretch_database_1

  • Now enable the same feature for a database. Right click on the database go to Tasks and then select Stretch and then Enable as shown in the below image.

sql_server_2016_stretch_database_2

  • It will pop up new wizard for configuration. Please note that there are lot of difference compared to CTP 2.1 version. Click next

sql_server_2016_stretch_database_3

  • Connecting to Azure cloud is encrypted by database key so you need to create master key (if it didnt created earlier) and then a database key. Just provide a new password in the screen and click next, GUI will do it for you.

sql_server_2016_stretch_database_4

  • Now it will list all the tables, choose the tables you want to stretch it and then click next

sql_server_2016_stretch_database_5

  • It will do all checks before enabling. If you remeber in the first article I said there are few limitations exists so it will validate those things. One more thing to note is that it wont create unique \ primary key in azure table.

sql_server_2016_stretch_database_6

  • In this screen you need to provide Azure credentails to which the subscriptions are added and then click next

sql_server_2016_stretch_database_7

  • In this screen you need to select the subscription and then the region. If you already have a Azure SQL Server then you can connect to it or else you can create a new one. In this one, I have a SQL Azure server so i used that.

sql_server_2016_stretch_database_8

  • Since I used my existing Azure SQL Server it’s asking the admin username and password. In addition to this I can provide the IP range to access this db. Im sure we need to exclude atleast the inhouse server IP.

sql_server_2016_stretch_database_9

  • Cross check the summary, if all looks good just click on finish

sql_server_2016_stretch_database_10

  • Awesome, selected tables are now stretched to Azure database.

sql_server_2016_stretch_database_11

  • Now if you look at the database symbol you can see it got changed. In addition to this I could find a external data source got created. I’m not able to guess what’s the the value appended after DBM.

sql_server_2016_stretch_database_12

All looks good. Let’s add some data to the table. I added couple of rows and it picked and migrated to Azure cloud

sql_server_2016_stretch_database_13

Insert works fine but Update \ Delete are not allowed. I believe since it’s an archieve table there wont be a need to update the data however delete is required to clean up older data or else we will again loosing cost in Azure storage. I assume we will get Delete \ Update functionality in RTM release.

Msg 14826, Level 16, State 1, Line 3
 Update and delete of rows eligible for migration in table 'trip' is not allowed because of the use of REMOTE_DATA_ARCHIVE.

Since it’s an archieve table, volume of data might be huge so I want to check how it process the data from Inhouse to Azure cloud. For this I connected to the Azure SQL Server and executed the below query. The name of the Azure db is different again with some unique value appended to the end, Im not clear what that unique value is.

sql_server_2016_stretch_database_14

A new column will be added to the table as “batch–ObjectIDofInhouseTable”. From this we are clear that it  will process the rows in batches. If you see it will pick a max of 4999 rows per batch which means the a batch size is less than 5000 rows. Cool 🙂

---IN AzureSQLServer
--Number of rows processed per batch
SELECT COUNT(*) [Number of Rows]
, [batchid--1285579618] [BatchID]
FROM [dbo].[dbo_Trip_1285579618_A1D2E41E-0C42-459B-80B7-D0C87C033F7C]
GROUP BY [batchid--1285579618];

Pausing Stretch

Let’s assume you are in a situation where you dont want to migrate the data as soon as it arrives to avoid network traffice etc. For this we can simply disable the stretch and then we can enable it back when required, it will pick up from the point where it left.

Right Click on the Table and then Stretch and then Pause “Data Migration” or you can use the below query

ALTER TABLE TRIP
SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) ) ; -- To resume change PAUSED to OUTBOUND

sql_server_2016_stretch_database_15

Enabling Stretch for additional tables at later point

Method 1

Right click on the User database and then “Tasks” and then “Stretch” and then “Reconfigure”. now you will get the same wizard but now it will start from the table page, choose the tables and follow the wizard

Method 2

Expand the user database, expand tables.
Right click on the table and then “Stretch” and then “Enable”

I’m done for this article, lets catch up tomorrow with some more details on database stretching.

2 thoughts on “SQL Server 2016 – Configure Database Stretching”

  1. This is very nice article,
    i have one query, lets assume i have an table called “EMP”.
    i inserted 1 million records today and it is Stretched to Cloud and i truncated the records from inhouse table, and second day again 1 million records inserted in my inhouse table, now i write a query to retrieve all data, can this query gives both the days data here ?

Leave a Reply

Your email address will not be published. Required fields are marked *

*