Atul Patel's Blog

Example of the export data file, generated using BCP utility from a local SQL server, upload to Azure blob storage and import to Azure SQL server.

we will do it in the following steps :

Export data from local SQL server
Create Azure blob storage
Create an Azure blob storage container
Upload exported files to blob storage
Import data from uploaded azure blob storage data file

1. Export data from the local SQL server:

Using BCP utility we can export.dat file and file format file.

You can download the latest version of BCP from here (https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017).

^{bcp "SELECT Name, Color, Price, Size, Quantity, Data, Tags FROM Product" queryout product.dat -S (localdb)\MSSQLLocalDB -d MemEligibiltyTest -T}

Here "product.dat" is an output filename.

-S Sql server hostname

-d Database Name

-T Trusted connection.

We need to provide a format for each field. bcp asks for each field like below.

_{Enter prefix-length of field Name 2: 8}
_{Enter field terminator none: none}

_{Enter the file storage type of field Color nvarchar: cr}
_{Enter prefix-length of field Color 2: 8}
_{Enter field terminator none: none}

_{Enter the file storage type of field Price money: cr}
_{Enter prefix-length of field Price 1: 8}
_{Enter field terminator none: none}

_{Enter the file storage type of field Size nvarchar: cr}
_{Enter prefix-length of field Size 2: 8}
_{Enter field terminator none: none}

_{Enter the file storage type of field Quantity int-null: cr}
_{Enter prefix-length of field Quantity 1: 8}
_{Enter field terminator none: none}

_{Enter the file storage type of field Data nvarchar: cr}
_{Enter prefix-length of field Data 2: 8}
_{Enter field terminator none: none}

_{Enter the file storage type of field Tags nvarchar: cr}
_{Enter prefix-length of field Tags 2: 8}
_{Enter field terminator none:}

_{Do you want to save this format information in a file? Y/n y}
_{Host filename bcp.fmt: product.fmt}

_{Starting copy...}

_{4 rows copied.}
_{Network packet size (bytes): 4096}
_{Clock Time (ms.) Total: 15 Average : (266.67 rows per sec.)}

As I have provided prefix -length 8 for each field, generated files keeps 8 characters readable instead of binary character. You can see the rest of the characters are in binary format.

It creates the following two files

This creates two files product.dat and product.fmt. 1st is the data file and another is the format file.

product.dat looks like below.

format file.

2. Create Azure blob storage

we will use Powershell for azure connection.

Azure PowerShell works with PowerShell 5.1 or higher on Windows or PowerShell 6 on any platform. To check your PowerShell version, run the command:

_{$PSVersionTable.PSVersion}

You can install Powershell Azure module using the following command

_{Install-Module -Name Az -AllowClobber}

Connect to Azure with a browser sign in token

_{Connect-AzAccount}

o/p:

^{Account SubscriptionName TenantId Environment}
^{------- ---------------- -------- -----------}
^{Atulxxxxxxx@xxxxxx.com ABCD - Development dxxxxxx3-xxxx-xxxx-xxxx-xxxxxxxxxxxx AzureCloud}

As you logged in to Azure and it shows the default subscription. If you want to execute everything in another subscription then need to select that subscription.

You can get an Azure subscription by subscription id using the following command.

PS C:\Windows\system32> $context = Get-AzSubscription -subscriptionId xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx

You can store output to a variable and use same variable to set Azure context to select particular azure subscription.

command:

PS C:\Windows\system32> set-AzContext $context

o/p:

