3

In our environment our production server is using MS SQL Server 2008 Enterprise Edition. We have a development and test server that runs MS SQL Server Standard Edition and the table structure between all three environments is exactly the same.

We frequently refresh our dev and test environments from our production database.

I'm looking into turning on data compression in our production environment, but reading this technet article it sounds like we will no longer be able to perform these refreshes. Am I reading this correctly?

Quote from article:

Databases with compressed tables or indexes cannot be restored, attached, or in any way used on other editions.

Will I be forced to upgrade our dev/test DB to Enterprise Edition to continue to refresh our data?

Improve this question

2 Answers 2

Reset to default
2

Correct, an error will be raised when attempting to RESTORE.

Same thing goes if other Enterprise features, such as table partitioning, are used. For those finding this answer in search, usage of Enterprise features are listed in sys.dm_db_persisted_sku_features.

What I would recommend is to use Developer Edition in your development and testing environments, if you don't support Standard in production (it sounds like your app is in-house only). This edition includes the Enterprise engine; it just isn't licenced to be used in production, and will be able to restore the Enterprise backups just fine.

Improve this answer
2
  • Much appreciated. I'd have to check on licensing. It's for a large ERP install, Im not sure if we'd be able to switch to a developer version easily.
    – ProfessionalAmateur
    May 1, 2014 at 22:56
  • 1
    @ProfessionalAmateur: It depends on costs... If Enterprise Edition grows on trees for you, then it might be better to upgrade to that than incur the expense and trouble of having to migrate everything to a new instance of Developer because that will have to be a fresh install. If it doesn't grow on trees, it's probably worth the effort to go to Developer... just being on Standard right now is literally like burning $$$.
    – Jon Seigel
    May 1, 2014 at 23:18
0

Jon is correct. Using Developer Edition in DEV/QA is a great idea when your production SQL Server is Enterprise. Another approach is unpack all your data (of your database in target) to NONE and make a restore on Standard. It isn't cool to do, but if possible can be a fast lifesaver for your problem.

[]'s

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.