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;
 GO
 /* Create a database to act as our subscriber */ CREATE DATABASE sandbox_subscriber;
 GO
 /* Navigate to our publisher db */ USEsandbox_publisher;
 GO
 /* Create a partitioning function */ CREATE PARTITION FUNCTION
     [test_monthlyDateRange_pf] (DATETIME)
     AS RANGE RIGHTFOR VALUES
    ('2008-01-01', '2008-02-01', '2008-03-01');
 GO
 /* Associate the partition function with a partitioning scheme */ CREATE PARTITION SCHEME test_monthlyDateRange_ps
     AS PARTITION test_monthlyDateRange_pf
     ALL TO ([PRIMARY]);
 Go
 /* 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 */ CREATE NONCLUSTERED INDEX IX_orders_aligned
    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 */ USEsandbox_publisher;
 GO
 /* Enable publication */ EXECUTE sp_replicationdboption
      @dbname = N'sandbox_publisher'
     , @optname = N'publish'
     , @value = N'true';
 GO
 /* 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 */ GO
 /* 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
 GO

You can find more about the @schema_option under sp_addarticle on Books Online (http://msdn.microsoft.com/en-us/library/ms173857.aspx)

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;
 GO
 /* 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:

• SQLFool - Partitioning posts - http://sqlfool.com/tag/partitioning/
• Partitioned Tables and Indexes in SQL Server 2005 - http://msdn.microsoft.com/en-us/library/ms345146.aspx
• Querying Data and Metadata from Partitioned Tables and Indexes

  - http://msdn.microsoft.com/en-us/library/ms187924.aspx