Stretch in SQL Server RC0
SQL Server Release Candidate 0 version has been released. We have been discussed about stretch database in CTP 3.3. There are many improvements in RC0. This article is about, how to enable the stretch in RC0.
Go through Stretch in SQL Server 2016 CTP 3.3.
Steps to enable the Stretch in RC0 Version,
Step 1: Right click on your database and go to Tasks->Stretch->Enable
Step 2: Choose the tables you want to stretch.
Step 3: Provide your Azure subscription to configure SQL Server and database for stretch.
Step 4: Set up master key for your database.
Step 5: Provide your IP, in case if you have configured.
Step 6: Configuration process started to enable stretch for database and table.
As you can see, there are some difference in steps to configure when compare with CTP 3.3 version. There is no changes on Monitoring the stretch.
Enable Stretch for Tables
Previously, we need to select Reconfigure option to add new tables to stretch but that “Reconfigure” option has been removed. Now, we have “Enable”, “Disable” & “Monitor”.
To add new tables to stretch, follow the below steps.
Choose the new table to add into stretch. Right click –> Stretch –>Enable
Disable Stretch
To Disable the stretch for the database, we need to disable the stretch for tables first. We don’t have option to disable in one short if multiple tables are in stretch. Need to manually disable all the tables from stretch.
If we try to disable stretch for database without disabling tables then below message will be appeared.
There are two options in disable on table,
1. Bring data back from Azure
2. Leave data in Azure.
Once stretch is enabled for a table, data migration will be initiated from local to azure through batch process. If we decided to disable the stretch then make sure whether we want to bring data back to local or keep them on azure it self. Based on that choose any of the above option.
Note: The table in Azure database will not be deleted by process, you need to delete it by going to azure management portal.
If you are selected “Bring data back from Azure” then below message will be prompted.
Once disable the stretch for all your tables, then disable the stretch on your database.
Issues in R Package in SQL Server 2016
I have got a below error when I try to use one of the R package “ggplot2”.
It says that there is no package called ggplot2 even though I installed the ggplot2 package in R.
Message from R when I install ggplot2 package,
The above message clearly explaining that the package is not installed on defined path, it has been downloaded on temporary directory. It means there was no proper installation happened in R. This was the reason for error in SQL Server.
Follow the below steps to sort out the issue,
Use “.libPaths” to define the library path before install a package in R.
Command is,
> .libPaths (.libPaths()[2:3])
> install.packages(“ggplot2”)
Package will be installed like below,
Now run the same SQL Script with R, this time it will be executed without an issue.
Data Driven & SQL Server 2016
Following today’s Data Driven live streaming from New York, Satya Nadella have opened the speech by providing insights on what CEOs and organization owners should see with Data being the key factor driving the business.
Also SQL Server 2016 RC (Release Candidate) is now available here and no more CTPs from Microsoft download site. Be the first to download the latest Release Candidate and try the brand new features of SQL Server.
Monitor Stretch in SQL Server 2016
Go through my previous article to enable the stretch for your database and table. This article is about how to monitor the stretch. You can monitor the stretch through GUI or DMVs.
We have below DMVs available to monitor the status of the stretch.
select * from sys.remote_data_archive_databases
The above script will provide the details about what is the database name in Azure used for stretch.
select * from sys.remote_data_archive_tables
This will tell you, what is the table name in azure used for stretch.
The above result has column called “filter_predicate”. It means, if apply any predicate function while configure the stretch then you can see that predicate function here. unfortunately, we don’t have option to apply the predicate function in GUI method. It is available when we enable stretch through T-SQL Script.
select * from sys.dm_db_rda_migration_status
This script will tell you about data migration. Data will start migrate from the table once stretch is enabled. Migration will start from on-premises SQL Server to Azure. It is using batch process so it will migrate maximum of 4999 records in a batch. The batch id will be available in Azure table.
To see the live migration, we can use “SP_SPACEUSED”. By seeing the rows and size we can identify the data migration.
You can monitor the stretch using GUI,
Enable Stretch Database in SQL Server 2016
We can enable the stretch feature either through GUI or T-SQL Script. This article will talk about GUI method.
To enable the stretch for your database,
1. You should have Azure subscription
2. If you are using existing SQL Server in Azure then firewall should be enabled.
Steps to enable the stretch,
Step 1: Choose your database which you need to enable stretch and right click –> Tasks –>Stretch –>Enable
Step 2: Create a database credentials, you need to provide the master key. you should use this key whenever you disable or enable the stretch for this database.
Step 3: Select the table which you want to do stretch, you can select multiple tables. This screen will give the information about your table and records in that table and also it will tell you that whether stretch is already enabled or not (Stretched = False).
Step 4: SQL Server settings will be validated here.
Step 5: You need to sign in using your credentials where azure subscription is active. Once sign in, then you need to select Azure SQL Server. You can create a new server or select from existing server.
Step 6: Provide your Azure SQL Server credentials.
Step 7: Finally , SQL Server will configure stretch for your database and table. To verify, check your database icon.
If you get an error while configuring stretch like below, then check the log file. Mostly you will get an error when you are not configured the firewall in Azure SQL Server.
Log file,
[Step by Step] Azure SQL Database Linked Server
As we are familiar with the known concept “Linked Server”. This article is about, how to create a linked server for Azure SQL database.
Make sure the firewall is enabled in Azure SQL Server.
Step 1: Go to SQL Server Management Studio
Step 2: Run the below scripts. This script using a stored procedure “SP_addlinkedserver”.
EXEC sp_addlinkedserver
@server=‘Azure’, –Provide Linked Server Name
@srvproduct=”,
@provider=‘sqlncli’,
@datasrc=‘tcp:<servername>.database.windows.net,1433’, –provide Azure SQl Server name
@location=”,
@provstr=”,
@catalog=‘SQLDB1’ –Provide azure database name
Step 3: Run the below script to provide the SQL Server login for the above created linked server.
exec sp_addlinkedsrvlogin ‘Azure’, ‘FALSE’, NULL, ‘<azure sql server login>’, ‘<sql server password>’;
Step 4: Verify the created linked server.
Step 5: Access the azure SQL database table. You need to use 4 part query. <linked server name>.<database name>.<schema name>.<table name>
In Memory Table in SQL Server
In a normal database, we can not create a in-memory tables. To create a in-memory table, we need to follow the below rules.
1. Create or Alter database to add a memory optimized data file group
2. Add a file into the file group.
Steps to alter your database to support in-memory tables. Before that let us try to create in-memory table in normal database.
Step 1: Alter the database to add memory optimized file group.
ALTER DATABASE [SampleDB] ADD FILEGROUP Analytics_Mode CONTAINS MEMORY_OPTIMIZED_DATA
Step 2: Add a file into the file group. Try to provide the path where SQL Server is installed.
ALTER DATABASE [SampleDB] ADD FILE (name=‘Analytics_Mode’,filename=‘E:\Local_Install_Applications\SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\InMemory1’) TO FILEGROUP Analytics_Mode
In the above script “InMemeory1” is a directory, don’t create this directory manually. The above script will create automatically and will populate the files.
Step 3: Now run the in-memory table script.
CREATE TABLE Account (
ID int NOT NULL PRIMARY KEY NONCLUSTERED,
AccountDescription nvarchar (50),
AccountType nvarchar(50),
Unit int
)
WITH (MEMORY_OPTIMIZED = ON );
GO
Azure Elastic Database Query
We have a new feature called “Elastic Database Query” in Azure SQL Database. By using this option, we can able to perform cross database queries.
It is supporting in Vertical & Horizontal Partitioning. Cross database queries using external data source and table as same as PolyBase in SQL Server 2016.
Scenario:
Consider you have two different databases. These two databases are residing on two different SQL Server in Azure.
SQL Server 1 has database called SQLDB1.
SQL Server 2 has database called SQLDB2.
Inside SQLDB1 database, we have a tabled called “Orders” with few records and SQLDB2 also contain a table called “Customers”.
To access these tables, we need to create a external source and table in any one of the database.
Step by Step Explanation:
Step 1: Create two SQL Server in Azure on same region or different region.
Step 2: Create a database “SQLDB1” in Server1.
Step 3: Create a database “SQLDB2” in Server2.
Step 4: Create a table “Orders” in SQLDB1 (Server1)
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL,
[CustomerID] [int] NOT NULL
)
INSERT INTO [dbo].[Orders] ([OrderID], [CustomerID]) VALUES (123, 1)
INSERT INTO [dbo].[Orders] ([OrderID], [CustomerID]) VALUES (149, 2)
INSERT INTO [dbo].[Orders] ([OrderID], [CustomerID]) VALUES (857, 2)
INSERT INTO [dbo].[Orders] ([OrderID], [CustomerID]) VALUES (321, 1)
INSERT INTO [dbo].[Orders] ([OrderID], [CustomerID]) VALUES (564, 8)
Step 5: Create a table “Customers” in SQLDB2 (Server2)
CREATE TABLE [dbo].[Customers](
[CustomerID] [int] NOT NULL,
[CustomerName] [varchar](50) NULL,
[Company] [varchar](50) NULL
CONSTRAINT [CustID] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
)
INSERT INTO [dbo].[Customers] ([CustomerID], [CustomerName], [Company]) VALUES (1, ‘Hari’, ‘ABC’)
INSERT INTO [dbo].[Customers] ([CustomerID], [CustomerName], [Company]) VALUES (2, ‘Raj’, ‘XYZ’)
INSERT INTO [dbo].[Customers] ([CustomerID], [CustomerName], [Company]) VALUES (3, ‘John’, ‘MNO’)
Step 6: Go to Server 1 and create a New Query window and then create database master key and scoped credentials. User name and Password should be Server2 credentials.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<password>’;
CREATE DATABASE SCOPED CREDENTIAL ElasticDBQueryCred
WITH IDENTITY = ‘<server2 username>’,
SECRET = ‘<password>’;
Step 7: Create a external source in same window (Server 1).
CREATE EXTERNAL DATA SOURCE MyElasticDBQueryDataSrc WITH
(TYPE = RDBMS,
LOCATION = ‘server2.database.windows.net’,
DATABASE_NAME = ‘SQLDB2’,
CREDENTIAL = ElasticDBQueryCred,
) ;
Step 8: Following with External source, create a external table, the definition of the table should be same as customers table in SQLDB2 (Server 2). The table name also should be same.
CREATE EXTERNAL TABLE [dbo].[Customers]
( [CustomerID] [int] NOT NULL,
[CustomerName] [varchar](50) NOT NULL,
[Company] [varchar](50) NOT NULL)
WITH
( DATA_SOURCE = MyElasticDBQueryDataSrc)
Step 9: Access the tables using below script from Server 1.SQDB1.
SELECT Orders.CustomerID, Orders.OrderId, Customers.CustomerName, Customers.Company
FROM Orders
INNER JOIN Customers
ON Customers.CustomerID = Orders.CustomerID