The following code sample shows the contents of the configureProductionReplicatorDb.sql file:DECLARE @productionDatabase nvarchar(100) = 'ProductionDB'; -- Replace with the
name of the actual production database
DECLARE @cloneDatabase nvarchar(100) = 'CloneDB'; -- Replace with the name of the actual
filtered/cloned database
DECLARE @productionGroup nvarchar(100) = 'production';
DECLARE @clonedGroup nvarchar(100) = 'cloned';
DECLARE @routerId nvarchar(100) = 'production_cloned_router';
DECLARE @channelId nvarchar(100) = 'production_cloned_channel';
INSERT INTO [dbo].[sym_node_group] ([node_group_id])
VALUES (@clonedGroup);
INSERT INTO [dbo].[sym_node_group_link] ([source_node_group_id], [target_node_group_id],
[data_event_action], [sync_config_enabled], [is_reversible])
VALUES (@productionGroup, @clonedGroup, 'P', 1, 0);
INSERT INTO [dbo].[sym_router] ([router_id],[target_catalog_name],[source_node_group_id],
[target_node_group_id],[router_type],[sync_on_update],[sync_on_insert],[sync_on_delete],
[use_source_catalog_schema],[create_time],[last_update_time])
VALUES (@routerId,@cloneDatabase,@productionGroup,@clonedGroup,'default',1,1,1,1,
GETDATE(),GETDATE());
INSERT INTO [dbo].[sym_channel] ([channel_id],[processing_order],[max_batch_size],
[max_batch_to_send],[max_data_to_route],[extract_period_millis],[enabled],
[use_old_data_to_route],[use_row_data_to_route],[use_pk_data_to_route],[reload_flag],
[file_sync_flag],[contains_big_lob],[batch_algorithm],[data_loader_type],[queue]
,[max_network_kbps],[create_time],[last_update_time])
SELECT @channelId as [channel_id],
1 as [processing_order],
[max_batch_size],
[max_batch_to_send],
[max_data_to_route],
[extract_period_millis],
1 as [enabled],
[use_old_data_to_route],
[use_row_data_to_route],
[use_pk_data_to_route],
0 as [reload_flag],
[file_sync_flag],
[contains_big_lob],
[batch_algorithm],
[data_loader_type],
[queue],
[max_network_kbps],
GETDATE() as [create_time],
GETDATE() as [last_update_time]
FROM [dbo].[sym_channel]
WHERE channel_id = 'DEFAULT'
-- Add default triggers for each schema
INSERT INTO [dbo].[sym_trigger] ([trigger_id],[source_catalog_name],[source_schema_name],
[source_table_name],[channel_id],[reload_channel_id],[sync_on_update],[sync_on_insert],
[sync_on_delete],[sync_on_incoming_batch],[use_stream_lobs],[use_capture_lobs],
[use_capture_old_data],[create_time],[last_update_time])
SELECT CONCAT(schemas.name,'_trigger') as [trigger_id],
@productionDatabase as [source_catalog_name],
schemas.name as [source_schema_name],
'*' as [source_table_name],
@channelId as [channel_id],
'reload' as [reload_channel_id],
1 as [sync_on_update],
1 as [sync_on_insert],
1 as [sync_on_delete],
0 as [sync_on_incoming_batch],
0 as [use_stream_lobs],
0 as [use_capture_lobs],
1 as [use_capture_old_data],
GETDATE() as [create_time],
GETDATE() as [last_update_time]
FROM (SELECT 'PR_Projects'
UNION ALL SELECT 'PR_EquipmentProvisioning'
UNION ALL SELECT 'PR_Authorization'
UNION ALL SELECT 'dbo'
UNION ALL SELECT 'HtmlFormSpecification'
UNION ALL SELECT 'PR_Version'
UNION ALL SELECT 'PR_TaskListFilters') schemas (name)
-- Add specific table triggers to exclude calculated columns
INSERT INTO [dbo].[sym_trigger]([trigger_id],[source_catalog_name],[source_schema_name],
[source_table_name],[channel_id],[reload_channel_id],[sync_on_update],[sync_on_insert],
[sync_on_delete],[sync_on_incoming_batch],
[excluded_column_names],[use_stream_lobs],[use_capture_lobs],[use_capture_old_data],
[create_time],[last_update_time])
SELECT CONCAT(calcColumns.tableName,'_calcTrigger'),
@productionDatabase as [source_catalog_name],
calcColumns.schemaName as [source_schema_name],
calcColumns.tableName as [source_table_name],
@channelId as [channel_id],
'reload' as [reload_channel_id],
1 as [sync_on_update],
1 as [sync_on_insert],
1 as [sync_on_delete],
0 as [sync_on_incoming_batch],
calcColumns.columnNames as [excluded_column_names],
0 as [use_stream_lobs],
0 as [use_capture_lobs],
1 as [use_capture_old_data],
GETDATE() as [create_time],
GETDATE() as [last_update_time]
FROM (SELECT 'dbo','Waste_Event_Meas','WEMT_Name'
UNION ALL SELECT 'dbo','Characteristics','Char_Desc'
UNION ALL SELECT 'dbo','Event_Reason_Tree','Tree_Name'
UNION ALL SELECT 'dbo','Event_Reason_Catagories','ERC_Desc'
UNION ALL SELECT 'dbo','PU_Groups','PUG_Desc'
UNION ALL SELECT 'dbo','Reason_Shortcuts','Shortcut_Name'
UNION ALL SELECT 'dbo','Product_Groups','Product_Grp_Desc'
UNION ALL SELECT 'dbo','Waste_Event_Type','WET_Name'
UNION ALL SELECT 'dbo','Views','View_Desc'
UNION ALL SELECT 'dbo','Timed_Event_Detail_History','Duration'
UNION ALL SELECT 'dbo','Timed_Event_Details','Duration'
UNION ALL SELECT 'dbo','Product_Properties','Prop_Desc'
UNION ALL SELECT 'dbo','Specifications','Spec_Desc'
UNION ALL SELECT 'dbo','Event_Reason_Level_Headers','Level_Name'
UNION ALL SELECT 'dbo','Production_Status','ProdStatus_Desc,LifecycleStage'
UNION ALL SELECT 'dbo','Sheets','Sheet_Desc'
UNION ALL SELECT 'dbo','Product_Family','Product_Family_Desc'
UNION ALL SELECT 'dbo','Event_Reasons','Event_Reason_Name'
UNION ALL SELECT 'dbo','Timed_Event_Fault','TEFault_Name'
UNION ALL SELECT 'dbo','Waste_Event_Fault','WEFault_Name'
UNION ALL SELECT 'dbo','Characteristic_Groups','Characteristic_Grp_Desc'
UNION ALL SELECT 'dbo','Timed_Event_Status','TEStatus_Name'
UNION ALL SELECT 'dbo','Sheet_Groups','Sheet_Group_Desc'
UNION ALL SELECT 'dbo','View_Groups','View_Group_Desc'
)
calcColumns(schemaName, tableName, columnNames)
-- Map every trigger into the router
INSERT INTO [dbo].[sym_trigger_router] ([trigger_id],[router_id],[enabled],
[initial_load_order],[ping_back_enabled],
[create_time],[last_update_time])
SELECT trigger_id as [trigger_id],
@routerId as [router_id],
1 as [enabled],
-1 as [initial_load_order],
0 as [ping_back_enabled],
GETDATE() as [create_time],
GETDATE() as [last_update_time]
FROM [dbo].[sym_trigger]
-- Ignore Primary key collision that result from backup starting after replication
INSERT INTO [dbo].[sym_conflict]
([conflict_id]
,[source_node_group_id]
,[target_node_group_id]
,[target_channel_id]
,[detect_type]
,[resolve_type]
,[ping_back]
,[resolve_changes_only]
,[resolve_row_only]
,[create_time]
,[last_update_time])
VALUES (
'IGNORE_PK_Collisions'
,@productionGroup
,@clonedGroup
,@channelId
,'USE_PK_DATA'
,'IGNORE'
,'OFF'
,0
,1
,GETDATE(),
GETDATE())
CREATE USER [comxclient] FROM LOGIN [ComXClient];
exec sp_addrolemember 'db_owner', 'comxclient';
