Summary
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.
Replication Performance
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$) )
Resolution
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.
Defragmentation
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.