Problem
We are currently evaluating Microsoft Fabric to see if it fits our needs. It seems the warehouse might be a bit overkill as it’s more geared towards big data scenarios. We also don’t have the technical skills right now to work with the lakehouse, most of our staff just use SQL. We would also like to store some small tables with reference data. What is the best option for our use case?
Solution
At the Microsoft Ignite 2024 conference, Microsoft announced a new workload for Microsoft Fabric: the SQL Database. This compute workload is exactly what the name suggests: it’s a SQL Server database, the very same we know from Azure as the Azure SQL Database. Only this time the database is provided to us as a Software-as-a-Service offering, while the Azure SQL Database is a Platform-as-a-Service (PaaS) offering.
Source: Microsoft Ignite
SQL Database in Microsoft Fabric Benefits
This feature has been anticipated for quite some time, as it provides an answer for several shortcoming of the Microsoft Fabric platform:
- storing small tables, containing metadata or reference data
- support for small data warehouses of maybe just a couple of Gigabytes in size. The Warehouse or the Lakehouse might be overkill in those scenarios, especially for teams that mainly use SQL and don’t have proficiency in Spark or Python. The Warehouse is geared towards bigger data sets and doesn’t provide fast query response times for small data.
- full support of the T-SQL language, or any feature that is absent from the Warehouse but present in other flavors of SQL Server. For example, the SQL Database supports primary, foreign key and unique constraints and you can use an IDENTITY column.
- When you want to run OLTP (transactional) workloads and want this data hosted in Fabric.
This makes the SQL Database an ideal candidate for teams that already have existing (small) data warehouses in either on-premises SQL Server or Azure SQL Database and that are looking to migrate their solutions to Fabric with minimal effort. The database is also a good alternative for storing metadata or reference tables, for example in solutions that use metadata to drive their ETL.
In this tip, we will explore how we can create a new SQL Database in Fabric and how we can load sample data into that database. At the time of writing, this feature was in public preview, which means the layout or functionality might change before the time it goes into general availability. It’s also possible the preview feature is not yet available in your Azure region.
SQL Database in Microsoft Fabric
Loading Sample Data
When you create a new database, you have the option to load some sample data into it. This data is the same sample data used as in the Azure SQL Database: the AdventureWorksLT database. You can find more information about this in the tip: How to Install the AdventureWorks Sample Database in Azure SQL Database.
First, we will need to create a new SQL Database. To do so, this option needs to be enabled in your Fabric tenant. You can find this setting in the Admin portal (you need to be a Fabric Administrator to be able to access this menu).
In the Admin portal, search for database to find the relevant setting.
You can enable this for the entire organization, or only for a selection of security groups. In a Fabric-enabled workspace, choose to create a new item.
A menu will open with all the different object types in Fabric. Select the SQL database option.
You will be prompted to give the new database a name.
Once the database is provisioned, you will get a screen with the option to load sample data into the database. Other options are displayed as well, such as the option to create a dataflow or pipeline. Click the Sample data option to load the AdventureWorksLT data. This might take some time.
Once the data has loaded, you can open up the database and see the different tables in the explorer:
The sample data will be loaded into the SalesLT schema. As you can tell from the screenshot, it supports features such as unique indexes and primary keys. There are two tables created in the dbo schema, BuildVersion and ErrorLog, but they typically don’t contain useful information.
Connecting to the Database
When you connect to the Fabric service using SQL Server Management Studio (SSMS) like you do for the Warehouse or the SQL Analytics Endpoint of a Lakehouse, you can see the database listed among the other databases:
However, it seems features are missing. Where are the indexes for example? We only get a limited view, because we are connecting to the SQL Analytics Endpoint of the SQL Database. When you create a SQL Database in Fabric, the data is stored in the proprietary format of SQL Server. But the data is synced in real-time to OneLake, where the data is stored in delta tables with the Parquet format. When you use the SQL Analytics Endpoint, you are connecting to the Parquet files in OneLake, not the actual SQL Server tables. This is why features are missing, and some SQL statements – such as CREATE TABLE – won’t work since the SQL Analytics Endpoint is read-only. The clue is in the name of the server. If it contains datawarehouse.fabric.com, you’re connecting to the SQL Analytics Endpoint.
Connect to Actual SQL Database
If you want to connect to the actual SQL Database, you need to use a different connection string. In the explorer of your SQL Database in the Fabric workspace, go to the settings.
In the Connection Strings section, you can find connection strings for different programming languages.
The server name is indicated in the red box. This time, it contains database.fabric.microsoft.com, which indicates we’re directly connecting to the database engine and not the delta tables in OneLake. The name of the database (indicated in the green box) is also not the name we specified at creation time. That name is preserved for the SQL Analytics Endpoint. The actual database name is the original name with a GUID appended to it.
We can use this info to connect to the SQL Database using our preferred tools, like SSMS.
Don’t forget to specify the database name in the connection properties:
Once connected, the Fabric SQL Database looks exactly like an Azure SQL Database:
An additional benefit is that the existing tooling that works with Azure SQL Database, will also work with the Fabric SQL Database. For example, the tool SQL Prompt of Red Gate will provide autocomplete for the Fabric SQL Database as well:
Next Steps
- If you want to try this feature out, you can use the free Fabric trial.
- You can watch the announcement of the SQL Database in Fabric for free on the Microsoft Ignite website (around the 8 minute mark).
- There’s also an official announcement blog post.
- For more Fabric tips, check out this overview.
