SQL Server Integration Requirements

To Do Before Integration Kick-off

1. Configure RDP or VPN access for Mosaic to connect to your server machine (Link to download Getscreen.me).
2. Ensure that Replication Components are enabled on SQL Server Management Studio (SSMS).
3. Ensure that Agent XPs are enabled on SQL Server.
4. Create a Windows Admin user for Mosaic with Admin (sysadmin) access to SQL Server
5. Ensure that if using SQL Server Management Studio 2012 or later, the required updates are installed

• Currently, we extend our support to the following versions of SQL Server Management Studio (SSMS):
  • SQL Server Management Studio 2012
  • SQL Server Management Studio 2014
  • SQL Server Management Studio 2016
  • SQL Server Management Studio version 17.x
  • SQL Server Management Studio version 18.x (Recommended Version)
  • Version Compatibility: At present, we've identified challenges with the replication process in SQL Server Management Studio 19.x when used for Mosaic integrations. While we anticipate future SSMS releases may address these issues, we currently advise using SQL Server Management Studio 18.12.1 to ensure a seamless integration experience.
    • Link to download and install SSMS 18.12.1.

Requirements

1. Standard Edition or Enterprise Edition SQL Server 2012 or later
   * Express edition is not supported in this process

• If using SQL Server Management Studio 2012: must have the latest Service Pack installed
• If using SQL Server Management Studio 2014: must have Service Pack 3 and Cumulative Update 4 or later installed

2. Replication components for SQL Server Management Studio must be enabled.
3. Agent XPs for SQL Server must be enabled.
4. Remote access to the server hosting your application's SQL Server database

• VPN account (with relevant documentation) + RDP details
• OR screen sharing session via Getscreen.me

5. Windows admin user that doesn't expire

• SysAdmin access to your application's SQL Server database

Replication Setup, Monitoring and Troubleshooting

👍

Please Note:

To ensure effective replication monitoring and troubleshooting, Mosaic recommends providing unattended remote access to the server machine for a minimum of 3 days after the replication setup. This can be achieved through VPN, RDP, or Getscreen.me.

To set up unattended remote access via Getscreen.me:

1. Install the Getscreen.me application
2. Open the application and navigate to the 'Permanent Access' tab and enter the following login/email: [email protected]
3. Provide your Server Name, Production Database Name and login credentials for the Windows Admin user (with sysadmin access to SQL Server) to your Customer Success Manager if you haven't done so already.

Mosaic Subscriber Database

The Mosaic Integrations Team will set up a publisher/subscriber job in your SQL Server Management Studio, replicating data to Mosaic's Azure database. Data becomes available in Mosaic within 24 hours from your source application. The process is one-way, from your database to Azure, with no data pushed back to your application.

It's essential for your database to be connected to SQL Server Management Studio, with the SQL Server Agent configured and operational. Additionally, ensure the provided Windows Admin user credentials remain valid and do not expire.

For detailed replication instructions to Azure SQL Database, please refer to the Microsoft Guide: Replication to Azure SQL Database.

SQL Server Replication: Security Overview

Security on Your Server

• Firewall Implementation: Mosaic enforces rigorous firewall rules to safeguard your data and prevent unauthorized access.

Security for Your Cloud Database Copy

• Azure Firewalls: Comprehensive protection through Azure firewalls, blocking unwanted traffic and potential threats.
• Active Directory Integration: Utilizes Azure Active Directory (AD) for identity management, ensuring secure and controlled access.
• SQL Authentication: Employs SQL Server Authentication, requiring valid credentials for database access, further securing your data.

Alternative Integration Methods

If you prefer to host your own replica database and provide us with the connection details, please refer to this Guide.