Replication FAQs + Best Practices

Frequently Asked Questions

Q: Why can't I use SQL Server Express?

  • SQL Server Express does not support database replication.

Q: What if I am using SQL Server Express and can't upgrade?

  • For your trial you are able to supply .csv files containing Member, Project, Plan, Time Entry, and Task data. See Trial Data Import for more details.
  • Unfortunately there is no way to integrate with Mosaic without a Standard or Enterprise version of SQL Server. Alternatives you may consider are:
    • Upgrading to a cloud version of your current software.

Q: Does Mosaic need unattended/ongoing access to our SQL Server Machine?

  • Mosaic needs to be able to access your server to complete the integration. After this we may request supervised access again for troubleshooting purposes, or to add additional data to your integration to support new features in Mosaic.

Q: Is it possible to set up Mosaic without copying our data to Mosaic’s cloud databases?

  • No. Mosaic does not offer any standalone solutions, all of our integrations require a cloud replication of the database.

Q: Can you set your sync to only take some of the tables from our database?

  • Yes. Mosaic only takes information from the Tables that are listed on the application's On-Premise Integration page.

Q: Do changes that are made in Mosaic get sent back to our database?

  • No. Data only flows in one direction, from your database to our Cloud database. Mosaic does not send any changes into your application's database.

Q: How can I make sure Replication Components are enabled on my SQL Server? If they’re not installed, how do I add them?

  • You can check if Replication Components are installed in SQL Server Management Studio by:
    • Expanding the server node in Object Explorer
    • Right-clicking on Replication and selecting "Launch Replication Monitor" or "Configure Distribution Wizard"
    • You will receive an error if Replication Components are not installed, similar to:

  • To install Replication Components:
  1. Run the SQL Server Setup again. Choosing "New SQL Server stand-alone installation or add features to an existing installation" from the Installation menu on the left. Make sure to select “SQL Server Replication” on the Feature Selection page of the Installation Wizard.

  1. Restart your SQL Server Agent from SQL Configuration Manager.
  2. Repeat the steps above to confirm that Replication Components have been successfully installed.

🚧

Please Note:

This process is typically done “after-hours” to avoid any disruption to your company’s daily activities.

Q: How can I check if Agent XPs are enabled on my SQL Server Management Studio, and how can I enable them?

  • To check if Agent XPs are enabled on your SQL Server Management Studio, you can follow these steps:
  1. Open SQL Server Management Studio and connect to the SQL Server instance you want to check.
  2. In the Object Explorer, locate theSQL Server Agent node.
  3. Look for the status displayed beside SQL Server Agent. If it shows "Agent XPs disabled," it means that Agent XPs are currently disabled.
  • To enable Agent XPs, you can follow these steps provided in this article to enable Agent XPs on your SQL Server instance.
    • Enabling Agent XPs will allow the SQL Server Agent to function properly.

  • Once the Agent XPs have been enabled, you will be able to expand theSQL Server Agent node.

Q: How do I maintain SQL Server integration and data continuity during patches and recovery?

Replication jobs in SQL Server should automatically restart during server patches, ensuring continuous operation. To verify this:

  1. Open SQL Server Management Studio and connect to your server.
  2. In Object Explorer, expand Replication > Local Publications > [Database]: Mosaic Publication.
  3. Right-click on the Subscription under [Database]. Mosaic Publication and select View Synchronization Status.
  4. Verify there are no errors to ensure replication jobs are functioning correctly.

For Snapshot or Log Reader Agent issues, refer to Microsoft's guide: Troubleshooting Transactional Replication.

Q: How can I add new tables to my SQL replication?

Here’s a step-by-step guide to assist you in this process:

  1. Open SSMS: Connect to your SQL Server instance with the same Windows Admin user used for the initial replication setup.
  2. Navigate to Replication: Expand the 'Replication' folder, then 'Local Publications'.
  3. Edit Publication: Right-click "Mosaic Publication" > 'Properties' > 'Articles'. Uncheck “Show only checked articles in the list”, select the new tables, and click 'OK'.
  4. Launch Replication Monitor: Right-click on "Mosaic Publication" again and select 'Launch Replication Monitor'.
  5. Restart the Snapshot Agent: In Replication Monitor, navigate to Publication > Agents. If there are any issues indicated by a red "x", right-click the Publication, select “Connect to Distributor”, and restart the Snapshot Agent by right-clicking it and choosing “Start Agent”. Refresh as needed via Action > Refresh.
  6. Verify Synchronization: Right-click the subscription and select “View Synchronization Status” to check for errors.
  7. Notify Us: Please let us know once you have completed these steps so we can verify the presence of the new tables and the integrity of the data replication into the subscriber database.

Q: How can I generate a database schema script?

  1. Open SQL Server Management Studio (SSMS):

    • Launch SSMS and connect to your SQL Server database.
  2. Access the Generate Scripts Wizard:

    • Right-click on the database you want to script.
    • Navigate to Tasks > Generate Scripts.
  3. Start the Wizard:

    • Click Next on the Introduction screen of the Generate and Publish Scripts Wizard.
  4. Select Database Objects:

    • You can choose to script the entire database or select specific objects.
    • Click Next after making your selection.
  5. Configure Script Options:

    • Click on the Advanced button to access additional settings.
    • In the Types of data to script dropdown, select Schema Only or Schema and Data.
    • Specify the name and location where you want to save the script.
  6. Generate and Save the Script:

    • Review your choices on the Summary screen.
    • Click Next to start generating the script.
    • Once the script is generated, it will be saved to the specified location.
    • Click Finish to close the wizard.

SQL Server Replication Best Practices

  1. Automate SQL Server Agent Startup: Set the SQL Server Agent to automatically restart with your machine to ensure continuous operation. See instructions here.

  2. Minimize Software Upgrade Disruptions: Ensure software upgrades do not disrupt the Replication > Local Publication folder, including key components like the Mosaic Publication and Subscription.

  3. Permanent Mosaic Admin Credentials: Configure the Mosaic Windows Admin User credentials to never expire, preventing access issues.