Stretch database – Modifying the filter function

-> 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,

Blog6_1.PNG

-> 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.

Blog6_2.PNG

-> 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,

Blog6_3.PNG

-> 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.

Advertisements

Disabling Stretch Database

-> 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

Blog5_1

-> 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.

Blog5_2.PNG

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

Blog5_3.PNG

Blog5_4.PNG

-> 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

Blog5_5.PNG

-> 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

Blog5_6.PNG

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

Blog5_7.PNG

Blog5_8

Blog5_9

Blog5_10

-> 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

Blog5_11

-> 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

Blog5_13

Blog5_12

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

Blog5_14.PNG

Blog5_15

Blog5_16

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.

Adding a table to the stretch database

-> 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

Blog4_1

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

Blog4_2

Blog4_3

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

Blog4_4

Blog4_5.PNG

Blog4_6.PNG

Blog4_7

Blog4_8.PNG

-> Monitoring the status for table3,

Blog4_9.PNG

Blog4_10.PNG

-> 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

Blog4_11.PNG

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.

Stretch Database in SQL Server 2016

We can use the stretch database to place our less-frequently accessed data securely in Microsoft Azure.

Benefits

-> Cost effective – The less-frequently accessed data can be stored on cost efficient and secure Microsoft Azure than storing them on costly On-premise storage.

-> No changes in queries – The queries that access a stretch database can be same and no additional logic is required to incorporate the data moved to Microsoft Azure.

-> Database Maintenance – Database maintenance will be comparably faster as we will touch only the frequently accessed data.

-> Faster queries – Queries touching frequently accessed database will be comparably faster with fewer IO and increased performance.

Blog1_Azure_visio

-> Let’s create the required database and objects for this demo,

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

-> Populating table Table1 with some data,

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

-> Looking at the data,

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

Blog3_1

-> The query completes in 60 MS.

Blog3_2

-> Select the database that will be part of stretch database,

Blog3_3.png

Blog3_4.png

-> Select the table and click on “Entire Table” under “Migrate”.

Blog3_5.png

-> You can move all the data to cloud or a certain number of rows. I will select “Choose Rows” in the below screen as I want the latest data in On-Premise database and just the old data’s be moved to cloud.

Blog3_6.png

Blog3_7.png

-> I already have a Microsoft azure instance created. Hence I will select “Existing server” and point the instance from the drop down box. You can create one yourself by using the article https://jbswiki.com/2017/05/31/creating-a-sql-database-in-microsoft-azure-portal/ or you can select “Create new server” and proceed.

Blog3_8.png

-> Provide the password for the database master key.

Blog3_9

Blog3_10

Blog3_11

Blog3_12

-> This is what I see in the SSMS after setting up the stretch database.

Blog3_13.png

-> Executing the below query and checking the execution plan. The query actually took 19 seconds.

Blog3_14.png

-> It is clear from the screenshot that we are spending most of our time on the remote query as we are querying close to 40000 rows from Azure database.

Blog3_15.png

-> The performance is not that great, but we create stretch database just to move data to cloud that are not frequently accessed.

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.

Restore on-Premise SQL server database to Azure SQL Database

-> We need to restore the database from on-Premise to Azure SQL database.

-> On-Premise database JB_test has two tables and let’s check the row count,

Blog2_1

1) Moving only the schema from on-Premise SQL database to Azure SQL database,

-> Right click on-Premise database -> Tasks -> “Extract Data-tier Application…” as shown in the screenshot below,

Blog2_2

-> Click on next,

Blog2_3

-> Provide the location where the file should be placed,

Blog2_4

Blog2_5

Blog2_6

-> Once the dacpac file is created. Get onto the SQL azure database, right click “Databases” and select “Deploy Data-tier Application”.

Blog2_7

Blog2_8

-> Specify the dacpac file,

Blog2_9

Blog2_10

Blog2_11

Blog2_12

-> It is complete and we see the database in Azure,

Blog2_13

2) Moving schema and data from on-Premise SQL database to Azure SQL database,

-> Right click on-Premise database -> Tasks -> “Export Data-tier Application…” as shown in the screenshot below,

Blog2_14

-> Click on next,

Blog2_15

-> Provide the location where the file should be placed,

Blog2_16

Blog2_17

Blog2_18

Blog2_19

-> Once the bcpac file is created. Get onto the SQL azure database, right click “Databases” and select “Import Data-tier Application”.

Blog2_20

Blog2_21

-> Specify the bacpac file,

Blog2_22

Blog2_23

-> When I clicked “Next” I got the below message,

Blog2_24

TITLE: Microsoft SQL Server Management Studio
——————————
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
—————————-
ADDITIONAL INFORMATION:
The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts. (Microsoft SQL Server, Error: 0)
——————————
Cannot open database “JB_test” requested by the login. The login failed.
Login failed for user ‘JBAdmin’. (Microsoft SQL Server, Error: 4060)

-> I don’t have the database “JB_test” on the instance, so I was not sure why I was getting this error. Later I understood the isssue, It seems like I have created a database JB_Test before 1 hour with the same name JB_Test and dropped it and that is causing the isssue. I spoke to one of the Azure expert and he pointed out that even though we delete the database, the database will be there hidden for atleast 1 day. Instead of waiting for 1 day, I used a different name and started the import.

Blog2_25

Blog2_26

Blog2_27

