To start the Create Publication Wizard, click the Publisher. On the Tools menu, point to Replication, then click Create and Manage Publications. In the databases and publications list, click the database to create a publication from, then click Create Publication to bring up the Create Publication Wizard welcome dialog box. Click Next to proceed. On the Choose Publication Type screen, choose Merge publication, as you see in Screen 3, page 26. The next dialog box lets you specify subscriber types. In this case, all subscribers are SQL Servers. As Screen 4 shows, Specify Articles lets you choose all or a subset of the articles (tables) for publication. Subscribers can subscribe only to a publication, not to individual articles within a publication.
When publishing a table under merge replication, SQL Server changes the database schema: It identifies a unique column for each row in the table being replicated. This column identifies the row uniquely across multiple copies of the table. If the table contains a uniqueidentifier column with the ROWGUIDCOL property, SQL Server automatically uses that column as the row identifier for the replicated table. Otherwise, SQL Server adds a rowguid column (with the ROWGUIDCOL property) to the table. SQL Server adds an index and the rowguid column, as in Screen 5.
After you specify articles, the wizard prompts you to provide a name and description for the publication, as in Screen 6. Next, build the dynamic and merge-filtering properties into the publication. Select Yes to implement filtering. The next screen confirms your decision to filter the data in the publication. We wanted to filter the data to the subscribers based on rep_id, which maps to the Windows NT logon ID. So we selected the dynamic filtering option, as in Screen 7. With the dynamic filter, all field users can subscribe to the same publication. During synchronization, the Merge Agent uses the subscriber's username, which maps to the value in the rep_id column of the field_user table, to connect to the publisher and transmits only the data specific to that username. We used the intrinsic function SUSER_SNAME() to return the login ID name from a user's SID, as you see in Screen 8.
The next screen leads to the creation of the join filters. Join filters let you extend the filtering of rows in one table to rows in related tables. We had already filtered the field_user table horizontally for publication. We also wanted to include in the publication other tables that contain data related to the data in the field_user table. However, we didn't want the publication to include all the data in the related tables. Join filters let you include only those rows in the related table that you define as relevant to the publication. In Screen 9 you can see that the wizard automatically specifies the necessary joins on all related tables.
On the next screen, you can choose to allow anonymous subscribers or enabled subscribers. We chose enabled subscribers because we wanted to limit access to the publications to only qualified field representatives. Next, a dialog box lets you set the schedule for the snapshot process. The wizard automatically creates a job based on the schedule you set. Before a subscriber can participate in the merge replication process, the subscription must have a starting point. The subscriber must have tables with the same schema and data as the publisher tables. The snapshot process generates the initial synchronization by copying the complete current publication from the publisher to the subscriber.
Now click Finish, and the wizard creates the publication based on the options you selected and returns you to the Create and Manage Publications dialog box, where you'll see under the Pubs database an icon displaying the new pubs_merge article that you created. Congratulations! You created your first merge article with dynamic and merge filtering.
The Subscriber's Side
Let's take a look at the other side of the replication process—the subscriber. This stage of replication is generally self-explanatory, and the Pull Subscription Wizard does an excellent job of walking you through the steps. However, in our scenario, it was important to look more closely at two aspects of security planning: publication access lists (PALs) and agent login security.
PALs. SQL Server lets you determine which logins have access to publications. SQL Server creates the PAL with default logins, but you can add or delete logins from the list. You can view or modify the properties of a publication through the Tools menu: Point to Replication, then click Create and Manage Publications. Select the publication and click Properties and Subscription. In the Properties dialog box, click Publication Access List. We added all the field reps' logins to the Publication Access List tab of the Properties dialog box, which Screen 10 shows.
Agent login security. Replication implements login security by requiring a user to have a valid login account and password to connect to a publisher, distributor, or subscriber. Replication agents, which run under SQL Server Agent, use the associated logins and passwords to connect to the various replication objects and to perform their roles in the synchronization process. On Windows 9x platforms, which our client uses on the client side, SQL Server Agent and the replication agents run under the security account of the user logging on to Windows. On NT platforms, the replication agents run under the login or security context of the SQLServerAgent service.
Dynamic filtering criteria based on the login ID works well for Win9x subscribers, but not for NT subscribers. For NT subscribers, you need to modify the Merge Agent's login properties so that it runs under the account of the user who is logging on to NT. You can access this feature by clicking the Pull Subscriptions folder in the database folder, as in Screen 11, then right-clicking the pull subscription icon and selecting Properties from the object menu. At the Pull Subscription Properties dialog box, click the Security tab. Here, you specify the login account the Merge Agent uses to access both the publisher and the distributor. We used the user's login account.
Whether you're supporting a mobile sales team, a data warehouse, or another distributed environment, replication becomes the keystone in carrying data quickly and reliably throughout the enterprise. SQL Server 7.0 provides a variety of scalable replication solutions that can fulfill the broadest spectrum of information requirements.
End of Article
Prev. page
1
[2]
next page -->
