Adding a new FILEGROUP to an existing mirrored database

  • Alex Webber

    SSCommitted

    Points: 1543

    I wanted to know if there was any way to add a new FILEGROUP to a currently mirrored database without having to completely break the mirror and re-establish it from backups after I've added the new FILEGROUP. Logically you should not be able to, but I was just wondering if there is some *hack* to achieve this?

    Cheers, Alex.

  • Perry Whittle

    SSC Guru

    Points: 233859

    You should be able to add files to a mirror session, there are some warnings though

    From SQL Server BOL


    :exclamation: Important

    Adding a file during a mirroring session without impacting the session requires that the path of the file exists on both servers. Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended. For information about dealing with a failed create-file operation, see Troubleshooting Database Mirroring Deployment.

    From SQL Server BOL


    Failed Create-File Operation

    Adding a file without impacting a mirroring session requires that the path of the file exist on both servers. Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended.

    To fix the problem:

    The database owner must remove the mirroring session and restore a full backup of the filegroup that contains the added file.

    The owner must then back up the log containing the add-file operation on the principal server and manually restore the log backup on the mirror database using the WITH NORECOVERY and WITH MOVE options. Doing this creates the specified file path on the mirror server and restores the new file to that location.

    To prepare the database for a new mirroring session, the owner must also restore WITH NO RECOVERY any other outstanding log backups from the principal server.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Alex Webber

    SSCommitted

    Points: 1543

    Thanks for your reply Perry.

    It figures you should be able to add new Files to an existing FileGroup, but my question was about adding a new FileGroup entirely to an existing mirroring session. As soon as you run "ALTER DATABASE ADD FILEGROUP...." the mirroring session goes into suspension and is un-resumeable which I guess makes sense.

    Alex.

  • Perry Whittle

    SSC Guru

    Points: 233859

    Alex

    i tested this on my mirror setup, adding the filegroup itself was fine. After adding the filegroup FG1, I failed over the principal (the mirror is now principal) and when checking the filegroups on the previously mirrored database FG1 exists, the mirror state was set to "mirror, synchronized/Restoring".

    Just adding the filegroup i really cannot get it to break. I'm using

    ALTER DATABASE [AdventureWorks] ADD FILEGROUP FG1

    And also

    ALTER DATABASE [AdventureWorks] REMOVE FILEGROUP FG1

    What mode is your mirroring session using and are you using a witness?

    I did manage to get the session to suspend by adding a file (not a filegroup) to the principal when the data file path was not mirrored on the mirror server. The paths i had in use were

    Principal

    D:\Program Files\Microsoft SQL Server\MSSQL10.PRINCIPAL\MSSQL\DATA

    On the mirror

    D:\Program Files\Microsoft SQL Server\MSSQL10.MIRROR\MSSQL\DATA

    Command adding the file was

    ALTER DATABASE AdventureWorks

    ADD FILE (NAME=AdventureWorks_IDX,

    FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10.PRINCIPAL\MSSQL\DATA\AdventureWorks_Idx.ndf',

    SIZE = 20MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

    TO FILEGROUP FG1

    Hence the warning below

    From SQL Server BOL


    Failed Create-File Operation

    Adding a file without impacting a mirroring session requires that the path of the file exist on both servers. Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended.

    To add a file to a mirror session where file paths are different perform the following.

    Switch off the mirror session using

    ALTER DATABASE [AdventureWorks] SET PARTNER OFF

    Create the file in the principal database (the mirror will be in the "restoring" state)

    alter database AdventureWorks add file(name = AdventureWorks_IDX,

    filename = 'D:\Program Files\Microsoft SQL Server\MSSQL10.PRINCIPAL\MSSQL\DATA\AdventureWorks_Idx.ndf',

    size = 200MB, maxsize = UNLIMITED, filegrowth = 10%) to filegroup FG1

    and then take a transaction log backup.

    backup database AdventureWorks to disk = '\\hanode1\backup\advfile.trn' with init

    Apply the log backup to the mirror database specifying the "with move" for the new file path as follows

    restore database AdventureWorks from disk = '\\hanode1\backup\advfile.trn' with norecovery,

    move 'AdventureWorks_IDX' to 'D:\Program Files\Microsoft SQL Server\MSSQL10.MIRROR\MSSQL\DATA\AdventureWorks_Idx.ndf'

    Start the mirroring session as usual from the mirror and then the principal, the session will now be established with the new file in place on the mirror database using the new file path

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • jasonmorris

    SSCrazy

    Points: 2492

    Fell foul of this one ourselves and ended up deciding to set the directory paths to be identical so we don't get caught again in the future.

  • Alex Webber

    SSCommitted

    Points: 1543

    Cheers Perry. You rock. The second option you provided works a treat.

    Alex.

  • Perry Whittle

    SSC Guru

    Points: 233859

    jasonmorris (1/24/2011)


    Fell foul of this one ourselves and ended up deciding to set the directory paths to be identical so we don't get caught again in the future.

    It can be a bit of a sticking point

    Alex Webber


    Cheers Perry. You rock. The second option you provided works a treat.

    Alex.

    😀 ha ha thank you Alex, you're too kind but hey we're here to help each other right? 😉

    It's important to remember that if you want to use disparate file drives\paths, when performing actual file operations you must stop the mirror session and complete them manually. You don't need to completely remove the session, basically just a stop and start! 😉

    The code above should help, one to dump into your scripts repository 😎

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 7 posts - 1 through 7 (of 7 total)

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