Follow

Add an Azure SQL Single Database

TimeXtender began supporting Azure SQL Database with the release of version 17.5. Azure SQL Single Database is a fully managed, database-as-a-service in the Microsoft Azure Cloud. This service is always running the latest SQL Engine, never require patches or updating and can be deployed in minutes. When configured using the "Serverless" Compute Tier, the database can even scale and pause automatically based on workload. It is an ideal solution for most cloud-based data warehouses utilizing fewer than billion row tables and requiring less than several terabytes of storage. 

Unfortunately, Azure SQL Database does not support cross-database queries, so the typical architecture of having separate Staging and Data Warehouse databases is not ideal. In this case, we can configure TimeXtender to connect to the same database for both layers, and separate the tables logically by using separate schemas. 

In this article you will learn how to:

  1. Configure Azure Resources
  2. Configure SQL Users & Permissions
  3. Setup Staging & Data Warehouses in a shared Azure SQL Database
  4. Configure Schemas to prevent table naming collisions

 

1. Configure Azure Resources

For a fast and simplified setup, we recommend using one of our preconfigured Azure Marketplace Templates to create the necessary Azure Services. If not, you will need to install/create the following:

  1. A Server to run the scheduler service, we recommend using an Azure virtual machine in the same Region as the SQL Database. 
  2. Install all the necessary prerequisites on the server. 
  3. Create an Azure SQL Database. You will need two, one for the project repository and one for your ODX / DSA / MDW. Using the "Serverless" Compute Tier, the database can even scale and pause automatically based on workload.

2. Configure SQL users & Permissions

You will need to create users in Azure SQL Database with the necessary permissions. Alternatively, you can use Azure Active Directory. 

3. Setup Staging & Data Warehouses in a shared Azure SQL Database

 

Create a new project, make sure SSIS is not selected, add a Business Unit and a STAGE/DSA database. Alternatively, you can use the ODX Server instead. 

Add the server name, use SQL Server authentication and add the same user configured above. Type in the name of your Azure SQL Database create in previous steps.

Set_up_the_DSA_db.PNG

 

 

Add a Data warehouse. It is set up so it points to the same database that created earlier. Use the same server and user account. In all Data Warehouse databases, be sure to set Direct Read to 'Matching Server and Database'.  This will enable TimeXtender to perform a "Select Into" operation to move the data rather than attempting to use ADO.Net. 

Set_up_the_DWH_db.PNG

4. Configure Schemas to prevent table naming collisions

In the Staging Database, create a DSA schema. This is to split up the two parts of the database in DSA and DWH. It needs to be set up as the "Main default schema".

mceclip0.png

In the Data Warehouse Database, create a DWH Schema and set it as "Main default schema". This will prevent table naming collisions with the DSA database.

mceclip1.png

 

Deploy/Execute. 

 

Alternative ways of using Azure Databases

Perhaps you want only the Data Warehouse database to be located on Azure. This is possible, as long as you do not use Direct Read and SSIS. 

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

 

Was this article helpful?
3 out of 3 found this helpful

10 Comments

  • 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.