How to define a deployment strategy to a SAN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

How to define a deployment strategy to a SAN

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-07-15 : 06:21:47

Hi there

I have been asked to provide some recommendations regarding deployment configurations to a SAN (for example, which databases require multiple files, which databases require their own disk/LUN, etc).

I am a database developer that leads a very sheltered life regarding the administrative and architectural side of database management/development. Therefore, any assistance, or links to helpful documentation will be greatly appreciated.

So far, I have gathered some of the following information about the databases included in the solution:

	Database Name	Data (MB)		Log (MB)				
		Size	Growth	Size	Growth	Traffic rate	Dependencies	Main CRUD Activity
1	ASPState	?	?	?	?	High		CD
2	CPF	10	1	10	1	Medium		R
3	CPFExpress	100	10	100	10	High		CD
4	CaseManagement	1024	100	1024	100	High		CR
5	Configuration	10	1	10	1	Low		R
6	Definition	100	10	100	10	Medium		CR
7	Indexing	10	1	10	1	Low (Batch)		RU
						Medium (Real Time)		RU
8	IndexingTables	5120	500	1024	100	High	Has to reside on same server for CRDM_Repos	CR
9	Journal	1024	100	1024	100	High		C
10	Metadata	10	1	10	1	Low		R
11	Notification	100	10	100	10	Low (Batch)		CR
						High (Real Time)		
12	Querying	1024	100	1024	100	High		RU
13	ReferenceData	1024	100	1024	100	High	Has to reside on same server for CRDM_Repos	R
14	Sentence	10	1	10	1	Low		R
15	CRDM_Staging	1000	500	1000	500	Low (Batch)		CUD
						High (Real Time)		
16	CRDM_Repository	100000	10000	20000	5000	High (Batch)		R
						High (Real Time)		CR

I assume the following deployment configurations:
--Temp DB on its own disk
--All log files on own disk
--All data files on own disk

However, regarding the databases, are there any best practices that I should consider? For example, should all the databases that have high traffic be distributed over multiple disks:

In the above DBs, we have 9 small sized high traffic DBs, 1 large sized (with multiple files) DB, 3 small sized medium traffic DBs, 3 small sized low traffic DBs

E.g.
For a SAN with 5 LUNS

Disk 1: temp db
Disk 2: log files
Disk 3: 1 ndf file for Large high traffic DB, 3 high traffic DBs, 1 medium low traffic, 1 low traffic
Disk 4: 1 ndf file for Large high traffic DB, 3 high traffic DBs, 1 medium low traffic, 1 low traffic
Disk 5: 1 ndf file for Large high traffic DB, 3 high traffic DBs, 1 medium low traffic, 1 low traffic

Please help as I have to send off this recommendation later today... ARGHHH

Hearty head pats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 06:35:57

You may want to put indexes on their own LUN as well as this should speed up any index seeks.

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-07-15 : 06:40:34

Hi RickD

Thanks for your reply.

In order to do that, would I need to put the indexes in their own filegroup?

Thanks

Hearty head pats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 07:05:18

Yes, as if created on the Primary they would be created wherever your primary filegroup is located.

Its a bit of pain, but if you can get this put in your procedures, it will make your life easier in the long run.

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-07-15 : 07:06:30

THanks Rick

I shall do that.

Don't suppose you have any advice over SAN deployment configuration?

Hearty head pats

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-15 : 07:20:13

I would say you've probably got it right. It's all about balancing your databases between the LUNs available.

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-07-15 : 07:44:43

Ok, thanks. I'll just send them some best case scenarios (money no object) and worst case scenarios (money IS an object), and also stress that this is theoretical and not based on rigorous testing (to cover my back)

Thanks again!

Hearty head pats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources