web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / BC on premise SQL Repl...
Small and medium business | Business Central, N...
Suggested answer

BC on premise SQL Replication

(0) ShareShare
ReportReport
Posted on by KC Lai 335

Hi ,

I would like check whether have anyone configured slq replication for BC on premise which replicate database from Live server to Disaster recovery server.  Both servers are running Sql 2019 and BC v21.

We were told by support team that merge repl is not supported. Any suggestion or sharing of experience is appreciated.

Regards

KC

I have the same question (0)
  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,051 Moderator on at
    RE: BC on premise SQL Replication

    I have experience with transactional replication and that works very well for Business Central. And it is absolutely correct that you can not use merge based replication with Business Central. Transactions and updates needs to happen in a specific order to keep your data consistent.

    That is why you should go for transactional replication.

  • KC Lai Profile Picture
    KC Lai 335 on at
    RE: BC on premise SQL Replication

    Hi Bruvik,

    Thanks. Is there any particular configuration at BC on premise server end that I have take note of to meet transactional repl.?

    Or would you mind sharing any documentation on sql repl specific to BC on premise.

    Thanks.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,051 Moderator on at
    RE: BC on premise SQL Replication

    The SQL replication is something totally handled by the SQL server. The Business Central service will have no knowledge of the underlaying SQL database being replicated and does not need that information.  The only thing is if you want to use the read scaleout option that is documented here: learn.microsoft.com/.../database-read-scale-out-configuration

    But that is normally only used for increased performance and not for disaster recovery.

    So if you use  standard SQL transactional replication there is nothing specific Business Central related that you need to take into consideration.  

  • RajanAFON Profile Picture
    RajanAFON 10 on at
    RE: BC on premise SQL Replication

    Hi Bruvik,

    Thanks sharing your experience, Is transactional replication configured for FULL Business Central database ( included all articles)? Interesting to know how Business Central extension program replicate to subscription database in transactional replication.

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,051 Moderator on at
    RE: BC on premise SQL Replication

    Yes, you will be replicating the complete database. And that will also include all tables even those who are table extensions or customized tables.  This is all handled by the SQL server.

    One important thing to notice is that if you want to switch over to your replicated database you will have to change the Business Central service to connect to that database. Unless you are running in a failover cluster as well. But even in a failover cluster you will have to restart the BC service when one cluster node is failing.

  • RajanAFON Profile Picture
    RajanAFON 10 on at
    RE: BC on premise SQL Replication

    Thanks Bruvik, We are getting below messages from SQL Server 2019 (Standard version), Is full database transactional replication achievable in this version? 

    pastedimage1679544444166v1.png

  • Ivan y Profile Picture
    Ivan y 5 on at
    RE: BC on premise SQL Replication

    Hello Inge,

    Rajan and I have tried the full replication process but when we tried to switch to the DR server to start the BC instance we are not able to do so.

    Not too sure which part of the configuration have we missed out.

    Appreciate if you can help take a look.

    Windows log showing these details:

    The description for Event ID 701 from source MicrosoftDynamicsNavServer$BC210 cannot be found. Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.

    If the event originated on another computer, the display information had to be saved with the event.

    The following information was included with the event:

    Server instance: BC210

    Category: Sql

    ClientSessionId: 00000000-0000-0000-0000-000000000000

    ClientActivityId: 64cadbbe-ba31-41cc-9496-9f89e5de54e0

    ServerSessionUniqueId: 00000000-0000-0000-0000-000000000000

    ServerActivityId: 769e3a56-f7c8-413d-84ee-8c53b3cdf7d5

    EventTime: 03/23/2023 06:34:41

    Message (NavSqlException): Exception happened during execution of a threshold action.

    Exception type: NavSqlException,

    Threshold tag: 000007L,

    Execution time over threshold: False,

    Execution time: 2 ms,

    Threshold: 750 ms

    Message: Long running SQL statement

    Task ID: 1

    Connection ID: 1

    SQL server session ID: 51

    Client Type:

    Database Server Name: DRXX,1439

    Database Name: DRCRONUS

    Current Company Name:

    Statement: UPDATE [SQLDATABASE].dbo.[Server Instance] SET [Status] = 2 WHERE [Server Computer Name] = @1 AND [Server Instance Name] = @2 AND [Status] <> 1;INSERT INTO [SQLDATABASE].dbo.[Server Instance] ([Service Name],[Server Computer Name],[Last Active],[Server Instance Name],[Server Port],[Management Port],[Status], [Last Tenant Config Version])VALUES (@0,@1,GETUTCDATE(),@2,@3,@4,0,0) SELECT CAST(SCOPE_IDENTITY() AS INT)

    ParentException: NavSqlException

    An unexpected error occurred after a database command was cancelled.

    ExceptionStackTrace:

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ThrowNavSqlException(SqlExceptionAdapter exceptionAdapter, Boolean transactionNoLongerValid, String commandText, Boolean isLastExceptionARollbackCause, Boolean logExceptions, NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.MapException(SqlExceptionAdapter exceptionAdapter, String commandText, Boolean isRollbackAction, Nullable`1 timeout, NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.<>c__DisplayClass129_0`1.<ExecuteFunction>b__0()

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func`1 function, String commandText, SqlCommand sqlCommand, Boolean isAdoCommand, NavCancellationToken cancellationToken)

    InnerException:

    RootException: SqlException

    Cannot insert the value NULL into column '$systemModifiedBy', table 'DRCronus.dbo.Server Instance'; column does not allow nulls. INSERT fails.

    The statement has been terminated.

    ExceptionStackTrace:

    at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

    at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

    at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

    at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()

    at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()

    at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)

    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)

    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)

    at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)

    at Microsoft.Data.SqlClient.SqlCommand.ExecuteScalar()

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.<>c__DisplayClass129_0`1.<ExecuteFunction>b__0()

    CallerStackTrace:

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.FailedSqlOperationExceptionFilter(Exception e, EventTask task, String commandText, Int64 startMilliSeconds, Boolean& recursionDetected)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func`1 function, String commandText, SqlCommand sqlCommand, Boolean isAdoCommand, NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ThrowNavSqlException(SqlExceptionAdapter exceptionAdapter, Boolean transactionNoLongerValid, String commandText, Boolean isLastExceptionARollbackCause, Boolean logExceptions, NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.MapException(SqlExceptionAdapter exceptionAdapter, String commandText, Boolean isRollbackAction, Nullable`1 timeout, NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.<>c__DisplayClass129_0`1.<ExecuteFunction>b__0()

    at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteFunctionWithTrace[T](EventTask task, Func`1 function, String commandText, SqlCommand sqlCommand, Boolean isAdoCommand, NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.NavSqlCommand.ExecuteScalar(NavCancellationToken cancellationToken)

    at Microsoft.Dynamics.Nav.Runtime.Heartbeat.CreateServiceInstanceRecord(NavDatabase database)

    at Microsoft.Dynamics.Nav.Runtime.Heartbeat.<>c__DisplayClass62_0.<WriteHeartbeatToServiceInstanceTable>b__0()

    at Microsoft.Dynamics.Nav.Runtime.NavPerformanceCounterSetter.UpdatePerformanceCountersWithDuration(Stopwatch stopWatch, Action action, NavPerformanceCounterEntry performanceCounterEntry)

    at Microsoft.Dynamics.Nav.Runtime.Heartbeat.WriteHeartbeatToServiceInstanceTable()

    at Microsoft.Dynamics.Nav.Runtime.PeriodicScheduler.RunAction()

    at Microsoft.Dynamics.Nav.Runtime.ThreadPeriodicScheduler.DoScheduledAction()

    at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)

    at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)

    at System.Threading.ThreadHelper.ThreadStart()

    ProcessId: 8040

    Tag: 000007T

    ThreadId: 20

    CounterInformation:

    CustomParameters: {

    }

    GatewayCorrelationId:

    The message resource is present but the message was not found in the message table

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 1,051 Moderator on at
    RE: BC on premise SQL Replication

    Are the user permissions the same for the two SQL servers ?

    Are you running Business Central as single tenant or multi tenant?

    If you are running multi tenant you need to replicate the tenant database as well.

  • Arul.NP Profile Picture
    Arul.NP 5 on at
    RE: BC on premise SQL Replication

    Hi Inge, yes the permission are same. This is single tenant only.

    what else could cause the issue? Please advise.

    Between we are using SQL server 2019 standard edition.

  • KC Lai Profile Picture
    KC Lai 335 on at
    RE: BC on premise SQL Replication

    Hi ,

    We were able to complete BC database to DR server using transactional repl. However we were unable to access from BC instance.

    Does repl modify the sql table?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Pallavi Phade – Community Spotlight

We are honored to recognize Pallavi Phade as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Small and medium business | Business Central, NAV, RMS

#1
Sumit Singh Profile Picture

Sumit Singh 2,039

#2
Rishabh Kanaskar Profile Picture

Rishabh Kanaskar 1,863

#3
YUN ZHU Profile Picture

YUN ZHU 1,736 Super User 2025 Season 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans