Category: SSDT

SSDT Limitations

~ David Cameron ~ 6 Comments

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)

SSDT wants to control everything. It isn’t enough for to just control tables, procs, functions and triggers. SSDT also must control Logins, App Roles, Certificates, Groups.
Unfortunately security controls tend to vary by environment. Production will not look like UAT. For SSDT, everything looks the same.
This would all be fine and dandy except that you cannot control whether SSDT will manage these things. You cannot tell SSDT to ignore logins when running upgrades.

This means storing in version control production:

  1. Logins & passwords
  2. app role passwords
  3. master keys for encryption

Other oddments

  • 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.

Conclusion

The product feels rather like a beta release, bringing even a simple project into production will expose some pretty serious limitation.
However the largest issue is more fundamental: the approach where you are expected to trust the magic box to generate a valid upgrade script. It is very easy to generate scripts that will fail to upgrade the database successfully. Earlier versions of Entity Framework had a similar approach, which they’ve now moved away from. The entire approach is fatally flawed.