Problem
With the announcement of databases in Microsoft Fabric at Microsoft Ignite 2024, Microsoft brings the power of Online Transaction Processing (OLTP) databases to their Software-as-a-Service (SaaS) solution. This article will cover how to create a database in Microsoft Fabric and integrate this database into a SQL database project.
Solution
Before we get started, let’s talk about why Microsoft introduced databases in Microsoft Fabric. Research from IDC shows that we can expect over 1 billion new logical apps to be created in the coming years. Therefore, Microsoft decided to develop a SaaS solution for relational databases, allowing app developers to focus on new app development instead of the database configuration behind the scenes. The good news is that databases in Microsoft Fabric run on the same engine as Azure SQL.
From a reporting point of view, the biggest benefit of databases in Microsoft Fabric is that the data is mirrored into OneLake automatically in the open-source delta parquet format. Since the data is mirrored in near real-time, you can easily use the data with the SQL analytics endpoint, which is provisioned automatically, for analysis without interfering with the processes on the live data.
Databases in Microsoft Fabric
The first part of this article is focused on creating a database in Microsoft Fabric. To get started, navigate to https://app.fabric.microsoft.com/ to sign in or sign up for a free trial. If you don’t have a workspace yet, create a new workspace and add the workspace to an existing Microsoft Fabric capacity or the Trial capacity.
Creating a Database in Microsoft Fabric
To create a SQL Database in Microsoft Fabric, navigate to the preferred workspace, and click New item in the top left corner.
The “New item” pane opens on the right-hand side. Scroll to the Store data section where the SQL Database component is located and choose the SQL Database component.
If you can’t create a SQL Database in Microsoft Fabric, ask your Microsoft Fabric Tenant administrator to check the tenant settings in the Admin portal. More specifically, ask to check the SQL Database setting as shown in the screenshot below.
After choosing the SQL Database component, you are required to provide a name for the SQL Database and click Create. A SQL Database called “DataRecipeBuilder” is created in the example below.
Confirm that the SQL Database in Microsoft Fabric was created. As you can see in the screenshot below, a default Semantic model and an SQL analytics endpoint were also created.
Difference Between the SQL Database and the SQL Analytics Endpoint
Before continuing, the difference between the SQL Database and the SQL analytics endpoint must be explained. At this point, an empty SQL Database has been created. If a new table needs to be added, the SQL Database endpoint (or via the preferred development tool) is the way to go. The SQL Database endpoint executes DDL and DML scripts (create objects and manipulate data), while the SQL analytics endpoint allows the end user to query the replicated data on OneLake for analytical purposes.
Adding a Table with the SQL Database Endpoint
Let’s add a table through the SQL Database endpoint. First, open the SQL Database endpoint via the web browser. When looking at the Explorer window on the left-hand side, we can see that no tables are available yet.
By clicking New Query, a new query window opens where we can write our first create table statement. The following code block contains the create table statement to create a table called Recipe.
CREATE TABLE dbo.Recipe
(
RecipeId INT NOT NULL PRIMARY KEY,
RecipeTitle VARCHAR(100) NOT NULL,
RecipeDescription VARCHAR(500) NOT NULL,
RecipeDateInserted DATETIME2,
RecipeDateModified DATETIME2
);To execute the statement, use the Run button or the F5 shortcut. As you can see in the code sample above or the screenshot below, there isn’t much difference between this and SQL Server syntax-wise, except maybe not needing to specify a file group in databases in Microsoft Fabric.
As a result, we can see the Recipe table via the Explorer window.
Adding an Existing Database in Microsoft Fabric to a SQL Database Project
Now that the SQL Database in Microsoft Fabric and a table have been created/added, a SQL Database Project based on this SQL Database can be created. To create a SQL Database Project, open Azure Data Studio and install the SQL Database Projects Extension via the Extensions pane. If you are unsure, you need to search for the following extension:
With the SQL Database Projects extension installed, you can now navigate to the Database Projects tab in Azure Data Studio. Choose More options (…) and Create Project From Database.
The Create project from database window opens. As a first step, you need to create a connection to the source database (our SQL Database in Fabric in this example).
Create Connection
To create the connection to the SQL Database in Microsoft Fabric, go back to the SQL Database endpoint in Microsoft Fabric to retrieve the connection details. Choose Open in and then Visual Studio Code to continue.
A window appears that is loaded with the required connection details. Copy the Server Name and Database Name to Azure Data Studio.
To continue, the Authentication type needs to be configured to Microsoft Entra ID. Connections via SQL authentication cannot be made.
After providing all the connection details, click Connect. If all the connection settings are provided correctly, a connection is created successfully in Azure Data Studio. Next, provide a proper Target project name and specify the folder where the SQL Database project must be created.
Finally, choose the SQL Database folder structure and click Create. In the example, the “Schema/Object Type” folder structure is used. If you want to implement your own folder structure, you can do that later.
As soon as the Create button is clicked, a task will appear in the tasks pane. This pane shows the progress of the SQL Database Project creation. The screenshot below shows the end result, confirming that the SQL Database project has been created successfully.
Validate SQL Database Project
To validate that our SQL Database project has been created successfully, navigate to the Database Projects pane to find the Recipe table in the expected dbo/Tables folder.
Next Steps
This article provides a “how to get started with databases” in Microsoft Fabric. As a next step, my advice would be to add this SQL Database Project to either Azure DevOps or GitHub. To learn more about Microsoft Fabric, you can use the following articles:
