Lesson 2: Planning for Replication | MCSE Microsoft SQL Server 2000 System Administration(c) Training Kit (c) Exam 70-228

3 4

Replication requires planning for a variety of issues, some of which will be covered in this lesson. In this lesson you will learn about planning replication security. You will also learn about filtering data for performance and security. Finally, you will learn about options for storing and applying the initial snapshot.

After this lesson, you will be able to

Plan replication security
Understand data filtering options
Choose among initial snapshot options

Estimated lesson time: 15 minutes

Planning for Replication Security

Replication security is implemented at a number of levels. First, only members of the sysadmin server role can create and administer Distributors, Publishers, and Subscribers. This includes enabling a database for replication. At the database level, only a member of the sysadmin server role or the db_owner fixed database role in the database being published can create and configure publications and subscriptions. Only members of the sysadmin server role or the replmonitor fixed database role in the distribution database can view replication activity.

When a remote Distributor is used, security can be configured for connections between the Publisher and the Distributor. The connection uses the distributor_admin SQL Server login account (Mixed Mode Authentication must be used). At the remote Distributor, the Publisher can be configured as trusted (no password required) or nontrusted (requiring a password). Using a nontrusted configuration is recommended.

Note

The only ways you should change the distributor_admin password are: using the Distributor tab in Publisher And Distributor Properties in Enterprise Manager or using the sp_changedistributor_password system stored procedure.

By default on Windows 2000 and Windows NT 4.0, the snapshot folder used for replication is located at C:\Program Files\Microsoft SQL Server\Mssql\Repldata on the Distributor and uses the hidden administrative share (for example, \\SelfPacedCPU\C$\Program Files\Microsoft SQL Server\Mssql\Repldata). On Windows Me and Windows 98, the same path is used but no share is created. Each Snapshot Agent must have full control access to this folder. Each Distribution Agent and Merge Agent must have read access to the snapshot folder location. By default on Windows 2000 and Windows NT 4.0, these agents run in the security context of the SQL Server Agent domain user account. Replication agents can only access this administrative share if the domain user account is a member of the local Administrators group on the Distributor. If the domain user account is not a member of the local Administrators group on the Distributor or if Windows Me and Windows 98 computers are involved in replication, you should create a hare for the snapshot folder and grant necessary permissions to the share to the domain user accounts under which these replication agents will run.

Each publication contains a publication access list (PAL) containing the logins permitted to access the publication. By default, the logins included on the PAL for a new publication are members of the sysadmin server role (this includes the SQL Server Agent domain user account) and the login of the user creating the publication (such as a member of the db_owner role). In complex replication environments, you might need to add additional users to the PAL.

The following permissions are required in order for replication to function properly.

The Snapshot Agent must be able to connect to the publication database on the Publisher and the distribution database on the Distributor (in addition to the snapshot folder discussed in the section "Transactional Replication").
The Log Reader Agent must be able to connect to the publication database on the Publisher and the distribution database on the Distributor.
The Distribution Agent must be able to connect to the distribution database on the Distributor and the subscription database on the Subscriber (in addition to the snapshot folder discussed in the section "Transactional Replication").
The Merge Agent must be able to connect to the distribution database on the Distributor, the publication database on the Publisher, and the subscription database on the Subscriber (in addition to the snapshot folder discussed in the section "Transactional Replication").

Filtering Published Data

Filtering published data is used for security purposes and to enhance performance. Filtering allows you to limit published data horizontally (only specified rows) or vertically (only specified columns). For example, columns containing sensitive information or large image data can be eliminated from replication. Also, rows containing information not related to a particular sales region can be eliminated. Filters can be static or dynamic.

Static filters limit rows or columns for a publication, and all Subscribers receive the same data (unless transformable subscriptions are used). All types of replication can use static filters. To create different partitions of data for different Subscribers using static filters, either separate publications must be created or transformable subscriptions must be used. Horizontal filtering can significantly affect the performance of transactional replication because every row must be evaluated in the publication database transaction log.

Dynamic filters are used to provide different partitions of data to different Subscribers based on SQL Server functions (such as user name or host name). Join filters are used to maintain referential integrity between two tables involved in replication (such as a primary key/foreign key relationship). Dynamic and join filters are available only for merge replication. When you are using dynamic filters, dynamic snapshots can also be used to create custom snapshots for each type of Subscriber. This can significantly improve the performance when applying the initial snapshot, but does require additional space for the snapshot folder and additional time to create the initial snapshot.

Transformable subscriptions with a custom filter can be used with snapshot and transactional replication to dynamically create partitions of data for individual Subscribers. Transformable subscriptions use the capabilities of DTS to customize and transform data being replicated based on the needs of individual Subscribers. However, updatable subscriptions are incompatible with transformable subscriptions.

Choosing Initial Snapshot Options

By default, initial snapshot files are copied to the Repldata folder on the Distributor. However, you can choose to store the snapshot files in an alternate location, such as a network drive or on a compact disc, instead of or in addition to the default location. Snapshot files saved to an alternate location can be compressed (using the Microsoft CAB file format) to fit onto removable media or to speed transmission over a slow network connection. Compressing snapshot files takes additional time.

By default, either the Distribution Agent or the Merge Agent applies the snapshot to the subscription database. For large publications, applying the initial snapshot manually from compact disc or other storage device (such as tape) might be faster than sending the file over the network.

Finally, because snapshot files can consume substantial hard disk space, you can choose not to maintain snapshot files. Snapshot files are automatically retained if you specify that the snapshot be retained or if you enable the publication for anonymous subscriptions. If you choose neither of these options, SQL Server will delete the snapshot after all Subscribers have applied the initial snapshot. If a new Subscriber attempts to synchronize, the Subscriber will either have to wait until the next time a snapshot is generated automatically or an administrator will have to manually start the Snapshot Agent.

Lesson Summary

Only members of the sysadmin server role can configure the overall replication topology. Members of the sysadmin server role and the db_owner fixed database role in a database can create and configure publications and subscriptions. The Snapshot Agent must have full control permissions and the Distribution Agent and the Merge Agents must have read permissions to the snapshot folder (unless the initial snapshot is applied manually). In addition, the appropriate permissions must be granted to the replication agents on the publication, distribution, and subscription databases. In addition, published data is frequently filtered horizontally and vertically to improve performance and customize data based on individual Subscriber needs. In addition to static filters, dynamic filters and transformable subscriptions are used to filter data based on custom needs. Finally, the initial snapshot can be copied to an alternate location (such as a compact disk) and compacted using the Microsoft CAB file format.