...
...
...
...
Purge Database
Purging is the process of freeing up space in the database or deleting obsolete data that is no longer required. The purge process can be based on the age of the data or the type of data.
Functionality
In case the replication is running on the main site and the database is set up for the purge feature, then, on the export stage, Replicator generates purge requests for the satellites where the purge is enabled.
The main site is never purged and, therefore, satellite sites do not export purge requests. The main site is the only site that can export purge requests and send them to other sites (satellites).
Replicator runs a separate replication phase called Purge database. This phase runs right after the import phase. When the purge database is database is in progress, you can see in Replicator Manager what tables are purged and how many rows are deleted from each table:
...
Technical Details
Purge requests are exported at the very end of the procedure that exports data to a site. Replicator walks through the export tasks for a site once again after tasks export. It handles every table task where the new DataPurgedAfterDays or PKPurgeQuery field of the repl_ExportTasks view is set to a value other than null.
...
| Code Block | ||
|---|---|---|
| ||
DELETE FROM dbo.repl_Distrib_PW001P01 FROM dbo.repl_Distrib_PW001P01 AS dsb INNER JOIN #pks_PW001P01 AS tmp ON dsb.PIN = tmp.PIN WHERE dsb.SiteNr = Destination_Site_Number |
Use Cases
Since the purge feature is an extension of the partial replication, the DataPurgedAfterDays, PKPurgeQuery and PKRestrictionQuery fields are related. Therefore, it is reasonable to store all these three fields in the same table (repl_Task or another one).
...
| Code Block |
|---|
CREATE TABLE dbo.repl_Restrictions ( ChannelNr int NOT NULL, TaskNr int NOT NULL, PKRestrictionQuery varchar (8000), CONSTRAINT PK_repl_Restrictions PRIMARY KEY (ChannelNr, TaskNr) ) |
In this case, PKRestrictionQuery can be added to the repl_ExportTasks view in the following way:
| Code Block |
|---|
ALTER VIEW [dbo].[repl_ExportTasks] AS SELECT …, PKRestrictionQuery = CASE WHEN repl_Channel.IsPartial = 1 THEN repl_Restrictions.PKRestrictionQuery ELSE NULL END FROM … LEFT OUTER JOIN dbo.repl_Restrictions ON repl_Task.TaskNr = repl_Restrictions.TaskNr AND repl_Channel.ChannelNr = repl_Restrictions.ChannelNr WHERE … Then, we could add the fields DataPurgedAfterDays and PKPurgeQuery to the repl_Restrictions table and references to them to the repl_ExportTasks view: ALTER TABLE dbo.repl_Restrictions ADD DataPurgedAfterDays int; ALTER TABLE dbo.repl_Restrictions ADD PurgeQuery varchar(8000); ALTER VIEW [dbo].[repl_ExportTasks] AS SELECT …, DataPurgedAfterDays = CASE WHEN repl_Site.IsPurged = 1 THEN repl_Restrictions.DataPurgedAfterDays ELSE NULL END, PKPurgeQuery = CASE WHEN repl_Site.IsPurged = 1 THEN repl_Restrictions.PKPurgeQuery ELSE NULL END FROM … WHERE … |
Housekeeping
Replicator enables you to benefit from the purge database functionality. For this, you have to set up the database for the purge feature. First, run APM and navigate to Setup > Adonis Replicator. Now, proceed as described below:
Make sure you are on the Site Properties tab. Within the grid, in the Is Purge column, select the checkbox(es) for the site(s) for which you want to purge database.
Switch to the Replication Tasks tab under Views -1 to define the number of days to apply purging and PK Purge Query:
In the Data Purged After Days column, enter the number days starting from today for purging to be applied.
In the PK Purge Query column, double-click the cell where a purge query is to be added. When the Browse button appears, click it to open the SQL Editor dialog:
Click
In the SQL Editor dialog, enter the SQL query or click Load to define the directory to the statement.Click Save to... to specify the save directory on your computer or Save&Close to apply the changes in the editor.
As a result, the affected cell becomes yellow-highlighted.
...