January 19, 2011 at 1:44 am
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.
January 20, 2011 at 4:30 am
You should be able to add files to a mirror session, there are some warnings though
From SQL Server BOL
:exclamation: ImportantAdding 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 OperationAdding 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" 😉
January 20, 2011 at 4:41 am
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.
January 22, 2011 at 1:38 pm
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 OperationAdding 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" 😉
January 24, 2011 at 6:25 am
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.
January 24, 2011 at 8:04 am
Cheers Perry. You rock. The second option you provided works a treat.
Alex.
January 24, 2011 at 9:14 am
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply