Skip to main content

Notifications

Announcements

Join us in the Community for an AMA: December 12th Register Today
News and Announcements icon
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 323

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

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 on at
    RE: BC on premise SQL Replication

    Do you have one or two Business Central servers running when you try to connect to the DR copy?

    I wonder if there can be a BC service running on the original database at the same time as you try to spin up a service on the DR replica?

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

    Hi ,

    We were able to replicate Cronus database from Live to DR server using transactional repl.

    However we were unable to access from BC instance.

    Does repl modify sql tables that results in the error message?

  • KC Lai Profile Picture
    KC Lai 323 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?

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

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 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.

  • 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

  • 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

  • Suggested answer
    Inge M. Bruvik Profile Picture
    Inge M. Bruvik 32,748 Super User 2024 Season 1 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

    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 32,748 Super User 2024 Season 1 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.  

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

News and Announcements

Join us in the Community for an AMA: December 12th Register Today

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,240 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,149 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans