There are several ways to upload data to SQL Database, including SSIS, BCP, API and several other ways.
For those who work with SQL Server it is natural to use tools that historically belong to the SQL platform, so I wanted to write this article to expose an alternative and to talk about different techniques that apply to different scenarios.
When designing an architecture, you may find yourselves at a crossroads, and often you do not know what to expect once made a decision. How requirements will change over time.
Azure Event hub allows us to decouple who collects the data (the client) from who receives the data, allow you to change kind of receivers or add additional targets.
For this reason, I consider it the entry point to Microsoft Azure.
The following picture shows the data flow that I am going to describe.
Following the check list
- Create Resource Group
- Create Event Hubs Namespace
- Create Event Hub Queue
- Create SQL Server
- Create SQL Database
- Create SQL Table
- Create Stream Analytics Job
- Configure Input
- Configure Output
- Configure Query
- Start Stream Analytics Job
To complete each step, you must first access the Microsoft Azure portal (https://portal.azure.com).
I think it is unnecessary to describe each step in detail, so I will just provide some screen shots.
Create Resource Group
Create Event Hubs Namespace
Create Event Hub
Create SQL Server (logical server)
Connect to SQL Server and create database objects
Before you can connect to SQL Database virtual server, you have to configure Azure firewall to allow the communication.
Use SQL Server Management Studio to create database and tables
For additional information refer to address https://msdn.microsoft.com/en-us/library/dn268335.aspx
EDITION = ‘basic’,
|CREATE TABLE OrdersByCity
entryId INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
city VARCHAR(50) NOT NULL,
orderValue NUMERIC(10,2) NOT NULL,
orderDate DATETIME NOT NULL
Create Stream Analytics Job
Create Stream Analytics Job Input
Create Stream Analytics Job output
Define Stream Analytics Job Query
Refer to the link https://msdn.microsoft.com/en-us/library/azure/dn834998.aspx for additional information about Azure Stream Analytics query language.
Save and start stream analytics job.
Write and run client application
Now you able to run the client application. To create a sample application, follow the steps below.
1 – Install WindowsAzure.ServiceBus NuGet Package
2 – Get Event Hubs Connection String
3 – Write your code
DataTable dataTable = <Put here your code to retrieve data>;
var data = JsonConvert.SerializeObject(dataTable);
var eventHubClient = EventHubClient.CreateFromConnectionString(<Event Hub Conn String>, <Queue Name>);
Refer to the link https://azure.microsoft.com/en-us/documentation/articles/event-hubs-csharp-ephcs-getstarted/ to additional information.