Spreading your SQL Server wings with SQL database in Fabric

Reading Time: 5 minutes

In this post I want to cover spreading your SQL Server wings with SQL database in Fabric. As part of a series of posts about spreading your SQL Server wings with the Microsoft Intelligent Data Platform.

It has been some time since a published in this series of posts. In fact, my last post in this series was almost a year ago when I covered Microsoft Fabric Lakehouses.

However, SQL database in Fabric was announced during the keynote at Microsoft Ignite and it absolutely deserves to be part of this series.

By the end of this post, you will have a good introduction to this new offering, including lifecycle management capabilities. In addition, where your SQL Server background can prove to be useful.

Recap about Microsoft Fabric along with new architecture diagram

Microsoft Fabric unifies the functionality you can find in various applications together within one Software as a Service solution to deliver a unified analytics platform.

So, all you have to do is go to one website and all the functionality that you see below will be at your fingertips. Including the new Databases workload which SQL database in Fabric is a part of.

To help with some jargon here, you can find out more about what I mean when I refer to Software as a Service (SaaS) in an old blog post called ‘SaaS, PaaS, and IaaS.. Oh my!’.

In reality, even though Microsoft Fabric has numerous data experiences you should configure some other services as well to get the most out of it. Especially if you want to work with it at an enterprise level.

For example, Microsoft Purview for your compliance and governance needs. In addition, either Azure DevOps or GitHub to work with Microsoft Fabric Git integration.

About SQL database in Microsoft Fabric

SQL database in Fabric is a new item in Microsoft Fabric which allows you to work with operational SQL databases directly within Microsoft Fabric.

Backed by the power of a dedicated database engine based on SQL Server. Allowing you to work with databases using the T-SQL language.

So those with a SQL Server related background will thrive working with this offering.

SQL database in Fabric was announced during the keynote at Microsoft Ignite and currently in public preview. Which means you can start deploying databases to work with in Microsoft Fabric now.

You can create a new SQL database a couple of different ways in Microsoft Fabric. However, as of late I have become more keener on going through the workload options in the bottom left-hand corner. Since they tend to promote additional functionality and links.

When you create a database the below three items are created:

• SQL database
• Semantic Model
• SQL Analytics endpoint

You get three items because data updates first take place in the SQL Database. Which stores data in the traditional SQL Server database file formats.

Afterwards, data updates are replicated from the SQL database to OneLake in Delta format. Allowing the data to be read optimally by the created Semantic Model and the SQL analytics endpoint items.

Which means that you get to take advantage of numerous SQL Server optimizations as well as all the advantages of working with the data in Microsoft Fabric.

Once created, you can click on the SQL database to open the SQL query editor.

SQL query editor

You can draw comparisons between the SQL query editor and the Warehouse Editor. Since they both work with T-SQL and come with some similar options. However, as you can see from the following image the SQL query editor has slightly different functionality.

Plus, as you can see in the previous image when you first deploy a database you get the option to create sample data in the editor.

Alternatively, you can ingest existing data into your new SQL database. Due to the fact that SQL database has already been added as a new connector in both Dataflows Gen 2 and Data Pipelines. Allowing you to work with SQL databases as either a source or a destination.

In addition, you can query data stored elsewhere with the same T-SQL you worked with elsewhere. You can view which T-SQL statements you can work with by viewing the updated Transact-SQL reference.

SQL database in Fabric already boasts some impressive features. Including the ability to auto-optimize and auto-scale. For example, all new databases come with auto indexing enabled by default. Which you can see by running the below code:

SELECT * FROM sys.database_automatic_tuning_options
WHERE name in ('CREATE_INDEX','DROP_INDEX');

As you can see from the above example, just like with SQL Server you can work with a variety Dynamic Management Views within these databases.

Plus, the database editor comes with its own Copilot experience to allow you to translate natural language into powerful T-SQL queries. Plus, built-in IntelliSense.

In addition, just like with Microsoft Fabric Data Warehouse you can connect to these databases with a variety of applications. Including Visual Studio Code and SQL Server Management Studio.

These are just some examples of the rich set of features and functionality you can take advantage of at an operational level. I highly recommend you experiment with them yourselves to see what SQL database in Fabric already offers.

Lifecycle management capabilities for SQL database in Fabric

One thing that I really like about SQL database in Fabric is that there are multiple lifecycle management capabilities for it already.

For example, you can look to perform CI/CD with SQLPackage or do deployments with deployment pipelines.

You can also perform source control with Microsoft Fabric Git integration as well. Below is an example of the metadata for a SQL database in Fabric stored in a Git repository within Azure DevOps.

Final words

I hope this post about spreading your SQL Server wings with SQL database in Fabric has proved to be useful and wanting you to learn more about it.

Microsoft published a post announcing SQL database in Microsoft Fabric Public Preview. Where you can find out more about this fantastic new offering.

In addition, you can find out more about the rich set of features functionality in Microsoft’s documentation about SQL database in Fabric. Plus, there is a Microsoft Learn module available to help you get started with SQL Database in Microsoft Fabric.

Of course, if you have any comments or queries about this post feel free to reach out to me.