Azure SQL Database (22) migrates part of the data to Azure Stretch database

Windows Azure Platform Family of articles Catalog

Azure SQL Database Stretch Database Overview

Azure SQL Database (20) using SQL Server Upgrade Advisor

Azure SQL Database (21) migrates the entire table to Azure Stretch database

Azure SQL Database (22) migrates part of the data to Azure Stretch database

In the previous document, I described how to migrate all the data from a single table to the cloud stretch database

Azure SQL Database (21) migrates the entire table to Azure Stretch database

Here, the author describes how to move some of the data in a table into the cloud stretch database.

For more information, please refer to the MSDN article:https://msdn.microsoft.com/en-us/library/mt613432.aspx

Preparatory work:

1. If you have already started the practice before, please delete the previous local sample Database:adventureworks2016ctp3

2. Login to Azure China management platform:https://manage.windowsazure.cn/, delete stretch Database

　　

3. Re-restore adventureworks2016ctp3

　Basic definition:

We can create the following method function in local SQL Server 2016

CREATE FUNCTION dbo.fn_stretchpredicate (@column1 datatype1, @column2 datatype2 [, ... n])  RETURNS TABLE with  SCHEMABINDING   as   RETURN  1 as is_eligible          < Predicate>  

　return value:

If the returned value is non-null (NON-EMPTY), the returned results are migrated to the cloud stretch Database.

The remaining value (that is, the content that is not in these return values) is preserved in the local SQL Server 2016

　　Conditions:

The condition here is the <predicate> parameter.

We also take the database ADVENTUREWORKS2016CTP3, table sales.ordertracking as an example.

1. First open the local computer's SQL Server Management Studio (SSMS) and run the following script:

Use adventureworks2016ctp3-Review the dataselect count (3--Review the dataselect count (3

As you can see, the amount of data for Trackingeventid <= 3 is 94364. The Trackingeventid>3 data volume is 94426. Entries

The next few steps, along with the previous documentation, are to set up and open the archive feature.

--For local SQL Server ., open archive function exec sp_configure'Remote Data Archive','1'; RECONFIGURE;--Azure SQL database for cloud user name and password, encrypted, encrypted password with SQL database password: use ADVENTUREWORKS2016CTP3; CREATE MASTER KEY encryption by PASSWORD='[email protected]'CREATE DATABASE SCOPED credential azuredbcred with IDENTITY='Sqladmin', SECRET ='[email protected]';--Place the local SQL Server 2016 archive target, point to Microsoft Cloud SQL Database Server (l3cq1dckpd.database.chinacloudapi.cn)--This l3cq1dckpd.database.chinacloudapi.cn is the new server that we created in preparation for the ALTER DATABASE [ADVENTUREWORKS2016CTP3] SET Remote_ Data_archive=On (SERVER='l3cq1dckpd.database.chinacloudapi.cn', credential = azuredbcred);

2. Then we plan to save the value of Trackingeventid <= 3 (altogether 94364 line) to the cloud stretch Database.

3. In native SSMs, we execute the following T-SQL statement. Create function

--int) RETURNS TABLE with SCHEMABINDING as    RETURN13

4. Start migrating some of the data in the data table

--= on    (= OUTBOUND    ,= Dbo.fn_stretchpredicate (Trackingeventid)));

5. We can execute the following statement to see the progress of the archived data migration

 from Sys.dm_db_rda_migration_status

6. After a period of waiting, we can execute the following T-SQL statements

Use Adventureworks2016ctp3go--display local data rows and data capacity exec sp_spaceused'sales.ordertracking','true','local_only'; GO--displays data rows and data volumes for cloud stretch database exec sp_spaceused'sales.ordertracking','true','remote_only'; GO

Execution Result:

As shown in: sales.ordertracking data stored locally has 94426 rows

Sales.ordertracking saved in the cloud Stretch database has 94364 rows of data

　　

Reference:https://msdn.microsoft.com/en-us/library/mt613432.aspx

Azure SQL Database (22) migrates part of the data to Azure Stretch database