Change Tracking Monitoring Setup Guide

Overview

This guide provides general guidance for setting up automated monitoring to detect when SQL Server change tracking gets disabled on your database.

Important Disclaimer

This approach has worked for some customers, but we cannot guarantee it will work for all environments due to varying server configurations, security policies, and system complexities. This article is meant as a reference guide only. Your server administrator should review these steps and implement a solution tailored to your specific configuration and requirements.

What You'll Need

• Application log file that records change tracking errors
• Windows Task Scheduler access or ability to run background processes
• Your preferred notification method (email, webhook, etc.)

Step 1: Create the Monitoring Script (Example)

Here's a sample script approach. Create a file called change-tracking-monitor.bat with content similar to this:

:: Loop through this infinitely... 
for /L %%n in (1,0,10) do (
	:: Find out if tracking has been disabled by searching for this string in the log file
	find "tracking not enabled" "C:\Program Files (x86)\commercebuild\sync\sync_agent.log" && (
		echo "Change tracking disabled"
		:: Open the URL here in a browser, which will then ping commercebuild's support team in Slack.
		start https://support.commercebuild.com/?change_tracking=demo300v2021.staging-mysagestore.com^&key=fhAGbvav14u2AgvA 
                :: - Send email notification
                :: - Call webhook/API endpoint  
                :: - Write to event log
                :: - Execute custom alert script
		timeout /t 86400
	)
	timeout /t 60
)

Replace the placeholders:

• [YourLogFilePath] - Full path to your application's log file
  Change tracking loss is logged in C:\Program Files (x86)\commercebuild\sync\sync_agent.log (or wherever Sync 2 is installed).
• Replace the notification section with your preferred alerting method

Step 2: Deploy the Script

Option A: Run as a scheduled task

1. Open Task Scheduler (type "Task Scheduler" in Windows search)
2. Click "Create Basic Task"
3. Configure the following:
   • Name: "Change Tracking Monitor"
   • Description: "Monitors application logs for change tracking issues"
   • Trigger: "At startup" or "At log on"
   • Action: Start a program
   • Program: Browse to your change-tracking-monitor.bat file

Option B: Run as a Windows service Your system administrator may prefer to run this as a Windows service for better reliability and automatic restart capabilities.

Step 3: Configure Task Settings

In the task properties, set these options:

• ✅ Run whether user is logged on or not
• ✅ Run with highest privileges
• ✅ Configure for your Windows version

Step 4: Test and Validate

Before implementing in production:

1. Run the script manually in a test environment to verify it correctly finds the log file
2. Add a test "tracking not enabled" entry to your log file to verify alerts are triggered
3. Confirm your notification method works as expected
4. Verify the 24-hour timeout works to prevent alert spam
5. Have your server administrator review the approach and adapt as needed for your environment

Next Steps

For production use, your system administrator should consider:

• Implementing robust notification methods (email, SMS, webhook APIs)
• Running as a Windows service for better reliability
• Adapting the script to work with your specific security policies and server configuration

Final Notes

This guide provides a starting point for change tracking monitoring. Each environment is unique, and your implementation may need to differ significantly based on your:

• SQL Server version and configuration
• Network security requirements
• Existing monitoring infrastructure
• Corporate policies and procedures

Always test thoroughly in a non-production environment first and have your server administrator review and approve any monitoring solution before deploying to production systems.