locked
Downgrade SQL Server 2012 Edition (Ent to Std) with SSIS on same Server RRS feed

  • Question

  • We got SQL Server 2012 with SSIS running on Enterprise Edition and need to downgrade it to Standard Edition. We need to uninstall SQL Ent and re-install Standard Edition.

    I am looking for instruction around backup, restore and post-testing of SSIS part.

    The document "Backup, Restore, and Move the SSIS Catalog" has instruction but not sure if I need to follow exact same instructions for SQL 2012 ? 

    I found another article where the guy the mentioned the easiest way is

    1. Back up the SSIS Database on Old Server
    2. Create an SSIS catalog on the new server.
    3. Restore the SSIS catalog database from the old server over the newly created catalog.
    4. Restore the original password use to create the catalog on the old server.

    Can anyone provide any feedback or any step-by-step instructions for SSIS.

    Saturday, August 3, 2019 3:10 PM

Answers

  • Hi MiamiKK,

    I hope you read the article provided by Zoe via link.

    It shows an interesting technique.

    • You copy and keep on a side all system databases.
      And copy them back to the original location, and overriding newly installed instance DBs.
    • You keep as-is all user databases such as SSISDB.

    So that method doesn't require to restore the SSISDB.

    • Edited by Yitzhak Khabinsky Monday, August 5, 2019 7:15 PM
    • Marked as answer by MiamiKK Monday, August 5, 2019 11:27 PM
    Monday, August 5, 2019 7:14 PM

All replies

  • Hi MiamiKK,

    This link provides the step by step process with screenshots to downgrade from Ent to Std.

    Please refer to Downgrade from SQL Server Enterprise Edition to Standard Edition.

    Hope it helps.

    Regards,

    Zoe


    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

    • Proposed as answer by Zoe Gu Wednesday, August 7, 2019 5:22 AM
    Monday, August 5, 2019 3:01 AM
  • Hi Zoe, I am looking for instructions for SSIS downgrade.
    Monday, August 5, 2019 5:03 AM
  • Hi MiamiKK,

    May I know whether your problem is backuping and restoring SSISDB?

    Regards,

    Zoe


    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, August 5, 2019 7:06 AM
  • Hi Zoe, the confusion is around SSISDB restoring after SQL Server is restored.  As part of SQL Server system databases restore (as outlined in article you linked), the SSISDB should get attached to SQL instance as well along with master/msdb databases. Do I still need to restore SSISDB ?

    If I still need to restore SSISDB, then considering the MASTER KEY, ##MS_SSISServerCleanupJobLogin## job, SSIS Server Maintenance Job, master.dbo.sp_ssis_startup stored procedure will get restored as part of master/msdb databases restore, do I need to simply perform the below tasks ?

    1. Create an SSIS catalog on the new server.
    2. Restore the SSIS catalog database from the old server over the newly created catalog.
    3. Restore the original password use to create the catalog on the old server
    Monday, August 5, 2019 6:48 PM
  • Hi MiamiKK,

    I hope you read the article provided by Zoe via link.

    It shows an interesting technique.

    • You copy and keep on a side all system databases.
      And copy them back to the original location, and overriding newly installed instance DBs.
    • You keep as-is all user databases such as SSISDB.

    So that method doesn't require to restore the SSISDB.

    • Edited by Yitzhak Khabinsky Monday, August 5, 2019 7:15 PM
    • Marked as answer by MiamiKK Monday, August 5, 2019 11:27 PM
    Monday, August 5, 2019 7:14 PM
  • My question is... do you "HAVE TO" uninstall the other components (SSIS, SSRS, SSAS) or can you simply uninstall the database engine and re-install the database engine.  Going from SQL Server 2012 Enterprise to SQL Server 2012 Standard edition.

    Tuesday, August 6, 2019 6:38 PM
  • Hi MiamiKK,

    Have you resolved your issue? Would you please kindly give us some feedback? 
    I hope you have read my link. As Yitzhak said before, you don't have to restore the SSISDB.

    Regards,
    Zoe

    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

    Wednesday, August 7, 2019 5:25 AM
  • My question is... do you "HAVE TO" uninstall the other components (SSIS, SSRS, SSAS) or can you simply uninstall the database engine and re-install the database engine.  Going from SQL Server 2012 Enterprise to SQL Server 2012 Standard edition.

    All those 3 components (SSIS, SSRS, SSAS) similar to Database Engine are edition specific service and need to be downgraded. Even if one of the component is running Enterprise Edition, we would need to pay Enterprise License for whole SQL Server.
    Wednesday, August 7, 2019 7:13 AM
  • Hi Zoe, I was gather details as part of preparation/planning to downgrade the SQL environment. The non-PROD environment downgrade is scheduled for next week and I will post a feedback late next week.

    I was looking for instructions on downgrading of SSIS Service as I have done SQL Server database engine downgrades in the past. Yitzhak's comment is is helpful as I wasn't sure if we can re-attach the SSISDB along with system databases post downgrade or I had to manually configure & restore SSISDB. 

    Wednesday, August 7, 2019 7:23 AM