SQL Server – Issues I have with SSDT based deployments: Part 2

In my previous post, I alluded to some of the issues I’ve found using SSDT based deployments into a live server, with lots of data.  For me, these are the configuration options that could cause significant pain performing a live release using the SSDT dacpac publish, that don’t cause a problem in development.

In this post, I’m going to look at the Database settings, and in particular, how filegroups behave when you perform a deployment.

2. Deploy Database Properties

DatabaseProperties.png

When discussing some of these settings with my devlopers, they were very quick to point out that they really didn’t care if the database was in the full or simple recovery model, and whether user data was on the PRIMARY filegroup or a specific user defined filegroup.  Their database worked, in development, with a little bit of data.  Their log didn’t fill up (because they dropped and recreated the database daily), and they really weren’t bothered about the log growing by 10%, or the primary file growing in 1MB chunks.

In production, we really care about these settings.  In fact, I’ve written before about creating processes to standardise database creation, and to ensure that filegrowth settings are setup correctly and that the PRIMARY filegroup is seperated from user data.  There’s also a whole bunch of other settings that are actually pretty important, from a performance and a security point of view, such as:-

  • Collation
  • Auto Shrink
  • Page verification
  • Snapshot isolation
  • Database chaining
  • Trustworthy

And so on.

By leaving the Deploy database settings option ticked, I’m putting my live database at risk of having these settings changed to those from the SSDT project, and in my experience, my developers don’t even open those properties to check that they’re anything other than the default.  Some of the defaults are horrible.  I can’t think of a reason that I would want to turn Page verification to anything other than CHECKSUM, yet SSDT by default sets it to NONE.  Hello to not knowing about corruption.  The default filegroup is set to PRIMARY, in direct contravention to what I like to do (more on this in a bit).  The default recovery model is FULL; this is fine, but we need to know about it to ensure that log backups are set up.

Worse still, they could well turn on features that open up security holes, such as Database Chaining and turning on the Trustworthy flag.  Next deployment, my database will have a big elevation security hole that could be exploited!

The solution to this, is to ensure that all the database properties are controlled properly in the project; an easy way to do this, is to create the database first, and import it into the project.  What irritates me, though, is that these can’t then be locked down: there’s nothing to stop me setting them all up, and someone going in and changing them.

3. Changing the default filegroup

Closely linked to the above setting, even after turning off the deploy database settings option, I was still finding that one of the database projects repeatedly changed the default filegroup back to PRIMARY.  Now, my understanding from turning off deploy database settings is that no database settings would be changed.  In this instance, there was a critical issue:-

DefaultFilegroupChange.png

The default filegroup in the project was set to PRIMARY, and furthermore, the only option was PRIMARY.  Basically, the project did not have a script to create the data filegroup we wanted to use, so even though this was created in my target database and set to the default, on deployment the script that was generated changed the default to PRIMARY and created all the new objects there.  Which would result very quickly in a problem, as it was capped at 100MB.

The only way to prevent this from happening was to ensure that the script that created the filegroup was present in the project, and that the default filegroup was set correctly, regardless of the deployment option to turn off database settings changes.

For me, this is something that developers don’t care about (I’m not going to argue whether they should) but seeing as they are the guys that create the projects and start to develop, this can actually be a bit of an issue when it comes to deploying to live.

That’s it for now; next up will be looking at the option to prevent data loss, and how it is, frankly, a bit of a pain.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s