Understanding Bullhorn Data Replication (Formerly Data Mirror)

Bullhorn Data Replication (formerly Data Mirror) takes the data held in your Bullhorn database and replicates it to a database that your company hosts, allowing you direct SQL access to Bullhorn data. Bullhorn Data Replication replicates your Bullhorn data into a MS-SQL Server environment.

After replication, you can migrate your data into a target standard RDBMS reporting platform and/or Database Management System.

Requirements

To use Bullhorn Data Replication, your company needs the following hardware and software:

• Server(s) with Internet access.
• Windows Server 2008+ (must support JAVA 8).
• Microsoft SQL Server Standard or Enterprise edition, version 2016+.
• 64bit Windows executable.
• RAM: 10% of data replication database recommended, minimum 16 GB.
• Java 8+ 64 bit.
• Hard-drive: 40% of the Bullhorn database you are replicating, minimum 500GB.
• Bandwith of 1MB/s up / down or higher.

Bullhorn is responsible for implementing Bullhorn Data Replication and uses the following process:

1. Creates a backup of your production data.
2. Delivers a copy of the data with instructions on how to create a SQL server database on your network.
3. Sends instructions on how to download two folders containing files to run Bullhorn Data Replication.
4. Schedules a call with your Database Administrator and Bullhorn Support to configure the files, set up the scheduled task, and get Bullhorn Data Replication up and running.

When implementation is complete, your company is responsible for:

• Any hardware, network connectivity and/or application licensing for the server(s) hosting Bullhorn Data Replication and SQL Server.
• Monitoring the Windows Event Viewer for any application errors and Windows Task Scheduler to ensure the application is running.
• Monitoring the log file named log-DataMirrorClient.txt for any errors.

Replication Process

The Bullhorn Data Replication is made up of two primary processes: the replication process and the self-healing process. These processes take new, modified, and deleted records in the core Bullhorn tables and populate your Bullhorn Data Replication with these changes.

The replication process runs continuously, adding and updating records in near real-time. For any inconsistencies, a nightly process called self-healing runs which compares the number of records added and modified to the number of records synced within the last 24 hours. If there is a discrepancy, the system will incorporate the records into the system.

Data Replication & Payroll/Billing Uses

Primary Uses for Data Replication

Bullhorn Data Replication is primarily used for analyzing and sampling data for business intelligence purposes. It provides greater insight into the trends of your staffing business. These trends inform the strategic direction of your business and allow you to take tactical short-term actions to improve your business. You can also use daily and weekly reports built off of Bullhorn Data Replication to understand your business performance (e.g., “how many job fills last week?”).

Uses Not Intended for Data Replication

Bullhorn Data Replication databases are not intended to serve as a data source for payroll and billing systems. Avoid using Bullhorn Data Replication to provide data to applications such as commission calculators, payroll applications, etc.

Common Unsupported Uses

The following situations are the most common uses that Bullhorn One and Bullhorn Data Replication customers try to achieve with Bullhorn Data Replication. These situations are better solved by other Bullhorn products.

FAQ and Troubleshooting

No Data is Replicating

• Make sure there are no errors in your task scheduler.
• Make sure that the machine running Bullhorn Data Replication can ping api.​bullhornstaffing.​com (to rule out any firewall or networking issues).

Specific Data is Not Replicating

Some data is only replicated once per day if it's part of the Master process. Data replicated once per day are entities that are not part of continuous transactions and primarily look ups. Examples include:

• CorporationDepartment
• BusinessSector
• Categories
• Skills
• Specialties
• PrivateLabel
• Field Maps

How to Check if Your Data is Syncing

Run a query against Data Last Sync to see the frequency of data updates:

                --Update to the appropriate DB Name
DECLARE @table_catalog nvarchar(50) = '' -- <--UPDATE ME TO DB NAME
DECLARE @SQL nvarchar(max) = ''

--Set table list into variable table to create list
DECLARE @tableList table
(
Num int IDENTITY (1,1),
TABLE_NAME nvarchar(100)
)

