Sage 100

 View Only

  • 1.  Sage SQL Planning upgrade

    Posted 07-15-2024 10:36

    I need some advice/planning tips from our SQL experts.

    My client is wanting to upgrade their Sage 100SQL V2020 to V2023. The challenge is they want to stay on the same SQL instance as they have a lot of other programs that are pulling data from that Sage SQL install. Nothing too extreme or complex, they just do not want to rewrite/repoint it all. Of the tables they are pulling, nothing in Sage's table structure will materially change. No Sage 100 3rd parties either, just in-home programs that are pulling data from Sage (ie AR_InvoiceHistoryHeader/Detail, etc)

    I explain to them we cannot do an in-place upgrade and they understand that I need to have another SQL instance for the upgrade. They are pushing me to get creative as they think I can upgrade to that SQL instance and then just back/restore to the existing SQL one or re-migrate back to the original existing one on the same version. Its not that simple but hoping someone can offer me another or easier path.

    Currently I am thinking,

    Install V2022 on app server and point to a new SQL instance.

    Delete/offline the existing MAS_System and MAS-xxx on the old/current SQL instance. May need to copy/backup any non Sage 100 scripts/ jobs to be imported back later.

    Install V2023 on app server and migrate data/system from new SQL instance back to the original SQL instance.

    As a result, Sage is still on the same application server and the original SQL instance name is still being used.

    Thoughts, suggestions, or a better plan for a what will appear to be fake in-place upgrade will be greatly appreciated. We are doing this next month, so we have time and even may wait for SP1 for 2024 too.

    Thank you

    Michael



    ------------------------------
    Michael Davis
    Warren Averett Technology Group
    ------------------------------


  • 2.  RE: Sage SQL Planning upgrade

    Posted 07-15-2024 11:00

    This is an interesting question and I'd like to read the advice people have.

    In the future I suggest keeping  titles as descriptive as possible. Something like "Possible to upgrade Sage 100 Premium to SAME instance (and how?)" might catch more views as I think many members read daily/weekly summaries sent to their inbox and having a descriptive title discussing the issue might catch some added lurkers.



    ------------------------------
    Wayne Schulz
    wayne@s-consult.com
    Schulz Consulting
    (860) 516-8990
    Moodus, CT
    ------------------------------



  • 3.  RE: Sage SQL Planning upgrade

    Posted 07-15-2024 11:34

    Install new temporary SQL instance (where it matches the Live instance exactly... including the same patch level... this is required for the two-way database copies to work).

    Install new Sage pointing at the temporary SQL instance.

    Normal migration and data conversion.

    After everything is done, take final SQL backups of the old pre-upgrade databases, then do backup / restore to copy the upgraded database back to the original instance... SQL commands to re-assert the MAS_User and MAS_Reports permissions. Sage SQL settings tool to point the new Sage at the original SQL instance.  Bullet points from our internal upgrade checklist (because this is something we do fairly regularly).



    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 4.  RE: Sage SQL Planning upgrade

    Posted 07-16-2024 08:19

    What Kevin said, and here's the SQL commands he mentioned. You'd execute this query once for each MAS_* database that you restore back into the original SQL location (execute it, update the USE [MAS_SYSTEM] line to USE [MAS_ABC], execute it, update to USE [MAS_XYZ], execute it, etc):

    USE [MAS_SYSTEM]
    GO
    DROP USER [MAS_Reports]
    DROP USER [MAS_User]
    GO
    CREATE USER [MAS_Reports] FOR LOGIN [MAS_Reports]
    CREATE USER [MAS_User] FOR LOGIN [MAS_User]
    GO
    ALTER ROLE [db_datareader] ADD MEMBER [MAS_Reports]
    ALTER ROLE [db_owner] ADD MEMBER [MAS_User]
    GO



    ------------------------------
    Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
    ------------------------------

    Original Message


  • 5.  RE: Sage SQL Planning upgrade

    Posted 07-17-2024 09:30

    Thank you to everyone. 

    The SQL scripts to move from one instance to another, should work perfectly.

    I was not seeing the forest for the trees and trying to make it harder than it needed to be.

    Thanks again for helping me make this simpler.

    Michael



    ------------------------------
    Michael Davis
    Warren Averett Technology Group
    ------------------------------

    Original Message


Related Content