Updates about Database Projects for Microsoft Fabric

Comments 0

Share to social media

comp A few weeks ago, I wrote a blog about Database Projects for Microsoft Fabric. It’s an interesting advance, but it was disappointing because it was only available in half. Updating the Fabric Data Warehouse was not possible yet.

The November updates for Microsoft Fabric announces the support for SQLPackage, but I couldn’t find additional details. Database Projects uses SQLPackage to execute its operations. If SQLPackage works, database projects should as well.

A white text on a white background

Description automatically generated

A new round of tests on the database projects for Fabric shows advances, but it’s still disappointing.

Database Projects 101

A quick remind about some basic concepts:

  • Database Projects have the extension .sqlproj
  • They are .NET projects
  • .NET Builds them, generating a resulting compilation
  • The resulting file uses the extension .DACPAC
  • SQLPackage works with the DACPAC for its operations

Database Project Operations

These are the main operations we can make over a database project:

Import: Import the schema from a source, creating a new database project

Compare: Compare source and target

Apply: Apply the comparison between source and target. This can be to bring to the project new objects from the database or update the database with the project changes.

What doesn’t work

Let’s look at a list of details which still don’t work very well, and finally see what works.

ALTER TABLE is not Supported

You can’t make changes to existing tables because ALTER TABLE is not supported on these tools.

This is a huge missing feature, because makes it impossible to use this resource for source control. You can’t update the schema of a table.

A screen shot of a computer

Description automatically generated

System Views are imported

The database project also imports the new System Views used to register information about the Data Warehouse access. This has the potential to cause failures and mistakes.

Dynamic Data Mask is not Supported

If you import the schema from a Data Warehouse after applying Dynamic Data Mask, it imports the script. However, when you try to Build the project, it fails, because it doesn’t support Dynamic Data Mask.

A strange constraint together the tables

A UNIQUE NOT ENFORCED constraint comes together some tables. This is not something we generated, it’s auto generated by the data warehouse, and it has the potential to cause failures on the database project process.

A screenshot of a computer

Description automatically generated

This strange constraint creates… an ALTER TABLE! (wow! This was not supported!) An ALTER TABLE is generated in the script, and it potentially will cause a failure.

A computer code with black text

Description automatically generated

What DOES work

Once you adjust your project to get rid of all the potential failures, it will work.

The command prompt works to generate a script:

sqlpackage /Action:Script /SourceFile:C:\Repos\FabricDW\FabricDW\bin\Debug\FabricDW.dacpac /TargetConnectionString:”Server=4ngkmr53hynexeqmk5pzxcjane-efsof4xhinfepeucrfriwvmmka.datawarehouse.pbidedicated.windows.net; Authentication=Active Directory Interactive; Database=MaltaDW” /OutputPath:C:\Repos\FabricDW\SQLScripts\script.sql

You can generate a script directly on Azure Data Studio

On both scenarios, you need to take care to exclude all the potential failures from the script. After the schema comparison, you can choose which differences you would like to apply and which differences you wouldn’t.

A screenshot of a computer

Description automatically generated

You can apply the changes directly from Azure Data Studio

A screenshot of a computer

Description automatically generated

You need to have the same care to avoid potential problems as when generating the script.

Important Last Notes

  • I completed all the tests using Azure Data Studio Insiders version
  • Once opened, it identified a new version of the Database Project extension. The versions I have installed after the updates are Azure Data Studio Insiders 1.47.0 and Database Project extension 1.4.1
  • Sometimes you still receive a token error mistake on Azure Data Studio, when connecting to Azure. Once you open it as an Administrator the problem is solved.
  • The need to fix the .sqlproj file on notepad (I mentioned this on the previous blog) still happened, but only once and probably because a mixed version of the Data Studio and extension.

Summary

It’s evolving and it’s evolving fast. But it’s still far from usable in a production environment. Maybe some more months.

It’s impossible to not notice how some features are being announced as available in/for Fabric when they still require a lot more work for production environments.

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com