The approach of comparing the model to the actual database and creating the modification scripts is nice, but it will just work for very simple update scenarios. Usually you modify a table and need to initialize fields with complex logic, like using a temporary table to calculate values from other tables in the system. Or you modify a 1:n relationship to be n:m. How would you do that with this system?
@@ErikEjlskovJensenWhen working in SSMS you can script a proc as CREATE OR ALTER. But when you copy/paste it into Visual Studio it does not compile, the data tools do not support that syntax. We have to manually change it every time to just CREATE. Again, hoping git integration in SSMS will negate the need to copy it manually.
@@drewskwierskoballaThe typical workflow has always been to develop in SSMS or Data Studio, then copy the contents into VS for source integration. Is there an alternative?
I am still using SQL2016 SSMS for all my coding and Scripts. Then I create a package for the latest versions. I tried the VS2022 SSDT and it was a pain. The separation of the debugger was not nice.
@@GC-qe8vcjust the Microsoft SQL family. If anyone knows of another declarative/state-based library for the other databases they’re welcome to chime in, but I don’t believe any of them have invested the time in this functionality.
@@drewskwierskoballaI hope once the tooling is stable, we see support for other databases. It’s not fun to have one db use state-based version control and another use migration-based, so if the tooling for state-based isn’t cross-vendor, then multi-vendor companies will reach for something like Flyway out of necessity even if they prefer the simpler tooling you guys have built.
How does the publishing of the dacpac handle the addition of a NOT NULL column to a table? When writing a script, I would create the column with a default constraint then remove the default constraint in the next statement to execute. Is this possible?
Good question! By default, we won't just make up data to make that operation succeed so you can get an error. But - there's a publish property (SqlPackage /p:GenerateSmartDefaults) that would fill in a minimal value for columns if null during deployment (like an empty string).
Yes and no - they all can deploy changes to a database. The first key difference is Flyway and Liquibase require you to manually design your database changes ("changesets") instead of defining a single source of truth for your database schema. Flyway does have some support for state-based deployments for a few databases. You don't end up with code that you can quickly read to understand the object definitions. The second key difference is additional tracking tables and information have to be stored on each database such that they can apply the right remaining scripts to run the deployment, where a SQL project (dacpac) deployment dynamically determines the differences. There's nothing wrong with Flyway or Liquibase, but lots of orgs prefer SQL projects because of how well it integrates with the entire dev process and lights up CI/CD capabilities for 1 to 1,000s of databases. And yes, SQL projects are only for the Microsoft SQL family of databases. This includes SQL Server running in a VM or container anywhere, Azure SQL Managed Instance, Azure SQL Database, SQL database in Fabric, or Fabric Data warehouse.
This is great news for database first teams who have a lot of branching and active db development
The approach of comparing the model to the actual database and creating the modification scripts is nice, but it will just work for very simple update scenarios. Usually you modify a table and need to initialize fields with complex logic, like using a temporary table to calculate values from other tables in the system. Or you modify a 1:n relationship to be n:m. How would you do that with this system?
CREATE OR ALTER support? Maybe Git in SSMS negates the need, but this has always been a pain point when editing and copying procs.
You only need CREATE
@@ErikEjlskovJensenWhen working in SSMS you can script a proc as CREATE OR ALTER. But when you copy/paste it into Visual Studio it does not compile, the data tools do not support that syntax. We have to manually change it every time to just CREATE. Again, hoping git integration in SSMS will negate the need to copy it manually.
_cries in SQL Server 2012_
@@jeffbarnard348which script capability are you using in SSMS that doesn’t have a create-only output option?
@@drewskwierskoballaThe typical workflow has always been to develop in SSMS or Data Studio, then copy the contents into VS for source integration. Is there an alternative?
I am still using SQL2016 SSMS for all my coding and Scripts. Then I create a package for the latest versions. I tried the VS2022 SSDT and it was a pain. The separation of the debugger was not nice.
What about PostgreSQL? Is it supported
No - postgres doesn't have a library for declarative development to provide the backend for SQL projects.
What databases other than Microsoft SQL Server, if any, are supported?
@@GC-qe8vcjust the Microsoft SQL family. If anyone knows of another declarative/state-based library for the other databases they’re welcome to chime in, but I don’t believe any of them have invested the time in this functionality.
@@drewskwierskoballaI hope once the tooling is stable, we see support for other databases. It’s not fun to have one db use state-based version control and another use migration-based, so if the tooling for state-based isn’t cross-vendor, then multi-vendor companies will reach for something like Flyway out of necessity even if they prefer the simpler tooling you guys have built.
Is the new project template already available in Visual Studio 2022, ver. 17.12? I can just see the old template...
I guess I need to install the 17.13 preview...
How does the publishing of the dacpac handle the addition of a NOT NULL column to a table? When writing a script, I would create the column with a default constraint then remove the default constraint in the next statement to execute. Is this possible?
Good question! By default, we won't just make up data to make that operation succeed so you can get an error. But - there's a publish property (SqlPackage /p:GenerateSmartDefaults) that would fill in a minimal value for columns if null during deployment (like an empty string).
What about database unit test support? Does the designer work with SDK style projects?
It is supported
Desinger support is not there yet
So this is similar to Flyway or Liquidbase but only for Microsoft SQL Server...
Yes and no - they all can deploy changes to a database. The first key difference is Flyway and Liquibase require you to manually design your database changes ("changesets") instead of defining a single source of truth for your database schema. Flyway does have some support for state-based deployments for a few databases. You don't end up with code that you can quickly read to understand the object definitions. The second key difference is additional tracking tables and information have to be stored on each database such that they can apply the right remaining scripts to run the deployment, where a SQL project (dacpac) deployment dynamically determines the differences. There's nothing wrong with Flyway or Liquibase, but lots of orgs prefer SQL projects because of how well it integrates with the entire dev process and lights up CI/CD capabilities for 1 to 1,000s of databases.
And yes, SQL projects are only for the Microsoft SQL family of databases. This includes SQL Server running in a VM or container anywhere, Azure SQL Managed Instance, Azure SQL Database, SQL database in Fabric, or Fabric Data warehouse.
yawn.