Play Around: Deploy Database Projects to Fabric Warehouse

I always like to have a dig around, try a few things and see if something works (most likely unsupported!) and I’ve been interested to see if I could get an offline SQL database project deployed to a Fabric Warehouse. It turns out I could…but with a lot of caveats, after all this is a play around. If you want to know more then read on…

Disclaimer

This is just a test, a bit of fun playing around with functionality. We’ll (hopefully) see support for database projects with Fabric Warehouses at some point. See my Fabric in Production blog which lists features I believe should be production. You should also keep an eye on Kevin Chant who blogs in this area.

To submit and support ideas and features to Microsoft, go to the Ideas site and make your voice heard.

---

Fabric Warehouse

The Fabric Warehouse is a SQL engine based item that can be created in a Fabric workspace, it is underpinned by the engine that Synapse Serverless SQL Pools was built on and all table data is saved as Delta (Parquet under the hood). A Warehouse allows us to create tables, transform and load data, and query with SQL. If you want to know about the differences between Lakehouse SQL Endpoints and Warehouses then check out this video.

---

Database Project Deployment Options

I tried creating a database project then deploying in both Azure Data Studio and also Visual Studio 2019 SSDT. I also tried using the new version of sqlpackage.exe which supports Serverless SQL Pools to deploy dacpacs.

• Azure Data Studio: Could deploy a database project
• Visual Studio SSDT: Could not deploy a database project
• sqlpackage: Could not deploy a dacpac created in either ADS or VS

I’ll continue to play around with the tools above and update this blog.

** Update **

I could deploy from Visual Studio SSDT, I initially couldn’t find the “allow incompatible platform” option but it’s in the Advanced section when publishing towards the top of the window.

---

Azure Data Studio

I’ll be honest, I don’t use Azure Data Studio day-to-day as I’ll still use SSMS (SQL Server Management Studio) for all SQL querying. However, I do like the extensions library in ADS and have been using things like database projects. To install database projects, open ADS, click on the Extensions icon in the left menu, search for and install SQL Database Projects.

Steps:

• Click View > Command Palette > Database Projects: New.

Then I can create a table by right-clicking the database project name and selecting Add Table.

Attempt Deployment

Let’s try and deploy the database project to a Fabric Warehouse.

Get SQL connection string

First we’ll need to get the connection string of the Fabric Warehouse. One method is if the Warehouse is already open then click the settings icon in the top-left and copy the SQL connection string.

Once we’ve got the connection string, bac in ADS do the following:

• Right-click database project name and click Publish
• Server: click icon and enter relevant Fabric Warehouse details

• Click Connect
• Then click Publish

You’ll see the deployment fails with an error:

“Deploy dacpac: Verification of the deployment plan failed.
Warning SQL0: A project which specifies Microsoft Azure SQL Database v12 as the target platform may experience compatibility issues with Azure Synapse Serverless SQL Pool.
Error SQL72030: An incompatible collation combination has been detected between the source and target. Deploying a case insensitive model to a case sensitive target can produce unintended changes and possible data loss. Consider using a case sensitive source model collation.”

Collation

Let’s have a look at the database collation in the Fabric Warehouse.

--get fabric warehouse collation level
SELECT 
    name as DatabaseName, 
    collation_name AS CollationName, 
    compatability_level AS CompatabilityLevel
FROM sys.databases;

Both the lakehouse and warehouse I created in the Fabric workspace show as Latin1_General_100_BIN2_UTF8. That looks familiar…yup, it’s the collation that is recommended a Serverless SQL Pools database is set to when working with text data. “Predicate pushdown for character columns in Parquet files is supported for Latin1_General_100_BIN2_UTF8 collation only” read more here.

---

Ignore Collation Check

Although we can’t do much about the target platform warning, we can set an option to ignore incompatible collation. Go through the steps to publish again however:

• select Advanced before clicking publish
• Scroll to the bottom of the Publish Options tab
• Untick Verify collation compatability
• Click OK
• Click Publish

This time we should see a dacpac succeeded message:

If we browse to the Warehouse in Fabric, there is the new WebTelemetry table deployed.

Things That Don’t Work

We can’t amend/add anything and redeploy as the schema compare functionality doesn’t work, which means it doesn’t know what has/hasn’t been deployed and tried to redeploy existing objects again. As I said, this is a play around just to see what works/doesn’t work.

Also, when adding referential integrity to a table, database projects don’t understand the NOT ENFORCED keyword.

CREATE TABLE [dbo].[tablev3]
(
	[Id] INT NOT NULL PRIMARY KEY NONCLUSTERED (Id) NOT ENFORCED
)

The NOT ENFORCED syntax above is not recognised and when building/deploying it will be replaced with the following syntax which is not then supported in Fabric Warehouses. When creating primary and foreign keys, the NOT ENFORCED syntax must be used (reference).

CREATE TABLE [dbo].[tablev3] 
(
    [Id] INT NOT NULL,
    PRIMARY KEY NONCLUSTERED ([Id] ASC)
)

---

References

SQL Database Projects extension – Azure Data Studio | Microsoft Learn

Data Warehousing in Microsoft Fabric – Microsoft Fabric | Microsoft Learn

Best practices for serverless SQL pool – Azure Synapse Analytics | Microsoft Learn