Using Replication’s Horizontal and Vertical partitioning capabilities to manage publications in a distributed database environment
By: Lynn Zhu and Demico Quinn
Before coming up with the solution that this article will describe, we decided that this
would be a great topic to write about and share our experiences with others. We also
found that there were not a lot of articles out there that focused on Replication. More
specifically, articles that chronicled case studies where column and row filters were
being used as an integral part of the solution. It is our hope that you will find this
article to be helpful and inspire readers to write more articles about Replication.
There are times that you may find yourself on a project where you have one data source
and need to publish to many subscribing databases. Often times, Replication is not looked
at as a viable solution to achieving this goal. We can only attribute this to the
“blood sweat and tears” that it took to get Replication in SQL 6.5 to work smoothly.
Fortunately those days are gone and Microsoft has only improved the way Replication
works in SQL 2000. For this project, we will demonstrate the way we utilized the
Horizontal and Vertical partitioning capabilities that Microsoft has added to the
Replication utility.
To begin, we have company A, which sells it’s products based on district as the following
heme illustrates. figure1
In this scenario a products given retail price may differ depending upon whether the
product was purchased using the website or sold at one of our store locations.
Additionally, a product can also have different discount prices in different districts,
and be sold on either the web or in the store. Often times the discounts that are
found on the website may or may not be applicable to “in store sales”, and the same
rule applies to discounts found in the store.
All transactions will write to one database, which will act as the publisher to multiple
subscribing databases. The database is called CentralInfo, and as earlier stated will
act as the publishing database for our demonstration. There will be two databases that
will subscribe to publications that are based off of the CentralInfo database. The
WebSale database only needs the information about the products, web sale prices, and
discounts that will be sold through the web by districts. The StoreSale database only
needs the information about the products, store sale prices, and discounts that will
be sold through the store by districts. To meet this business requirement we had to
make some small modifications to the scheme. In order to restrict the rows and columns
that will be defined in the publications, we added two columns (Use4WebSale and
Use4StoreSale) to the Products and Products2District tables as row filters.
(After adding Use4WebSale, and Use4StoreSale columns)
The filters are defined as integer data types and the default value for columns
Use4WebSale and Use4StoreSale is 0. If a given product is for web sales then the value
of column Use4WebSale will be updated to 1. If a given product is for store sales then
the value of column Use4StoreSale will be updated to 1. For products that are meant for
both web and store sales the value of both Use4WebSale and Use4StoreSale are set to 1.
The following chart illustrates the possible scenarios.
Product2Distirct
| productid | districtid | websaleprice | storesaleprice | discountid | discountstartdate | discountenddate | use4websale | use4storesale |
| 3 | 2 | 20 | 15 | 1 | 12/16/2002 | 7/31/2003 | 0 | 1 |
| 4 | 2 | 30 | 25 | 2 | 12/25/2002 | 1/15/2003 | 1 | 1 |
| 5 | 2 | 25 | 20 | 1 | 12/16/2002 | 7/31/2003 | 1 | 0 |
Products
| productid | productname | productmodel | warehouseid | producttypeid | use4websale | productcost | use4storesale |
| 3 | Oil filter | RS-300 | 2 | 1 | 1 | $7.99 | 1 |
| 4 | Side mirror | PA-100 | 2 | 2 | 0 | $19.99 | 1 |
| 5 | Door Bulb | DB-022 | 1 | 3 | 1 | $15.99 | 0 |
| 6 | Window Bulb | BW-012 | 1 | 3 | 0 | $6.99 | 0 |
There will be two publications and two subscribers to be set up.
1a. Store publication configurations –
Now that we have established what changes needed to be made to the scheme, we can now
start defining our publications. We will define store publication and identify the
articles that will be used in the publication. To create the publication the “Create
Publication wizard” was used. And defined as follows:
Publication Name: CentralInfo_to_StoreSale
Publishing Database: CentralInfo
Articles:
| Products |
| Products2Districts |
| Districts |
| States |
| WareHouses |
| Discounts |
| ProductType |
Snapshot Options : The following options refer to the initial snapshot.
-Drop existing tables and recreate
-Uncheck “Include declared referential integrity”
-Uncheck Clustered indexes
-Uncheck Non-clustered indexes
After initial snapshot has been applied, the filters can be added to the articles and
re-initialization occurs on the publication. You must also change the snapshot property
to “ Delete data in the existing tables that match the row filter statement”. In the
case where you have populated your CentralInfo database with data, you can apply all
filters prior to the initial snapshot. This will eliminate the need to run the
initialization snapshot a second time. Filters are defined as follows:
For the Products article add this row filter (table) Row Filter: Use4StoreSale =1
Syntax: SELECT <published_columns>
FROM <<TABLE >>
WHERE <<TABLE>>. Use4StoreSale =1
For the Products2Districts article add this row filter(table) Row Filter and Column
Filter: Use4StoreSale =1
Under the Column filter tab for the article “Products2Districts”, uncheck column
WebSalePrice
Syntax: SELECT <published_columns>
FROM <<TABLE>>
WHERE <<TABLE>>. Use4StoreSale =1
1b. Store subscriber configurations –
The following describes the subscriber options that define how the StoreSale database
will receive data from CentralInfo Database. The subscribing database can be created
during the subscription process or pre-exist.
Subscription name: Server name: StoreSale Type: Push Publish Interval: Optional Scheme: Scheme is created by the Snapshot agent
Subscriber StoreSale
2a. Web publication configurations –
Now, we will define the web publication and identify the articles that will be used.
To create the publication the “Create Publication wizard” was used. And defined as
follows:
Publication Name: CentralInfo_to_WebSale Publishing Database: CentralInfo Articles:
| Products |
| Products2Districts |
| Districts |
| States |
| WareHouses |
| Discounts |
| ProductType |
Note: The articles in this publication are the same as defined in our StoreSale publication. Repeat all steps in this publication as you did for the StoreSale publication, the only exception will be to filter on the column; “Use4WebSale”.
2b. Web subscriber configurations –
The following describes the subscriber options that define how the WebSale database will receive data from CentralInfo Database. The subscribing database can be created during the subscription process or pre-exist.
Name: SQLSERVERNAME: WebSale Type: Push Publish Interval: Optional Scheme: Scheme is created by the Snapshot agent
Summary of implementation steps –
The following is a step-by-step summary of the tasks
performed in order to implement this solution.
1.Run script to create the database scheme (CentralInfo). 2.Create Transactional Publication which includes the following sub tasks: 2.1Create StoreSale database 2.2Define articles with article options 2.3No article filters 3.Run Snapshot 4.Run distribution 5.Modify Transactional publication which includes the following sub tasks: 5.1Add filter to each article with the following “Use4StoreSale =1” 5.2Update Snapshot option to “ Delete data in the existing tables that match the row filter statement” 5.3Re-initialize the publication 6.Create Transactional Publication which includes the following sub tasks: 6.1Create WebSale database 6.2Define articles with article options 6.3No article filters 7.Run Snapshot 8.Run Distribution 9.Modify Transactional publication which includes the following sub tasks: 9.1Add filter to each article with the following “Use4WebSale =1” 9.2Update Snapshot option to “ Delete data in the existing tables that match the row filter statement” 10.Re-initialize the publication 11.Run Snapshot (Once data has been mapped via the Customer Care department) 12.Run Distribution 13.Run scripts to load data into the CentralInfo database. 14.Once data has been published to the subscribers, check to insure that the filters are working as expected.
