Infrastructure at your Service

Stéphane Savorgnano

SQL Server 2016 CTP3.0: Stretch Database enhancements

Some months ago, my colleague Nathan explained you the bases of the new Stretch Database functionality via two blogs here and here.
With the new SQL Server 2016 CTP 3.0, Stretch Database now includes new features and enhancements that I will sow you in this blog.

 

In previous versions of SQL Server 2016 CTP 2.x, before to be allowed to Enable Stretch Database, you had to enable the “Remote Data Archive” option for the instance. For my new SQL Server CTP 3.0 instance, this option is for the moment disable:

Stretch1

It is now possible to Enable Stretch Database for individual table. Let’s try if it works with this configuration:

Stretch2

The wizard to Enable Table for Stretch appears:

Stretch3

Follow this wizard and enter:

  • SQL Server database credentials
  • Select tables from your database that you want to stretch to Azure (for me table command_queue)

During the validation of SQL Server settings, the first test will check if the instance is configured for Stretch Database, fires an information message telling you that the configuration has not been done for the moment and will be done by the wizard:

Stretch4

The wizard will now run the deployment process:

Stretch5

When the deployment is finalized, a special icon in front of the database name in Management Studio changed in order to directly visualized database with Stretch Database functionality:

Stretch6

Another great add-on is the monitor screen, accessible from the instance properties:

Stretch7

The monitor screen gives an overview of:

  • On-premise server information: server name, database name, locally allocated space
  • Azure server information: server name, database name, service tier, server region, database storage cost (not yet available)
  • State of the connection between on-premise and Azure servers
  • Stretch configured tables: table name, migration state, eligible rows, local rows, Rows in Azure and Details (not yet available)

Stretch8

Here all Eligible rows have not already been transferred to Azure.
A way to check the data transfer is to use the Extended Events. In fact, an Extended Event Session named StretchDatabase_Health is automatically created and started in your instance where the Stretch Database functionality is enabled:

Stretch9

Per default those events are captured:

  • stretch_database_disable_completed: Reports the completion of a ALTER DATABASE SET
    REMOTE_DATA_ARCHIVE OFF command
  • stretch_database_enable_completed: Reports the completion of a ALTER DATABASE SET
    REMOTE_DATA_ARCHIVE ON command
  • stretch_index_reconciliation_codegen_completed: Reports the completion of code generation for stretch remote index operation
  • stretch_remote_index_execution_completed: Reports the completion of remote execution for the generated code for a stretched index
  • stretch_table_codegen_completed: Reports the completion of code generation for a stretched table
  • stretch_table_remote_creation_completed: Reports the completion of remote execution for the
    generated code for a stretched table
  • stretch_table_row_migration_event: Reports the completion of the migration of a batch of rows
  • stretch_table_unprovision_completed: Reports the completion removal of local resources for
    a table that was unstretched
  • stretch_table_validation_error: Reports the completion of validation for a table when the user
    enables stretch

Some others events can be added, following the complete list:

  • stretch_codegen_errorlog: Reports the output from the code generator
  • stretch_codegen_start: Reports the start of stretch code generation
  • stretch_create_migration_proc_start: Reports the start of migration procedure creation
  • stretch_create_remote_table_start: Reports the start of remote table creation
  • stretch_create_update_trigger_start: Reports the start of create update trigger for remote data archive
    table
  • stretch_index_update_step_completed: Reports the duration of a stretched index update operation
  • stretch_database_events_submitted: Reports the completion telemetry transfer
  • stretch_migration_debug_trace: Debug trace of stretch migration actions
  • stretch_migration_queue_migration: Queue a packet for starting migration of the database and object
  • stretch_migration_sp_stretch_get_batch_id: Call sp_stretch_get_batch_id
  • stretch_migration_start_migration: Start migration of the database and object
  • stretch_schema_queue_task: Reports when a packet is about to be queued for processing a schema task for the database and object
  • stretch_schema_script_execution_completed: Reports the completion of stretch script execution during processing stretch schema task
  • stretch_schema_script_execution_skipped: Reports the skipping of stretch script execution during processing stretch schema task
  • stretch_schema_script_execution_start: Reports the start of stretch script execution during processing stretch schema task
  • stretch_schema_task_failed: Reports the failure of a stretch schema function during the stretch schema task
  • stretch_schema_task_start: Reports the start of stretch schema function during the stretch schema task
  • stretch_schema_task_succeeded: Reports the successful completion of stretch schema function during the stretch schema task
  • stretch_sync_metadata_start: Reports the start of metadata checks during the migration task
  • stretch_table_provisioning_step_duration: Reports the duration of a stretched table provisioning
    operation
  • stretch_table_provisioning_step_completed: Reports the duration of a stretched table provisioning operation
  • stretch_table_row_migration_results_event: Reports an error or completion of a successful
    migration of a number of batches of rows
  • stretch_unprovision_table_start: Reports the start of stretch table un-provisioning

An example of the stretch_table_row_migration_event shows:

  • Number of row migrated per batch (5000)
  • Duration in millisecond
  • Error number and state
  • Table and database id
  • If the migration succeed

Stretch10

 

Stretch Database is a new functionality and, as each new functionality, has some limitations that will decrease in the future.
Nevertheless the ability to stretch individual tables from a database to Azure could help organizations to easily archive their data.

 

 

Leave a Reply


3 + = five

Stéphane Savorgnano
Stéphane Savorgnano

Consultant