Mind-blown by the SQL DB & Fabric sync-up. Drew nailed it! From branching to auto-commits, got tons of value. Source control for DBs just leveled up. Can't wait to apply these strategies!
Great explanation of source control in Fabric! I've been struggling to manage changes in my database, will definitely give this a try. Thanks for sharing!
As an SQL Developer using version control for many years, the two examples of branching and updating tables cover the simplest scenarios; 1. how does this cope with changes on multiple branches that affect the sqlproj file which would usually result in a merge conflict - manually merging the changes is something that trips up many SQL developers, it would likely also be a problem for application developers. 2. The columns you added were both NULL which is simple for updating an existing database, but if you're trying to add NOT NULL columns to an existing table which has data a custom script will be required, how would the Fabric workspace handle that? 3. when refactoring SQL databases in a visual studio project you can track via the refactor log, how would this be understood when the changes are made to the database via a app code update. On the surface this looks like a great addition to Fabric, but I would suggest it comes with all the limitations of existing SQL source control techniques via SQL Server Data Tools / SQL Project files managed in Visual Studio but in a way that users are less able to work around those limitations 🤷♂ sorry for being a pessimist, but curious to know if there's anything introduced which addresses even the basic gotchas of database code management....
Don't apologize, I love the questions! For 1 - the original SQL projects files have a file include entry for every file that is the root of 90-something percent of the merge conflicts (ugh). The rest of the SQL projects ecosystem are in the midst of moving to SDK-style SQL projects and this is all Fabric will ever know - a succinct and merge-friendly project file format. It's not just SQL database in Fabric that's getting improved for database development, the improvements for SDK-style SQL projects apply to the whole SQL product family. (yay) For 2 - at this moment with SQL projects in general (Visual Studio, VS Code, SqlPackage), if you want to deploy a not-null column and want it to "just work" there's a publish property "/p:GenerateSmartDefaults" that will use a baseline value for existing data based on the data type. You would set this as an additional parameter on the dacpac publish either temporarily or ongoing to enable easily adding null columns to environments with existing data. This publish option isn't enabled in Fabric but is something we expect to add by mid-year. For 3 - refactorlog in SSDT is an immensely powerful tool for renaming and moving tables between schemas, and you're correct that it is more complex than the Fabric interface. While the underlying SQL project format is compatible between Fabric, Visual Studio, VS Code, and SqlPackage - the more advanced features like refactorlog and deployment contributors are likely to stay in the client tooling. Tracking changes made to the database in Fabric relies on checking the git commit history - either the list of commits or the changes a commit/PR like at 11:50 in the video. I totally agree that database code management from basic visibility to deployments is full of hidden traps but I'm also an optimist that we can make development for SQL better across the board while making SQL database in Fabric the easiest possible place to have your code in source control. I love learning from folks like yourself the things that have tripped you up the most so we can make that better, thank you for bringing all those questions up!
Mind-blown by the SQL DB & Fabric sync-up. Drew nailed it! From branching to auto-commits, got tons of value. Source control for DBs just leveled up. Can't wait to apply these strategies!
Great explanation of source control in Fabric! I've been struggling to manage changes in my database, will definitely give this a try. Thanks for sharing!
Great stuff! Thanks!
As an SQL Developer using version control for many years, the two examples of branching and updating tables cover the simplest scenarios;
1. how does this cope with changes on multiple branches that affect the sqlproj file which would usually result in a merge conflict - manually merging the changes is something that trips up many SQL developers, it would likely also be a problem for application developers.
2. The columns you added were both NULL which is simple for updating an existing database, but if you're trying to add NOT NULL columns to an existing table which has data a custom script will be required, how would the Fabric workspace handle that?
3. when refactoring SQL databases in a visual studio project you can track via the refactor log, how would this be understood when the changes are made to the database via a app code update.
On the surface this looks like a great addition to Fabric, but I would suggest it comes with all the limitations of existing SQL source control techniques via SQL Server Data Tools / SQL Project files managed in Visual Studio but in a way that users are less able to work around those limitations
🤷♂ sorry for being a pessimist, but curious to know if there's anything introduced which addresses even the basic gotchas of database code management....
Don't apologize, I love the questions!
For 1 - the original SQL projects files have a file include entry for every file that is the root of 90-something percent of the merge conflicts (ugh). The rest of the SQL projects ecosystem are in the midst of moving to SDK-style SQL projects and this is all Fabric will ever know - a succinct and merge-friendly project file format. It's not just SQL database in Fabric that's getting improved for database development, the improvements for SDK-style SQL projects apply to the whole SQL product family. (yay)
For 2 - at this moment with SQL projects in general (Visual Studio, VS Code, SqlPackage), if you want to deploy a not-null column and want it to "just work" there's a publish property "/p:GenerateSmartDefaults" that will use a baseline value for existing data based on the data type. You would set this as an additional parameter on the dacpac publish either temporarily or ongoing to enable easily adding null columns to environments with existing data. This publish option isn't enabled in Fabric but is something we expect to add by mid-year.
For 3 - refactorlog in SSDT is an immensely powerful tool for renaming and moving tables between schemas, and you're correct that it is more complex than the Fabric interface. While the underlying SQL project format is compatible between Fabric, Visual Studio, VS Code, and SqlPackage - the more advanced features like refactorlog and deployment contributors are likely to stay in the client tooling. Tracking changes made to the database in Fabric relies on checking the git commit history - either the list of commits or the changes a commit/PR like at 11:50 in the video.
I totally agree that database code management from basic visibility to deployments is full of hidden traps but I'm also an optimist that we can make development for SQL better across the board while making SQL database in Fabric the easiest possible place to have your code in source control. I love learning from folks like yourself the things that have tripped you up the most so we can make that better, thank you for bringing all those questions up!