Configuring Replication for Partitioned Tables Using T-SQL

, 2009-01-29

By default, partitioning schemes are not persisted when replicating partitioned tables to a subscriber. This can be nice if you want to replicate partitioned data from SQL 2005 Enterprise to SQL 2005 Standard (where partitioning is not supported), but most of the time, you probably want the replicated table to be partitioned, too. This post will walk you through the basics of creating transactional replication for a partitioned table to ensure the subscription table is also partitioned.

First, let's set up our test databases and data:

 /* Create a database to act as our publisher */
 CREATE DATABASE sandbox_publisher;
 /* Create a database to act as our subscriber */
 CREATE DATABASE sandbox_subscriber;
 /* Navigate to our publisher db */
 /* Create a partitioning function */
     [test_monthlyDateRange_pf] (DATETIME)
    ('2008-01-01', '2008-02-01', '2008-03-01');
 /* Associate the partition function with a partitioning scheme */
 CREATE PARTITION SCHEME test_monthlyDateRange_ps
     AS PARTITION test_monthlyDateRange_pf
     ALL TO ([PRIMARY]);
 /* Create the partitioned table that we'll replicate */
 CREATE TABLE dbo.orders
       order_id intidentity(1,1)
     , orderDate DATETIME
     Constraint PK_orders
         PrimaryKey Clustered
        (order_id, orderDate)
 ) ON test_monthlyDateRange_ps(orderDate);
 /* Create some records to play with. */
 INSERT INTO dbo.orders
 SELECT '2007-12-31' UNIONAll
 SELECT '2008-01-02' UNIONAll
 SELECT '2008-01-03' UNIONAll
 SELECT '2008-01-04' UNIONAll
 SELECT '2008-02-01' UNIONAll
 SELECT '2008-02-02' UNIONAll
 SELECT '2008-03-01' UNIONAll
 SELECT '2008-03-02';
 /* Let's create an aligned index */
    ON dbo.orders(order_id);
Now that we have a partitioned table and some databases to work with, let's set up replication using T-SQL.
 /* Now, let's set up replication */
 /* Enable publication */
 EXECUTE sp_replicationdboption
      @dbname = N'sandbox_publisher'
     , @optname = N'publish'
     , @value = N'true';
 /* Create our publication */
 EXECUTE sp_addpublication
      @publication = N'myTestPublication'
     , @sync_method = N'concurrent'
         /* synchronization mode;
           concurrent = native bcp w/o locking */
     , @description = N'Test Publication'
     , @status = N'active'
     , @independent_agent = N'true'
         /* Specifies if there is a stand-alone Distribution
           Agent for this publication */
     , @immediate_sync = N'true'
     , @retention = 0;
         /* Is the retention period in hours for subscription
           activity; if 0, well-known subscriptions to the
           publication will never expire */
 /* Create our snapshot agent */
 EXECUTE sp_addpublication_snapshot
      @publication = N'myTestPublication'
    , @frequency_type = 1;-- Execute once

Up until now, this has been pretty straight forward. This next step is where we specify the bitwise product for article options. We're going to specify that we want to copy partitioning schemes for tables and indexes, and we also want to copy nonclustered indexes.

Personally, I prefer to cheat and let SQL Server tell me what the appropriate bitwise product should be for a given article. To do this, I walk through the process of creating a new article using the GUI, then I script it out and snag the @schema_option value.





Using the @schema_option above, let’s now create our article.

 /* Add our partitioned table as an article */
 EXECUTE sp_addarticle
      @publication = N'myTestPublication'
     , @article = N'orders'
     , @source_owner = N'dbo'
     , @source_object = N'orders'
     , @type = N'logbased'
     , @destination_table = N'orders'
     , @destination_owner = N'dbo'
     , @ins_cmd = N'CALL sp_MSins_dboorders'
     , @del_cmd = N'CALL sp_MSdel_dboorders'
     , @upd_cmd = N'MCALL sp_MSupd_dboorders'
     , @schema_option = 0x00000000081B50DB; -- partitioning options

You can find more about the @schema_option under sp_addarticle on Books Online (

Now let's finish up with our script to create the snapshot and add a subscription,

 /* Generate an initial snapshot */
 EXECUTE sp_startpublication_snapshot
      @publication = N'myTestPublication';
 /* Create our subscription */
 EXECUTE sp_addsubscription
      @publication = N'myTestPublication'
     , @subscriber = N'YourServerName' -- change!
    , @destination_db = N'sandbox_subscriber';

When everything is done, check your subscriber and ensure your table has been created. Also verify that the table is partitioned. If you do not see it right away, wait a minute and try again... SQL Server just may not have caught up yet.

 USE sandbox_subscriber;
 /* You should now have a partitioned table with a partitioned
    nonclustered index in your subscription database... */
 EXECUTE sp_help N'dbo.orders';


It may sometimes be beneficial to use a different partitioning scheme on the subscription table. In that case, create the table on the subscriber in advance using the desired partitioning scheme; then specify that, during initialization, the objects should be retained if they already exist.


I hope that helps get you started with replicating partitioned tables. In my next post, we'll walk through the process of swapping out a partition on a replicated table (SQL 2008 only). For more information on partitioning, please check out the following resources:


4.9 (10)




4.9 (10)

Related content

Creating a table with horizontal partitioning in SQL Server

During the planning phase I found out about the new partitioning feature in SQL Server 2005 and later editions of SQL Server. It is a vast topic with several associated operations and configurations involved. In this tip I will share what I learned about partitioning, show an example to implement horizontal table partitioning along with a comprehensive list of related benefits and considerations.


4,028 reads

Review of SQL Comparison and Synchronization Toolkit

This week Andy takes a look at a new product from Red-Gate that gives you even more options than you get with their SQL Compare and SQL Data Compare tools - now you can build your own. Add the features you always wanted. Build automated processes instead of using the GUI. It's an interesting product - read the review and then maybe even enter the contest to win a free copy.


5,364 reads

Review of Data Compare v.151

In this product review Andy takes a look at Data Compare, the second of three products in the SQL Bundle available from Red-Gate software. It's a very handy program that lets you compare data between two tables and optionally generate sql statements to syncronize the data. An interesting alternative to replication!


8,759 reads