SQL Server's replication feature lets you reproduce data from one database to another. A major component of SQL Server's replication technology involves horizontal (row) and vertical (column) partitioning, which lets you control what data you replicate. Once you understand horizontal and vertical partitioning, you can streamline how you store and distribute data from your SQL Server databases.
To send data from your system to other systems, you publish the data on the source machine. For example, to distribute data from server VSI10 to other machines, you begin by installing Publishing on VSI10, as you see in Screen 1 (page 124).
SQL Server uses a dedicated database, the distribution database, as a repository of replication information. The distribution database holds replicated information until the system passes it to the appropriate subscribers. Note that users can't access this database; it is for system use only.
After you install the replication publishing, you determine the publishing options on the VSI10 system, as you see in Screen 2 (page 124). In this dialog box, I've set SQL Server to publish information from the tracking database and to let the VSI97 machine subscribe to this data. The next step is to create a publication. Publications consist of articles, which are the data to replicate.
For example, suppose you want to maintain an application that tracks sales leads by location, as you see in Table 1 (page 124). You create a new publication to replicate the data in this table. Screen 3 (page 124) shows the dialog box for editing the new publication. After you create the publication, you can use horizontal, vertical, and combined partitioning to selectively distribute articles for this publication.
The Restriction Clause portion of the dialog box is where you can tell SQL Server what rules to apply when determining how to replicate information. In this case, you create an article holding rows with a territory_code between 0 and 1000. You can then replicate this article to appropriate machines. You can create as many articles as necessary and replicate them to the right locations.
The revenue_potential column contains sensitive information that you don't want your users to see. You can create a view that doesn't show the column, you can restrict permissions, or you can block user access to this column by not replicating it. To keep from replicating this column, you clear the Replicate check box for the revenue_potential column, as you see in Screen 5, when you configure the replication. Keep in mind that you must always replicate the primary key columns.
Learn more by visiting SQL Server Pro online.
|TABLE 1: Tracking Sales Leads|
|TABLE 2: Sales Leads with Revenue Potential|