You can use the Enable Database for Stretch wizardto configure a database for Stretch Database. To launch it, you need to right-click on the newly created Mila database in SQL Server Management Studio (SSMS), and from the right-click context menu, select Tasks |  Stretch |  Enable respectively. When you launch the wizard, you should get the screen shown in Figure 6.8:

Figure 6.8: Enable Database for Search Wizard - Introduction Page

You can see an intro screen that describes what you can achieve with the Stretch Database feature and what you need to use it. Since your database has no tables, the second section of the wizard is Configure Azure. You are asked to enter your Azure credentials and to connect to Azure. The screen is shown in Figure 6.9:

Figure 6.9: Enable Database for Search Wizard - Configure Azure page

After signing in to Azure, you should select one of your Azure subscriptions and an appropriate Azure region. Create a new or choose an existing Azure server, as shown in Figure 6.10:

Figure 6.10: Enable Database for Search Wizard - sign in to Azure and select subscription and server

The next part of the wizard is Secure credentials. The wizard lets you create a database master key (if your database does not have one) in order to protect the database credentials and connection information stored in your SQL Server database. Database security is covered in detail in Chapter 8, Tightening the Security. The appropriate screen is shown in Figure 6.11:

Figure 6.11: Enable Database for Search Wizard - Secure credentials

As already mentioned, you need to create Azure firewall rules to let your Azure SQL database communicate with your local SQL Server database. You can define a range of IP addresses with the Enable Database for Stretch wizard's page Select IP address, as shown in Figure 6.12:

Figure 6.12: Enable Database for Search Wizard - Select IP address

And the tour is almost done. The next screen is Summary and it displays what you have already selected and entered, but it also provides an estimated price for the Stretch DB setup. Figure 6.13 shows the Summary screen:

Figure 6.13: Enable Database for Search Wizard - Summary

As you can see, the Summary screen brings one very important piece of information to you: the estimated price for enabling the Stretch DB feature. The Estimated Pricing section in the summary report is a bit strange: it shows two prices: $61 USD per TB per month and 1,825 USD per month. If you enable Stretch DB for your database with no tables, you would need to pay at least 1,825 USD per month! It does not seem to be cheap at all for an empty database. However, there is also a third piece of information in that section—a link to the pricing page at Microsoft Azure—and you can find more details about pricing there. The pricing is covered later in this chapter, in the SQL Server Stretch Database pricing section. For now, it is enough to know that you don't need to pay a full month's cost if you remove your database from the cloud before that. The minimum period for payment is 1 hour.

However, this is not immediately clear, and even if you want to just try or play with the feature to find out how it works or to explore it, you need to pay for this or apply for a trial subscription (which involves giving credit card details). I expected a non-complicated trial version with limited functionalities but without required registration and payment data, where I can check and learn about the feature. Stretch DB as a new and promising feature should be easy to try. Now it is time to click on the Finish button to instruct the wizard to perform the final step in the process of enabling the Stretch DB feature. After the last wizard action is done, the stretch database is created in Azure. You can use SSMS to see that the action was successful. When you choose the database Mila, you will see a different icon near to the database name, as displayed in Figure 6.14:

Figure 6.14: Database in SSMS with enabled Stretch DB feature

After the feature is enabled for your sample database, you should not expect anything, since there are no tables in it. You will create a table and continue to play with stretching later in this chapter.

You can enable the Stretch DB feature by using Transact-SQL only. As you saw in the previous section, to enable Stretch DB, you need to create and secure communication infrastructure between our local database and the Azure server. Therefore, you need to accomplish the following three tasks:

The following code creates a database master key for the sample database Mila:

USE Mila;   
CREATE MASTER KEY ENCRYPTION BY PASSWORD='<very secure password>'; --you need to put your password here 

Next, we create a credential. This is saved authentication information that is required to connect to external resources. You need a credential for only one database; therefore you should create a database-scoped credential:

CREATE DATABASE SCOPED CREDENTIAL MilaStretchCredential   
WITH  
IDENTITY = 'Vasilije',  
SECRET = '<very secure password>'; --you need to put your password here 

Now you can finally enable the Stretch DB feature by using the ALTER DATABASE statement. You need to set REMOTE_DATA_ARCHIVE and define two parameters: Azure server and just created database scoped credential. Here is the code that can be used to enable the Stretch DB feature for the database Mila:

ALTER DATABASE Mila   
    SET REMOTE_DATA_ARCHIVE = ON   
        (   
            SERVER = '<address of your Azure server>,   
            CREDENTIAL = [MilaStretchCredential]  
        );   

With this action, you have created an infrastructure, necessary for communication between your local database and the Azure server that will hold the stretched data. Note that this action can take a few minutes. When I executed the command, it took about 3 minutes, as shown in Figure 6.15:

Figure 6.15: Enabling Stretch Database by Using Transact-SQL

The next and final step is to select and enable tables for stretching.

You can create a new table with the Stretch DB feature enabled or enable it for an existing table using the Enable Table for Stretch wizard. To launch it, you need to navigate to the T1 table under the database Mila in SQL Server Management Studio (SSMS). Then, after right-clicking, you need to select the option Tasks/Stretch/Enable respectively. You should get a screen as shown in Figure 6.16:

