The Merge Replication feature of SQL Server allows multiple databases to sync data bi-directionally, while keeping all participating databases in a “live” state. This differs from the SQL Server's Mirroring feature, which requires one of the databases be designated as primary, while the others to be marked as secondary and set to “read only”.
The Merge Replication feature requires that all tables marked for replication contain a column with a data type of “uniqueidentifier” and that the columns have the property “RowGuid” set to ‘Yes’. By setting the default value to ‘(newid())’, the database will automatically generate a new guid for each existing row, as well as on insert of new rows.
The schema of Automate BPA Server’s data store does not contain this column by default. However, the software is designed to function properly when extra columns are added to BPA tables, so enabling this column is a non-issue. This column can be added to the tables manually with SQL Management Studio, or by running the following ALTER statements:
ALTER TABLE agentconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE agentgroupconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE agentpropertiesconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE automateconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE bpasystem ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE constants ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE eventlogtriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE excludeschedules ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE executionevents ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE filetriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE folderconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE generalevents ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE idletriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE itempermissions ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE keytriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE performancetriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT(newid())
ALTER TABLE processconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE processtriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE scheduletriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE serverproperties ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE servicetriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE snapshots ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE snmpcredentials ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE snmptriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE startuptriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE systempermissions ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE taskconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE taskpropertiesconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE triggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE userconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE usergroupconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE variables ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE windowcontrols ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE windowtriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE wmitriggerconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE workflowconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE workflowitemconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE workflowitempropertiesconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT(newid())
ALTER TABLE workflowlinkconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT (newid())
ALTER TABLE workflowpropertiesconstructs ADD RowGUID uniqueidentifier ROWGUIDCOL NOT NULL DEFAULT(newid())
Links to SQL Server Merge Replication topics:
Merge Replication Overview
How Merge Replication Works
Designing and Implementing (Replication)
Applies To: Automate BPA Server 9
