When you query a Stretch Database, the SQL Server Database Engine runs the query against the local or remote database depending on data location. This is completely transparent to the database user. When you run a query that returns both local and remote data, you can see the Remote Query operator in the execution plan. The following query returns all rows from the stretch T1 table:
USE Mila; SELECT * FROM dbo.T1;
As expected, it returns five rows:
id c1 c2 ----------- -------------------- ----------------------- 2 Manchester United 2016-06-02 00:00:00.000 4 Juventus Torino 2016-06-25 00:00:00.000 5 Red Star Belgrade 2016-06-25 00:00:00.000 1 Benfica Lisbon 2016-05-15 00:00:00.000 3 Rapid Vienna 2016-05-28 00:00:00.000
You are surely much more interested in how the execution plan looks. It is shown in Figure 6.19:
Figure 6.19: Execution plan for query with stretch tables
You can see that the Remote Query operator operates with an Azure database and that its output is concatenated with the output of the Clustered Index Scan that collected data from the local SQL Server instance. Note that the property window for the Remote Query operator has been shortened to show only context-relevant information.
What does SQL Server do when only local rows are returned? To check this, run the following code:
SELECT * FROM dbo.T1 WHERE c2 >= '20160601';
The query returns three rows, as expected:
id c1 c2 ----------- -------------------- ----------------------- 2 Manchester United 2016-06-02 00:00:00.000 4 Juventus Torino 2016-06-25 00:00:00.000 5 Red Star Belgrade 2016-06-25 00:00:00.000
And the execution plan is shown in Figure 6.20:
Figure 6.20: Execution plan for query with stretch tables returning local data only
The plan looks good; it checks only the local database and there is no connection to Azure. Finally, you will check the plan for a query that logically returns remote data only. Here is the query:
SELECT * FROM dbo.T1 WHERE c2 < '20160601';
You will again get the expected result:
id c1 c2 ----------- -------------------- ----------------------- 1 Benfica Lisbon 2016-05-15 00:00:00.000 3 Rapid Vienna 2016-05-28 00:00:00.000
Figure 6.21 shows the execution plan:
Figure 6.21: Execution plan for query with stretch tables returning remote data only
You probably did not expect both operators here... only Remote Query should be shown. However, even if the returned data resides in the Azure SQL database only, both operators should be used since data can be in an eligible state, which means that it has not yet been moved to Azure.
Querying stretch tables is straightforward; you don't need to change anything in your queries. One of the most important things about Stretch Databases is that the entire execution is transparent to the user and you don't need to change your code when working with stretch tables.
However, you should not forget that enabling Stretch DB can suspend primary key constraints in your stretched tables. You have defined a primary key constraint in the T1 table; thus you expect that the next statement will fail (T1 already has a row with an ID with a value of 5):
INSERT INTO dbo.T1 (id, c1, c2) VALUES (5, Red Star Belgrade,'20170101');
However, the statement has been executed successfully even though an entry with the same ID already exists. You can confirm this by checking the rows in the T1 table.
SELECT * FROM dbo.T1 WHERE c2 >= '20160601';
The query returns four rows, and you can see two rows with the same ID (5):
id c1 c2 ----------- -------------------- ----------------------- 2 Manchester United 2016-06-02 00:00:00.000 4 Juventus Torino 2016-06-25 00:00:00.000 5 Red Star Belgrade 2016-06-25 00:00:00.000 5 Red Star Belgrade 2017-01-01 00:00:00.000
This is probably something that you would not expect, therefore, you should be aware of it when you enable the Stretch DB feature for a database table.
As mentioned earlier, queries against Stretch-enabled tables return both local and remote data by default. You can manage the scope of queries by using the system stored procedure sys.sp_rda_set_query_mode to specify whether queries against the current Stretch-enabled database and its tables return both local and remote data or local data only. The following modes are available:
LOCAL_AND_REMOTE(queries against Stretch-enabled tables return both local and remote data). This is the default mode.LOCAL_ONLY(queries against Stretch-enabled tables return only local data).DISABLED(queries against Stretch-enabled tables are not allowed).
When you specify the scope of queries against the Stretch database, this is applied to all queries for all users. However, there are additional options at the single query level for an administrator (member of db_owner group). As administrator, you can add the query hint WITH ( REMOTE_DATA_ARCHIVE_OVERRIDE = value ) to the SELECT statement to specify data location. The option REMOTE_DATA_ARCHIVE_OVERRIDE can have one of the following values:
LOCAL_ONLY(query returns only local data)REMOTE_ONLY(query returns only remote data)STAGE_ONLY(query returns eligible data)
The following code returns eligible data for the T1 table:
USE Mila; SELECT * FROM dbo.T1 WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = STAGE_ONLY);
Here is the output:
id c1 c2 batchID--581577110 ---------- -------------------- ----------------------- ------------------ 1 Benfica Lisbon 2016-05-15 00:00:00.000 1 3 Rapid Vienna 2016-05-28 00:00:00.000 1
Run this code to return data from the T1 table already moved to Azure:
SELECT * FROM dbo.T1 WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = REMOTE_ONLY);
Here is the output:
id c1 c2 batchID--581577110 -------- ------------- ---------------------- ------------------ 1 Benfica Lisbon 2016-05-15 00:00:00.000 1 3 Rapid Vienna 2016-05-28 00:00:00.000 1
Finally, this code returns data in the T1 table from the local database server:
SELECT * FROM dbo.T1 WITH (REMOTE_DATA_ARCHIVE_OVERRIDE = LOCAL_ONLY);
As you expected, three rows are returned:
id c1 c2 ----------- -------------------- ----------------------- 2 Manchester United 2016-06-02 00:00:00.000 4 Juventus Torino 2016-06-25 00:00:00.000 5 Red Star Belgrade 2016-06-25 00:00:00.000
By default, you can't update or delete rows that are eligible for migration or rows that have already been migrated in a Stretch-enabled table. When you have to fix a problem, a member of the db_owner role can run an UPDATE or DELETE operation by adding the preceding hint and will be able to update data in all locations.