Improved collection of offline tracking in SafeCom Multiserver setup
In high volume SafeCom Multiserver environment where the secondary servers are set to use offline tracking, the Tracking database on the secondary server can become so big, that collection of offline tracking data stalls.
The result on the primary server is that tracking data from specific periods of time seems to be missing.
The reason can be that because of the size of the secondary database, the search routine during collection of offline tracking data, takes longer than allowed by the timeout. The result is that the SafeCom primary server will wrongly report that no tracking data was to be collected from the secondary, and in SafeCom Administrator the log will state: "secondaryserverxx: 0 entries collected"
By making sure that the columns "ExportID" and "trackingstate" in the sctracking table of the sctracking database is indexed, the search routine will complete much faster.
This will improve the performance success rate of the collection of offline tracking data from secondary to primary database.
The attached sql script will create the mentioned indexes.
When completed, the sql search time for tracking data will be dramatically improved.
It is to be run from a SQL Query in SQL Management Studio while connected to the secondary servers database.
At the time running the script, the Print Spooler service and SafeCom Service on the secondary must be stopped. And no other SQL connection to the secondary database be open. Please also ensure that the SafeCom primary server will not attempt to collect tracking data from the secondary at the time the script is run. Eventually temporary disable collection of offline tracking in SafeCom Administrator, server settings for the primary server.
Please note that while the SQL script is running, it has to be allowed time to complete.
A FULL SQL backup of the secondary database should be done prior to running the script.
Procedure:
1. Do a FULL SQL backup of the SafeCom secondary database. (sccore, scevent, scpurse, sctracking)
2. In SafeCom Administrator, look at the server settings for the primary server. Tracking tab.
Make sure collection of offline tracking will not happen within the next hour.
Alternatively temporary disable collection of tracking data by unticking checkboxes for "Monday", "Tuesday",..... Press Apply button to commit changes.
3. On the secondary server, stop the Print Spooler service and SafeCom service.
(If the secondary is clustered, use the Cluster Administrator to take the Print Spooler and SafeCom service offline)
4. Close any SQL connection to the secondary database
5. Open up one instance of SQL Management Studio, and connect to the secondary database
6. Open a new Query Analyzer (File, New Query)
7. Paste in the content of the attached .sql file into the Query.
8. Press the "Parse" button to validate the script. If successful continue
9. Press the "Execute" button
Allow for the process to complete. If the tracking database contains like 2 million entries, the process depending on the speed of the SQL server, could take more than 5 minutes.
10. When done, the SQL query will respond with "Command(s) completed successfully."
11. Close the SQL Management Studio.
12. On the secondary server start the Print Spooler service and SafeCom service
(if the secondary is clustered, use the Cluster Administrator to take the Print Spooler and SafeCom service online)
13. In SafeCom Administrator, enable collection of offline tracking in the server settings for the primary server. Tracking tab.
Enable collection for "Monday", "Tuesday", .... Press the "Apply button to commit changes"
Attachments:
Create ExportID and tracking state indexes.zip