IdleTymes Blog

Merge Replication has four perquisites:

1. You have two servers, with SQL Server and Enterprise Manager installed.
2. On each server you have created the databases that you want to merge.
3. Also the database on the server that will be acting as the Publisher has the complete structure.
4. The service that manages the replication and connects the two SQLservers should manage the SQLSERVERAGENT as well. This account should be the same on both machines and it should have an identical windows authentication and password on both machines.

Data can be added at any time, but the basic structure needs to be in place when the article is established. Changes can be made later so it is not essential to get it absolutely right first time.

Job 1: Configure the main machine as a publisher
The first job is to set up the main machine so that it is configured as a Publisher and Distributor.

Step one: Log on to the main machine and load up Enterprise Manager.

Step two: Right-hand-click on the server symbol for the machine that you’re on. This will bring up a floating menu. Click on the second to last choice ‘Properties’.

Step three: Highlight the Replication tab and click on ‘Configure’

Step four: Continue through the Publishing and Distribution wizard with these settings:

• Click ‘Next’ from the welcome page.
• Go with the default ‘Make XXX it’s own Distributor’ and click ‘Next’
• Make sure the directory name is to your liking and click ‘Next’
• Use the default settings on the ‘Customize the Configuration’ page and click ‘Next’
• Click ‘Finish’ & wait for the system to set up replication.

Share This

This document is design to provide a procedure for any one wanting to make structal changes to a merged database.

The Topic covered are:
• Add a column to a table
• Remove a column from a table.
• Alter a column
• Drop a table
• Add a table

Note: If you want to make changes to a table that is not being replicated then you do not need to follow any of the procedure here, just apply the changes as per usual. It is only table that are part of a Article that require these changes. To find out what tables are part of a Article you can either checkin the properties of the publication, but Enterprise manager will stop you from making changes anyway.

Share This

Okay Ed this is the script that you wrote for the that creates a archive table for MS_Merge_History and then creates a trigger which copy all the data across from the orginal so that we have a longer achive.

I also include the trigger that fires of a email alert each time the connect fails:

CREATE TABLE [dbo].[MSmerge_history_archive] (
[agent_id] [int] NOT NULL ,
[runstatus] [int] NOT NULL ,
[start_time] [datetime] NOT NULL ,
[time] [datetime] NOT NULL ,
[duration] [int] NOT NULL ,
[comments] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[delivery_time] [int] NOT NULL ,
[delivery_rate] [float] NOT NULL ,
[publisher_insertcount] [int] NULL ,
[publisher_updatecount] [int] NULL ,
[publisher_deletecount] [int] NULL ,
[publisher_conflictcount] [int] NULL ,
[subscriber_insertcount] [int] NULL ,
[subscriber_updatecount] [int] NULL ,
[subscriber_deletecount] [int] NULL ,
[subscriber_conflictcount] [int] NULL ,
[error_id] [int] NOT NULL ,
[timestamp] [datetime] NOT NULL ,
[updateable_row] [bit] NOT NULL ,
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
FROM inserted

CREATE TRIGGER [Error_Alert] ON [dbo].[MSmerge_history_archive]
FOR INSERT
AS
DECLARE @Countit int
SELECT @Countit = count(*) from inserted where comments like (’%could not%’);
If @countit > 0
BEGIN
execute master.dbo.xp_cmdshell ‘C:\Components\replicationEmail.exe’;
END

Share This

Like the numeric data types, the DateTime data type implements IFormattable, allowing it to be formatted as a string with one of the overloads of DateTime.ToString. The standard format provider class used for formatting DateTime objects in the .NET Framework is DateTimeFormatInfo.

DateTime format strings fall into two categories: standard format strings and custom format strings. The output of the standard format strings is influenced by the current culture (a passed CultureInfo object or the one associated with the current thread). The custom format strings allow DateTime objects to be formatted for situations where the standard formatting strings are not useful.

Share This