FRx with NAV2009 / SQL 2008 64bit

Skeeve · 2010-03-22

Hi everyone,

Please imagine for a minute you have a NAV 2009 (classic client) implementation on SQL 2008 64 bit in a validated environment. This means that you cannot downgrade to SQL2005 and/or 32bit.

FRx does not officially support 64bit on any version of SQL. It also does not easily support SQL2008, although I was told there is a way to make it work for SQL2008/32bit (modifying the FRx DTS packages).

I am now working on a solution using SQL replication to real-time mirror the production NAV DB from 2008/64bit to a 2005/32bit server which the MS Integration Designer (Data Refresher) uses to pull the data from. One of the major problems I ran into was/is the fact that a NAV2009 DB on SQL2008 is using a different collation than on SQL2005 (Latin1_General_100_CS_AS (SQL2008 is build #s 10.0.xxx) versus Latin1_General_CS_AS).

Replication requires both DBs to use the exact same collation but Latin1_General_100_CS_AS is not available on SQL 2005. That being said, it looks like my only option is to change the collation on SQL2008 to Latin1_General_CS_AS. Unfortunately, you cannot do that if you have views depending on tables (i.e. VSIFT). So I am creating a new DB, switching it to Latin1_General_CS_AS and restoring an FBK (which requires re-validation in a validated environment, so it's not a smooth ride ](*,) )

Has anyone worked on a similar scenario and found a different (easier) solution?

DenSter · 2010-03-22

Just thinking out loud here: can you temporarily disable VSIFT, then change the collation, then re-enable VSIFT? Wouldn't that recreate the VSIFT with the new collation?

Skeeve · 2010-03-23

Yeah, that could be an option. Due to the urgency, however, I found it faster to recreate the DB (it's very small). But now that red flags are popping up due to validation concerns, I may have to try that approach. VSIFT was the error I got when trying to change the collation. Who knows if there will be other issues but I will try that now. :roll:

Is there a script or something to do that globally :?:

DenSter · 2010-03-23

I've used the Key virtual table before to set the clustered property back with the 4.0 SP1 CI bug. See if you can use that approach for MaintainSIFTIndex. Save all tables in an object file, so you can import the originals back. Filter SumIndexFields <>'', and VALIDATE MaintainSIFTIndex to FALSE. That will even set the object's Modified flag and the object's date and time stamp. Then change the database's collation, and import back your original objects.

Skeeve · 2010-03-23

Duh! Of course and Perform-Tools even comes with a report doing just that, even preserving the date, time and modified flag (99800).

Thanks though for pointing me in the right direction =D>

DenSter · 2010-03-23

You got it, always happy to help

Come back and let us know if you were able to change collations like that

davmac1 · 2010-03-23

I thought the problem with FRX was the bridge product it uses to get the data from the NAV database. Can't you install that on the 32 bit server with the 32 bit SQL Server and then set it up to extract the data from the 64 bit SQL Server?

Supposedly FRX is going to be replaced, but I have not seen any definite schedule for the replacement product. Maybe Microsoft will bite the bullet and upgrade FRX to work with 64 bit.

Skeeve · 2010-03-23

davmac1 wrote:

I thought the problem with FRX was the bridge product it uses to get the data from the NAV database. Can't you install that on the 32 bit server with the 32 bit SQL Server and then set it up to extract the data from the 64 bit SQL Server?

Supposedly FRX is going to be replaced, but I have not seen any definite schedule for the replacement product. Maybe Microsoft will bite the bullet and upgrade FRX to work with 64 bit.

Unfortunately, the "bridge" tool, which is called Microsoft Integration Manager or Data Refresher, must be installed on the SQL server that hosts the NAV DB, because it does not work over the network. Hence all the replication work.

Also, I did get information about the new product, but there is no release date for NAV yet. They are releasing it for all the other Dynamics products first and NAV is actually going to be last. It will be called "Management Reporter" and is supposed to be released for GP in May.

canadian_bacon · 2010-03-31

Skeeve wrote:

Hi everyone,

Please imagine for a minute you have a NAV 2009 (classic client) implementation on SQL 2008 64 bit in a validated environment. This means that you cannot downgrade to SQL2005 and/or 32bit.

FRx does not officially support 64bit on any version of SQL. It also does not easily support SQL2008, although I was told there is a way to make it work for SQL2008/32bit (modifying the FRx DTS packages).

I am now working on a solution using SQL replication to real-time mirror the production NAV DB from 2008/64bit to a 2005/32bit server which the MS Integration Designer (Data Refresher) uses to pull the data from. One of the major problems I ran into was/is the fact that a NAV2009 DB on SQL2008 is using a different collation than on SQL2005 (Latin1_General_100_CS_AS (SQL2008 is build #s 10.0.xxx) versus Latin1_General_CS_AS).

Replication requires both DBs to use the exact same collation but Latin1_General_100_CS_AS is not available on SQL 2005. That being said, it looks like my only option is to change the collation on SQL2008 to Latin1_General_CS_AS. Unfortunately, you cannot do that if you have views depending on tables (i.e. VSIFT). So I am creating a new DB, switching it to Latin1_General_CS_AS and restoring an FBK (which requires re-validation in a validated environment, so it's not a smooth ride ](*,) )

Has anyone worked on a similar scenario and found a different (easier) solution?

Have you considered log shipping to a 32-bit environment and using that for FRx?

Skeeve · 2010-03-31

Yup, I have.

Log Shipping does not work between different versions of SQL. And because the DTS packages of the FRx integration do not work on SQL2008/32bit, we had to go to 2005/32bit, throwing log shipping out the window.

Heck, you can't even restore a backup from a higher version of SQL, so the only way seems to be replication, and that has it's own hoops to jump through.

FRx with NAV2009 / SQL 2008 64bit

Comments

Categories