0

We are currently running on SQL Server 2017 standard edition and for higher traffic weekends like Mothers day we want to go on SQL Server 2017 enterprise version and during non peak hours we want to come back on Standard. We run on AWS EC2 images of respective versions. My question is

  1. Can we just take the backup of 2017 standard and restore on 2017 enterprise? I mean do we have to do an in place upgrade and not a backup and restore
  2. Similarly can we do a restore from 2017 enterprise to 2017 standard during non peak hours? I mean it is mandatory to do an in place downgrade

We tried the backup restore option and running into memory issues

What is the best approach?

Improve this question
10
  • This is a super strange pattern - why do you think changing the edition of the engine you're using will increase performance?
    – George.Palacios
    May 4, 2021 at 7:53
  • to use more memory ?
    – Stephen Morris - Mo64
    May 4, 2021 at 8:04
  • @StephenMorris-Mo64 max memory for standard is 128GB - if they're hitting a bottleneck on memory I'd expect they have enough cash for Enterprise full stop. That's why this is confusing.
    – George.Palacios
    May 4, 2021 at 8:37
  • George, Stephen, yes you guys are correct. we go to enterprise so that we can set higher max memory to cater to higher load. Well i cant comment on how much cash our client has :) , they want us to come back to Standard full stop :)
    – Muzammil Ahmed
    May 4, 2021 at 9:38
  • Here is the chronology 1. Went from Standard to Enterprise , ran for 6 hours . Ran into memory issues 2. Restored the DB back from Enterprise to Standard, still ran into memory issues. Upgraded the box to 8x EC2 large. After which running fine 3. But we want to remain on 4xLarge due to higher cost of 8x Basically we want to find what is causing memory issues when we upgrade or downgrade @George.Palacios
    – Muzammil Ahmed
    May 4, 2021 at 9:42

1 Answer 1

Reset to default
3

Answers to your questions below, this doesn´t necessarily mean that I think it is a good idea, it seems quite a high risk approach to me.

  1. Yes restoring a standard edition db to enterprise edition just works as long as it is not on a later version than the destination server.
  2. Restoring an enterprise edition to a standard edition works AS LONG AS you have not started to use enterprise features

you can query sys.dm_db_persisted_sku_features

see https://www.brentozar.com/blitz/enterprise-edition-features/

Improve this answer
3
  • ... and note that this DMV still show some features as "enterprise only" even they nowadays are available on Standard Edition as of SQL Server 2016 (things such as compression, partitioning, columnstore indexes etc).
    – Tibor Karaszi
    May 4, 2021 at 12:20
  • @stephenmorris , i discussed the aspect of DB using the Enterprise features with my DBA, and for that he said that it is not possible that the DB may be using the Enterprise features because the DB is set to use 2008 compatibility level which is 100
    – Muzammil Ahmed
    May 4, 2021 at 12:50
  • Well the database itself is not going to spontaneously implement enterprise features but I can think of a few that are present even in 2008
    – Stephen Morris - Mo64
    May 4, 2021 at 12:58

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.