INSERT INTO @tableList (TABLE_NAME)
SELECT DISTINCT ts.TABLE_NAME
--SELECT *
FROM INFORMATION_SCHEMA.TABLES ts
JOIN INFORMATION_SCHEMA.COLUMNS cs
on ts.TABLE_NAME = cs.TABLE_NAME
WHERE ts.TABLE_TYPE = 'BASE TABLE' AND ts.TABLE_CATALOG=@table_catalog
AND cs.COLUMN_NAME = 'dateLastSync'

--SELECT * FROM  @tableList

--Generate @SQL statement
DECLARE @endLoop int = (SELECT MAX(Num) from @tableList)
DECLARE @Pointer int = 1

WHILE @endLoop > @Pointer
BEGIN
DECLARE @tableName nvarchar(100) = (SELECT TABLE_NAME FROM @tableList WHERE Num = @Pointer)
SET @SQL = @SQL + '
SELECT ''' + @tableName + ''' AS entity, MAX(dateLastSync) AS dateLastSync
FROM ' + @tableName  + ' (NOLOCK) UNION ALL '
SET @Pointer = @Pointer + 1
END

--Clean off last 'UNION ALL STATEMENT'
SET @SQL = LEFT(@SQL, LEN(@SQL)-9)
--SELECT @SQL
EXECUTE (@SQL)
            

The query will return the maximum date that each table was last synced. If you know data has been updated recently in a table and the sync date does not match, inform Bullhorn Support and provide the results of this query.

Locating the Replicator Logs

Replicator logs are located in the main folder called log-DataMirrorClient.txt. Provide this document to Bullhorn Support if there are issues with Bullhorn Data Replication.

Best Practices

To ensure optimal performance and reliability of replicated systems, robust maintenance and best practices are necessary. Tailored best practices for single-sourced data replication systems play a crucial role in guaranteeing data integrity, minimizing latency, and mitigating potential issues. These practices equip administrators with the knowledge needed to operate their system seamlessly.

Management of Data and Log Files

• Auto growth should really be a backstop option. it should be set in a fixed size increment instead of a percentage-based increment. The fixed increment should not be too large(>1GB) as that can have a detrimental impact to performance while the file grows. instead, they should monitor the growth rate of the files over a period of time to be able to predict the growth rate and then manually increase the file size on an as needed basis during off-peak hours.
• Confirm Instant file initialization is configured.
• Auto-shrink is not enabled, and shrink is not part of any maintenance plans. If auto-shrink is enabled, disable it by using the following command:
Copy

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

• Schedule rebuilding of indexes based on fragmentation at least on a weekly basis.
• Update Statistics at least on a weekly basis.
• Check integrity of the database (detect corruption) at least on a weekly basis.

Correct Data Gaps

Data Replication is a real-time reporting database that may experience temporary data sync issues. However, any gaps can be repaired using the following mechanisms:

• Data Inserts (automatic)
• Self-Heal (automatic)
• Self Seed (by request)
• Provide a backup database copy of the ATS system formatted; also called a "re-seed" (by request)

Callout InfoIt is recommended that clients maintain a comprehensive Database Backup and Recovery Strategy based on the criticality of the Data Replication product. Bullhorn’s timing of addressing data gaps may not align with the client’s business needs.

The Timeline for restoring data can be a minimum of 2 business days (self heal/seed) to 2 weeks (provide db back-up)

Memory Allocation

Memory allocation on the SQL instance - barring a few GB on the server for the OS itself, all the available memory should be allocated to SQL

• SQL 2016, 2017, 2019 Standard Edition memory limit is 128GB per SQL instance + 32GB for in memory OLTP (if used) + 32GB for column store indexes (if used)
• Enterprise Edition limit is the OS memory limit

Parallelism Options

Recommendations can be found from Microsoft here.

Tuning Advisor

Recommended to be enabled and compared quarterly as new entities are added and flow of transactions may occur.