Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sql server how to split a mirrored db on to multiple devices Expand / Collapse
Author
Message
Posted Sunday, April 7, 2013 9:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:27 PM
Points: 11, Visits: 107
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
Post #1439650
Posted Monday, April 8, 2013 4:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 6,189, Visits: 13,338
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"
Post #1439743
Posted Monday, April 8, 2013 4:20 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:27 PM
Points: 11, Visits: 107
No the actual Drive for the .MDF device is different on both servers.

relative path however is identical. .\MSSQL\Data
Post #1440052
Posted Monday, April 8, 2013 4:30 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 6,189, Visits: 13,338
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"
Post #1440059
Posted Monday, April 8, 2013 4:53 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 12:27 PM
Points: 11, Visits: 107
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
Post #1440068
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse