On-Premise SQL Server Reference Architecture

This is a standard reference architecture for implementing Jet Analytics Data Integration fully on-premises.

To prepare your Jet Analytics Data Integration environment, follow the steps below.

Step 1: Create Application Server — Local Server

To host the Jet Analytics Data Integration application on-premises, we recommend using a Windows environment such as Windows Server, sized according to your solution's requirements.

Guide: Configure an On-Premise Application Server & Storage

Considerations:

• The linked guide highlights the Separate Application and Database Server architecture, where the Ingest Service is installed on an "Application" server and the SQL databases are stored on a separate "Database" server. It is also possible to run the Ingest Service and SQL on a consolidated single server.
• Once you have chosen your desired configuration and deployed the necessary servers, install all required Jet Analytics Data Integration prerequisites.
• Configure your server firewall to allow Jet Analytics Data Integration's required firewall exceptions.

Step 2: Create Storage for Ingest Instance — SQL Server Data Storage

On-premises SQL Server can store your raw data quickly and easily, making it a straightforward option for integrating siloed data.

Guide:Add an Ingest Instance

Considerations:

• The Ingest Storage database can be created directly within Jet Analytics Data Integration.
• The Jet Analytics Ingest Service and Jet Analytics Execution Service should be hosted locally on the application server.
• SQL Server 2017 or higher is recommended.

Step 3: Create Storage for Prepare Instance — SQL Server Data Storage

SQL Server is a strong option for Prepare instance storage, providing a reliable foundation for data warehousing on-premises.

Guide: Add a Prepare Instance

Considerations:

• Use an on-premises SQL Server database (or Azure-hosted SQL Server) for your Prepare instance, where data is stored for queries and analysis in your data warehouse.
• A typical Jet Analytics Data Integration solution consists of one data warehouse that consolidates data from one or more staging databases and a number of data sources.
• SQL Server 2017 or higher is recommended.
• Multiple data areas can be configured within your Prepare instance — for example, one data area for your Data Staging Area and a separate data area for your Modern Data Warehouse.

Step 4 (Optional): Create Semantic Model — SQL Server Analysis Services

SQL Server Analysis Services is an ideal way to serve data to Power BI and Excel. Since there are many ways to connect visualization tools directly to the Prepare instance, this step is optional.

Guide:Add a Deliver Instance

Considerations:

• Jet Analytics Data Integration stores semantic models built in Deliver instances as Analysis Services Tabular models behind the scenes.
• Each Deliver instance can be deployed to one or more semantic model endpoints.