In this blog, I will present you the new Stretch Database feature in SQL Server 2016. It couples your SQL Server On-Premises database with an Azure SQL Database, allowing to stretch data from one ore more tables to Azure Cloud.
\nThis mechanism offers to use low-cost hard drives available in Azure, instead of fast and expensive local solid state drives. Indeed SQL Database Server resources are solicited during data transfers and during remote queries (and not SQL Server on-premises). <\/p>\n
First, you need to enable the “Remote Data Archive” option at the instance level. To verify if the option is enabled: To enable this option at the instance level: Now, you have to link your on-premises database with a remote SQL Database server: The process may take some time as it will create a new SQL Database in Azure, linked to your on-premises database. The credential entered to connect to your SQL Database server is defined in SQL Database. Previously you need to secure the credential by a database master key.<\/p>\n To view all the remote databases from your instance: Now, if you want to migrate one table from your database ([Purchasing].[PurchaseOrderDetail] in my example), proceed as follows: Of course repeat this process for each table you want to stretch. You can still access to your data during the migration process.<\/p>\n To view all the remote tables from your instance: To view the batch process of all the data being migrated: (indeed, you can filtrate by the a specific table) It is also to easily migrate your data back: Moreover, you can select rows to migration by using a filter function. Here is an example: Then when enable the data migration, specify the filter function: Of course in Microsoft world, you can also use a wizard to set up this feature. The choice is up to you!<\/p>\n","protected":false},"excerpt":{"rendered":" In this blog, I will present you the new Stretch Database feature in SQL Server 2016. It couples your SQL Server On-Premises database with an Azure SQL Database, allowing to stretch data from one ore more tables to Azure Cloud. This mechanism offers to use low-cost hard drives available in Azure, instead of fast and […]<\/p>\n","protected":false},"author":14,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[],"type_dbi":[],"class_list":["post-9100","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring"],"acf":[],"yoast_head":"\n
\nUSE master
\nGO
\nSELECT name, value, value_in_use, description from sys.configurations where name like 'remote data archive'<\/code><\/p>\n
\n
\nEXEC sys.sp_configure N'remote data archive', '1';
\nRECONFIGURE;
\nGO<\/code><\/p>\n
\nUse AdventureWorks2014;
\nGO
\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'masterPa$$w0rd'
\nGO
\nCREATE DATABASE SCOPED CREDENTIAL Stretch_cred
\n WITH IDENTITY = 'dbi' , SECRET = 'userPa$$w0rd' ;
\nGO
\nALTER DATABASE AdventureWorks2014
\n SET REMOTE_DATA_ARCHIVE = ON
\n (
\n SERVER = 'dbisqldatabase.database.windows.net' ,
\n CREDENTIAL = Stretch_cred
\n ) ;
\nGO<\/code><\/p>\n
\nSelect * from sys.remote_data_archive_databases<\/code><\/p>\n
\nALTER TABLE [Purchasing].[PurchaseOrderDetail]
\n SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND) ) ; <\/code><\/p>\n
\nSelect * from sys.remote_data_archive_tables<\/code><\/p>\n
\nSelect * from sys.dm_db_rda_migration_status<\/code><\/p>\n
\nALTER TABLE [Purchasing].[PurchaseOrderDetail]
\n SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;<\/code><\/p>\n
\nCREATE FUNCTION dbo.fn_stretchpredicate(@column9 datetime)
\nRETURNS TABLE
\nWITH SCHEMABINDING
\nAS
\nRETURN SELECT 1 AS is_eligible
\n WHERE @column9 > CONVERT(datetime, '1\/1\/2014', 101)
\nGO <\/code><\/p>\n
\nALTER TABLE [Purchasing].[PurchaseOrderDetail] SET ( REMOTE_DATA_ARCHIVE = ON (
\n FILTER_PREDICATE = dbo.fn_stretchpredicate(ModifiedDate),
\n MIGRATION_STATE = OUTBOUND
\n) )<\/code><\/p>\n