Setup / Configuration

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. An alternative you may consider is:
- 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 these Replication Components:
- 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.
- Restart your SQL Server Agent from SQL Configuration Manager.
- Repeat the steps above to confirm that Replication Components have been successfully installed

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:
- Open SQL Server Management Studio and connect to the SQL Server instance you want to check. In the Object Explorer, locate the SQL Server Agent node.
- 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 the SQL 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:
- Open SQL Server Management Studio and connect to your server.
- In Object Explorer, expand Replication > Local Publications > [Database]: Mosaic Publication.
- Right-click on the Subscription under [Database]. Mosaic Publication and select View Synchronization Status.
- 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:
- Open SSMS: Connect to your SQL Server instance with the same Windows Admin user used for the initial replication setup.
- Navigate to Replication: Expand the 'Replication' folder, then 'Local Publications'.
- Edit Publication: Right-click "Mosaic Publication" > 'Properties' > 'Articles'. Uncheck “Show only checked articles in the list”, select the new tables, and click 'OK'.
- Launch Replication Monitor: Right-click on "Mosaic Publication" again and select 'Launch Replication Monitor'.
- 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.
- Verify Synchronization: Right-click the subscription and select “View Synchronization Status” to check for errors.
- 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?

Open SQL Server Management Studio (SSMS):

Launch SSMS and connect to your SQL Server database.

Access the Generate Scripts Wizard:

Right-click on the database you want to script.
Navigate to Tasks > Generate Scripts.

Start the Wizard:

Click Next on the Introduction screen of the Generate and Publish Scripts Wizard

Select Database Objects:

You can choose to script the entire database or select specific objects.
Click Next after making your selection

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

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.

Patching & Recovery

Q: Does applying Windows or SQL Server updates affect replication?

Applying operating system or SQL Server updates may temporarily interrupt replication due to required service restarts or system reboots. This behavior is expected for SQL Server transactional replication. Replication typically resumes automatically once required services are available again.

Q: Is data lost during patching or planned maintenance?

No. SQL Server replication is designed to be resilient to temporary interruptions. Transactions committed during maintenance are preserved and replicated once the system returns to normal operation, provided replication metadata and transaction logs remain intact.

Q: Does replication automatically recover after a restart or outage?

Yes. In most cases, replication resumes automatically after unplanned restarts or short outages. Replication agents continue processing from the last committed point once services are restored.

Q: What components are required for replication to continue after patching?

Replication depends on core SQL Server services, replication components, and SQL Server Agent. If these components are available and properly configured after patching, replication should continue without manual intervention.

Q: Can patching cause replication latency?

Yes. Temporary latency can occur during patching or recovery windows. This latency generally resolves once replication resumes and pending transactions are processed.

Q: When would replication require reinitialization after recovery?

Reinitialization is not normally required after standard patching or restarts. It may be necessary only in scenarios where replication metadata, transaction logs, or publication configuration are altered or removed.

Q: Are special precautions required when scheduling patch windows?

It is generally recommended to schedule patching during periods of lower database activity to reduce replication backlog and recovery time. No special replication configuration changes are required for routine patching.

Q: Should replication be paused during Deltek version upgrades or application updates?

In some cases, it may be appropriate to temporarily pause SQL Server replication during major application upgrades, such as Deltek version updates.
Pausing replication can help prevent schema or data changes from being captured while the application is offline or mid-upgrade.
Whether replication should be paused depends on the scope of the upgrade and whether database schema changes are expected.

Q: What happens when replication is stopped and restarted?

When replication is paused, changes made to the source database are not immediately propagated to the subscriber.
Once replication is restarted, SQL Server replication resumes processing from the last committed point.
Any accumulated changes are replicated once services and agents are running again, provided replication metadata and transaction logs remain intact.

Q: Does stopping replication affect data integrity?

Temporarily stopping replication does not result in data loss by itself.
SQL Server transactional replication is designed to maintain data integrity across controlled stop-and-start cycles.
Extended pauses may result in increased replication latency after restart, but data consistency is preserved.

Q: Are special steps required after restarting replication following an application upgrade?

After replication is restarted, it is recommended to verify that replication agents are running normally and that data is flowing as expected.
In most cases, no reconfiguration or reinitialization is required following a controlled pause for application maintenance.

Q: Is Mosaic involved in stopping or restarting replication?

Customers typically manage replication state during their own application maintenance windows.
Mosaic may request notification of planned upgrades or assist with post-upgrade validation if needed.
Any Mosaic-specific requirements related to replication control are communicated separately.

Q: Does Mosaic require continuous access during patching or recovery?

Continuous access is not required during maintenance windows. Access may be needed after patching to validate replication health or address any post-maintenance issues.

Q: How can we confirm replication health after maintenance?

Replication health can be assessed using standard SQL Server monitoring tools and by verifying that downstream systems are receiving updated data as expected.

Q: Are these behaviors specific to Mosaic?

No. The behaviors described above are inherent to SQL Server transactional replication and apply broadly across integrations. Any Mosaic-specific requirements or validations are documented separately.

SQL Server Replication Best Practices

Automate SQL Server Agent Startup: Set the SQL Server Agent to automatically restart with your machine to ensure continuous operation. See instructions here.
Minimize Software Upgrade Disruptions: Ensure software upgrades do not disrupt the Replication > Local Publication folder, including key components like the Mosaic Publication and Subscription.
Permanent Mosaic Admin Credentials: Configure the Mosaic Windows Admin User credentials to never expire, preventing access issues.