Installation

Deploy Synchronize database

Restore the Synchronize BAK file to each Federated server. It is strongly recommended that the database be called Synchronize.

Identify FederationMaster and FederationMaster2

SQLFed has a hub and spoke model, with a central clearinghose - this is FedMaster. Any node can be FedMaster, and the role can be switched seamlessly during operation. SQLFed has a provision for a secondary FedMaster, which can also be any node. Each Federated Node must have Linked Servers for FederationMaster.PRO and FederationMaster2.PRO in order to operate.

Create the necessary DNS (as in the image above) or Alias entries or have your Network Team create universal DNS entries such that all Federated Nodes will be matched.

Create FederationMaster and FederationMaster2 Linked Servers

Create the two above Linked Servers on each federated server using whatever credentials are appropriate for your environment for full access.

Configure DM_SETTINGS on each node

Create four entries in the Synchronize.DM_SETTINGS table on each node.
Federation: Indicates whether the current node is Master or Slave.
FederationUse: Indicates whether the Primary or Secondary master should be used.
AlertFrom: The email address that SQLFed alerts will originate from.
AlertTo: The email address that SQLFed alerts will originate from.

Setting Names are not case sensitive.

Configure emailing

Modify Synchronize.pr_sendEmail for your environment, so that if SQLFed has a problem it can communicate that problem to you.

Apply the Synchronize trigger

For each table to be Federated, apply this common trigger code - substituting your TABLENAME, and ensuring that the boolean parameter on pr_sync_trigger is set appropriately for your table.

Create the Synchronize job

Each node will need to have a running Synchronize job. The provided script will create a DISABLED job. You need to enable it when ready.

Optional - Federated Identity / ReadyRecords

In cases where IDENTITY needs to be centrally generated for a given table to ensure consistency across the environment, SQLFed offers two options:
1) Record flow-through. In this model when a new record is needed, a stored procedure is called which creates the new record at FedMaster - then waits for the new record to arrive locally. Once that has occurred, the stored procedure completes.
2) ReadyRecords. In this model, FedMaster creates new records in advance for each federated node and marks them. A stored proceudre is called each time a new record is needed which checks for and claims a ReadyRecord if one is available, and failing that, will follow the Record Flow-Through model mentioned above.

An example of this is included in the stored procedure pr_federate_users_create, which is a part of Synchronize BAK. Note that ReadyRecords require a string field to convey which server the ReadyRecord is for - and will require some code on your part.

If you desire to use ReadyRecords (which we strongly recommend), the stored procedure pr_maintainReadyRecords should be edited for your environment. On line 26 you will want to specify a list of TableName values to consider - and code for #readyRecordsExisting on line 38. This stored procedure must be called by a job periodically (15m recommended) on FedMaster to create these advance records. A SQLFed Consulting Engagement is recommended for setup of ReadyRecords.

Optional - LaggyLink Configuration

In deployments where link reliablity or speed are a factor, SQLFed has the ability to note slowdowns and deploy records in bulk to "catch up." If your environment has this as a factor, we recommend a Consulting Engagement with the SQLFed team to discuss how to configure this advanced feature.

For more on this, please see existing code in pr_synchronizeCommands and pr_sendFile_uftp.

Optional - Monitor SynchronizerLog and ERRORMESSAGES

SQLFed writes non-critical errors to the ERRORMESSAGES table, which you should monitor.

If you require additional logging data, the "Verbose Logging" settingName should be added to DM_SETTINGS with a settingValue of "1" - this will cause the synchronizeLog table to be populated with detailed logging data.

Further monitoring can be enabled by utilizing the template of pr_monitorSynchronizer and pr_monitorSynchPerformance, which are pre-loaded in the Synchronize BAK.

Optional - Scheduled Maintenance

It is not desirable for COMMANDS or REMOTECOMMANDS tables to grow indefinitely. It is recommended to follow the template of pr_maintenance_commandsTrim and pr_maintenance_remoteCommandsTrim and call these jobs daily.