Step by step teach you how to configure SQL server merge and copy (6) Configure publisher (bottom)

In the pop-up Add filterOn this page, you can list all the fields in the table in the left column. Double-click the cityid column to move it to Fileter statementTo add a condition like cityid = 1, which will reduce the number of data synchronized by the subscriber. Of course, the value here must be defined as a constant, so this is called Static Filter(Static query ).

In our applications, it is normal to have millions of data entries in the table, but sometimes the data required by the device is only several thousand of the millions, in this case, static filtering comes in handy. Let's continue. Delete cityid = 1 first because we have more important conditions to apply.

If you want to reduce the number of data synchronized from the client, for example, you just want to use the same app here. Every user only needs to synchronize the data in the corresponding city, the cityid field can be used for this filtering, but we cannot use static filtering because it can only be filtered to one city. In fact, we have a method that usesParameterized Filter(Parameter query ).SQL ServerOfHost_name ()To implement this filter, you only needReplicationSet the object propertyHost_name ()The corresponding value is enough. You can write the following conditions:

Where [cityid] = convert (INT, host_name ())

However, unfortunately, this result cannot be created.Publication,BecauseHost_name ()Cannot be forcibly converted to integer type. However, you can write as follows:

Convert (nchar, cityid) = host_name ()

In fact, it is not difficult to solve this problem. You can executeSp_changemergearticleStored Procedures to replace the following conditions

Where [cityid] = convert (INT, host_name ())

The entire execution process is as follows:

Use [parksurvey]
Exec sp_changemergearticle
@ Publication = 'parksurveypublication ',
@ Article = 'cities ',
@ Property = 'subset _ filterclause ',
@ Value = 'cityid = convert (INT, host_name ())',
@ Force_invalidate_snapshot = 1,
@ Force_reinit_subpipeline = 1;
Go

The result is to create a new snapshot folder, but the performance is better than the above.

Finally, let's take a look.Add filterThe single-choice button at the bottom of the page. For each table that creates a parameter query, you cannotHostnameWhen the value is the same, select the first button.HostnameSelect the second button when the value is different, which is very important because an error is reported. IfHostnameWhen the value of is unique, you should select the second button. Therefore, you must select the first button for all static queries to send data in each row to each subscriber. ClickOKA table with query conditions is displayed inFilter table rowsPage.

Now, a table with synchronization parameters is created. Next I will teach you how to associate it with another table. In our example, the cities table only allows the subscriber to synchronize the city corresponding to the subscriber. However, if the subscriber wants to view the data in the parks table, this will not meet our needs. Cityid is the foreign key of parks. We can extendHostnameQuery condition, select cities, and clickAddAnd then selectAdd join to extend the selected filterAs shown in.

Add joinThe menu lists all the tables you can join.

Here, we select parks as the table to be joined.

In our exampleFiltered table columnDisplays the columns of the primary table.Joined table columnDisplays the columns to be associated with the primary table. In the preview text box below, you can see how the primary table is associated with its foreign key. You can click to select the columns you want to associate with, and you can also manually create them when you clickWrite the join statement manually. Here we associate cities and parks cityid with a one-to-many relationship. Because cityid is the primary key in each tableUnique keyCheck box selected. (In this way, the sub-database can improve the synchronization performance, and is only valid when the associated field is a primary key). Then, clickOK.

InFilter table rowsThe associated table of the table with the synchronization query condition is displayed. ClickNext.

InSnapshot agentThe page will prompt you whether to create a snapshot immediately. You can also click hereChangeIn this example, we select 14 days, and then clickNext.

InAgent SecurityPage, we want to select the account under which the snapshot proxy thread runs, so we choose to clickSecurity Settings.

NextSnapshot Agent SecurityPage, selectRun under the following Windows AccountSingle region, and then enterSyncdomain/syncuserInProcess accountIn the text box, enter P @ ssw0rd in both the password and the confirmation password. You will find that this user is the domain user we created.By impersonating the process accountYou can click the selected button.OK.

BackAgent SecurityThis page,Snapshot agentWe just joinedSyncuserUser. Then clickNext.
 

InWizard actionsPage, we select the content of both check boxes, and then clickNext.

InScript File PropertiesPage, this page allows us to select the script storage location, we keep the default selection, and then clickNext.

At the endComplete the wizardPage, wePublication nameEnter parksurverypublication as the name of our release copy. When we confirm that the information we entered is consistent with the information shown below, we can clickFinishTo complete thisPublicationNow.

InCreating publicationOn this page, we can see thisPublicationIf all of them are displayedSuccessThen we can clickCloseTo close it, suchPublicationIt is created.