Open Forum

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
------------------------------

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

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
------------------------------


Original Message:
Sent: 10-30-2014 08:58
From: Kyle Hardin
Subject: Converting from Native to SQL.

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

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
------------------------------

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
------------------------------


Original Message:
Sent: 10-30-2014 10:59
From: Mark Miranda
Subject: Converting from Native to SQL.

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
------------------------------

Spot on Mark, that was the answer I was looking for!

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

Original Message:
Sent: Oct 30, 2014 10:59 AM
From: Mark Miranda
Subject: Converting from Native to SQL.

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
------------------------------

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
------------------------------


Original Message:
Sent: 10-29-2014 14:56
From: Corey Smith
Subject: Converting from Native to SQL.

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
------------------------------

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
------------------------------


Original Message:
Sent: 10-30-2014 11:50
From: Aristides (Ari) Smith
Subject: Converting from Native to SQL.

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
------------------------------


Original Message:
Sent: 10-29-2014 14:56
From: Corey Smith
Subject: Converting from Native to SQL.

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
------------------------------

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
------------------------------

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
------------------------------


Original Message:
Sent: 10-30-2014 11:53
From: Kyle Hardin
Subject: Converting from Native to SQL.

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
------------------------------