NAV Database SQL with huge growth rate

juiho

Hi Guys,

We are currently planning the hardware sizing and NAV 5.0 system design for a client who has a huge appetite for harddisk space.

In the initially estimation, they will have a daily growth rate of 3GB, amounting to 1 terabyte per year. They are thinking to run over 70 Companies in the same database. We are talking about SQL Server 2005 here. There will be an average of 120,000 sales orders per day (batch posting at night), >400,000 in peak season.

We are quite concern on the impact the size will have on NAV in terms of performance and usibility. In terms of H/W, the client will purchase something with 4CPU, 16Gb RAM, HDD (multiple physical disks and controllers) that can support up to 5 years data.

As I have never encounter such a huge demand, some pointers from your guys will be great. Thanks!

Waldo

I think you're not going to find many people that is willing to do a recommendation for a demand of this size.

I really wonder what locking is going to do when putting in 120000 sales orders each day.

If I were you, I would include Microsoft in this.. .

nunomaia

In a database that huge and with 70 companies, you probably you have to break in several databases /severs

bbrown

juiho wrote:

Hi Guys,

...the client will purchase something with 4CPU, 16Gb RAM, HDD (multiple physical disks and controllers) that can support up to 5 years data...

I don't disagree with the multi db/server comment. This will also raise licensing concerns.

If you plan to put this on one box then think bigger, MUCH BIGGER

colin_leversuch-roberts

I have a 1 terabyte data app. v4 on sql2005 and it doesn't perform tooo well currently. My observation as a SQL Server DBA of long standing is that Navision doesn't scale too well - expect to have to do some serious work on the indexing. Can't say if v5 is any different.
From the observations on the current system lack of memory is an issue, it's partly the way Navision works, so I'd suggest you should consider at least 64gb of ram if not 128gb and most certainly full 64bit o/s and sql server. I haven't found cpu to be an issue, we have 4, disk subsystem needs to be fast so no raid 5, but a full discussion of this is probably out of scope here. Look to see how you can archive or partition the data you're going to be working with, this will give you the most gain by reducing the size of your working data set.

Waldo

colin leversuch-roberts wrote:

... Can't say if v5 is any different ...

It is different, but don't expect it to be perfect. There has been many index-changes for SQL Server, which has (in general) a positive effect on the performance, but the database should always be "tuned" in favour of the cusomer's use.

juiho

Thanks for the advice.

Yes we wanted to think BIG, but then comes the client's budget concern =; We have Microsoft looking this as well and SQL-wise in terms of size should not be a problem.

I am forseeing a lot of code optimization, large volume tables on their own physical disks, turning off unnecessary keys, running reports on secondary servers, definitely no analysis view or change log here... etc etc...
#-o

colin_leversuch-roberts

in real terms 3 terabytes isn't that large unless your server infrastructure can't support it. You might want to consider using a product such as Lite Speed for your backups, this will bring all the benefits claimed - and it is very fast. Redgate also have an excellent product but I've only used lite speed.
For the disk sub system just use raid 10, don't share physical disks if you're on a SAN and make sure you have lots of spindles.

Savatage

120,000 order a day ...that's entering an order every .7 seconds. :-k

& 400,000 a day YIKES! :shock:

Even if evey order generated just 1 piece of paper thats 24 cases of paper a day. #-o

nunomaia

Savatage wrote:

120,000 order a day ...that's entering an order every .7 seconds. :-k

& 400,000 a day YIKES! :shock:

Even if evey order generated just 1 piece of paper thats 24 cases of paper a day. #-o

Don't forget that are 70 companies.

Waldo

True ... that's why it's probably better to split it over several databases.

I know one installation like this.
Licensewise it was solved with buying an "all-in" license (not really an official term though). They bought an unlimited amount of users / database size for one price (around the 750000 EUR) ... all granules included. They can use this license over as many database as they wanted.

All companies had to be subsidiaries off course...

<edit>
Microsoft did a special deal for this customer ... that's why I suggested to involve Microsoft in this.

colin_leversuch-roberts

numbers are missleading and sometimes don't really show a measure of scale, consider the statement "our system handles 1 million transactions a day" - well that's actually only 11.5 transactions/second - it's difficult to scale too as a single transaction may equate to several transactions within the database. On an average server ( 4 cpu + DAS ) I've tested at over 2k inserts / sec into a table, and I wasn't really stressing the server at all. Of course the transactions might not be spread over 24 hours.
The important point is to test, and consider data partitioning, but 70 databases vs 1 database , I know which I'd choose ( not 70 ) . For the curious you can collect performance stats quite easily for your sql server to establish trending and set baselines. There are a number of ( free ) tools which will assist in understanding the performance of your disk subsystem, sqlio, sqlstress, iometer. There are more sophisticated tools such as load runner, you can do application testing , without the app, by using multi threaded replay from profiler traces.