Defining a Horizontal Partition

Defining a Horizontal Partition

Using transformable subscriptions, you can exclude certain rows on a per Subscriber basis. To partition data horizontally for a transformable subscription, you must:

• Enable the publication to use horizontal DTS partitions by selecting the Provide support for horizontal DTS transformation scripts option in the properties for each article for which you want a horizontal DTS partition.
  
  
• Use the Transform Published Data Wizard to build the DTS package. The Define Transformations page of the Transform Published Data Wizard lists the published tables. To partition published data horizontally, select a table in that page by clicking its transform (...) button. On the subsequent Column Mappings and Transformations page, click the Transformations tab. The Transformations tab contains an edit box you use to write the Microsoft® ActiveX® scripts that define the horizontal partition.
  
  
• Include Microsoft ActiveX scripts written to the DTS object model with the DTS package. The ActiveX script needs to specify the filter criterion and be able to check if:

1. A newly inserted row needs to be propagated to the Subscriber.
   
   
2. Rows updated at the Publisher no longer meet the partitioning criterion and need to be deleted at the Subscriber.
   
   
3. Rows updated at the Publisher meet the filter criteria and need to be inserted at the Subscriber.

Supporting steps 2 and 3 is possible by using XCALL syntax for UPDATE, which ensures before and after values for the row after an update. This allows the ActiveX script to determine if the row is moving into or out of the partition.

The ActiveX scripts you use to define the horizontal partition need to follow guidelines that are explained in the following example. You would only need to provide the two functions IsInPartition() and Transform(). You do not need to change the Main() function.

You can also use the system stored procedures sp_addarticle and sp_changearticle to specify horizontal partitions for snapshot or transactional publications that allow data transformations.

If a publication allows transformable subscriptions, you can set @status parameter for sp_addarticle to a value of 64 to indicate that the article supports DTS horizontal partitions. If the parameter is not set, it is not possible to insert or delete rows at the Subscriber when rows move into or out of the partition resulting from an update at the Publisher. If the status is set, the before image of an updated row will be sent to the distribution database by the Log Reader Agent and then to the DTS package inside the Distribution Agent. This will increase network traffic, and should be used with caution.

If the publication allows data transformations, sp_changearticle accepts the values of 'dts horizontal partitions' and 'no dts horizontal partitions' for the status article property. Changes to this property are not allowed when there are active subscriptions (snapshots have been generated for the subscriptions). If the publication allows immediate updating, the Snapshot Agent needs to be run after this property is changed. Snapshots generated previously will be obsolete.

Validation of Custom Data Partitions

Using transformable subscriptions to exclude rows or columns and create horizontal and vertical partitions means that data at the Subscriber will be different than data at the Publisher. ROWCOUNT and CHECKSUM validation will report discrepancies. Typically, you would not want to run validation for those Subscribers.

Dynamic filters available with merge replication do not have this limitation because the validation is compared with the SQL Server view that defines the partition.

Example

Function Main()  
   '  If the current source data is the old values of the row before an update, verify if the old row is in the partition or not.  
   '  ReplicationChangeType values:  
   '   1 = Insert. Source data is from a row that was inserted at the source.  
   '   2 = Update. Source data is from a row that was updated at the source. The data are values before the update.  
   '   3 = Delete. Source data is from a row that was deleted at the source.  
   '   4 = Before Update. Source data is from a row that was updated at the source. The data are values before the update.    
   
   If DTSGlobalVariables("ReplicationChangeType").Value = 4 Then  
   OldRowInPartition = IsInPartition()  
   Main = DTSTransformStat_SkipRow  
   Else    'Error check to prevent users from forgetting to enable the article for DTS horizontal partition.
   If DTSGlobalVariables("RelicationChangeType").Value=2 and IsEmpty(OldRowInPartition)=True Then
      Err.Raise 1, "Replication DTS ActiveX Script", "The article does not support DTS horizontal partitions."
   End If   
If IsInPartition() Then  
   'Set default return status, which means using the query type set by the replication process.  
   Main = DTSTransformStat_OK  
   Transform  

   ' If it is an update, test to see if the row has just moved into the partition. If so, overwrite the query type to insert from update.  
   If DTSGlobalVariables("ReplicationChangeType").Value = 2 And _  
      Not OldRowInPartition Then  
   Main = DTSTransformStat_InsertQuery  
   End If  
   Else  

   ' If it is an update, test to see if the row has just moved out of the partition. 
   ' If so, overwrite the query type to insert from update.  
   If DTSGlobalVariables("ReplicationChangeType").Value = 2 And _  
      OldRowInPartition Then  
   Transform  
   Main = DTSTransformStat_DeleteQuery  
   Else  
   Main = DTSTransformStat_SkipRow  
   End If  
   "  
   End If  
   End If  
   End Function  

Function Transform()  
      DTSDestination("CustID") = DTSSource("CustID")
      DTSDestination("LName") = DTSSource("LName")
      DTSDestination("FName") = DTSSource("FName")
      DTSDestination("Item") = DTSSource("Item")
      DTSDestination("SaleAmt") = DTSSource("SaleAmt")
End Function  
   Function IsInPartition()  
   ' In partition if the first char is uppercase and begins with A, B, C or D.  
    If Left(DTSSource("LName"), 1) <= "D"  Then     
   IsInPartition = True  
   Else  
   IsInPartition = False  
   End If  
   End Function