T-SQL Tuesday #86: But I can do it in Dev…

T-SQL Tuesday #86: SQL Server Bugs & Enhancement Requests

This month’s T-SQL Tuesday, hosted by Brent Ozar (b/t), is about SQL Server Bugs & Enhancement requests.

You can almost sense it, like a disturbance in the force. There’s that one dev, or one team, and when they start the long walk over you know what it is.

“… Can’t you just enable it on the server? Why are you making this so difficult? We really want/need to use CDC/TDE/(this really cool Enterprise only feature.)”

I am of course talking about Developer Edition just giving you all the features of Enterprise out of the box, and the subject of the following Connect item from Des Norton: https://connect.microsoft.com/SQLServer/feedback/details/496380/enable-sql-developer-edition-to-target-specific-sql-version

There were several legacy servers from before my time, but I’ll focus on just three – all of which were Standard Edition.

  • Server one hosted a database for Application X (“AppX”), consisting of data for Business Unit 1 (“BU1”);
  • Server two hosted a schematically identical database for AppX, but with data for Business Unit 2 (“BU2”)
  • Server three hosted the database for Application Y (“AppY”), a financial application that was to include data from both AppX databases.

Due to the nature of the data and to prevent conflicts of interest, BU1 should never have access to the data for BU2 and vice-versa. AppX didn’t have functionality to implement information firewalls, and existing business policy at the time was the data should be hosted separate. Dev had gone about setting up their own little test lab of 3 servers, using the Developer Edition (as you’d expect.) Dev was to enhance AppX with auditing functionality due to new requirements on the business, and also to provide a solution to integrate the financial data from both AppX databases into AppY. The project had been going on several months prior to my starting, and I joined near the end of the project when UAT was to begin. The Dev had opted to use CDC to provide the requested functionality. You can probably guess what came next.

Msg 22988, Level 16, State 1, Procedure sp_cdc_enable_db

In the end, the options came down to the following:

  1. Redesign the solution to account for the lack of Enterprise edition; or
  2. Dish out the dosh for servers one and two to become Enterprise; or
  3. Undertake a server consolidation exercise, and only upgrade one to Enterprise edition; or
  4. Ensure we have SA, virtualise, and license the host for Enterprise edition.

The business went for 1. Dev had an extra couple of months of redeveloping the solution, which eventually went live. In total the project ended up 4-5 months past the original expected completion date. There’s more that helped to delay the project than just Developer Edition having Enterprise feature sets by default – I don’t know what the communication between the previous DBA and the Dev was like, or whether these were decisions taken during the period between the previous DBA leaving and myself joining. Being able to target Developer Edition to have the feature set of Standard could have saved those few extra months (and a full week of meetings!)

Whilst those running SQL Server 2016 can apply SP1, which brings some Enterprise level functionality to Standard edition, being able to target a specific edition will still help with future Enterprise only feature releases.