Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Change filegroups for existing table from Primary to FG_JJ


Change filegroups for existing table from Primary to FG_JJ

Author
Message
saqlainkhan
saqlainkhan
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 422
SQL Server: SQL 2008 r2

1)
Is there a way to change the FileGroup for a sql table having 16m records from Primary to (for instance) FG_IT

If 1 is possible then i would need help on what will be the best approach for the following scenario:

Sever A:
It has an Indexed View for a base table that has a File Group IT_JJ. In the past we setup Indexed View replications BUT with with the source table having a file group pointing to Primary.

Using tsql for creating logbased replication there is an option in sp_addarticle @schema_option and use the following option (i have to yet try this option out..never done this)

0x40000 (Replicates filegroups associated with a partitioned table or index.)

Server B:
This has file group name FG_JJ

If 1. is true and i did change the filegroup on the Destination table, will replication break?

Help would be greatly appreciated.

Noli Timere
DBA From The Cold
DBA From The Cold
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 1731
If the table has a clustered index on it you could rebuild the table, moving the clustered index to the new filegroup. That will move your data.

I would not think that replication will break but I would test this first.

Andrew
DBA From The Cold
DBA From The Cold
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1788 Visits: 1731
Any luck with this?

Andrew
saqlainkhan
saqlainkhan
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 422
Thank you for the response Andrew.

There is no luck with indexed view replication. In order to have this working we went a different route.
* We did straight Table --> Table replication.
* On the destination created a view (with all the conversion date --> small datetime) on the destination table.


At this point we did not care on the performance and had to get data moving. Reason for not looking into performance is we are planning to upgrade the DW to SQL 2012 that way there are no legacy data types.


Thank you for looking into and throwing out suggestions....

Noli Timere
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search