In an earlier post I mentioned some of the limitations of SSDT. It’s worth covering them in a little more detail as they are significant.
The most significant issues relate deployment. Deployment for SSDT involves setting up a publish profile with a destination database to upgrade. This gives you the option of either publishing directly to the database or generating a script to run for the deployment.
Maximalist approach (with no control)
This means storing in version control production:
- Logins & passwords
- app role passwords
- master keys for encryption
- SSDT does not handle multiple file groups. Because clearly nobody would actually use that in production
Single file generated for upgrades
While SSDT provides the option to upgrade the database in place, but this a somewhat risky option for a production database. It is generally preferred to at least have the option to inspect what will be deployed.
Unfortunately for SSDT the upgrade generates a single file. In practice this means is that if the database upgrade fails for any reason recovery is rather complex. You’ll need to work out where the upgrade script failed and then what action you will take from there.