Follow

Set up a Azure SQL Database project

Author: Thomas Lind Last updated:
Azure Direct Read DWH

Create a project with only Azure SQL databases

Since the release of version 17.5 we have the possibility of creating and using databases on a Azure SQL Database.

Update!

Since the release of version 17.12 we have had the possibility of deploying to a Azure Analysis Services tabular model. I made a guide for it, the bit regarding I have a Azure SQL Server running is what you can use for this.

What is needed

To use TX DWA and Azure SQL Database together, TX DWA 17.5.xxx, CLR and SMO is required. Here is a link to those ENU\x86\SQLSysClrTypes.msi ENU\x86\SharedManagementObjects.msi

A full SQL server is also possible, but it is not needed to connect and create to Azure.

If you want to use the scheduler service, it is also important that you install TX DWA on a server, or computer that is always on. It could be a Azure virtual machine, like i used i my own test, or another form of server.

It is also necessary to know the Azure SQL server name and have a SQL user with the rights to create databases on it.

How to set it up

The first step is to create a repository on the Azure SQL database.

Add the server name, repository name, set it to use SQL server authentication and add the SQL user.

Create_repository.PNG

Press create and it will then give a visual representation of what is being done.

The_creation.PNG

Create a new project, make sure SSIS is not selected, add a BU and a STAGE/DSA database.

Add the server name, use SQL Server authentication and add the same user. It should not be named xxx_STAGE, as it will be used for both DSA and DWH.

Set_up_the_DSA_db.PNG

Create the database.

The_creation_of_2nd_db.PNG

Attach a data source. It can be anything really, but in my test case i used a NAV database, that was conveniently placed on the same Azure SQL database.

Nav_Data_Source.PNG

Add some tables and fields. Then create a DSA schema. This is to split up the two parts of the database in DSA and DWH. It need to be set up as the "Main default schema".

Stage_schema.PNG

Then add the Data warehouse. It is set up so it points to the database that were created earlier. Use the same server and user account. The only exception is that the Direct Read option is used. It is important that it is set to "Matching Server and Database". In Azure SQL Database, two tables cannot transfer between each other. As it will not be able to do distributed database queries.

Set_up_the_DWH_db.PNG

Before the the fields from stage to DWH are attached, The other Schema needs to be set up, so you avoid tables having the same names.

DWH_schema.PNG

Deploy/Execute. See that the Azure SQL Database set up.

Final_setup.PNG

Limitations

The use of SSIS is not available.

It is not possible to create OLAP servers on Azure Analysis Services. It does not work with Azure SQL Databases yet. It is going to be added in the future.

If you want to create a DWH database and STAGE database you cannot use direct read.

Alternative ways of using Azure Databases

Perhaps you want only the DWH table to be located on Azure. This is possible, as long as you do not use Direct Read and SSIS. You can also do it the other way, where it is STAGE that are on Azure and DWH is locally.

You can also set up multiple environments, have a Dev environment locally and a Prod environment fully on Azure.

Questions

Do you have questions regarding a way to use Azure and set it up? Please add it in the comments.

Was this article helpful?
3 out of 3 found this helpful
Have more questions? Submit a request

10 Comments

Date Votes
  • 0
    Avatar
    David Zebrowitz

    If you're just running TX DWA on an Azure VM (no SQL Server installed) what are the recommended specs for that VM?

  • 0
    Avatar
    Thomas Lind

    Hi David

    You only need CLR and SMO. I added a link to where you can download them, in the description.

  • 0
    Avatar
    David Zebrowitz

    Yep, I saw that.  I'm looking for details on the infrastructure requirements side, such a suggested number of CPU cores & RAM for the Azure VM.  I'm guessing the number of cores will correspond with how many parallel processes you need to run?

  • 0
    Avatar
    Bjarki Már

    Hi Thomas, so what is the best way to handle the ETL process since SSIS is not available. given all db are in azure (no VM).

    Thanks

  • 0
    Avatar
    Thomas Lind

    Hi David

    Regarding the infrastructure requirements.
    You can split out the threads in as many as you have cores, but it is not a limitation in itself. If you want, you can split it out into 8, even though you have 4 cores. That said it is still a good rule to follow.

    In this instance where the SQL server is located in another location, i would probably use a "Compute Optimized" server. How fast is up to you, but it seems to be the best fit.

  • 0
    Avatar
    Thomas Lind

    Hi Bjarki

    Regarding the ETL process and no SSIS.
    In my article i use a middle step with the direct read that get used instead of the normal transfer. Other than that there still is the normal data cleansing procedure afterwards.

    There will be a new article that will be about the differences between SSIS and ADO.Net.

  • 1
    Avatar
    Andri Páll Heiðberg

    When not using one database and schemas to split up the layers we have to rely on ADO.NET for data transfer between database. How much performance degradation can we expect compared with using SSIS in a typical server setup?

  • 0
    Avatar
    Stuart Cuthbertson

    I would be very interested to see comments from anyone who is using an Azure SQL DB for TX, with an idea of which pricing model (DTU/vCore) and service tier is working for them.

    This is clearly going to be data-dependent, but some starting point in terms of general DWH scale (total gigabytes or rows) and appropriate service tier for that scale, would be very helpful. 

    Thanks in advance if anyone sees this and replies! 

  • 0
    Avatar
    Dror Svartzman

    Hi, 

    Is it best practice to only use SQL login when accessing Azure SQL database? 

    If i set windows authentication to on, it get the following error message: 

    'Windows login are not supported for this version of SQL server'

     

    From security perspective i would preffer to use windows authentication instead of static sql login. 

     

     

     

  • 0
    Avatar
    Thomas Lind

    Hi Dror

    Are you able to connect to a Azure SQL database through Microsoft Management Studio with a windows authenticated user?

    If so then yes, it probably should be an added possibility.

Please sign in to leave a comment.