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”.
@server=‘Azure’, –Provide Linked Server Name
@datasrc=‘tcp:<servername>.database.windows.net,1433’, –provide Azure SQl Server name
@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>
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.
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)
( DATA_SOURCE = MyElasticDBQueryDataSrc)
Step 9: Access the tables using below script from Server 1.SQDB1.
SELECT Orders.CustomerID, Orders.OrderId, Customers.CustomerName, Customers.Company
INNER JOIN Customers
ON Customers.CustomerID = Orders.CustomerID
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.
Issue 2: Stretch option is not enabled for table even though I enabled the stretch on database with selected tables.
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
Issue 4: Almost all the time, all the data got migrated to Azure database, in local only 0 record.
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
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.
5. Click “Create”, Azure will create and deploy DW for you.
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.
2. Provide the Azure storage account details and select the container
3. Check the Azure storage for the .bacpac file.
Once exported the database, your next step is to import into Azure SQL Server
1. Choose Your SQL Server in Azure and Import database
2. Map your .bacpac file from your storage container.
Once above steps are completed then your database will be deployed on Azure
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
Azure SQL database,
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.
o.name AS [table_name],
sum(p.reserved_page_count) * 8.0 / 1024 AS [size_in_mb],
p.row_count AS [records]
sys.dm_db_partition_stats AS p,
sys.objects AS o
p.object_id = o.object_id
AND o.is_ms_shipped = 0
GROUP BY o.name , p.row_count
ORDER BY o.name DESC
Script 2: Using sp_spaceused stored procedure.
Exec sp_spaceused [Users] –Table Name
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.
Select the Manage Access Keys and copy your key.
Use your storage account name and key in SSMS.
We discussed before about how to export the database from azure and stored the bacpac file in azure storage container.
This article about how to create a database in on-premises SQL Server using bacpac file.
Step 1: Download the bacpac file from azure storage container
Step 2: Use data tier application to import the bacpac file in SQL Server
Step 3: Follow the wizard instructions. You can able to create database using your azure storage container as well.
Now, you have successfully created a database using bacpac file.