Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Change filegroups for existing table from Primary to FG_JJ Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2013 1:45 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 9:46 AM
Points: 40, Visits: 371
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
Post #1413762
Posted Wednesday, January 30, 2013 3:27 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 1,157, Visits: 849
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
Post #1413783
Posted Sunday, February 10, 2013 12:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:05 AM
Points: 1,157, Visits: 849
Any luck with this?

Andrew
Post #1418127
Posted Monday, February 11, 2013 10:49 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, August 7, 2014 9:46 AM
Points: 40, Visits: 371
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
Post #1418563
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse