Follow

Reference Architecture with Azure SQL DB

Author: Taylor Hagin Last updated:

A great way to start in Azure with TimeXtender

mceclip1.png

This is a standard reference architecture to implement TimeXtender fully in Azure, the goal is to balance performance and cost, when working in the cloud.

To prepare your TimeXtender environment in Azure, here are the steps we recommend.

  1. Create Application Server - Azure VM
  2. Create Project Repository - Azure SQL DB
  3. Create ODX Storage - Azure Data Lake Storage Gen2
  4. Prepare for Ingest and Transport - Azure Data Factory (recommended)
  5. Create MDW (and DSA) Storage - Azure SQL DB
  6. Create Semantic Layer Server - Azure Analysis Services
  7. Estimate Azure Costs

 

mceclip1.png

1. Create Application Server - Azure VM
To serve the TimeXtender application in Azure, we recommend using an Azure Virtual Machine (VM), sized according to your solution's requirements.
Guide:

Create a TimeXtender Application Server in Azure
Video Tutorial:

TimeXtender Tuesday: Create a TimeXtender App Server in Azure
Considerations:
  • Recommended Sizing: DS2_v2 (for moderate workloads)
  • If Azure VM (App Server) serves the ODX Server, it must remain running for TimeXtender to run.
  • This VM will host the services to run TimeXtender.
    • ODX Service
    • Scheduler Service
    • Server Services

 

mceclip2.png

2. Create Project Repository - Azure SQL DB
When you save a project, metadata is written to the repository database, and when you open a project, this metadata is read from the project repository and presented in the UI.
Guide:

Use Azure SQL Single DB with TimeXtender
Video Guide: TimeXtender Tuesday: Configure TimeXtender Environment in Azure App Server
Considerations:
  • Recommended SQL Single DB (vCore - General Purpose) Sizing:
    • Provisioned - Min 2 vCores
    • Data Max Size - 50 GB
  • You may increase the vCores to decrease the time it takes to save a TimeXtender project. 
  • One Project Repository can contain multiple projects
  • Each environment requires a separate Project Repository database.

 

mceclip6.png

3. Create ODX Storage - Azure Data Lake Storage Gen2
ADLS Gen2 is highly performant, economical, scalable, and secure way to store your raw data.
Guide:

Use Azure Data Lake Storage with TimeXtender
Video Guide:
 TimeXtender Tuesday: Use Azure Data Lake Storage for ODX Storage
Considerations:
  • When creating the ADLS Gen2 data lake service, you must enable Hierarchical Namespaces
  • TimeXtender writes files in Parquet file format, a highly compressed, columnar storage in the data lake.
  • It is possible for ODX Server to store data in Azure SQL DB, but this adds cost and complexity but no additional functionality
  • When using Azure Data Lake for ODX and SQL DB for the Data Warehouse, it is highly recommended to use Data Factory to transport this data. 
  • ADLS will require a service principle, called App Registration in Azure, for TimeXtender to access your ADF service. 
    • Both Data Lake and ADF, may share the same App Registration if desired. 

 

mceclip0.png

4. Prepare for Ingest and Transport - Azure Data Factory (recommended)
For large data movement tasks, ADF provides amazing performance and ease of use for both ingestion and transport.
Guide:

Use ADF for Data Movement with TimeXtender
Considerations:
  • When creating ADF resources use Gen2, which is the current default
  • A single ADF service can be used for both transport and ingestion
    • Ingestion from data source to ODX Storage
    • Transport from ODX to MDW
  • The option to use ADF is not available for all data source types, but many options are available.
  • ADF Data sources do not support ODX Query Tables at this time. 
  • ADF's performance can be quite costly for such incredible fault-tolerant performance
  • ADF will require a service principle, called App Registration in Azure, for TimeXtender to access your ADF service. 
    • Both Data Lake and ADF, may share the same App Registration if desired. 

 

mceclip3.png

5. Create MDW (and DSA) Storage - Azure SQL DB
With it's ability to auto-pause, Azure SQL Single DB Serverless is a great, potentially cost-saving option for the data warehouse storage, both Modern Data Warehouse (MDW) and Data Staging Area (DSA).
Guide:

Use Azure SQL Single DB with TimeXtender
Considerations:
  • Recommended SQL Single DB (vCore - General Purpose) Sizing:
    • Serverless -  Min 10 vCores - Max 10 vCores
    • Data Max Size - 50 GB
  • When Serverless is conducting load balancing, it will drop connections, therefore, when using Serverless for your data warehouse, you want to set the Min vCores and Max vCores to the same level. 
  • The Serverless compute tier can be a cost saving option, if you do not require your database to be online more that 50% of the time. If you do require >50% uptime for the Data Warehouse, then the Provisioned compute tier will be more economical. 
  • Azure SQL Single DB cannot communicate with other databases, by design.
    • If using a Data Staging Area (DSA) in your solution, you can use the same SQL DB, but must separate by schema, such a 'etl' and 'dbo'

 

mceclip4.png

6. Create Semantic Layer Server - Azure Analysis Services
To serve the Semantic Model in TimeXtender, Azure Analysis Services provides enterprise-grade, scalable performance.
Guide:

Use AAS with TimeXtender
Considerations:
  • Recommended AAS Tier: Developer - D1 (for prototyping and modest workloads, but may not be suitable for production workloads.)
    • There are three tiers and multiple options for various use cases.
  • Like ADF, Azure Analysis Service requires a service principle, called an App Registration, for TimeXtender to connect to the service
  • AAS can be quite costly, though it provides great performance if that fits the solution requirements
  • TimeXtender stores semantic models in Analysis Services Tabluar model behind the scenes.

 

mceclip3.png

7. Estimate Azure Costs
Balancing cost and performance requires montioring and forecasting of your services and needs.
Guide:

Azure Pricing Calculator*
Considerations:
  • Azure provides a pricing calculator to help you estimate your costs for various configurations.

*Please note, this Azure pricing calculator does not include the price of the TimeXtender License or Consulting services.

 

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

2 Comments

Date Votes
  • 1
    Avatar
    Bas Hopstaken

    Hi,

    Great article! And I love the TimeXtender Tuesday videos.

    I do have a question regarding this infrastructure. How does a Multiple environment setup fit in this architecture? 

    Which components of the architecture need to be duplicated. I know the Project repository database needs to be separated for each environment and also the MDW databases.

    But multiple environments can be accessed using the same Azure VM right?

    Is it needed to create separate datalakes, Azure Analysis Services and Azure Data Factory for multiple environments? Using global databases I can add a prefix or postfix to the name of the AAS endpoints that include the environment name.

    How does this work for datalakes and ADF? I assume you can use the same datalake and Data Factory in for multiple environments?  

  • 2
    Avatar
    Taylor Hagin

    Hi Bas,

    Great question! Due to the broad flexibility of options to set up multiple environments, we decided to add this as another TimeXtender Tuesday episode in the near future. Stay tuned!

    In the meantime, I'd recommend you check out the KB article about multiple environments. Configure Multiple Environments

    You are correct that project repos need to be separated, but the single VM (App Server) is designed to handle all environments. In the article cited, you can see Prod on a different VM than the lower environments, which is a common practice, though not a requirement.

    You are right on the other points, as well: no need to replicate ADLS Gen2, AAS, or ADF resources for the different environments, as they can be shared by all environments. Again, we'll be covering these topics in more detail in upcoming sessions of TimeXtender Tuesday.

    Check out the TimeXtender Tuesday Playlist on YouTube, for live How-To, mini-lessons on all things TimeXtender.

Please sign in to leave a comment.