August 22, 2009 at 10:55 pm
Hi,
I have a SQL 2005 DB set up with asynchronous mirroring without a witness.
My question is: how can we add data files to both the primary and mirror databases without having to remove the mirroring then re-apply it?
we are running out of storage on the main data drive for the primary site, so i added a file to the primary file group on a different disk. Now, the mirroring session broke down and is disconnected.
Is there any way to re-establish the mirroring session without re-initializing (ie take a full backup, take a log backup, restore the full, restore the log with norecovery, then configure mirroring) because it will take quite some time (about 2 hours) for me to get the 25GB DB (I know, not very large) copied over to our mirror location about 50-100 miles away (somewhat of a slow pipe).
After thinking about it, i suppose SQL Server has no way of knowing where to place the file on the mirror site, but maybe there should be some type of warning before it allows you to do so...
Quite a scenario I created for myself. I couldn't find anything to relate to within the forum here, so that's why I'm posting a new topic.
Thanks in advance for any additional thoughts.
Steve
August 23, 2009 at 9:21 am
If you add a file to the primary, I think this gets pushed across to the mirror as the same command, meaning same paths. If you need that changed, you'd have to break mirroring and restart it.
You might need to do that anyway.
August 23, 2009 at 1:06 pm
Thanks Steve. That makes sense based on my config.
Clustered active/passive (Primary) -- all SAN
G:
H:
I:
J:
K:
L:
M: MDF (per initial DB creation)
N: NDF -- added per my initial post.
Standalone (Mirror) -- All local disk
C:
D: MDF (per initial mirror creation)
E:
Because the N: partition doesn't exist, the file wouldn't be created.
Think that scenario is worth of a Connect post?
Steve
August 23, 2009 at 8:59 pm
I'm not sure it is because Mirroring is set to be at the database level, not server. The creation of those files, to me, is technically a server level command. It alters master.
It would be nice if you could specify how this would be transferred to the mirror, at least pathing. Maybe it is worth a Connect post. Worst case, they close it as "won't fix".
August 24, 2009 at 9:12 am
I suppose you could pick and choose where to place the secondary file on the mirror database.
In my case, I mapped the .NDF to the E: partition, but I had to re-initialize the mirroring session to get it back up and running, and simply used the "with move" options to place the mdf, ndf where I needed on the mirrored DB.
I opened up this connect post:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=484895
Thanks,
Steve
August 24, 2009 at 9:28 am
Added my vote. Did you try to alter the mirror db first?
August 24, 2009 at 11:57 am
Thanks for the vote.
How would that be possible? Do you mean failover first then add? The mirror is inaccessible to modify while it's running at the secondary DB...
The goal was to not affect production by increasing the storage and adding a data file so the application would not effectively hit a wall.
I'm intrigued to hear a workaround or additional thoughts to accomplish that.
August 25, 2009 at 3:58 am
I belive MS suggests that the primary and the mirror have identical drive mappings for reasons like this. Yes, adding a file on the primary will transfer to the mirror if the servers are laid out identically (logically). Since we use the mirror as a "very warm" backup, we pretty much put in a duplicate server.
I love mirroring. It is to me the single best new feature in SQL Server since Version 7.
Student of SQL and Golf, Master of Neither
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply