Azure

ByHariharan Rajendran

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

image

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.

image

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

image

Step 4: SQL Server settings will be validated here.

image

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.

image

image

Step 6: Provide your Azure SQL Server credentials.

image

Step 7: Finally , SQL Server will configure stretch for your database and table. To verify, check your database icon.

image

image

 

image

 

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.

image

Log file,

image

ByHariharan Rajendran

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

image

 

Step 5: Access the azure SQL database table. You need to use 4 part query. <linked server name>.<database name>.<schema name>.<table name>

 

image

ByHariharan Rajendran

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

 

ByHariharan Rajendran

Issues in Stretch Database–SQL Server 2016 CTP 3.3

In latest version of SQL Server 2016 CTP 3.3, we have issues in Stretch option. I have listed out the issues, hope this will be sorted out before GA (General Available).

Issue 1:  No option to move only set of rows to Azure Database. The default option is “Migrate All Rows”. Using inline function, we can apply the condition to split the records but available only through T-SQL Scripts.

image

Issue 2: Stretch option is not enabled for table even though I enabled the stretch on database with selected tables.

image

Issue 3: I have enabled the stretch for the database, there is no option to disable in GUI. We have to disable through T-SQL Script

image

Issue 4: Almost all the time, all the data got migrated to Azure database, in local only 0 record.

image

ByHariharan Rajendran

Create Data Warehouse in Azure

Creating a SQL Data Warehouse is very easy in Azure. It is the industry first cloud data warehouse with full fledged SQL capabilities and ability to grow, shrink pause in seconds.

Follow the below steps to create data warehouse

1. Open portal.azure.com

2. Choose New –> Date_Storage –> SQL Data Warehouse

image 

3. Define a name. You can create a new SQL Server or select existing one. Choose the DWU (Data Warehouse Unit) based on your requirement .

4. You can create DW with blank database or with Sample.

image

5. Click “Create”, Azure will create and deploy  DW for you.

image

ByHariharan Rajendran

Database Migration On-Premises to Azure

I have talked about how to import the database on premises SQL Server using .bacpac file which was taken from Azure export (find here).

This is article will speak about, how to migrate the database from on-premises SQL Server to Azure. We to need to perform two steps here.

1. Export the database and store it in Azure Storage Container

2. Import in Azure SQL Server

Exporting the database using data tier application since we need to get .bacpac file format.

Steps to Export and store in Azure Storage,

1. Choose your database which you want to migrate. Right Click on database and choose Tasks –> Export Data-tier Application.

image

image

2. Provide the Azure storage account details and select the container

image

image

image

3. Check the Azure storage for the .bacpac file.

image

 

Once exported the database, your next step is to import into Azure SQL Server

1. Choose Your SQL Server in Azure and Import database

image

2. Map your .bacpac file from your storage container.

image

Once above steps are completed then your database will be deployed on Azure

image

ByHariharan Rajendran

SQL Azure Database Table Size

Identifying the table size of the azure SQL database is not possible through GUI since the properties of the table not providing the size information.

The different between the table properties in on-premises SQL database and Azure SQL database.

Right-Click the table and go to Storage,

On-premises  SQL Server

image

 

Azure SQL database,

image

As you can see, SQL Azure database table doesn’t have storage information of the table.

To identify the table size and number of records, we need to use SQL scripts.

Script 1 : It is using “sys.dm_db_partition_stats” & “sys.objects” system tables.

SELECT   

      o.name AS [table_name],

      sum(p.reserved_page_count) * 8.0 / 1024  AS [size_in_mb],

      p.row_count AS [records]

FROM  

      sys.dm_db_partition_stats AS p,

      sys.objects AS o

WHERE   

      p.object_id = o.object_id

      AND o.is_ms_shipped = 0 

GROUP BY o.name , p.row_count

ORDER BY o.name DESC

 

image

Script 2: Using sp_spaceused stored procedure.

Exec sp_spaceused [Users] –Table Name

image

ByHariharan Rajendran

Connect Azure Storage in SQL Server Management Studio

It is very simple to connect your azure storage in SSMS.

Make sure that you are not using full name of  your storage account.

For example: If you have storage account named “Test”. Usually, the full name of the storage account will be “https://Test.blob.core.windows.net”.

To connect your storage account in SSMS, use only the name of your account (here it is “Test”). Then provide the storage key.

To get the storage key, follow the below steps.

Visit Management portal and choose your storage account.

image

Select the Manage Access Keys and copy your key.

image

 

Use your storage account name and key in SSMS.

image

image

image