Fabric SQL Database and Visual Studio Project

[DebbieE]

Just been through all the documentation and it hasnt helpe but i guess everything is new. 

I am trying to figure out how to include the Fabric SQL Database and I went through the motions of setting up as I would with an Azure SQL Database. 

I already had Fabric added to devops and cloned to local so I created a database folder in here. and then I crate a sql database because I have SQL Database  projects installed

 

I then created a table.sql file and a storedproc.sql file usually I would then deploy these to the SQL Database but going to deploy from Database Projects,. (then I can stage and commit the files into Devops)  But I cant seem to get any further when it comes to getting the code deployed into the database. 

My question is.... Have I messed up. Is there a completely different way i should be doing this?

[DebbieE]

I think I may have gor somewhere by added the following into my publish.xml

    <IgnoreExtraColumns>True</IgnoreExtraColumns>

    <IgnoreExtraObjects>True</IgnoreExtraObjects>

    <BlockOnPossibleDataLoss>False</BlockOnPossibleDataLoss>

    <AllowIncompatiblePlatform>True</AllowIncompatiblePlatform>

    <DoNotAlterReplicatedObjects>True</DoNotAlterReplicatedObjects>

    <ExcludeObjectTypes>Schema,Table</ExcludeObjectTypes>

Still some testing to do but it has got me a little further

Nope it didnt work. Still cant get any further

[DebbieE]

 OK so I am getting through to this message  

Deploy dacpac: Failed. Could not deploy package. Warning SQL72015:  Msg 368, Level 14, State 1, Line 1 The external policy action 'Microsoft.Sql/Sqlservers/Databases/Schemas/Tables/Create' was denied on the requested resource. Error SQL72045: Script execution error. The executed script: CREATE TABLE [metadatafwk].[tmp_ms_xx_bronze_audit] 

So I know some have you have said it works but it doesnt for me. this is something I have maybe found 

Unfortunately, dacpac incremental publish is not fully supported for Fabric SQL DB because of these restrictions.  I am basically the workspace admin so i shouldnt have a problem? 

Anyway. It would be good to know if there is something I can do or if Fabric SQL DB isnt quite up to speed yet because I havent been able to deploy

[DebbieE]

I had build issues and I was using the new DSK which changes thing so with a bit of rework on the sdk project file its now building but seeminly you cant deploy to the database in the same way you can with an Azure SQL Database. there seems to be an isue working with DACPAC's which is a roadblock that feels like a gap in the GA release.

Here’s the situation:

• I created a SQL Database Project using the new Database SDK (DSK).
• After some adjustments to the SDK project file, I successfully built the project and generated a DACPAC.
• Naturally, I expected to deploy this DACPAC to my Fabric Database the same way I would with Azure SQL Database using sqlpackage /a:Publish.

The problem:
Fabric Database does not currently support DACPAC deployment. There’s no way to publish directly from SSDT or DevOps pipelines using the standard DACPAC workflow. The only workaround is to extract the schema scripts and run them manually or automate custom scripts against the Warehouse SQL endpoint.

This breaks the normal CI/CD pattern for database projects and makes Fabric feel incomplete at this level. For enterprise workflows, DACPAC support is critical.

Questions:

• Is DACPAC deployment on the roadmap for Fabric Database?
• Will Fabric achieve parity with Azure SQL for schema deployment and CI/CD?
• Are there any official migration tools or recommended approaches beyond manual script execution?

[apturlov]

Hi @DebbieE, I did testing and confirm that DACPAC deployment using Publish in the Database project in VS Code from a remote computer works  for Fabric SQL database with no issues. I ran a full experiment in Fabric to research your scenario and my confirmation is solid: you can use a Database project created by Fabric and committed to Git on a remote computer with the correct SSDT (in my case Database project extension in VS Code) to publish changes directly to Fabric SQL database. I can provide more information with the specific steps and screenshots in another post if you need that.

The outstanding question remains: can I use sqlpackage to deploy a DACPAC built in VS Code directly to a Fabric SQL database? I am going to test that next and will report my answer here.

[DebbieE]

Thank you but I am struggling with this specific issue and the documentation isnt really helping. its not specific enough (I have read them)

I have created a Fabric SQL Database

I then created a new folder in my dataengineering workspace for databases

I then opened this folder in visual Studio code.

And then created a SQL Database projeect. from Database Projects. Create Project from database

then I created my folder structure for tables views and stored procedures. 

Then I clicked on Database projects. Right clicked on the project and Publish ' publish to an existing SQL Server

ue Profile (I used by publish xml file)

Chose the database and then clicked publish

Deploy DacPac failed. Could not load packages...... 

So yep. I want to know if this is specifically because its a Fabric database and it cant be done and I need to do something completely different and how to I backtrack not I have all the database project set up like I was used to doing with Azure Database?

from the documentation i can see I hadnt clicked build or checked out the sqlproj file to make sure everything was included which I have now done

[OnurOz]

could you share the exact error message that you're receiving when trying to publish your sql project to fabric? Maybe this could give us more hint. Please add this line into your publish profile:

<DeploymentDebug>true</DeploymentDebug>

 
One important hint: not every sqlproj is same. When you create a sql project using the fabric UI, it creates a different sqlproj file than what you normally create in visual studio...

