-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. If you refer the article I would have named the function “StretchByYear” in the wizard page “Select rowst  stretch”.

-> I will open the function StretchByYear to check what it contains,

-> The modify script for the function is as below,

USE [JB_StretchDB]
GO
/****** Object: UserDefinedFunction [dbo].[StretchByYear] Script Date: 17/06/2017 7:59:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[StretchByYear] (@Year Int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN SELECT 1 AS is_eligible
WHERE @Year <= CONVERT(Int, N’1982′)

-> The modify function updates us that all rows less than or equal to 1982 will be moved to the cloud.

-> Checking the stretch monitor, We see 20000 rows on On-Premise and 30000 rows on cloud.

-> The current filter function moves all data less than equal to 1982 to the cloud. Now lets consider that the  requirement changes to move all data less than equal to 1983 should be moved to cloud. In order to achieve this, I will create a new function as below,

USE [JB_StretchDB]
GO

CREATE FUNCTION [dbo].[StretchByYear_1983] (@Year Int)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN SELECT 1 AS is_eligible
WHERE @Year <= CONVERT(Int, N’1983′)
GO

-> One thing to remember is, the new filter function has to be less restrictive than the previous function.

-> Enabling the filter function for table Table1,

ALTER TABLE Table1 SET ( REMOTE_DATA_ARCHIVE = ON (
FILTER_PREDICATE = dbo.StretchByYear_1983(Year),
MIGRATION_STATE = OUTBOUND
) )

-> Executed below coommand to insert an row,

insert into Table1 values(replicate(‘A’,25),1980)

-> Now checking the Stretch monitor,

-> It is clear from the screenshot above that the new filter function has taken effect.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. In this article, I will be disabling stretch database on the SQL server instance.

-> Executing the below query to see what all tables are part of stretch database,

select object_name (object_id),* from sys.remote_data_archive_tables

-> We must first disable the stretch for each individual tables before disabling it for the database.

-> Disabling it for Table1 by migrating the data back from Azure database to On-Premise database. Please note that migrating the data from azure will involve extra cost.

-> Make sure we have sufficient space on the data drive to bring back the data and then click on “Yes”.

-> Thats quick. Usually it takes time for migrating, if the data involved is too huge.

-> Executing the below query to see what all tables are part of stretch database. We dont see Table1 anymore,

select object_name (object_id),* from sys.remote_data_archive_tables

-> Checking the execution plan after executing the below query. The “Remote Query” is no longer present and the “actual number of rows” now is 50000.

select sname, year, count(*)
from Table1
group by sname,year

-> Disabling stretch feature for Table2 by selecting option “Leave Data in Azure”.

-> Executing the below query to see what all tables are part of stretch database. We dont see any tables now,

select object_name (object_id),* from sys.remote_data_archive_tables

-> Checking the execution plan after executing the below query. The “Remote Query” is no longer present and the “actual number of rows” now is 10000. It is clear that all the rows in azure is not migrated back to On-Premise database.

select sname, year, count(*)
from Table2
group by sname,year

-> Now that the tables are done, I will disable the stretch feature for the database,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

-> Please refer “https://jbswiki.com/2017/06/15/stretch-database-in-sql-server-2016/” if you want to setup Stretch database from scratch. In this article, I will be adding table Table3 to the existing stretch database.

-> Let’s create the required objects for this demo on the already configured Stretch database JB_StretchDB,

use JB_StretchDB
go
create table Table3(
sno int primary key identity (1,1),
sname varchar(255),
Year int)

-> Populating tables Table3 with some data,

set nocount on
insert into Table3 values(replicate(‘A’,25),1980)
go 10000
insert into Table3 values(replicate(‘B’,25),1981)
go 10000
insert into Table3 values(replicate(‘C’,25),1982)
go 10000
insert into Table3 values(replicate(‘D’,25),1983)
go 10000
insert into Table3 values(replicate(‘E’,25),1984)
go 10000

-> Looking at the data,

select sname, year, count(*)
from Table3
group by sname,year

-> Select the table that will be added to stretch database and perform as shown in the screenshot,

-> Click on “Entire Table” under “Migrate”.

-> Monitoring the status for table3,

-> Executing the below query and looking at the execution plan. The execution plan shows us that we are querying 10000 rows from the On-Premise database and 40000 rows from the Azure database.

select sname, year, count(*)
from Table3
group by sname,year

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.