SQL DBA Interview Q & A on Stretch Database

Introduction to Stretch Database in Azure

There are different databases and services are available in Microsoft Azure.  Here, I am going to focus on stretch database/feature which is available from SQL Server 2016 onward.

Different types of databases & services in Azure

  1. Azure Cache for Redshift
  2. Azure Cosmos DB
  3. Azure Data Explorer
  4. Azure Database for Maria DB
  5. Azure Database for MySQL
  6. Azure SQL Database
  7. Azure database for PostgreSQL
  8. Data Factory
  9. SQL Data Warehouse
  10. SQL Server Stretch Database
  11. Table Storage

SQL Server Stretch Database:

This feature allows us to stretch our database to Azure for warm and cold transactional data. We can provide longer retention times. It saves our cost around 40% less expensive than adding more enterprise storage.

Stretch database bills compute and storage separately. Compute usage is represented with Database Stretch Unit (DSU), customer can scale up and down the level of performance/DSUs they need at any time.

Here, Database sizes are limited to 240 TB.

Stretch Database

This picture shows how to enable the Stretch feature and use it to save our cold data to Azure.

Interview Q &A

1.What editions of SQL Server are compatible with Stretch Database?

Stretch Database is a feature of SQL Server 2016. It is available on all editions of SQL Server 2016.

2.Can I change the performance level of my Stretch Database?

You can adjust the performance level of your Stretch Database. Changing performance levels takes a matter of minutes and your databases are online and available during the change.

3.What is a DSU (Database Stretch Unit)?

A SQL Stretch Database Stretch Unit (DSU) represents the power of the query and is quantified by workload objectives: how fast rows are written, read and computed against. This measure helps customers assess the relative amount of performance needed for their workload.

4.How much data can I stretch to Azure?

You can stretch up to 240 TB of data with Stretch Database.

5.How can I un-stretch my database?

You can move your data back to the on-premises environment at any time. If you decide to unsubscribe from Stretch Database, you must move data back on premises via un-stretching or export to a storage service in order to retain access to the data.

Note: Egress charges apply for data moving out of Azure. For more information on un-stretch or exporting data, please see the SQL Server 2016 Stretch Database documentation.

6.How much storage will my databases consume?

You will consume approximately the same amount of storage in Stretch Database as you currently do in SQL Server as the data originally stored in SQL Server is moved to Stretch Database.

7.Can I stretch to another SQL Server instead of Stretch Database?

Stretch Database currently does not support stretching to another SQL Server. You cannot stretch a SQL Server database to another SQL Server database.

8.If I have an existing Stretch database from SQL Server 2016 RC0 or earlier, can I just upgrade RC1 or later and run on the new Stretch Database service?

Direct upgrade is not supported. If you need to retain data already stretched to Azure, you will need to first un-migrate the data from Azure to your SQL Server database. Upon completion, you can upgrade and re-stretch to the new Stretch Database service. Please note that un-migrating data from Azure will incur data egress charges. If you are on RC1 or later, you may upgrade to a newer version using any existing upgrade methods supported by SQL Server.

9.How can I stretch to a SQL Stretch database?

You will need to first un-migrate the data from Azure back to your SQL Server database. Upon completion, you can upgrade and re-stretch to the new Stretch Database service. Please note that un-migrating data from Azure will incur data egress charges.

About Mirza Husain

Mirza Husain is a senior consultant-database and having more than 10+ years of experience in the IT industry with different domains. He is fond of writing and speaking about SQL Server and also keen to learn new technologies. He is holding MCA degree and having Microsoft certifications as MCP & MCTS. He is also ITIL certified. In his past years, he worked with many clients as Microsoft, Bank of America etc. Currently he is associated with a France based client and offering his best services. You may reach him on his email id -- mirza_dba@outlook.com. Thanks!
This entry was posted in Azure, Database Administration, Q & A, Sql Server New Feature, windows azure and tagged , , , , , , , , , . Bookmark the permalink.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s