Changing Filegroup names and Filegroup files - midstream. . .

  • SQL Server Enterprise 2005 - SP3:

    I have a developer who is pumping in millions of rows of data to about 4 different tables (not all at once) and he has the expectation of me reconstructing my file system that was constructed for a couple of different tests (we have decided on one of the two systems we tested, and now I need to get things cleaned up before we roll this system out into PROD - this goes without saying), but the part that has me concerned is that he has stopped his process of importing the data he needs to import - wants me to get this all cleaned up - and then he will resume his import there after.

    I'm concerned that in the renaming of the filegroups and their files, that this is going to cause some sort of issue with the remaining data he is currently importing to one paritcular table. If this is all transparent to what he is doing, fine - but I am concerned that it truly isn't, and honestly - I do not know (nor think) that the filegroup naming nor files come into play with his import, but I want to be safe rather than sorry here.

    Any and all insight into this would be appreciated. I was planning to start with a clean database that was constructed with our selected system choice, but there is just too much data to get form point A to point B to allow for this (we intend to deploy this out tomorrow night - I know, but I am only a consultant and not being listened to very much on these decisions, plus our timing is being driven by the system we are replacing this one with, which is nearly out of disk space).

    Thank you all!

    Rich

  • Filegroup and file names are completely irrelevant when inserting into tables. All that inserts need is the database name, the schema name (if not default) and the table name.

    This is one of the requirements of a relational database engine, that the physical structure, layout and implementation is completely irrelevant to anyone using the database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Awesome...So I should just be able to take the database offline, deattach it, change my Filegroup Directory names, and Filegroup File names, bring it back up online, and be good to continue forward?

  • And Gail - also and lastly - if my previous question turns out to be a yes, can I then reconstruct a partition scheme without having to drop a table and it's data to use a new set of Filegroups? Say it was something like this...

    Current

    USE [TESTDB2]

    GO

    /****** Object: PartitionScheme [RecordsPartitionScheme] Script Date: 04/27/2012 14:54:13 ******/

    CREATE PARTITION SCHEME [RecordsPartitionScheme] AS PARTITION [RecordsPartitionFunction] TO ([FG11], [FG12], [FG13], [FG14], [FG15], [FG16], [FG17], [FG18], [FG19], [FG20], [FG21], [FG22], [FG23], [FG24], [FG25], [FG26], [FG27], [FG28], [FG29], [FG30], [FG31], [FG32], [FG33], [FG34], [PRIMARY])

    Can I now change it to this without having to drop and recreate and re-import all the data again, to this?

    USE [TESTDB2]

    GO

    /****** Object: PartitionScheme [RecordsPartitionScheme] Script Date: 04/27/2012 14:54:13 ******/

    CREATE PARTITION SCHEME [RecordsPartitionScheme] AS PARTITION [RecordsPartitionFunction] TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12], [FG13], [FG14], [FG15], [FG16], [FG17], [FG18], [FG19], [FG20], [FG21], [FG22], [FG23], [FG24], [PRIMARY])

  • Rich Yarger (4/27/2012)


    Awesome...So I should just be able to take the database offline, deattach it, change my Filegroup Directory names, and Filegroup File names, bring it back up online, and be good to continue forward?

    No, that will leave your database offline and unusable.

    Inserting into tables doesn't require the file names, but if you take the DB offline, rename the files and bring it online, how do you expect SQL Server itself to know where the file went.

    Look up ALTER DATABASE, specifically the WITH MOVE option (or something like that)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Rich Yarger (4/27/2012)


    And Gail - also and lastly - if my previous question turns out to be a yes, can I then reconstruct a partition scheme without having to drop a table and it's data to use a new set of Filegroups? Say it was something like this...

    Create a new partition scheme on the filegroups you want, then rebuild the clustered index onto the new partition scheme. Slow and big operation if the table is large

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/27/2012)


    No, that will leave your database offline and unusable.

    Inserting into tables doesn't require the file names, but if you take the DB offline, rename the files and bring it online, how do you expect SQL Server itself to know where the file went.

    Look up ALTER DATABASE, specifically the WITH MOVE option (or something like that)

    ARG...That was my fear, and yes - I too was just wondering if it dynamically knew in the Database Properties if I did that, or what.

    So - I need to do that through a T-SQL ALTER DATABASE command...OK - more research.

  • Gail - you are a genius! Thank you again!

    Reference:

    http://technet.microsoft.com/en-us/magazine/gg452698.aspx

  • Hi Gail (et al) - Just one last thing...

    The system we chose to go with needed to have the Filegroups redone (because we had 2 test systems, and the directories were at the same level - thus numerics came into play with the names...FG1, FG2, etc...).

    I am needing to recreate the index for the partitioned table in the system, and that means that I need to recreate the FUNCTION and SCHEME (actually just the SCHEME, but I am going to do them both anyway). The main problem right now is 2 fold...1, the SCHEME needs the proper list of Filegroups to use, and 2. the current Clustered Index/Primary Key on the partitioned table is using a single filegroup that it never should have used to begin with.

    My question is when I recreate the Clustered Index/Primary Key, does my ON clause need to be the name of the Partition Scheme I am using? And if so - is this going to migrate the data from the Filegroup currently being used by the table, or is there something else needing to be done to ensure that it gets migrated across to the Filegroups for this partitioned table? Is this even the best approach for the Index?

    Here is some DDL currently in place, along with the old FUNCTION that I am going to reuse, and the new SCHEME...

    PARTITION FUNCTION

    USE [MyDatabase]

    GO

    /****** Object: PartitionFunction [RecordsPartitionFunction] Script Date: 04/30/2012 12:25:02 ******/

    CREATE PARTITION FUNCTION [RecordsPartitionFunction](datetime) AS RANGE LEFT FOR VALUES (N'2011-04-30T23:59:59', N'2011-05-31T23:59:59', N'2011-06-30T23:59:59', N'2011-07-31T23:59:59', N'2011-08-31T23:59:59', N'2011-09-30T23:59:59', N'2011-10-31T23:59:59', N'2011-11-30T23:59:59', N'2011-12-31T23:59:59', N'2012-01-31T23:59:59', N'2012-02-29T23:59:59', N'2012-03-31T23:59:59', N'2012-04-30T23:59:59', N'2012-05-31T23:59:59', N'2012-06-30T23:59:59', N'2012-07-31T23:59:59', N'2012-08-31T23:59:59', N'2012-09-30T23:59:59', N'2012-10-31T23:59:59', N'2012-11-30T23:59:59', N'2012-12-31T23:59:59', N'2013-01-31T23:59:59', N'2013-02-28T23:59:59', N'2013-03-31T23:59:59')

    NEW PARTITION SCHEME

    USE [MyDatabase]

    GO

    /****** Object: PartitionScheme [RecordsPartitionScheme] Script Date: 04/30/2012 12:25:53 ******/

    CREATE PARTITION SCHEME [RecordsPartitionScheme] AS PARTITION [RecordsPartitionFunction] TO ([FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG8], [FG9], [FG10], [FG11], [FG12], [FG13], [FG14], [FG15], [FG16], [FG17], [FG18], [FG19], [FG20], [FG21], [FG22], [FG23], [FG24], [PRIMARY])

    CLUSTERED INDEX / PRIMARY KEY on Partitioned Table...

    USE [MyDatabase]

    GO

    /****** Object: Index [PK_BW_RECORD_EVENT_NEWFORMAT2] Script Date: 04/30/2012 12:27:17 ******/

    ALTER TABLE [dbo].[BW_RECORD_EVENT_NEWFORMAT2] ADD CONSTRAINT [PK_BW_RECORD_EVENT_NEWFORMAT2] PRIMARY KEY CLUSTERED

    (

    [ID] ASC,

    [GPS_RECORD_DATE] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NC1]

    What you will notice is that the ON Clause for the Index is using a Filegroup it never should have been using to begin with. This was all in TEST when it was originally constructed, and unfortunately - they do not want to rebuild everything is possible (other than the Index itself along with the Partition Function/Scheme in an effort to get it right).

    Any and all help here would be greatly appreciated!

    Thank you,

    Rich

  • Rich Yarger (4/30/2012)


    My question is when I recreate the Clustered Index/Primary Key, does my ON clause need to be the name of the Partition Scheme I am using? And if so - is this going to migrate the data from the Filegroup currently being used by the table

    Yes and yes.

    Do a CREATE ... WITH DROP EXISTING, then the nonclustered indexes won't get rebuilt in the process.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (4/30/2012)


    Rich Yarger (4/30/2012)


    My question is when I recreate the Clustered Index/Primary Key, does my ON clause need to be the name of the Partition Scheme I am using? And if so - is this going to migrate the data from the Filegroup currently being used by the table

    Yes and yes.

    Do a CREATE ... WITH DROP EXISTING, then the nonclustered indexes won't get rebuilt in the process.

    Gail - Thank you,and forgive me, but could I get a little help from you on the syntax?

    Would it be...

    CREATE PRIMARY KEY CLUSTERED INDEX 'MyClusteredIndexPKeyName'

    ON dbo.PartitionedTable

    WITH (DROP_EXISTING = ON);

    GO

    I know this sample above is incomplete, but I just want to be sure I get this right.

  • Or I guess I am wondering if this is what I need...

    CREATE PRIMARY KEY CLUSTERED INDEX 'PK_BW_RECORD_EVENT_NEWFORMAT2'

    ON dbo.BW_RECORD_EVENT_NEWFORMAT2

    WITH (DROP_EXISTING = ON); ON [RecordsPartitionScheme]

    GO

  • No Books online available?

    No such syntax as create primary key.

    CREATE UNIQUE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON) ON <whereever it's supposed to go>

    Untested.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Sorry - should have checked bol first, but when you say "whereever it's suppose to go" you mean the name of my Partition Scheme, and not a Filegroup - yes?

  • Also - by making this a UNIQUE CLUSTERED INDEX, is this going to replace the PKey? Sorry for being so off-center here on this, but I am a little lost.

    🙁

Viewing 15 posts - 1 through 15 (of 32 total)

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