Create a Azure Data Factory Using PowerShell

It is easy to create a Azure Data Factory using PowerShell.  However, you might fail to create one if you follow Microsoft’s Example without upgrading your PowerShell.

Prerequisites: you need to have a valid Azure Subscription and a resource group.

Step 1: Login Azure Account. run the following command. It will pop up a windows. You simply enter your username and password.

Login-AzureRmAccount

Step 2: Find your subscription. The following command will show you all subscriptions.

Get-AzureRmSubscription

Step 3: Choose your subscription. If you have multiple subscriptions, you need to choose which one you want to use. You need the subscription Id which you can find from the result of step 2.

Select-AzureRmSubscription -SubscriptionID “you subscription Id”

Step 4: Define resource group name and data factory name parameters. You need to know which resource group do you want to put your data factory. If you don’t remember the resource group name, you can get all your resource group names by running Get-AzureRmResourceGroup command. The data factory name must start with a letter or a number, and can contain only letters, numbers, and the dash (-) character. That means you can not use underscore which usually can be used for naming. Data factory name should be at least 3 characters long and no more than 63 characters long. You can check here for the data factory naming convention.

$resourceGroupName = “Your existing resource group name”;

$dataFactoryName = “Your Data Factory Name”;

Step 5: Create new data factory. In my case, you should use New-AzureRmDataFactory instead of Set-AzureRmDataFactoryV2 command.

New-AzureRmDataFactory -ResourceGroupName $resourceGroupName -Location “East US” -Name $dataFactoryName;

If everything runs well, you should see a similar screen shot shown as below.

CreatDataFactory

Advertisements

Generate Table-Creation Script Using FOR XML Clause with PATH Mode

There are multiple ways to automate the generation of table-creation script based on existing tables in SQL server. The common way is to use the ‘generate script’ function to automate this process. This GUI method is relatively easy but it often produces a lot extra command which may not fulfill your expectation. Other methods include recursive CTE and cursor. In this blog, I am going to show you how to automate this process with FOR XML clause with path mode. If you are not familiar with this method, here is a nice blog you can check.

To create a table, we need to know the table name, schema name, column name, column data type. Fortunately, we can get all this information from two views: INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLS.

Furthermore, we need to know the length for a string data type like varchar and the precision and scale number for a decimal data type. In contrast,  we don’t need to define the length for a integer data type and a datetime data type. This information is also available in INFORMATION_SCHEMA.COLUMNS. We can use a CASE clause to decide whether we need to add those information. Similarly, we can use a CASE clause for nullable or non-nullable columns. Finally, we need to concatenate all columns information together with the FOR XML clause.

For the detailed query, please check the attached screenshot. You can also find a similar snippet from my githubTable_Creation

This snippet can be useful when you need to write table-creation script with limited length. For example, it can be used for modifying a Azure Data Factory pipeline JSON file.

 

 

Connect an on-Premises SQL Server Database with an Azure SQL Database Using a Linked Server

Azure SQL database, also known as a platform as a service (Paas) or a database as a service (DBaas), is designed for software-as-a-service application development. Although you can move all your data into the Azure SQL database, there are times that one wants to connect a Azure SQL database to an on-Premises SQL server database.

Linked servers provide a powerful tool to access data from another instance of SQL server database. In this blog, I want to show you that you can also use a linked server to access data reside in Azure SQL database from your on-premises SQL server database.

Create a Linked Server

There are two steps to establish a connection with Azure SQL database.

Step 1. Create a linked server.  We can create a linked server using sp_addlinkedserver stored procedure.  To execute this stored procedure, you essentially need to give four parameters.

  • LinkedServerName. You can give any name as long as it is legal and unique.
  • AzureServerName. You can easily find it on your azure portal. An Azure server name has four parts ending with ‘.database.windows.net’.
  • PortNumber, usually is 1433.
  • AzureDatabaseName

EXEC sp_addlinkedserver
@server=N’LinkedServerName’,
@srvproduct=N’Azure SQL Db’,
@provider=N’SQLNCLI’,
@datasrc=N’AzureServerName, PortNumber’,
@catalog=’AzureDatabaseName’;

Step 2. Set up login mapping. We can use sp_addlinkedsrvlogin stored procedure. To execute this stored procedure, you need to give three parameters.

  • LinkedServerName. The one you just created in step 1
  • UserName.
  • Password.

EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘LinkedServerName’,
@useself = ‘FALSE’,
@locallogin = NULL,
@rmtuser = ‘UserName’,
@rmtpassword = ‘Password’

Test a Linked Server

After executing the above two steps, you can test your linked server by executing sp_testlinkedserver stored procedure. This stored procedure require one parameter which is the LinkedServerName you just created. If success, you can see “command(s) completed successfully” message in the SQL Server Management Studio.

EXEC sp_testlinkedserver LinkedServerName;

Use a Linked Server 

Example 1. Read data

SELECT TOP 10 *
FROM [LinkedServerName].[AzureDatabaseName].[dbo].[AzureTableName]

Example 2. Update data

UPDATE top(5000) A
SET A.Salary = A.salary * 1.1
FROM [LinkedServerName].[AzureDatabaseName].[dbo].[AzureTableName] A

Summary

In this blog, I have shown you how to create a linked server which connects to an Azure SQL database. Basically, we need to execute two stored procedures: sp_addlinkedserver and sp_addlinkedsrvlogin. We can also test the connection with another stored procedure called sp_testlinkedserver. I have also included two simple examples which show you how to use a linked server to read and update an Azure SQL database table. For more information, please check Microsoft Azure Documents