On November 30th, I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0
\nI decided to share you my demonstration into a blog.<\/p>\n
<\/p>\n
First, you need to enable the \u201cRemote Data Archive\u201d option at the instance level.
\nTo check if the options is enabled:<\/p>\n
sp_configure 'REMOTE DATA ARCHIVE';\nGO<\/pre>\n<\/p>\n
As it returns a \u201crun-value\u201d set to \u201c0\u201d, I need to enable the option as follows:<\/p>\n
sp_configure 'remote data archive', '1';\nRECONFIGURE\nGO<\/pre>\n<\/p>\n
II – Enabling the feature at the database level<\/h3>\n
I create a database named \u201cStretch_DB\u201d for this demonstration:<\/p>\n
By default, the feature is obviously disabled for my database:<\/p>\n
SELECT is_remote_data_archive_enabled FROM sys.databases WHERE name = 'Stretch_DB';<\/pre>\nDuring the activation process of the feature, I need to link my local database to Azure. So I have to create a SQL Database server. I will not detail this creation in this blog because you can find several sources on the web describing how to do (cf. Getting Started with Azure SQL Database v12<\/a>).<\/p>\n
Do not forget to configure the remote firewall to accept the connection of the local SQL Server instance.<\/p>\n
To access to the remote SQL Database server, I also need to use the Server Admin Login (provided during the SQL Database server creation step).<\/p>\n
<\/p>\n
So I create the credential on my local instance:<\/p>\n
USE Stretch_DB;\nGO\nCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'pa$$w0rd';\nCREATE DATABASE SCOPED CREDENTIAL StretchCred WITH IDENTITY = 'myLogin', SECRET = 'myPa$$w0rd';\nGO<\/pre>\nAnd I link my local database to my remote server:<\/p>\n
ALTER DATABASE STRETCH_DB\n SET REMOTE_DATA_ARCHIVE = ON ( SERVER = 'JSS.DATABASE.WINDOWS.NET' , CREDENTIAL = STRETCHCRED ) ;\nGO<\/pre>\n<\/p>\n
I can check that my feature is enabled for my database:<\/p>\n
SELECT IS_REMOTE_DATA_ARCHIVE_ENABLED FROM SYS.DATABASES\n WHERE NAME = 'STRETCH_DB';\nGO<\/pre>\n<\/p>\n
Also, I can see my remote database created in Azure:<\/p>\n
SELECT * FROM SYS.REMOTE_DATA_ARCHIVE_DATABASES<\/pre>\n<\/p>\n
III – Enabling the feature at the table level<\/h3>\n
I create a table named \u201cERRLOG\u201d in \u201cdbo\u201d schema.<\/p>\n
Then I enable the migration for this table:<\/p>\n
ALTER TABLE DBO.ERRORLOG\n SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND ) ) ;\nGO<\/pre>\n<\/p>\n
I can check that my feature is enabled for my table:<\/p>\n
SELECT IS_REMOTE_DATA_ARCHIVE_ENABLED FROM SYS.TABLES\nWHERE NAME = 'ERRORLOG'<\/pre>\n<\/p>\n
Also, I can see the remote table created in Azure:<\/p>\n
SELECT * FROM SYS.REMOTE_DATA_ARCHIVE_TABLES<\/pre>\n<\/p>\n
I can pause the migration process for my table. Useful if I need to troubleshoot or monitor my feature.<\/p>\n
ALTER TABLE dbo.ErrorLog\n\tSET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) ) ;\nGO<\/pre>\n<\/p>\n
IV – Monitoring the Stretch Database feature<\/h3>\n
To check the migration state of the table:<\/p>\n
SELECT REMOTE_DATA_ARCHIVE_MIGRATION_STATE_DESC FROM SYS.TABLES\nWHERE NAME = 'ERRORLOG'<\/pre>\n<\/p>\n
To list the migration batch of the table:<\/p>\n
SELECT * FROM SYS.DM_DB_RDA_MIGRATION_STATUS ORDER BY START_TIME_UTC DESC;\nGO<\/pre>\n<\/p>\n
To obtain a data consumption of the table (locally and remotely):<\/p>\n
SP_SPACEUSED 'ERRORLOG', 'TRUE', 'REMOTE_ONLY'\nGO\nSP_SPACEUSED 'ERRORLOG', 'TRUE', 'LOCAL_ONLY'\nGO<\/pre>\n<\/p>\n
As soon as the Stretch Database feature is enabled, an Extended Events session is created to monitor the Stretch Database ecosystem.
\nTo see these events, I made a script:<\/p>\nSELECT CAST(EVENT_DATA AS XML) AS EVENTDATA
\nINTO #CAPTURE_EVENT_DATA
\nFROM SYS.FN_XE_FILE_TARGET_READ_FILE(‘C:MSSQL13.MSSQLSERVERMSSQLLOGSTRETCHDATABASE_HEALTH*.XEL’, ‘C:MSSQL13.MSSQLSERVERMSSQLLOGMETAFILE.XEM’, NULL, NULL);<\/p>\nSELECT CAST(EVENT_DATA AS XML) AS EVENTDATA\nINTO #CAPTURE_EVENT_DATA\nFROM SYS.FN_XE_FILE_TARGET_READ_FILE('C:MSSQL13.MSSQLSERVERMSSQLLOGSTRETCHDATABASE_HEALTH*.XEL', 'C:MSSQL13.MSSQLSERVERMSSQLLOGMETAFILE.XEM', NULL, NULL);\n\nSELECT \n\tEVENT_DATA.VALUE('(.\/@NAME)', 'NVARCHAR(50)') AS EVENT_NAME,\n\tEVENT_DATA.VALUE('(.\/@TIMESTAMP)', 'DATETIME') AS EVENT_DATE,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"DATABASE_ID\"]\/VALUE)[1]', 'INT') AS EVENT_DB_ID,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"TABLE_ID\"]\/VALUE)[1]', 'BIGINT') AS EVENT_TABLE_ID,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"IS_SUCCESS\"]\/VALUE)[1]', 'NVARCHAR(5)') AS EVENT_SUCCESS,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"DURATION_MS\"]\/VALUE)[1]', 'INT') AS EVENT_DURATION,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"ROWS\"]\/VALUE)[1]', 'BIGINT') AS EVENT_ROWS,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"ERROR_NUMBER\"]\/VALUE)[1]', 'BIGINT') AS EVENT_ERROR_NUMBER,DIRE\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"SEVERITY\"]\/VALUE)[1]', 'INT') AS EVENT_SEVERITY,\n\tEVENT_DATA.VALUE('(DATA[@NAME=\"MESSAGE\"]\/VALUE)[1]', 'NVARCHAR(1000)') AS EVENT_MESSAGE\nFROM #CAPTURE_EVENT_DATA\n\tCROSS APPLY EVENTDATA.NODES('\/\/EVENT') XED (EVENT_DATA)<\/pre>\n<\/p>\n
The Stretch Database feature is an interesting feature from SQL Server 2016. If you want more information, you should take a look to our other blogs:<\/p>\n
SQL Server 2016 CTP2: Stretch database feature \u2013 Part 1<\/a>
\nSQL Server 2016 CTP2: Stretch database feature \u2013 Part 2<\/a>
\nSQL Server 2016 CTP3.0: Stretch Database enhancements<\/a><\/p>\n<\/p>\n","protected":false},"excerpt":{"rendered":"
On November 30th, I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog. I – Enabling the feature at the instance level First, you […]<\/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":[566,712,213],"type_dbi":[],"class_list":["post-6416","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-sql-server-2016","tag-stretch-database","tag-windows-azure"],"acf":[],"yoast_head":"\n
Manipulate Stretch database feature by script<\/title>\n<meta name=\"description\" content=\"I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Manipulate Stretch database feature by script\" \/>\n<meta property=\"og:description\" content=\"I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-12-04T12:49:31+00:00\" \/>\n<meta name=\"author\" content=\"Nathan Courtine\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Nathan Courtine\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\"},\"author\":{\"name\":\"Nathan Courtine\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"headline\":\"Manipulate Stretch database feature by script\",\"datePublished\":\"2015-12-04T12:49:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\"},\"wordCount\":480,\"commentCount\":0,\"keywords\":[\"SQL Server 2016\",\"Stretch database\",\"Windows Azure\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\",\"name\":\"Manipulate Stretch database feature by script\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-12-04T12:49:31+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\"},\"description\":\"I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Manipulate Stretch database feature by script\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1\",\"name\":\"Nathan Courtine\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g\",\"caption\":\"Nathan Courtine\"},\"description\":\"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Manipulate Stretch database feature by script","description":"I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/","og_locale":"en_US","og_type":"article","og_title":"Manipulate Stretch database feature by script","og_description":"I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.","og_url":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/","og_site_name":"dbi Blog","article_published_time":"2015-12-04T12:49:31+00:00","author":"Nathan Courtine","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Nathan Courtine","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/"},"author":{"name":"Nathan Courtine","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"headline":"Manipulate Stretch database feature by script","datePublished":"2015-12-04T12:49:31+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/"},"wordCount":480,"commentCount":0,"keywords":["SQL Server 2016","Stretch database","Windows Azure"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/","url":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/","name":"Manipulate Stretch database feature by script","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-12-04T12:49:31+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1"},"description":"I presented the Stretch Database feature in \u201cLes Journ\u00e9es SQL Server 2015\u201d in Paris. I explained how to manage by script this new feature in SQL Server 2016 CTP 3.0 I decided to share you my demonstration into a blog.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/manipulate-stretch-database-feature-by-script\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Manipulate Stretch database feature by script"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/38305b5ebdcdb4fb784fa31d760862d1","name":"Nathan Courtine","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0fcc6c91bbc35c976c9d470585e48ae5d500680f1f55de5bbc5f8373b8ebb02c?s=96&d=mm&r=g","caption":"Nathan Courtine"},"description":"Nathan Courtine has more than four years of experience in Microsoft solutions. He is specialized in SQL Server installation, migration, performance analysis, best practices, etc. Moreover, he has a background in Oracle Java and .NET software and web development. Nathan Courtine is Microsoft Certified in Administering SQL Server 2012 Databases. Nathan Courtine holds an Engineer\u2019s Degree in Computer Science from the ENSISA (Ecole Nationale Sup\u00e9rieure d'Ing\u00e9nieurs Sud Alsace) in Mulhouse (F). His branch-related experience covers Public Sector, Automotive, IT, Financial Services \/ Banking, etc.","url":"https:\/\/www.dbi-services.com\/blog\/author\/nathan-courtine\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6416","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=6416"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/6416\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=6416"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=6416"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=6416"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=6416"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}