SQL Server Layout- single controller

[KC_VENTYX]

Hello-

We are in the process of setting up SQL Server on Netapp. We have 96 DBs (between 5 GB - 600 GB in size) and all the DBs are in SIMPLE recovery model.  The questions we have are:

1. Since we only have one controller - should log and data files still be placed on different volumes or could they be placed on same volume ?

2. Assuming that we place multiple MDF file (single LUN)   on one volume and multiple LDF files (single LUN) on another volume- then during restore of a single DB from that volume (which contains multiple DBs), would that be stream based or snapshot based?

Thank you!

[olson]

Hello,

          1. With the use of simple mode backups and if your databases are not split into multiple file groups, your databases and logs could reside on the same volume and lun. SMSQL requires the system and temp database be located elsewhere. The maximum number of concurrent backup databases is 35.  it is possible to put up to 500 db per lun however multiple snapshots would be required to complete consistent backups. I would use that as a maximum again using snapmanager to complete backups. 

          2. Yes if all database files exist on a single lun the restore process will mount a snapshot and copy back database(s)  there is no other way to complete this task using smsql and maintaining the original file structure.

I would recommend the following, group database that belong to the same app together and then group by size. many smaller dbs can go on the same lun. Isolate larger db's if there are SLA's around recovery.

I hope this helps

John

[KC_VENTYX]

Thanks, John.

I was wondering if there were any drawbacks in placing TEMP DB along with other system DBs on the same volume? My understanding was that even if TEMP DB shares the same volume (with the other system DBs)  it will not get backed up by default ( greyed out)- correct?

Yes, we do have multiple file groups. My question around placing data and log files on different volumes (but within the same controller) was to find out if it does help (in isolating data and log I/O) by doing that (as the documented best practices, I believe talks about placing Data and Log files on different volumes on different controllers- instead of just one)?

[dcornely1]

You may want to review the "Best Practice Guide for Microsoft SQL Server on NetApp Storage TR-3821" here:

http://media.netapp.com/documents/tr-3821.pdf