Post reply

sql server how to split a mirrored db on to multiple devices

  • April 7, 2013 at 9:55 am

    #272785

    Say I have a large production mirrored 1TB DB that resides on a single MDF device and I would like to split that up into say 5 200 Gig devices. I want to do this without interruption to Production.

    I thought I could break the mirror and use the RESTORE process for creating a mirror to achieve the split to multiple devices quickly and without interruption to Production. Doing this twice would allow me to get this done in a few hours.

    Has anyone done this? Is it the preferred method seeing as we are mirroring anyways?

    What are other my alternatives, Pros and Cons? And gotchas?

    Also, I recall another more organic process where one would create the 5 new New Devices and somehow, over time get the objects to move over to the new devices. Not sure of the process for this but I seem to recall it being discussed. Sounds like this could take a long time and possibly cause some clocking at times?

    Thanks

    ...Ray

  • April 8, 2013 at 4:40 am

    #1604325

    First question is

    Do the database file paths match on both servers?

    So, for instance if the database files on the Principal are located in

    D:\MSSQL_10.50.Prod\MSSQL\Data

    Does this path exist on the mirror or is the mirror using another path such as

    D:\MSSQL_10.50.Mirror\MSSQL\Data

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

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

  • April 8, 2013 at 4:20 pm

    #1604592

    No the actual Drive for the .MDF device is different on both servers.

    relative path however is identical. .\MSSQL\Data

  • April 8, 2013 at 4:30 pm

    #1604599

    In that case the create file operations on the principal will put the mirror session into suspended mode. Check this link for details of how to fix this scenario.

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

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

  • April 8, 2013 at 4:53 pm

    #1604607

    Ah but I started this post saying I didn't mind using the technique to break and rebuild a mirror as the means to the end.

    I don't think I mind mirroring being suspended as I would break the mirror anyway.

    Here is the process I had in mind;

    Break Mirror

    Backup

    RESTORE to multiple devices on future Mirror

    Restore tlog

    Create Mirror

    Failover

    Break mirror

    and so on..

    I'm exhausted just thinking about it but I think it would result in my DB being on multiple devices, evenly distributed among 5 200 Gig devices without interruption

    Am I missing something? Like a much easier way of achieving same result?

    Thanks for your help.

    ...Ray

Viewing 5 posts - 1 through 4 (of 4 total)

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