SQL Server Merge replication: Create dynamic filter using hostname parameters of the Merge Agent skillfully

server| Create
We know that SQL Server 2000 's merge replication supports dynamic filter functionality. If we have multiple subscriber, each Subscriber only needs to maintain its own specific data, we simply create a publication and then take advantage of the function of dynamic filter, Copy the specific data that each subscriber needs to the past. The most commonly used system functions for dynamic filter are SUSER_SNAME () and HOST_NAME (), and SQL Server calculates the specific merge for each Subscriber Merge Agent when connected to Publisher The SUSER_SNAME () or HOST_NAME () value of the agent connection, and then filter the data based on the results.



The value of the corresponding SUSER_SNAME () or HOST_NAME () in the table is required when the SUSER_SNAME () or HOST_NAME () is used directly. For example, if our publisher server is called Shanghai to maintain all the data, three subscriber are called Nanjing, Suzhou and Hangzhou to maintain their data, if we want to use HOST_NAME () To copy the data to three subscriber, we should have a column in the table to store the value of HOST_NAME (), and then we can filter the data with dynamic filter such as "Filter_column = HOST_NAME ()":



CREATE TABLE orders (OrderID int not null, Filter_column char (), Quantity int)

Go

INSERT into orders values (1, ' Nanjing ', 100)

INSERT into orders values (2, ' Suzhou ', 200)

INSERT into orders values (3, ' Hangzhou ', 120)

INSERT into orders values (4, ' Hangzhou ', 100)

Go



But what do we do with dynamic filter if the data in our table doesn't exactly match the SUSER_SNAME () or HOST_NAME ()?



For example, in the following table, only one zoneid can be used to differentiate between the data required by different subscriber, but its value is different from that of SUSER_SNAME () or HOST_NAME ().



CREATE TABLE orders (OrderID int not null, ZoneID nvarchar (5), Quantity int)

Go

INSERT into orders values (1,n ' 1 ', 100)

INSERT into orders values (2,n ' 2 ', 200)

INSERT into orders values (3,n ' 3 ', 120)

INSERT into orders values (4,n ' 3 ', 100)



To do dynamic filter for this data, we can use a parameter of the merge Agent, hostname, to achieve. SQL Server Books Online does not explain the use of this parameter very well. When we do not specify this parameter for the Merge Agent, the HOST_NAME () function connected to the Merge Agent returns the computer name of the server on which the Merge Agent resides; When we specify the parameter for the Merge Agent, HOST_NAME () function returns the value specified by the hostname parameter.



For example, if we want to copy ZoneID = N ' 1 ' Records to Nanjing, we can still use "ZoneID = HOST_NAME ()" Filter, just add "Nanjing" parameters to the-hostname=1 's Merge Agent.



To add this parameter, right-click the Merge Agent, select Agent Properties, select the Steps option page, double-click the Run agent, and then add "-hostname 1" at the end of the command.







See MSDN for all the available parameters for the Merge Agent.