For instance, below it is the default sql proj created by fabric UI (pay attention to DSP and 

PackageReference tags:

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="2.0.0-preview.5" />
  <PropertyGroup>
    <Name>sqldb</Name>
    <ProjectGuid>{00000000-0000-0000-0000-000000000000}</ProjectGuid>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlDbFabricDatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.SqlServer.Dacpacs.DbFabric">
      <SuppressMissingDependenciesErrors>False</SuppressMissingDependenciesErrors>
      <DatabaseVariableLiteralValue>master</DatabaseVariableLiteralValue>
      <Version>170.0.0</Version>
    </PackageReference>
  </ItemGroup>
  <Target Name="BeforeBuild">
    <Delete Files="$(BaseIntermediateOutputPath)\project.assets.json" />
  </Target>
</Project>

[DebbieE]

I created the SQL project using Visual Studio code and going to terminal. Should I not have done that?

[apturlov]

As correctly mentioned by @OnurOz, DSP in the .sqlproj file should be specific for a Fabric SQL database. Depending on a setting, deployment may fail if the DSP is different. The suggested way is to use a Database project generated by Fabric when a Fabric SQL database is committed to Git.

[DebbieE]

" The suggested way is to use a Database project generated by Fabric when a Fabric SQL database is committed to Git. " But how do you do this. What are the steps?

[OnurOz]

You have to open the fabric portal, navigate to your workspace and create a new SQL Database item. Once created, you can now edit the sqlproj. I strongly suggest having a look into this Microsoft Reactor video: SQL database in Fabric Ep. 5: Application Lifecycle Management with SQL database in Fabric

[apturlov]

Hi @DebbieE, thanks for sharing your journey with Fabric SQL database. You are absolutely correct that managing SQL database schema and implementing CI/CD with a Database project is considered a best practice and is very similar for Fabric SQL database, Azure SQL database, and even a SQL Server database. To answer your specific question, no, you haven't messed up, but you are in the beginning of setting up your database development environment and process, so you'd need more steps before it will be a smooth sailing.

There are two major approaches in database development and management and both can be used to complement each other: code-centric and visual. Code-centric means using a coding IDE for SQL like VS Code or Visual Studio, where the SQL database schema is represented by a set of SQL scripts. This approach is supported by a Database project, that you can use natively in VS Code and Visual Studio. The visual approach would be using graphical user interface like Fabric UI or SQL Management Studio to make schema shanges through UI operations.

In my personal opinion, SQL-based code-centric way is more efficient and straight forward and better aligned with development practices including CI/CD process. If you are not well familiar with that I suggest that you need to experiment a little and learn some basics. Because the database is represented as SQL code it starts from Git integration. Take a look at this article to understand how a Fabric database looks as a code Fabric SQL database source control integration - Microsoft Fabric | Microsoft Learn.

The next suggested steps:

1. Initialize your database with sample data to create a learning sample. You can easily delete it later once you are done with an exercise.

2. Integrate your Fabric workspace with Git and commit your Fabrci SQL database into Git.

3. Create a local development environment with VS Code (my suggestion as much easier and faster cross-platfrom environment) and Database project extension. Checkout your Fabric Git repo and open your Fabric SQL database as a database project in VS Code.

From this point you have flexible options to create your development process: you can develop code-first inside VS Code using SQL or visual and then commit changes into Git and update your SQL database in Fabric workspace from Git. Another approach is database-first, when you make changes to the database in Fabric UI and then commit the changes to Git. Either way gives you the same result as a SQL representation of your database schema in Git that you can use to deploy your database to practically any compatible SQL Server in the world.

For database deployment you, again, have multiple options: you can deploy using built-in Fabric deployment pipelines (Get started with SQL database deployment pipelines - Microsoft Fabric | Microsoft Learn) or you can use an external deployment pipeline in Azure DevOps or GitHub that can deploy from Git to a database SQL endpoint using sqlpackage command line utility (SqlPackage for SQL database - Microsoft Fabric | Microsoft Learn).

All that said, be prepared that some options are more complicated than the others and it will take some time to set the process up to your liking. If you need more support with this topic than this posts allow feel free to message me directly.

Hope this answer helps you get started.

If you find it useful, please consider giving kudos or marking as a solution.

[OnurOz · Accepted solution]

Hi @DebbieE 

If I've understood you correctly, to deploy SQL changes into a Fabric SQL Database that is under source control, you should use the Fabric deployment flow. Change your database code, commit to git (if necessary PR into fabric's branch), then use Source Control => Updates => “Update all” in Fabric UI. Then the item state is applied to the SQL Database.

I guess it publish a SQLproj directly to Fabric SQL Database is not always working ideally, although you could use SqlPackage.exe (with /p:AllowIncompatiblePlatform=true)

I recommend checking this page: SqlPackage for SQL database - Microsoft Fabric | Microsoft Learn and this one: Fabric SQL database source control integration - Microsoft Fabric | Microsoft Learn

Hope that helps

Onur

---

😊If this post helped you, feel free to give it some Kudos! 👍

✅And if it answered your question, please mark it as the accepted solution.

---