SQL Server 2016: CREATE TABLE for Stretch Database is available

This month, the Release Candidate 1 (RC1) for SQL Server is available.

With this new version, you can now use the “CREATE TABLE” command for Stretch Databases.

If you read the msdn page from CREATE TABLE for SQL Server 2016, you can see these options:

The first step is to enable the functionality for the database “AdventureWorksDW2012”

To enable the Stretch Database, create a master key and a database scoped credential and set the REMOTE_DATA_ARCHIVE with your sql database on azure

USE [AdventureWorksDW2012];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pa$$w0rd';
CREATE DATABASE SCOPED CREDENTIAL StretchCred WITH IDENTITY = 'sth', SECRET = ' pa$$w0rd'';
GO

ALTER DATABASE [AdventureWorksDW2012]
    SET REMOTE_DATA_ARCHIVE = ON ( SERVER = 'ovv16an9fs.database.windows.net' , CREDENTIAL = StretchCred ) ;
GO

Now, I create directly my table with the option REMOTE_DATA_ARCHIVE:

use [AdventureWorksDW2012]

CREATE TABLE dbo.TestStretchTable
(
  FirstName VARCHAR(50),
  LastName  VARCHAR(50)
) WITH (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = PAUSED )) 

GO

I insert data to have something to transfer:

With the option MIGRATION_STATE = PAUSED, on the SQL Database, I have no table.

ALTER TABLE [dbo].[TestStretchTable]
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND )  ) ;
GO

I change the MIGRATION_STATE to Outbound and now, I see that the “Select” command have a remote query:

In Visual Studio, you can see the Table on azure

Now, I create directly a table with the option MIGRATION_STATE set to Outbound

CREATE TABLE dbo.TestStretchTable2
(
  FirstName VARCHAR(50),
  LastName  VARCHAR(50)
) WITH (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND)) 

GO

And the table is automatically created on sql database.

This new option “CREATE TABLE” completes the stretch database functionality before the SQL Server 2016 Release To Manufacturer (RTM)… 🙂