July 19, 2012 at 8:47 am
what is the procedure to add a datafile in mirroring?
July 19, 2012 at 8:56 am
do the filepaths on the mirror mimic the principal server or are the drive\path assignments different?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 19, 2012 at 12:35 pm
I am not sure about this but if you create a data file on the principal it should get created on the mirror as well (after the changes are propagated) as long as the same drive name and folder exists on the mirror server.
I mean DB mirroring should take care of that automatically.
July 21, 2012 at 11:14 pm
Vikrant is correct. Just make sure you use a file path that exists on the mirror. the whole path to the file has to exist (but not the file itself) on the mirror side. SQL won't create the file path.
July 22, 2012 at 5:19 am
that's why - although not mandatory - it is best to keep disk-layout equal at db instances involved in db mirroring.
If you need to add a data file in a mirrored db on a drive that isn't available at the mirror server, you'll have to break mirroring, modify the db, and setup mirroring from scratch so you can route the data files using move with the restore statement.
Keep in mind you may need to redo all actions you performed outside of sqlserver ( e.g. add a new drive, create the paths and grant the service account(s) ) on the server that is hosting the instance that hosts your secondary.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 22, 2012 at 6:31 am
You don't need to break mirroring at all. Pause the mirror and then perform a backup on principal and restore with move to mirror then resume mirroring. It's all in my article linked above
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 22, 2012 at 7:29 am
Perry Whittle (7/22/2012)
You don't need to break mirroring at all. Pause the mirror and then perform a backup on principal and restore with move to mirror then resume mirroring. It's all in my article linked above
What article? I don't see it linked above.
July 22, 2012 at 7:35 am
July 22, 2012 at 7:53 am
Ummm, that article clearly states that you have to break mirroring to fix that.
It says:
Once you have defined the filegroup, you need to switch off the mirroring session as directed by BOL. You will remove the mirror session but leave all configurations in place (TCP endpoints, authentication, etc). Switch off the mirror session as shown below from the principal server;
ALTER DATABASE [AdventureWorks] SET PARTNER OFF
At this point the mirroring session has been switched off and the mirror database is now in the "Restoring" state. Add the new database file to the Principal database using the following:
July 22, 2012 at 3:10 pm
Yes so it does. Apologies for that, been a long day
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply