Creating a replicated instance of Nav - indexed views issues?

Business Central/NAV Technical Forum
1

Hi all,

I’ve got my hands on a couple of new SQL servers and I would like to set up a replicated instance of NAV. On a different database (which is a reporting DB which accesses the NAV DB amongst other things) I was able to successfully set up replication between the two servers. However, with the Nav instance, we have some indexed views which subsequently fail when I try to connect the client to the replicated (subscriber) database.

The error states ‘Hint noexpand’ on object … is invalid’ for several views.

Has anyone been able to set up a replicated instance of NAV which uses indexed views and got around this problem?

The error message (seem to be unable to upload images):

"The following SQL Server error or errors occured when accessing the Investment Ledger Entry Table:

8171,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server]Hint ‘noexpand’ on object 'dbo/…Entry$VSIFT$10’is Invalid.

8171,“42000”,[Microsoft][ODBC SQL Server Driver][SQL Server] Statements(s) could not be prepared."

This occurs for quite a few tables/views, not just the Investment Ledger Entry table.

Many thanks in advance.

2

I’m curious, why did you decide to use replication for this? Also what type of replication?

3

I wanted a second, instantly available backup in the event of a catastrophic failure of the primary server. Point the clients to the secondary server, and everyone could be back up and running within a couple of minutes. It is using transactional replication.

I’ve got a feeling I’m going to just have transactional log shipping instead and have the secondary server kept up to date in that manner, however, so could lose up to 15 mins worth of transactions in a failure which would be acceptable, considering.

4

Are the two servers on the same LAN? If so, you might consider SQL mirroring in High Availability mode.