Open Forum

Expand all | Collapse all

Converting from Native to SQL.

  • 1.  Converting from Native to SQL.

    Posted Oct 29, 2014 02:57 PM
    Hello,

    We've been in the process (albeit a slow one) of finally getting off of 3.7 native and onto 2013 (more likely 2015, though). We've been playing around with how we want to execute this move given that we want to bring the ~70GB worth of data over to the new version, and also obviously need to get over to SQL.  

    To make a long story short, we've gotten to 2009 R2 classic (on a test system) and we are now trying to convert from native to SQL within 2009 R2. Everything seemed to be going fine until we went to restore the native version of the database in the newly created 2009 R2 database, when after several hours we got a message that there wasn't "enough memory to continue the current activity" and the restore stopped.

    My question is what settings can we alter on either the NAV side or the SQL Server side to get this restore/migration to finish?

    The SQL database that we are restoring to is on a separate server with 64GB of RAM, and when the restore stopped, the SQL exe was using 55GB of it.

    Any advice would be appreciated. And if any other info is needed, please let me know.

    Thanks!

    ------------------------------
    Corey Smith
    Programmer Analyst
    ANICA, Inc.
    Seattle WA
    ------------------------------


  • 2.  RE: Converting from Native to SQL.

    TOP CONTRIBUTOR
    Posted Oct 30, 2014 08:59 AM
    The process of getting a database from FDB to SQL is a bit more involved than simply doing an FBK restore.  The way that dates and code fields are stored is different between the two, and there almost always has to be data cleanup in the FDB prior to doing the restore.  The out of memory error doesn't mean that the server ran out of memory; it means that the FINSQL.exe process ran out of its own memory, and since that is a 32 bit application, it can only reference 2GB of ram.  My experience is that you get that error because of data problems, not because of server hardware resources.

    Also be aware that you cannot just do an FBK restore from an older version into 2013 or newer because the data is now stored in UNICODE, so your best bet (until you are ready to do an actual NAV upgrade including objects) is to target 2009R2 with the latest update rollup.  Don't think about 2013 or newer until you get this onto SQL in 2009R2.

    Other caveats - there may be some object changes (code changes) that also have to be done, as some behaviours are different for table locking.  You'll want a lot of user testing prior to going live to reveal code problems.

    I'll do a little research to see if I can find the conversion instructions from Microsoft.  But for planning purposes, your steps will look something like this (I am greatly simplifying here):

    Copy the FDB from last night's Hotcopy (you are using hotcopy for backups every night, yes?) to your new SQL server
    Open that FDB with 2009R2 classic client fin.exe - this will convert the data from 3.7 to 2009R2 on the SQL server
    Run the data examination steps from the Upgrade Toolkit for 2009R2
       This step examines code and date fields
    Correct the data problems 
    Test the whole integrity of the database
    Correct any key problems from the integrity tests
    Create a new FBK from this cleansed FDB
    Restore FBK with finsql.exe onto SQL server
    Have users test extensively - chart of accounts is sorted correctly, accounting schedules work, etc
    Repeat these steps for your go-live (did I mention the extensive user testing)?

    For a 70G database, I'd allow a full day for the above process, though the time is very dependent on how many data problems exist, and how beefy your SQL server hardware is.

    But don't go live with SQL until everyone has signed off that testing has been successful.

    Kyle Hardin
    Developer and SQL Wrangler
    ArcherPoint


  • 3.  RE: Converting from Native to SQL.

    Posted Oct 30, 2014 10:51 AM
    Thanks for the insight, Kyle.

    To clarify, we weren't attempting a simple FBK restore into 2009R2. We have been following the Upgrade Toolkit write-up for jumping from 3.7 to 2009R2, including the upgrading of the objects (using Mergetool) and running the SQL migration tool (fixed the found data issues, also). And as you also suggested, we're not even thinking about the 2009R2-to-2013 jump yet, as we're probably looking at a two-phase upgrade.

    This has all been done off of an old copy of our production database, and on a separate test server. We are just trying to step through the process to see what exactly is involved time wise before we do anything on a live database. We haven't gotten far enough to test anything, but we most definitely will prior to ever going live.

    Could you give any advice on what sort of data problems may be causing this error? As far as I could tell, we corrected all the date, number errors that came out of the migration tool's field check.

    Thanks again.

    ------------------------------
    Corey Smith
    Programmer Analyst
    ANICA, Inc.
    Seattle WA
    ------------------------------




  • 4.  RE: Converting from Native to SQL.

    SILVER CONTRIBUTOR
    Posted Oct 30, 2014 10:59 AM
    Hi Corey,

    After you run the objects from the migrate.fob to check for bad dates, remove them from the database.  I have found that those objects (in particular one of the codeunits, but I would just remove them all) can cause the client to throw an out of memory error during the restore to SQL.

    Also, I just wanted to confirm that you were not restoring to SQL express as there are database size limitations on express versions.

    ------------------------------
    Mark Miranda
    Director of IT / NAV Technical Architect
    Western Computer
    Oxnard CA
    ------------------------------


  • 5.  RE: Converting from Native to SQL.

    Posted Oct 30, 2014 11:46 AM
    Hey Mark,

    The codeunits are still in there, so I'll definitely remove those and give it another shot. And we are not using the express version.

    Thanks!

    ------------------------------
    Corey Smith
    Programmer Analyst
    ANICA, Inc.
    Seattle WA
    ------------------------------




  • 6.  RE: Converting from Native to SQL.

    Posted Jan 26, 2021 05:48 PM
    Spot on Mark, that was the answer I was looking for!

    ------------------------------
    Justin Arnold
    Western Computer
    Oxnard CA
    ------------------------------



  • 7.  RE: Converting from Native to SQL.

    TOP CONTRIBUTOR
    Posted Oct 30, 2014 11:50 AM
    Hi Corey,
    I wanted to offer you a possible solution to your SQL memory problem that we ran into a couple years ago. Bottom line, if you have taken the default configuration on your new SQL Server, you may be running out of memory. SQL has a nasty habit of consuming all available memory regardless of how much you have on your server. The end result is that, over time, it can "starve" your operating system thus slowing or stopping other Windows Server Operations from functioning. We solved this issue by going into the SQL 2008R2 configuration menu and assigning a min / max range on SQL memory. Once you assign a min / max you will need to reboot the server. Hope this helps!

    ------------------------------
    Aristides (Ari) Smith
    President & CEO
    Next Generation Logistics, Inc.
    Inverness IL
    ------------------------------




  • 8.  RE: Converting from Native to SQL.

    Posted Oct 30, 2014 12:06 PM
    Ari,

    Funny that you mention that, because our director of IT had JUST mentioned that to me prior to me logging back on here. We are going to try this, along with removing the migration objects, and see where that gets us.

    Thanks!

    ------------------------------
    Corey Smith
    Programmer Analyst
    ANICA, Inc.
    Seattle WA
    ------------------------------




  • 9.  RE: Converting from Native to SQL.

    TOP CONTRIBUTOR
    Posted Oct 30, 2014 11:53 AM
    If you have sanitized the data, then it could be an object problem.  I have had issues with the upgrade toolkit objects, as well as any legacy SQL Monitoring objects (in the 150,000 range) and the 20 million range special tables.

    The best way to locate which object(s) are causing your trouble is to bring in objects to a blank SQL database in ranges.

    In your FDB, go into object designer and create a FOB of all objects.
    Create a blank SQL database
    Load the FOB in pieces into SQL- just tables, just tables in a certain object range (1..99999), just codeunits, etc.  It will take you some iterations to locate the problem objects.  This is creating an object-only database, but you don't care about keeping this database - the point is to figure out which objects are the problem.

    Once you know which object(s) are the problem, just delete those out of your FDB and then make a fresh FBK.  You can always attempt to load the problem objects into the new database once everything is restored, assuming you want to keep those objects. Delete the object-only SQL database, and try a restore of the FBK.

    You may also have to break the FBK restore into pieces - just do objects and common data for the first pass, then company specific data for the second pass - but the problem is probably objects.

    ------------------------------
    Kyle Hardin
    NAV Developer
    ArcherPoint LLC
    Lawrenceville GA
    ------------------------------



  • 10.  RE: Converting from Native to SQL.

    Posted Oct 30, 2014 12:07 PM
    Kyle,

    If setting the limits and removing the necessary objects do not do the trick, this will be the next thing I try.

    Thanks, again.

    ------------------------------
    Corey Smith
    Programmer Analyst
    ANICA, Inc.
    Seattle WA
    ------------------------------




If you've found this thread useful, dive deeper into User Group community content by role