none
SQL 2016 Enterprises to SQL 2017 standard RRS feed

  • Question

  • Hi,

    we are going to migrate SQL 2016 Enterprises to SQL 2017 Standard edition.

     what are the things to take care for this?

    •   Compatibility?
    •   can we upgrade SQL 2016 Ent to SQL 2017 Stand directly ( In-place upgrade)
    •   we are using Rebuild with online /Table partition features in Enterprises. (what are the features differentiate with         standard)
    •   Is there any difference in  Performance compare with SQL 2016 Ent vs SQL 2017Std.
    • we configured Transnational Replication.

      

      


    Friday, October 11, 2019 1:55 AM

All replies

  • See Supported Version and Edition Upgrades; in-place upgrade Enterprise => Standard is not supported.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 11, 2019 5:50 AM
  • No you cannot do in-place upgrade, like Olaf said it is not allowed. What you can do is create a new SQL Server 2017 instance and then move database by backup and restore. You need to be careful when restoring enterprise edition backup on standard edition as it may fail to restore if you are using enterprise features. to see whether you are using run

    select * from sys.dm_db_persisted_sku_features

    If it returns a row, you need to disable that feature, take backup again and then restore.

    There should not be any other issue restoring SQL Server 2016 backup on SQL Server 2017. After restore you can change compatibility level to match that of SQL Server 2017.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, October 11, 2019 6:40 AM
    Moderator
  • *   we are using Rebuild with online /Table partition features in Enterprises. (what are the features differentiate with         standard)

    Online rebuilds are not available in Standard Edition. I don't know off-hand if rebuild on partition level is supported in Standard.

    *   Is there any difference in  Performance compare with SQL 2016 Ent vs SQL 2017Std.

    Yes. There *is* a reason that Enterprise Edition is more expensive. Whether you will actually notice this depends on your workload. This is not like buying a car with less horsepower, which is overall slower. Rather, in Enterprise there are optimizations for specific cases that are not available in Standard. For instance, if you have indexed views, the optimizer will not consider these in Standard Edition.

    If performance is important for you, you should run a test where you emaulate your actual workload.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, October 11, 2019 9:40 AM
  • hi ,

    this one return only " Partitioning "   features.. but this one available @ Std 2017 also.


    Friday, October 11, 2019 12:33 PM
  • Hi KIRUBAKARAN J A,

     

    >>this one return only " Partitioning "   features.. but this one available @ Std 2017 also.

     

    Can you explain what features are only available in the Standard Edition?  Please refer tohttps://www.sqlconsulting.com/archives/before-you-upgrade-to-sql-server-enterprise-edition/

     

    >>we are using Rebuild with online /Table partition features in Enterprises. (what are the features differentiate with         standard)

     

    Online index create and rebuild only support in sql server 2017 Enterprise Edition:  

     

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 14, 2019 9:37 AM