Figure 6.16: Enable Table for Stretch Wizard - Select tables

As you can see, T1 can be selected for stretching, since it meets the Stretch DB requirements discussed in the previous sections. You can choose to migrate the entire table or (by clicking on the link Entire Table ) only a part of it. When you click on the link, you'll get a screen similar to the one shown in Figure 6.17:

Figure 6.17: Enable Table for Stretch Wizard - Select rows to stretch

You see a query builder that can help you to write the correct filter function. Filter function is used to define which rows have to be migrated to the cloud. In this example, you are going to return all rows from the T1 table, where the value in the c2 column is less than 2016/06/01.

However, developers find query builders a bit clumsy, and most of them prefer to work with Transact-SQL. In the next section, you will see how to use Transact-SQL to configure Stretch DB.

In order to support table stretching, the CREATE and ALTER TABLE statements have been extended in SQL Server 2016. Here is the syntax extension for the ALTER TABLE statement that supports the Stretch DB feature:

<stretch_configuration> ::=   
    {   
      SET (   
        REMOTE_DATA_ARCHIVE    
        {   
            = ON (  <table_stretch_options>  )   
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )   
          | ( <table_stretch_options> [, ...n] )   
        }   
            )   
    }   
   
<table_stretch_options> ::=   
    {   
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]   
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }   
    }   
 

You can specify the following options to enable Stretch DB:

If your table contains both hot and cold data, you can specify a filter predicate to select the rows that should be migrated. The filter predicate is an inline table-valued function. Its parameters are identifiers for stretch table columns. At least one parameter is required. Here is the function syntax:

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datatype1, @column2 datatype2 [, ...n])   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
RETURN  SELECT 1 AS is_eligible   
        WHERE <predicate>   

The function returns either a non-empty result or no result set. In the first case, the row is eligible to be migrated, otherwise it remains in the local system.

The <predicate> can consist of one condition, or of multiple conditions joined with the AND logical operator.

<predicate> ::= <condition> [ AND <condition> ] [ ...n ]

Each condition in turn can consist of one primitive condition, or of multiple primitive conditions joined with the OR logical operator. You cannot use subqueries or non-deterministic functions. For a detailed list of limitations, please visit this page in the SQL Server Books Online: https://msdn.microsoft.com/en-us/library/mt613432.aspx.

The following code example shows how to enable the Stretch DB feature for the T1 table in the database Mila:

USE Mila; 
CREATE FUNCTION dbo.StretchFilter(@col DATETIME)   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
       RETURN SELECT 1 AS is_eligible  
WHERE @col < CONVERT(DATETIME, '01.06.2016', 104); 
GO 
ALTER TABLE dbo.T1  
    SET (  
   REMOTE_DATA_ARCHIVE = ON (   
        FILTER_PREDICATE = dbo.StretchFilter(c2),   
        MIGRATION_STATE = OUTBOUND 
   )  
);    

After executing the preceding commands, Stretch DB is enabled for T1 table. Figure 6.18 shows the SSMS screen immediately after the execution:

Figure 6.18: Enabling table for stretch by using Transact-SQL

The Stretch DB feature is enabled, but you can also see a warning message that informs you that although your T1 table has a primary key constraint, it will not be enforced! Thus, you can have multiple rows in your table with the same ID, just because you have enabled the Stretch DB. This schema and integrity change silently implemented as part of Stretch DB enabling can be dangerous; some developers will not be aware of it, since the information is delivered through a message warning.

When you ignore this problem, the rest of the action looks correct. After the table is enabled for stretching, you can expect three rows to remain in the local database (they have a value in the c2 column greater than 1 June). Two rows should be moved to the Azure SQL database. You will confirm this by querying stretch tables, but before that you will learn a tip about the creation of a filter predicate with sliding window.

As mentioned earlier, you cannot call a non-deterministic function in a filter predicate. If you, for instance, want to migrate all rows older than 1 month (where a date column has a value older than 1 month), you cannot simply use the DATEADD function in the filter function because DATEADD is a non-deterministic function.

In the previous example, you created the filter function to migrate all rows older than 1 June 2016. Assume that you want to send all rows older than 1 month to the cloud. Since the function must be deterministic and you cannot alter the existing one because it is defined with SCHEMABINDING attribute, you need to create a new function with the literal date again. For instance, on 1 August, you would need a function that instructs the system to migrate rows older than 1 July:

CREATE FUNCTION dbo.StretchFilter20160701(@col DATETIME)   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
       RETURN SELECT 1 AS is_eligible  
WHERE @col < CONVERT(DATETIME, '01.07.2016', 104);

Now you can assign the newly created function to the T1 table:

ALTER TABLE dbo.T1    
SET (REMOTE_DATA_ARCHIVE = ON    
    (FILTER_PREDICATE = dbo.StretchFilter20160701(c2), 
     MIGRATION_STATE = OUTBOUND    
     )   
);   

Finally, you should remove the old filter function:

DROP FUNCTION IF EXISTS dbo.StretchFilter;