StretchDB Got Easier With SQL Server 2016

The world is moving towards the cloud and when I talk with decision makers around the world, there is one thing that comes up on top of the mind – how can I embrace the cloud adoption within my company. Yes, there are a select few who still love being on the on-premise infrastructure because of the investments they have already made. But that number is reducing faster than you can ever think off. I am amazed that the day for learning something completely new seems to be coming really faster than I really thought. I like it when customers push the boundaries of technology to achieve a larger objective.

Coming back to our topic of discussion for us. The next version of SQL Server is loaded with features and capabilities where the cloud technologies are playing a very important and vital role. This cannot be negated with the fact being this is evolving and changing to customer needs.

With SQL Server 2016, we have the luxury of integrating seamlessly with the Cloud offering of SQL Azure databases from our onpremise with the help of Stretch Database (or simply StretchDB). Here parts of your older data is moved seamlessly to the cloud (Azure SQLDB) based on how we configure things. This got simpler with the fact that we can enable this using the wizard with SQL Server Management Studio.

We have a number of ways to do this, let us use the simplest wizard format for this task. We can select the database -> right click -> Tasks -> Stretch -> Enable.

This starts the wizard and will take us through a number of steps wherein we need to feed some vital information from Azure account and the passwords for the setup to complete.

Creating a database master key is one of the important steps and make sure you remember the passwords being used. It is easy to get confused with the credentials being used for Azure and your on-premise default SQL Server instance. So keep track of this.

Once your firewall and IP addresses have been given access, the database is configured for stretch. At the instance level we can go ahead and use the TSQL command of doing the same with:

EXECsys.sp_configureN’remote data archive’,’1′;

RECONFIGURE;

GO

As you can see, the same above steps are available on TSQL with Configure server/instance setting, Database master key, IP settings, enabling stretch at the DB level etc. Once done, we can go ahead and stretch our table to cloud seamlessly using a command as shown below:

ALTERTABLE [tblStretch]

ENABLE REMOTE_DATA_ARCHIVE WITH ( MIGRATION_STATE =ON);

GO

If during the wizard you selected the table too, then the wizard will add another line item as shown below:

There is no point in adding a stretch if you are not able to track what data is moving and what is the current status of the data movement from various databases. This can be identified using the DMV as shown below:

–Check the migration status

SELECT*FROMSYS.dm_db_rda_migration_status

StretchDB is a powerful feature and can enable new workloads to be configured with Azure SQLDBs seamlessly. Using the power of cloud and building such hybrid architectures with the comfort of working inside SQL Server Management Studio is the key to success as far as SQL Server 2016 is concerned. I am glad to see such innovations happening inside SQL Server 2016 and this is pushing the comfort zone of traditional DBA’s who need to learn these things every single day. Do let me know if you explored working with StretchDB in your organization. Are you interested in utilizing this hybrid approach for storing historical / archive data? What are your thoughts on this capability?