SQL Server Integration Troubleshooting

📘

Please Note:

This guide is intended solely for IT personnel who have knowledge of SQL Server Management Studio and are familiar with the overall SQL Server replication process.

Welcome to this page! If you've landed here, it's likely due to missing data on Mosaic, possibly from one of the following On-Premise Integrations:

• Ajera On-Premise
• BST10 On-Premise
• Vantagepoint On-Premise
• Vision On-Premise
• Vista On-Premise

Q: How can I determine if my integration has been disconnected?

If you haven't seen new data appear in Mosaic within the past 24 hours, it's possible your integration may have been disconnected. Upon confirmation from your Customer Success Manager that a reconnection is necessary, you have two options:

1. Schedule a call with Mosaic to assist in the reconnection process.
   1. You will need to configure permanent access via GetScreen.me for [email protected], provide Windows User Login credentials for a sysadmin user and confirm your Production Database Name.
2. If you have IT personnel available and you prefer to troubleshoot this issue internally, please have them follow the guide below. It's essential that this process is handled by someone with the appropriate technical expertise to ensure accuracy and system stability.

SQL Server Troubleshooting Guide

Q: Why did my SQL Server replication job get disconnected?

There can be several reasons why a SQL Server replication job gets disconnected. One common scenario is when the snapshot agent stops and fails to restart automatically. Here's what you can do to address this issue:

Q: What should I do if the snapshot agent for my SQL Server replication job stops and doesn't restart automatically?

A: If the snapshot agent for your SQL Server replication job stops and fails to restart automatically, follow these troubleshooting steps:

1. Check agent status: Open SQL Server Management Studio and connect to the publisher instance. Navigate to the "Replication" folder, expand it, and click on "Local Publications." Right-click on the Mosaic Publication and select "View Snapshot Agent Status." Verify if the agent status is stopped or failed.
   
2. Restart the snapshot agent manually: Right-click on the publication and select "Reinitialize." In the Reinitialize Subscription Wizard, choose the "Use a new snapshot" option and proceed with the wizard. This will restart the snapshot agent and generate a new snapshot.
3. Please note that it may take up to 24 hours for new data to appear in Mosaic after the integration connection has been restored.

If the preceding measures do not resolve the integration connection issue, please consider these additional troubleshooting strategies:

• Verify agent process account credentials: Ensure that the account used by the snapshot agent has sufficient permissions and is not locked or disabled. Check the SQL Server Agent service account and confirm its credentials are valid.
  • In the SQL Server Configuration Manager, click "SQL Server Services" in the left pane.
  • In the right pane, right-click "SQL Server Agent", and then click "Properties".
  • Click the "Log On" tab, and note the account listed. This is the account under which the SQL Server Agent service runs.
  • Confirm that this account has the correct permissions in SQL Server and is not locked or disabled in Windows.

• Review agent error logs: Examine the agent error logs to identify any specific error messages or warnings that could provide insight into the issue. Logs are typically found in the SQL Server installation directory under the "MSSQL\ReplData" folder.
• Inspect distribution agent status: If the snapshot agent is running but the distribution agent is stopped, you may need to troubleshoot the distribution agent. Right-click on the publication, select "View Distribution Agent Status," and verify its status. If needed, restart the distribution agent or examine its error logs for further details.
• Check for network or connectivity issues: Ensure there are no network interruptions or firewall restrictions blocking communication between the publisher and subscriber. Check if the distributor and subscriber servers are accessible and reachable.
• Restart SQL Server Agent: In some cases, restarting the SQL Server Agent can help resolve connectivity or agent-related issues. Exercise caution and consult with your database administrator before proceeding. To minimize potential disruption to operations, we advise performing this action during non-peak hours.
  • Open SQL Server Configuration Manager.
  • Click on SQL Server Services, right click on SQL Server (MSSQLSERVER), and click on Restart

Please note that these troubleshooting steps provide a general guideline and may vary depending on your specific SQL Server environment and replication configuration. Please reach out to your Customer Success Manager for tailored assistance.