{"id":100799,"date":"2023-11-26T17:39:47","date_gmt":"2023-11-26T17:39:47","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=100799"},"modified":"2024-09-03T20:04:48","modified_gmt":"2024-09-03T20:04:48","slug":"updates-about-database-projects-for-microsoft-fabric","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/updates-about-database-projects-for-microsoft-fabric\/","title":{"rendered":"Updates about Database Projects for Microsoft Fabric"},"content":{"rendered":"

comp A few weeks ago, I wrote a blog about Database Projects for Microsoft Fabric<\/a>. 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.<\/p>\n

The November updates for Microsoft Fabric<\/a> announces the support for SQLPackage<\/strong>, but I couldn’t find additional details. Database Projects uses SQLPackage<\/strong> to execute its operations.\u00a0If SQLPackage<\/strong> works, database projects should as well.<\/p>\n

\"A<\/p>\n

A new round of tests on the database projects for Fabric shows advances, but it’s still disappointing.<\/p>\n

Database Projects 101<\/h2>\n

A quick remind about some basic concepts:<\/p>\n

    \n
  • Database Projects have the extension .sqlproj<\/em><\/li>\n
  • They are .NET projects<\/li>\n
  • .NET Builds them, generating a resulting compilation<\/li>\n
  • The resulting file uses the extension .DACPAC<\/em><\/strong><\/li>\n
  • SQLPackage<\/strong> works with the DACPAC<\/strong> for its operations<\/li>\n<\/ul>\n

    Database Project Operations<\/h2>\n

    These are the main operations we can make over a database project:<\/p>\n

    Import:<\/strong> Import the schema from a source, creating a new database project<\/p>\n

    Compare:<\/strong> Compare source and target<\/p>\n

    Apply:<\/strong> 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.<\/p>\n

    What doesn’t work<\/h2>\n

    Let’s look at a list of details which still don’t work very well, and finally see what works.<\/p>\n

    ALTER TABLE is not Supported<\/h3>\n

    You can’t make changes to existing tables because ALTER TABLE<\/strong> is not supported on these tools.<\/p>\n

    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.<\/p>\n

    \"A<\/p>\n

    System Views are imported<\/h2>\n

    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.<\/p>\n

    <\/p>\n

    Dynamic Data Mask is not Supported<\/h2>\n

    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.<\/p>\n

    <\/p>\n

    A strange constraint together the tables<\/h2>\n

    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.<\/p>\n

    \"A<\/p>\n

    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.<\/p>\n

    \"A<\/p>\n

    What DOES work<\/h2>\n

    Once you adjust your project to get rid of all the potential failures, it will work.<\/p>\n

    The command prompt works to generate a script:<\/p>\n

    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<\/em><\/p>\n

    You can generate a script directly on Azure Data Studio<\/strong><\/p>\n

    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.<\/p>\n

    \"A<\/p>\n

    You can apply the changes directly from Azure Data Studio<\/strong><\/p>\n

    \"A<\/strong><\/p>\n

    <\/p>\n

    You need to have the same care to avoid potential problems as when generating the script.<\/p>\n

    Important Last Notes<\/h2>\n
      \n
    • I completed all the tests using Azure Data Studio Insiders version<\/li>\n
    • 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<\/li>\n
    • 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.<\/li>\n
    • The need to fix the .sqlproj<\/strong> 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.<\/li>\n<\/ul>\n

      Summary<\/h2>\n

      It’s evolving and it’s evolving fast. But it’s still far from usable in a production environment. Maybe some more months.<\/p>\n

      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.<\/p>\n","protected":false},"excerpt":{"rendered":"

      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……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,159164],"tags":[145424,123645,159041,158997],"coauthors":[6810],"class_list":["post-100799","post","type-post","status-publish","format-standard","hentry","category-blogs","category-microsoft-fabric","tag-azure-data-studio","tag-data-warehouse","tag-database-project","tag-microsoft-fabric"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100799","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=100799"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100799\/revisions"}],"predecessor-version":[{"id":100810,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/100799\/revisions\/100810"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=100799"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=100799"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=100799"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=100799"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}