Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Configuring Replication for Partitioned Tables Using T-SQL

By Michelle Ufford,

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:

Total article views: 3716 | Views in the last 30 days: 31
Related Articles

Replication Publication issue

Replication Publication issue


Urgent Questions about Replication and Partition table

Replication Table Partition


Partition after Replication

Creatition Partition after replication creation


Error creating Publication

Error creating Publication - can't find procedure error 2812


can not create publication

can not create publication


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones