November 7, 2007 at 2:07 pm
In a couple of months we are going to convert an MS. Dynamics Navision Native Database to SQL2005.
Since i'm interested in SQL2005 i've been reading some articles and based on this info I've designed a server. Keeping in mind that i'm no expert at the subject i appreciate some input.
The database is +/- 50 GB and will be growing quickly.
There will be heavy traffic since there are lots of warehouse activities and the users will be entering a lot of orders during the day. There are many reports which require heavy queries.
I know that the client uses a lot of cursors which will be a drain on performance but that is nothing i
can do anything about.
I'd like to set up this server like this.
Processors : As much as possible, I'd like 4 CPU's
Memory : As much as possible, I'd like at least 16 GB
Disks
RAID 1 A-> Log file
Raid 10 A-> Datafiles :
Primary Tables :
FG1 : Item + Clustered Index
FG1 : Customer + Clustered Index
FG1 : Sales Order Header + Clustered Index
...
FG2 : Item Entry Table -> All Nonclustered Indexes
FG2 : Customer Entry -> All Nonclustered Indexes
FG2 : Sales Order Line -> All Nonclustered Indexes
...
Raid 10 B-> Datafiles :
Secondary Tables :
FG3 : Item Entry Table + Clustered Index
FG3 : Customer Entry + Clustered Index
FG3 : Sales Order Lines + Clustered Index
FG4 : Item Table -> All Nonclustered Indexes
FG4 : Customer -> All Nonclustered Indexes
FG4 : Sales Order Header -> All Nonclustered Indexes
Raid 10 C -> TEMPDB
Raid 1 B -> OS + SQL Executables
All disks will be on a SAN.
We would set up a Mirror with a witness.
I was thinking about taking a snapshot on the mirror and use this snapshot to create reports from which create a heavy load. Will this affect performance when the mirror sever is sychronising transactions and a heavy query is launched upon the mirror at the same time ?
I hope someone finds the time to make some suggestions.
Thanks a lot
November 8, 2007 at 7:07 am
I currently support a 1.5TB navsion on sql 2005 send me an email and we'll talk direct - there's just no way we could do this through the forum ( sorry Steve )
put the address together
colin [dot] leversuch-roberts [ at ] alliancepharmacy [dot] co [dot] uk
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
November 8, 2007 at 8:17 am
I completely understand, but please post some specs when you decide. It would be helpful for others. Also, can you post the current server specs.
Colin will have great advice, but I'd really recommend leaning towards 64-bit if you're doing this conversion. The memory advances will help.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 9, 2007 at 3:58 pm
One thought: if the various raid partitions don't have many ACTIVE spindles you could have more files/file groups that would be optimal. Sounds like you have a pleathora of hardware though! My guess is that ongoing maintenance will be the true determinant of how efficient your system is.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply