PROBLEM WHEN DOING TRANSACTIONAL REPLICATION WITH PARTITIONED TABLES WHEN HAVING VIEWS IN ARTICLES

  • I am currently doing Transactional Replication from a SQL Server 2005 Enterprise Edition 64bit SP3 running OS Windows Server 2003 R2 Standard x64 Edition SP2

    to another SQL Server with exactly the same SPs and OS and OS SPs

    I have a couple of tables that are partitioned and tables that are not.

    On all the table articles I have set the following to TRUE

    Copy Noncluster indexes

    Copy file group associations

    Copy table partitiong schemes

    Copy index partitiong schemes

    I had all running OK, until I added 3 views into the articles of the publication.

    After I did a reinitialize on the subscriber I noticed that all the data went into the PRIMARY file group.

    I noticed that on the subscriber the tables where partitioned as normal but the Patition Scheme had all the File groups pointing to PRIMARY as follows

    CREATE PARTITION SCHEME [ps_DW_General] AS PARTITION [pf_FactMonth] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY])

    GO

    but the Partition Scheme should look like this

    CREATE PARTITION SCHEME [ps_DW_General] AS PARTITION [pf_FactMonth] TO ([FACT_General_190001], [FACT_General_200410], [FACT_General_200411], [FACT_General_200412], [FACT_General_200501], [FACT_General_200502], [FACT_General_200503], [FACT_General_200504], [FACT_General_200505], [FACT_General_200506], [FACT_General_200507], [FACT_General_200508], [FACT_General_200509], [FACT_General_200510], [FACT_General_200511], [FACT_General_200512], [FACT_General_200601], [FACT_General_200602], [FACT_General_200603], [FACT_General_200604], [FACT_General_200605], [FACT_General_200606], [FACT_General_200607], [FACT_General_200608], [FACT_General_200609], [FACT_General_200610], [FACT_General_200611], [FACT_General_200612], [FACT_General_200701], [FACT_General_200702], [FACT_General_200703], [FACT_General_200704], [FACT_General_200705], [FACT_General_200706], [FACT_General_200707], [FACT_General_200708], [FACT_General_200709], [FACT_General_200710], [FACT_General_200711], [FACT_General_200712], [FACT_General_200801], [FACT_General_200802], [FACT_General_200803], [FACT_General_200804], [FACT_General_200805], [FACT_General_200806], [FACT_General_200807], [FACT_General_200808], [FACT_General_200809], [FACT_General_200810], [FACT_General_200811], [FACT_General_200812], [FACT_General_200901], [FACT_General_200902], [FACT_General_200903], [FACT_General_200904], [FACT_General_200905], [FACT_General_200906], [FACT_General_200907], [FACT_General_200908], [FACT_General_200909], [FACT_General_200910], [FACT_General_200911], [FACT_General_200912], [FACT_General_201001], [FACT_General_201002], [FACT_General_201003], [FACT_General_201004], [FACT_General_201005], [FACT_General_201006], [FACT_General_201007], [FACT_General_201008], [FACT_General_201009], [FACT_General_201010])

    GO

    So this made sense why all the data landed up in the PRIMARY file group, but obviously this is wrong.

    Ok so I removed the views and ran the Snapshot again and did a reinitialize on subscriber and everything was back to normal and partitioned table data

    went into the correct file groups.

    OK tables and Views have no relation to one another and I find it very strange how this happens.

    I have made sure that all tables in the Views are being replicated and do appear in the table articles.

    I even created a basic view with the following code that has no refernce to a any table and the some issue ocuurs

    CREATE VIEW v_Test

    As

    Select 1 as something

    Why are parition schemes affected by views??????

  • Hi all,

    Just to let everyone know, I landed up logging a call with MS about this issue and its been confirmed as a bug with SQL Server 2005 SP3.

    MS says SQL Server 2008 doesnt have this issue.

    Awaitng further instructions from MS, will let you know what the final out come is.

  • SQL Server 2008 R2 with SP1 has this problem too.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply