SQL Databasefiles on more then one lun --> any caveats?

Manfred,

According to the RM documentation multiple SQL Server databases can exist on the same volume or across multiple volumes so it is definitely a supported configuration.

As for benefits I can only think of more granular restore options¿

As for drawbacks you will be using more RLPs for the ISC and could also be bottling out on the total number of Clone images, concurrent sessions or source LUNs for ISC depending on the size of your environment.

Please remember that the source LUN limit include MirrorView/A primary images and SnapView snapshot source LUNs in addition to the incremental SAN Copy logical units.

I am sure you are already aware but just in case a word of warning, the system databases (master, msdb, model, etc.) should not be located on the same volume as user databases. Microsoft SQL Server does not currently support using Virtual Device Interface (VDI) and snapshot technology to restore system databases.

I hope this helps and I also invite further feedback from the user community

Best Regards
Carl

Good info Carl

And dont forget the 10 second VSS window !!

The more luns you have in a job, the longer it takes to split those devices on the Clariion (which is only fair!)...

Anything above 7/8 luns in one job is where you can start running into trouble, depending on the optimization of the back end and the ultiization of the SPs.

James.

Manfred,

If you are using RM then it takes care of all the hard work of managing the splits and the consistency of data. I used RM for a SQL cluster on a CLARiiON (we have it on a DMX now) with the SQL data and log files split across 7 LUNs that totaled about 4TB in size (there were 12 LUNs masked to the server, but only 7 were required for cloning). Every night the clone job would run and fracture the clones at the end ensuring the data was application consistent, and we would take snaps of the clones in the morning for the dba's to develop against.

RM did a good job of managing the clone sync and the split, which had to pause the database to get a SQL consistent snap of the data. It took only a couple of seconds, never any more than that.

Hope that helps,
Aran

Damn! Totally forgot about the VSS 10 second rule but it is good thing that we can rely on you James to fill in the blanks¿

Sounds like Aran knows how to lay out his Luns on his Clariion properly *cheer*

Heh heh, thanks . Our DBAs choose to scale out instead of optimizing the database or application so it forces me to actually read the best practices document to find out how to keep up with their storage and i/o demands....

Hy Aran,
Can you tell something more about your layout of the SQL databases (on the cx)?
Actually we have big metaluns but I would like to test the performance with some smaller luns and spread the dbfiles accross them...

best regards
Manfred

Manfred,
Well the SQL database in question is an OLTP type application and is quite large so we had to build performance and capacity into our layout. The layout is actually simple, but uses up a lot physical spindles.

We used dedicated RAID1/0 RAID Goups for the data file LUNs: 16 x 73GB 15k FC2 drives gave us a usable space of about 530GB. The data files for the SQL DB were spread across four of those groups. Like I said, lots of spindles but it gave us the i/o we needed. Those data file groups alone used up 64 drives.

Logs and tempdb LUNs were dedicated RAID1/0 RAID groups as well with between four and eight 73GB 15k FC2 drives in the group depending on storage needs.

All told there were about eight full disk array enclosures for that one application on a Cx700. But for an application that generated about 1 million dollars a day in revenue we could justify all the spindles required to support the i/o requirements of the application.

Most of our other SQL databases I use RAID5 for the data file LUNs and RAID1/0 (even if it is a 2-disk group use RAID1/0 so that you can expand it if need be later) for the log and tempdb LUNs.

The challenge from the application side though was to get the dba's to spread the workload evenly across the data LUNs. It is up to them to optimize the application not hit one LUN heavily and leave the others at near idle. I provided the i/o necessary for the application but they had to take the time to spread the workload out.

Hope that helps,
Aran

Thanks for your response.
I would like to move away from big metaluns to some smaller luns spreading the sql db files accross them (as you mentioned)
So your configuration sounds good.
I also see the problems with spreading the workload accross Db files on the luns. (this could be a advantage for the metaluns...)
I will look forward to not take to much luns for the db files (as james mentioned above) to get no troubles with RM.

Thanks and regards
Manfred

I think that if you want to use metaluns you could design a layout that would provide the performance you need and still be manageable. The attraction of metaluns to me is ability to easily expand (striped of course for best performance) storage allocated to a host/application. The problem is that to do it right and provide maximum flexibility you need to design a layout for an array from the beginning for metaluns. I have never had that luxury (except for our DMX which uses a form of metas by design) on our Clariions.

So if you have the time and can play around with the design on your array you may find that the flexibility and spread of i/o across spindles that metaluns can provide will prove beneficial to you.

Good luck,
Aran

Thanks for your response.
best regards
Manfred