THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Auto-Injection of Enterprise Edition Features

There’s an interesting scenario that’s come up in creating some new samples for SQL Server 2016.

I know that for many, many years people have asked for a way to limit developer edition to only use standard edition features, or at least to have a way to flag them. I’ve often thought that I’d like a way to set a “target environment” and have the colorization change anything that I can’t use in standard to an obviously different color.

However, previously, if you used developer edition to create a database, as long as you didn’t use any enterprise features, you could then backup and restore that database to a standard edition server.

That’s no longer the case with SQL Server 2016.

If you create a temporal table in a database on developer edition, it automatically chooses PAGE compression for the history file. There is also no DDL to avoid this. The database can no longer be restored on a standard edition server. To work around it, you would need to rebuild the table without compression after it’s created.

I see this as quite a change in behavior. I don’t think that features that are only available in enterprise (or other perhaps Azure) editions should be “auto-injected”.

Clearly, if you script out the database and create a new database on standard edition using the script, it will create the history table without compression. But I suspect that many people create DBs as starter DBs using developer edition, and then deploy them to other editions that don’t support compression.

I’d be interested in thoughts on how common this practice currently is.

Published Friday, June 3, 2016 1:34 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Cody said:

That's a good catch and sounds like something they just didn't think about.

June 2, 2016 9:38 PM
 

TiborKaraszi said:

Wow! Good catch.

June 3, 2016 3:00 AM
 

Brent Ozar said:

Greg, you are one of the most diligent and detail-oriented guys I know. Good catch, and you should totally put in a Connect item for this.

June 3, 2016 7:23 AM
 

Greg Low said:

Thanks Brent

June 3, 2016 11:37 PM
 

Tim McKay said:

Thanks for the post.

We / I have always just conceptualized the Dev edition as Enterprise, but way less cost for development.

From an application development thinking we have always built new databases and the schemas with DDL.  It would seem a best practice as the logical implementation doesn't break with a physical implementation dependency.  The application is doing application things and the database is doing database things.

June 4, 2016 11:28 AM
 

Kevin Fries said:

This is silly but it had me intrigued.

https://msdn.microsoft.com/en-us/library/mt590957.aspx

"The history table is created as a rowstore table. PAGE compression is applied if possible, otherwise the history table will be uncompressed. For example, some table configurations, such as SPARSE columns, do not allow compression."

I wouldn't be surprised if that shows up for an answer on any connect item opened.

June 4, 2016 8:56 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement