SSDT Limitations

~ David Cameron

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.

Published by David Cameron

6 thoughts on “SSDT Limitations”

  2. Hi Javier,

    I'm not sure whether you can use SSDT for Windows Phone. It seems to be more focussed on SQL Server, which I'm reallt not sure would be a good idea (or available) on Windows Phone.

    That aside, I really wouldn't be recommending using SSDT.

    Like

    Reply

  3. Hi David,

    When you deploy you can use a deployment contributor to filter the steps, so you can stop it dropping users that don't exist in the dacpac or anything you like really (filegroups etc).

    You have to write your dpeloyment contributor in .net or I have a generic one:

    http://agilesqlclub.codeplex.com/

    With more details:

    https://the.agilesql.club/Blogs/Ed-Elliott/HOWTO-Filter-Dacpac-Deployments

    SSDT is great and the extensible api makes it really good for multiple environments, even if you do have to do smoe up front work to get it going.

    Like

    Reply

  5. Hi Ed,

    nice to see this, it looks like it would resolve the issues I'd experienced.

    It's a pity this isn't available out of the box and that you need to write code to enable this. I'd expect this to be a configuration option, ideally say a config file you could pass to this.

    Like

    Reply

Leave a reply to Javier Siancas Cancel reply