Blog2_28

Blog2_29

-> It is complete and we see the database JB_TestWithData as an Azure SQL Database,

Blog2_30

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.

Creating a SQL database in Microsoft Azure portal

1) Open http://portal.azure.com/.

2) Start a free trial there. Put your email and password and get in. You will see
the below screen.

3) Click on “All resources”

Azure_Blog1_1

4) Click on “Add”,

Azure_Blog1_2

5) Click on “Databases” and then “SQL Database”.

Azure_Blog1_3

6) Click on “Sign Up for a new subscription” and selected as shown in the below screenshot,

Azure_Blog1_4

7) Provide details about you and get the Azure Subscription.

8) Once you have the subscription in place, Perform steps 3 to 5 again.

9) Below screen comes up once you click on “+Add”, “Databases” and then “SQL Database”. Click on “Server” and provide all details related to the server and click “Select”.

Azure_Blog1_5

10) You will see a screen like the below. Click on “Server” and in this case I have selected “Basic” instead of default “Standard” for cost reasons and Click “Apply”.

Azure_Blog1_6

11) Click on “Create” now. Wait for the deployment to complete,

Azure_Blog1_7

12) Once the deployment completes. Click on “All Resources” and you will see the “SQL serve” and the “SQL Database” as shown in the screenshot below,

Azure_Blog1_8

13) Click on the “SQL Database” and you will see the “Server name” and “connection strings”.

Azure_Blog1_9

14) Click on “Set server firewall” and add the IP addresses of machines that will be accessing the SQL server. Incase there are ipaddresses from 1.2.3.56 to 1.2.3.678, you can basically put that in START IP and END IP. Click on Save.

Azure_Blog1_10

Azure_Blog1_11

15) Open SQL server management studio and type the server name. Select “SQL server Authentication” and use the login that you created in step 9. In my case I created login “JBAdmin”.

16) Below is what I see,

Azure_Blog1_12

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.

Why Bookmark Lookup is BAD?

-> As per article “https://technet.microsoft.com/en-us/library/ms180920(v=sql.105).aspx“. The Bookmark Lookup operator uses a bookmark (row ID or clustering key) to look up the corresponding row in the table or clustered index. The Argument column contains the bookmark label used to look up the row in the table or clustered index. The Argument column also contains the name of the table or clustered index in which the row is looked up. If the WITH PREFETCH clause appears in the Argument column, the query processor has determined that it is optimal to use asynchronous prefetching (read-ahead) when looking up bookmarks in the table or clustered index.

-> Create below database and objects,

create database JB_BookMark
go
use JB_BookMark
go
create table Table1(
Col1 int primary key identity(1,1),
Col2 int,
Col3 int,
Col4 varchar(100))
CREATE NONCLUSTERED INDEX IX_Table1 ON dbo.Table1(Col2)

-> Populate the table using below tsql query,

set nocount on
declare @i int=1
declare @j int=65
while (@i<=100000)
begin
if(@j=91)
begin
set @j=65
end
insert into Table1 values(@i,rand(@i*9)*1000,replicate(CHAR(@j),5))
set @i=@i+1
set @j=@j+1
end

-> Rebuild the indexes to remove any fragmentation,

USE [JB_BookMark]
GO
ALTER INDEX [PK__Table1__A259EE54D34C8103] ON [dbo].[Table1] REBUILD
GO
USE [JB_BookMark]
GO
ALTER INDEX [IX_Table1] ON [dbo].[Table1] REBUILD
GO

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=1
set statistics time off
set statistics IO off

Blog20_3.png

Blog20_4.PNG

-> The query cost is at 0.0065704. The operator “Index seek” returns 1 row(s).  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is just 1 time. Logical reads done is around 4. The query overall took 49 MS to complete.

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=10
set statistics time off
set statistics IO off

Blog20_1.png

Blog20_2.PNG

-> The query cost is at 0.0327403. The operator “Index seek” returns 10 rows.  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is 10 times. Logical reads done is around 22. The query overall took 62 MS to complete.

-> Execute the below query with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=100
set statistics time off
set statistics IO off

Blog20_5.png

Blog20_6.PNG

-> The query cost is at 0.291569. The operator “Index seek” returns 100 rows.  The operator “Key Lookup” scans through the cluster index for each row that comes from the “Index seek” operator. In this case it is 100 times. Logical reads done is around 217. The query overall took 370 MS to complete.

-> It seems like whenever the execution plan for a query has key lookup, its performance degrades with the number of rows that it has to process. In our case, below is the final test result,

Blog20_7

-> We can create below index to avoid key lookup,

USE [JB_BookMark]
GO
CREATE NONCLUSTERED INDEX [IX_Table1_1]
ON [dbo].[Table1] ([Col2])
INCLUDE ([Col1],[Col3],[Col4])
GO

-> Execute the below query after the above index is created with actual execution plan enabled,

set statistics time on
set statistics IO on
select col1,col2,col3,col4 from table1
where col2 <=100
set statistics time off
set statistics IO off

Blog20_8.png

Blog20_9

-> Once we remove the key lookup, the query completes in 1 MS with fewer cost and logical reads.

-> So this means, we can create covering index for all queries that has key lookup? Creating covering index will remove the key lookup and the performance of the query will definitely increase, but it will also increase the space used by the index, database maintenance time, backup size. So it is wise to test things before taking a decision.

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.