Since when we started building Blendo, we wanted to make it flexible enough to send your data anywhere. With Blendo you can send data to Amazon Redshift, Google BigQuery or send data to PostgreSQL. Now we make it easy to load data to Microsoft SQL Database too.
This post is about:
- How to setup a Microsoft SQL Database in Azure. If you have one already skip to Part 2
- How to load data to Microsoft SQL Database in Azure.
A small intro first.
What is Microsoft Azure?
Microsoft Azure is a growing collection of integrated cloud services, analytics, computing, database, mobile, networking, storage, and web—for moving faster, achieving more, and saving money.
In order to connect to the Microsoft Azure portal, you must have a subscription. If you do not have a Microsoft account you will have to create one. In order to create a new account at Microsoft Azure go here.
What is Windows Azure SQL database?
SQL Azure is Microsoft’s cloud database service that lets you easily setup an SQL database without messing around with database management. Based on SQL Server database technology and built on Microsoft’s Windows Azure cloud computing platform, SQL Azure enables organizations to store relational data in the cloud and quickly scale the size of their databases up or down as business needs change. Their plans begin from 5$/month at the Basic – Single Database model.
How to setup an SQL Database in Azure?
1> Login to Azure Portal.
2> We will first create an Azure SQL Database logical server.
3> Click on New, type SQL Database and then click SQL Database (new logical server)
4> On the new window, click SQL Database (new logical server).
5> In the new slider window that opens, click Create.
6> A new slider opens to type the credentials we want:
- Server name: Type a server name (ie I used: gandalfxero) NOTE: In order to access the server the default address is .database.windows.net, in our case that will be: “gandalfxero.database.windows.net”
- Server admin login: type a username you want (ie I used: testusr)
- Password: Type a secure password.
- Choose your Subscription: Leave the default or you may have more choices only if you have multiple subscriptions.
- Resource group: Create a new (ie I used the name gandalfxero) or use one you have already.
- Location: Leave the default or choose the region where you want your server to deploy.
7> Click Create. In the notification area (on the top right side), you can see that deployment has started and finished.
8> Now it is time to create a new Azure SQL database. Let’s do it a bit differently.
9> Click on New, next to the plus sign on your top left. At the new slider click Data + Storage. On the next slider click on SQL Database (new database)
10> In the new window:
- Type a Database Name: I used the name Myxerodb
- Choose your subscription: Leave the default. It will have more choices only if have multiple subscriptions.
- Choose resource group: Leave the default or use an existing.
- Select source: Choose “Sample” (you may also get a blank database or a database backup).
- Select Sample: Leave the default
- Server: Click and select the server we created before (ie gandalfxero).
- Server admin login: type a username you want (I used: testusr)
- Password: Type a secure password.
- Pricing tier: Check if it matches with what you use. We will use the default value S0 for our example.
- Collation: Ignore it for our example
- Click on Create
11> Then wait some minutes for the setup. When ready you will see this on the top right side.
12> Click SQL databases on the right sidebar. This will give you a list of your SQL databases. There you will see our new DB named “Myxerod”.
13> We will need to send data to the newly created database. In order to access it we will need to add some firewall rules that allow connections from an individual IP address.
Want to save hours on your data management tasks? Blendo can help!
How do I add a firewall rule for accessing an SQL Server in Windows Azure?
1> At the Azure Portal, click on All Resources on the left sidebar. In the new window, start typing the name of the server we created before (ie gandalfxero). From the filtered results click the server named “gandalfxero”.
2> Click on Firewall on the window on your right side.
3> Type a rule name eg Blendo, at START IP box type the address you will get from Blendo. Put the same at END IP (Or you can put a range of addresses).
3> Click Save to create the rule.
Part 2: Export data to Azure Microsoft SQL Database
1. Go to your Blendo Dashboard.
2. If it is the first time you login to Blendo you will see the screen bellow. Click on the box with SQL Server.
On the next screen, click on the box with SQL Server.
3. Add your credentials to connect to Microsoft SQL Server.
- SQL Server Host: For our example, this will be the gandalfxero.database.windows.net
- SQL Server Port: Leave the default
- SQL Server Database: For our example, this will be Myxerodb
- SSL: In our example, we do not use SSL, so leave it blank. NOTE: It is advisable to have SSL!
- Username: For our example it is testusr
- Password: the password
- *Export table prefix: It is not mandatory, I used myxero_ for me to know which tables will be created inside the SQL database.
If You have successfully connected all your data will automatically go from Any source to Azure MS SQL and that will happen for every new update that happens to that source. Blendo will get your data in certain intervals and send it to your SQL database.
If you also need a full guide take a look at our MS SQL Server documentation:
- Step 1 (Azure): Connect Windows Azure Microsoft SQL Server
- Step 2 (Azure): Setup Windows Azure MS SQL Server
- Step 3 (Azure): PermissioningBlendo to Windows Azure MS SQL Server
- Step 4 (Azure): How to connect Windows Azure MS SQL Database as Data Warehouse Integration
