September 24, 2009 at 12:14 pm
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??????
September 29, 2009 at 5:46 am
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.
January 31, 2012 at 8:19 am
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