Open Forum

moving from SQL 2008 r2 to SQL 2012

  • 1.  moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 07, 2020 09:56 AM
    I am moving databases from the SQL 2008 r2 server to SQL 2012.

    I did a backup and restore to the new server.  After restoring I update the domain in the system database but I still do not see the system or application databases when opening Dynamics SL 2011.

    I can connect to the SQL Server in Database Maintenance and log into the new server, and I can see the system and application databases, and can even run update views.  I tried to run Synchronize All Ownership but it seems to hang and not respond for hours.

     I must be missing a step - any suggestions?
    Thanks

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: moving from SQL 2008 r2 to SQL 2012

    GOLD CONTRIBUTOR
    Posted Jun 07, 2020 10:29 AM
    UPDATE dbo.Domain SET Servername = @@SERVERNAME;

    You may need to set the databases to trustworthy:

    ALTER DATABASE <dbname> SET TRUSTWORTHY ON;



    ------------------------------
    Jonathan Van Houtte
    Architect
    XLstatements, LLC
    Clifton Park NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: moving from SQL 2008 r2 to SQL 2012

    BRONZE CONTRIBUTOR
    Posted Jun 08, 2020 11:45 AM
    It's probably a trust issue.  Run the following on your databases:

    ALTER DATABASE {APP_DB_NAME_GOES_HERE} SET TRUSTWORTHY ON



    ------------------------------
    Cindy Hall
    Velosio
    Louisville KY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 09, 2020 09:30 AM
    Do the 077... and and E8F... SQL Users exist at the server level?  Then check for orphaned users and fix any that need it for each DSL DB.

    exec sp_change_users_login 'Report'

    if they exist at the SQL server level this should fix the orphaned use.
    sp_change_users_login 'auto_fix', '07718158D19D4f5f9D23B55DBF5DF1'

    ------------------------------
    Dwight Neate
    SL Administrator
    OnPoint Group
    Perrysburg OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 09, 2020 04:58 PM
    I guess I need to run in all DB, I did see that it corrected the user but still no luck : (​

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: moving from SQL 2008 r2 to SQL 2012

    GOLD CONTRIBUTOR
    Posted Jun 09, 2020 05:06 PM
    You must run the Synchronize All Database Ownership & Security scenario if this was not an in-place upgrade of SQL Server.

    ------------------------------
    Jonathan Van Houtte
    Architect
    XLstatements, LLC
    Clifton Park NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 09, 2020 05:08 PM
    I let that run overnight and it never completed, I am trying now on 1 database rather than all - but I don't recall it being such a long process.​

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 8.  RE: moving from SQL 2008 r2 to SQL 2012

    GOLD CONTRIBUTOR
    Posted Jun 09, 2020 05:12 PM
    Did you restore the databases again after aborting the Database Maintenance utility?

    I've done hundreds of these and I don't recall it ever taking more than an hour.

    ------------------------------
    Jonathan Van Houtte
    Architect
    XLstatements, LLC
    Clifton Park NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 9.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 09, 2020 05:16 PM
    Good thought - that would probably be best.  I will do that and then attempt again with the smallest db thanks​

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 10.  RE: moving from SQL 2008 r2 to SQL 2012

    GOLD CONTRIBUTOR
    Posted Jun 09, 2020 05:19 PM
    You must use the Synchronize All scenario, not database by database, because the system database also needs updating.

    ------------------------------
    Jonathan Van Houtte
    Architect
    XLstatements, LLC
    Clifton Park NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 11.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 09, 2020 05:23 PM
    Yes - I mean I am just going to try and sync the system to 1 of the application databases to see if it completes.  Then I will restore all the application databases and system db and run it ​again.  I just want to do one so I can make sure it is working, the last time when ran on all dbs I ran it all night and it never completed.

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 12.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 19, 2020 11:21 AM
    Just an update -
    I had to run the script to fix orphan users, then I ran synchronize ownership and all  worked!  Thanks for all the help from everyone.

    Bill


    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 13.  RE: moving from SQL 2008 r2 to SQL 2012

    BRONZE CONTRIBUTOR
    Posted Jun 09, 2020 04:03 PM
    Bill - I believe you would be best to start synchronize ownership and let it run to completion - even if it takes several hours - just minimize the RDP session and check it once in a while.  Staring at that process will make you crazy !

    ------------------------------
    Randy Andrews
    Manager - SL Support
    RSM
    Chicago
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 14.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 09, 2020 05:00 PM
    I let it run overnight with no luck.  I will try again with just 1 db selected rather than all 4 - will let you know. Thanks​

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 15.  RE: moving from SQL 2008 r2 to SQL 2012

    GOLD CONTRIBUTOR
    Posted Jun 09, 2020 07:51 PM

    Hi Bill,

    Did you check the event log of the Sync Ownership?  One problem I encountered is a custom stored procedure created before I started in my role was set up to execute as the 'E8' user.  This created a problem when sync ownership tried to drop the user (can't because of 'execute as') and it caused the sync to fail.

    This might not be your particular scenario, but checking the event log for some possible clues could help.

    Good luck!
    Marc



    ------------------------------
    Marc DeLozier
    Finance SysAdmin
    David Evans and Associates Inc.
    Portland OR
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 16.  RE: moving from SQL 2008 r2 to SQL 2012

    GOLD CONTRIBUTOR
    Posted Jun 19, 2020 11:52 AM
    The Synchronize All Database Ownership and Security scenario deletes and recreates the required SQL Server authentication server logins and their corresponding database users. I.e., you did not need to delete the orphaned database users.

    ------------------------------
    Jonathan Van Houtte
    Architect
    XLstatements, LLC
    Clifton Park NY
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 17.  RE: moving from SQL 2008 r2 to SQL 2012

    SILVER CONTRIBUTOR
    Posted Jun 19, 2020 11:55 AM
    ​Actually the synchronize would not work until I corrected the orphan users - not sure why but my first attempts skipped that step and was never successful.  After running the script on orphan users the Synchronized took just minutes and I was able to proceed.

    ------------------------------
    Bill Welch
    Just Dough It, Inc
    Camdenton MO
    ------------------------------

    Academy - Online Interactive Learning from Experts