For an instructor lead, in-depth look at learning SQL click below.
Given today’s unprecedented data growth rate, database scalability (the ability to expand or shrink capabilities as required) is increasingly a compelling topic. This post will introduce SQL Server Stretch Database, a feature designed to provide a cost-effective solution to the issue of scalability.
What is SQL Server Stretch Database?
SQL Server Stretch Database is a feature that allows you to ‘stretch’ your tables to Azure, meaning you can store portions of table data in the cloud. The benefit of this is that the data remains online and is accessible to apps, but it doesn’t consume expensive, on-premises storage.
Stretch Database Architecture
|
1 2 3 4 5 6 7 8 |
USE master; GO EXEC sp_configure 'remote data archive' , '1'; GO RECONFIGURE; GO |
This code activates the Stretch Database for the entire SQL Server instance. Now let’s enable the feature at the database and table level.
Enabling Database
|
1 2 3 4 5 6 |
USE YourDatabase; GO ALTER DATABASE YourDatabase SET REMOTE_DATA_ARCHIVE = ON; GO |
Enabling Tables
|
1 2 3 4 5 6 7 |
USE YourDatabase; GO ALTER TABLE YourTable SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND)); GO |
Cloud Data Access
The Stretch Database appropriately handles any queries that involve table data. If the query only involves data that is located on-premises, SQL Server will handle it in the usual way. If the query involves stretched data, SQL Server will work with Azure to get the relevant data.
Conclusion
SQL Server Stretch Database can become a very useful scalability solution to consider. It can help manage your data efficiently and cost-effectively, allowing you to free up resources and ensure potentially excellent performance. With SQL Server scaling, the possibilities are virtually limitless.