Hey all
For performance reasons i want to move the Merge Replication tables in a DB of mine (mainly MSMerge_Tomestone and MSMerge_Contents) to a seperate filegroup ... it's a heavy transactional DB and the index fragamentation rate is quite phenominal!
The issue i've got however, is that when i try to transfer the table i get a warning saying that the current ANSI_PADDING / NULLS state is off in the current table, but will be switched on in the new version of the table! Not sure why it wants to do this, but clicked the cancel button regardless as these settings potentially affect queries, and not knowing what the replication queries do, switching this state over seemed a little, well, dangerous!
So the actual question is, has anyone ever done this before and is there any adverse effect i need to defend against
Cheers
Vinny
Merge Tables and FileGroups
Siggi Bjarnason
So 500+ ppl have read this and NO-ONE has ever tried to move these replication tables to a seperate filegroup! Seemed like a logical thing to do, personally!
Microsoft A little help please lol
breed
This post probably belongs in the "SQL Server Database Engine" forum, as you're asking about functional behavior when moving a table between filegroups. Regardless, I'll see if I can find out and get back to you before end of day (PST).
One question - I assume you're using SQL Server 2000
DanielAnywhere
Moving the contents and tombstone tables to an alternate filegroup is a nice idea, but unfortunately, this is an unsupported scenario. The original ANSI PADDING/NULLS settings needs to be maintained for backwards compatibility.
As an alternative, you may consider defining your user defined tables to be stored on the non-default filegroup and allow replication to configure the replication system tables to use the default filegroup of the database. This will still get you to the point where you have the user data and replication system tables on separate filegroups. Note that you need to define the user tables to be stored on the non-default filegroup before adding them as table articles to the merge publication. The UI will not allow you to move tables to different filegroups after they are marked for replication.
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
imkamran
Wow ... i had actually given up on this! lol
Really wondering why i hadn't thought of moving the other tables now! Might be cos theres about 180+ of them and the part of my brain that does like avoiding work overrode the logical side!
Thx for the additional note about moving Tables after they're already an article! Wud've sworn alot if hit that one!