Name Account SubscriptionName Environment TenantId
---- ------- ---------------- ----------- --------
ABCD (xxxxxxxx-xxxx-xxx... Atul.Patel@xxxx... ABCD AzureCloud xxxxxxxx-xxxx-x...

Define a variable for the resource group.

_{$resourceGroupName="your-resource-group-name"}

$blobStorageAccountName="yourblobstorageaccountname"

You can create a New Azure storage account using the following command. You can store azure account context to a variable.
PS C:\Windows\system32> $storageAccount=New-AzStorageAccount -ResourceGroup $resourceGroupName -Name $blobStorageAccountName -skuname Standard_LRS -Location WestUS2

o/p:

StorageAccountName ResourceGroupName Location SkuName Kind AccessTier CreationTime ProvisioningState
------------------ ----------------- -------- ------- ---- ---------- ------------ -----------
yourblobstorageaccountname your-resource-group-name westus2 StandardLRS Storage 3/5/2019 8:55:10 PM Succeeded

If you already have a azure storage account then you can get it using following command.

PS C:\Windows\system32> $storageAccount=Get-AzStorageAccount -ResourceGroup $resourceGroupName -Name $blobStorageAccountName

PS C:\Windows\system32> $storageAccount

o/p:

StorageAccountName ResourceGroupName Location SkuName Kind AccessTier CreationTime ProvisioningState
------------------ ----------------- -------- ------- ---- ---------- ------------ -----------
yourblobstorageaccountname your-resource-group-name westus2 StandardLRS Storage 3/5/2019 8:55:10 PM Succeeded

3. Create an Azure blob storage container

Storing storage account context into variable

$ctx=$storageAccount.Context

-- Variable for Container name
$containerName="yourcontainername"

Create new container using following command:
new-AzStorageContainer -Name $containerName -Context $ctx

o/p:
Blob End Point: https://yourblobstorageaccountname.blob.core.windows.net/

Name PublicAccess LastModified
---- ------------ ------------
blogstoragename Off 3/5/2019 8:59:56 PM +00:00

context looks like below:

4. Upload exported files to blob storage

You can use the same Azure blob storage container context to upload files.

command:
_{set-AzStorageblobcontent -File "C:\Users\atul.patel\product.fmt" `}
_{-Container $containerName `}
_{-Blob "product.fmt" `}
_{-Context $ctx}

o/p:

Container Uri: https://yourblobstorageaccountname.blob.core.windows.net/blogstoragename

Name BlobType Length ContentType LastModified AccessTier SnapshotTime IsDeleted
---- -------- ------ ----------- ------------ ---------- ------------ ---------
product.fmt BlockBlob 755 application/octet-stream 2019-03-05 21:37:54Z Unknown False

Another file.

set-AzStorageblobcontent -File "C:\Users\atul.patel\product.dat" `
-Container $containerName `
-Blob "product.dat" `
-Context $ctx

o/p:

Container Uri: https://yourblobstorageaccountname.blob.core.windows.net/blogstoragename

Name BlobType Length ContentType LastModified AccessTier SnapshotTime IsDeleted
---- -------- ------ ----------- ------------ ---------- ------------ ---------
product.dat BlockBlob 456 application/octet-stream 2019-03-05 21:38:08Z Unknown False

Select list of upload files on Azure blob storage container.

PS C:\Windows\system32> Get-AzStorageBlob -Container $containerName -Context $ctx | select Name

Name
----
product.dat
product.fmt

5.Import data from uploaded azure blob storage data file

** Import data into Sql server db from azure blob storage

-- Import into DB
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXXxxxxxxxxxxxxXXX'

-- Create credential with Azure Blob SAS

CREATE DATABASE SCOPED CREDENTIAL youcredentialname
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2018-03-28&ss=bfqt&srt=sco&sp=xxxxxxxxxx&se=2019-03-06T23:30:14Z&st=2019-03-06T15:30:14Z&spr=https&sig=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';
-- NOTE: DO NOT PUT FIRST CHARACTER '?'' IN SECRET!!!

-- Create external data source
CREATE EXTERNAL DATA SOURCE blogstoragename
WITH ( TYPE = BLOB_STORAGE,
LOCATION = 'https://yourblobstorageaccountname.blob.core.windows.net/blogstoragename',
CREDENTIAL= youcredentialname --> CREDENTIAL is not required if a blob storage is public!
);

--Create table

CREATE TABLE dbo.Product(
Name nvarchar(50) NOT NULL,
Color nvarchar(15) NULL,
Price money NOT NULL,
Size nvarchar(5) NULL,
Quantity int NULL,
Data nvarchar(4000) NULL,
Tags nvarchar(4000) NULL
)

BULK INSERT Product
FROM 'product.dat'
WITH ( DATA_SOURCE = 'blogstoragename',
FORMATFILE='product.fmt',
FORMATFILE_DATA_SOURCE = 'blogstoragename',
TABLOCK);

--Just select
SELECT Name, Color, Price, Size, Quantity, Data, Tags
FROM OPENROWSET(BULK 'product.dat',
DATA_SOURCE = 'blogstoragename',
FORMATFILE='product.fmt',
FORMATFILE_DATA_SOURCE = 'blogstoragename') as products;