The replication of Exact Online to SQL Server needs maintenance on SQL Server. This note gives some guidelines. Consult your local DBA for assistance.
Exact Online Replication to SQL Server
Many sites replicate the contents of Exact Online data using Invantive Data Replicator and the REST and XML APIs to a relational database on SQL Server or other platforms. This scales great up to thousands of Exact Online companies.
However, we have had various reports on SQL Server performance going down over time. Often, replication runtime changes from minutes to hours or from hours to days. This especially occurs on small environments that have been live over months or years, and on large environments that have been live over months.
SQL Server is a great database platform, but still needs some maintenance. A typical error scenario is that the delete of obsolete facts such as General Ledger Transaction Lines takes over 30 minutes even with a batch size of 10.000 rows:
For instance, the dc_event_log_r view or the trace file present an error like:
Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
when executing a SQL Server statement like:
delete top(10000) [DB].dbo.dcd_gyo6s where ( tpn_id$ in (@i1tpn_id$) )
The actual solution is to follow Microsoft’s recommendations for SQL Server maintenance. To avoid timeouts during Exact Online replication execute the following steps:
This solution is platform-specific. SQL Server has a specific implementation of index maintenance with advantages and disadvantages.
On Oracle, MySQL or PostgreSQL, other and or similar recommendations apply.
Please consult your local DBA for optimizing your SQL Server environment.
The defragmentation compacts fragmented indexes, reducing runtime for delete statements as shown above. The following query present a list of possible issues:
select dbschemas.name schema_name , dbtables.name table_name , dbindexes.name index_name , round(indexstats.avg_fragmentation_in_percent, 0) fragmentation_pct , indexstats.page_count , 'ALTER INDEX ' + dbindexes.name + ' on ' + dbtables.name + ' REBUILD' stmt from sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats join sys.tables dbtables on dbtables.object_id = indexstats.object_id join sys.schemas dbschemas on dbtables.schema_id = dbschemas.schema_id join sys.indexes AS dbindexes on dbindexes.object_id = indexstats.object_id and indexstats.index_id = dbindexes.index_id where indexstats.database_id = DB_ID() and indexstats.avg_fragmentation_in_percent > 25 order by indexstats.avg_fragmentation_in_percent desc
Consult your local DBA for their opinion on the suggested optimizations. The last column ‘stmt’ contains suggested SQL statements to execute.
Depending on the SQL Server platform, the defragmentation can also be automated.