Expanding Native database above 132Gb

beetlestone

I have a question regarding Expanding a Native Database.
I have just joined a new End User, and their retail database implementation includes a Head Office database, Native Server v3.6. The database is presently ONE file of 132Gb !!! ( [-X I know).
Database usage is presently at 83%, and growing at 0.3% per day.

The plan is to migrate to a new server, with a different and optimal confirguaration within the next 2/3 weeks, so my question is for a short-term, stop gap fix.

My IT Director wants a full risk analysis of expanding the current free database space. The options open to me are
1. Expand by adding another file on the same disk (147Gb Free)
2. Expand by adding another file on a different disk (28Gb Free)
3. Deleting old data temporarily (Until new server is implemented)

Normally, expanding the database and adding more files would involve backup and restore, but, as the database is in use for almost 20hours per day, this is not necessarily practical. Ideally we wouold create a new database, equal file sizes, different disks etc, but.......

Can someone advise the potential risks of
1. Expanding the database during the 4 hour period to the same disk
2. Expanding the database during the 4 hour period to a different disk
The main reson for the concern is because of issues with restoring into a database larger than 132Gb, although this could possibly have been caused by having different database files, totalling more than 132 Gb, on the same disk.

I am not bothered about achieving a performance improvement in the short term - this will come with the new server.
I need to ensure that the the current performance will not suffer as a result of a simple expand and addition of a new file.
We only need to grab maybe 20Gb to see us through until the new server is implemented, but as this is a retail environment, with back office/head office processes for 20 hours per day, I need to be careful...

Cheers, everyone.

kine

If you need only space, you can add new file. It will not be optimal, but for some temporary space it is enough. Navision will divide new data to old and new file.

But i do not know right now the maximum allowed size of Native DB!

kriki

To gain some DB-space, you might optimize some tables, starting with the smaller ones and working up to the bigger once.
For the REALLY big ones, you might drop 1 secondary key and then recreate it. And this for all secondary keys. This way not all keys are optimized at the same moment and the DB will be blocked for a smaller period.

beetlestone

Thanks guys.
All redundant keys have been disabled, and optimisation has been carried out regularly.

My concern, and that of other people I have spoken to, is whether performance will be affected if a 20Gb file (for example) is added to a different Hard Disk, bearing in mind that we have no intention of rebuilding or restoring the database at the moment.

I can't see there being an issue with database size, as the Internal License allows up to 264Gb database.

At 83%, time isn't really on our side.

Thanks for the replies.
Darren

Kowa

Performance will definitely be affected if a second database file is added on the same drive, always use a separate drive. The database files should all be of equal size for maximum peformance, but if that is not possible there isn't much of a choice.

beetlestone

Thanks Kai.

You have confirmed my thoughts, but I am now hoping that someone else can offer an opinion.

Thanks,

Mehdi.Chahed

Just a stupid Question:
Why are you looking to expend you database wher it's used up to 83%?
I've read somewhere, that DB performance is optimal when this rate is close to 85%.
But about perforance, I've been faced to a similar problem. Expending in the same file will not help as your occupation ratio will go down. Only another disk could give some fresh air to your database. If you have a spare Disk controller, that will be better.
But anyway,
More disks you have, beter is the performance (if you have a good server).

Good luck!

Mehdi.Chahed

That's me again!

did you compress previous Accounting periods?

Captain_DX4

Mehdi.Chahed wrote:

Just a stupid Question:
Why are you looking to expend you database wher it's used up to 83%?
I've read somewhere, that DB performance is optimal when this rate is close to 85%.

I'd read that too, but when a Native Navision database gets to a certain size, it seems that a larger gap results in better performance.

I've worked with several customers over the years with tiny 3-8 GB databases, and I've seem them all working comfortably up to 85-90%. But at the client I'm with now, anything over 80% and we see very serious performance issues. I'm sure the number of concurrent users does affect this up-and-down. We are licensed for 88 sessions presently on an 88GB database, 64GB utilization.

dtotzke

Mehdi.Chahed wrote:

Just a stupid Question:
Why are you looking to expend you database wher it's used up to 83%?
I've read somewhere, that DB performance is optimal when this rate is close to 85%.

Good luck!

On what planet? Our database is currently 88 GB and runs on a fibre attached EMC SAN. Dual XEONs. 4GB RAM.

Anything over 70% and our performance goes into the toilet.

I have yet to find our performance bottleneck. The CPUs barely ever register more than 2%. The client machines are P4's and we have Gigabit Ethernet to the desktop. At most we have 80 concurrent users.

We book and ship maybe a few hundred orders a day. It's not like we're shooting for the TPC-C record or anything. Blocking is an ongoing problem that seems to get worse every month.

beetlestone wrote:

I am not bothered about achieving a performance improvement in the short term - this will come with the new server.

I wouldn't count on that. See above. We even took this thing over to Hitachi last year (have some friends in the SAN department) and put it on a million bucks worth of hardware just for laughs. Nothing. Didn't help at all.

I have been told that any version prior to 3.7 has issues and you should at least be there with the client and server executables. We've just identified that the v3.6 server.exe is leaking handles. Until recently, our servers were rebooted rather regularly. Uptime is important to us now and they don't go down so often. After a period of a few weeks being up, our Navision server (the actual server not the program) stops allowing connections from the network. I barely got logged onto the console to discover that the server.exe process had amassed over 200,000 handles. The server was entirely out of resources. Reboot the server and they just start to build again.

So there you go. That's my experience.

Have fun.[/quote]

beetlestone

Thanks, guys.
Upgrading the executables is something we are analysing when performance testing the new server.
As we are a retail company, with 60+ stores, each running data replication softwware we need to be aware of any implications of upgrading the executables - I don't fancy doing this job on 60 stores, each with a number of tills!! I think we will be able to get away with just doing Head Office though, because the stores do not actually connect to the HeadOffice database.
Does anyone recommend v4 executables?
The business is also pushing for a move to SQL, whereas I have more than a couple reservations about this, because of probable performance downturns and the increased management required of the server when it uses a database of this size. Any thoughts on this?

kriki

beetlestone wrote:

Does anyone recommend v4 executables?
The business is also pushing for a move to SQL, whereas I have more than a couple reservations about this, because of probable performance downturns and the increased management required of the server when it uses a database of this size. Any thoughts on this?

-Use 4.0 SP1 executables!
-If you put a Navision DB into SQL, it is 10 times slower. If you finetune the DB for SQL, it is 10 faster. This is what they told me in a course for finetuning Navision for SQL. The fist part is definitly true. The second part. I think 10 times faster is exagerated, but I am sure it is possible to get the same speed as a Navision-DB, maybe even better. BUT you need to finetune a lot like not maintaining BIG indexes/SIFT in SQL that SQL doesn't even use. Create new indexes (!) that are smaller and that SQL WILL use (search the forum for a lot more info or take the course).
You also need a server that is a lot heavier for running SQL.

In short:with a DB of this size, it is worth thinking about moving to SQL. Also Navision 4.0 SP1 has commands specially designed to be used on SQL (FINDFIRST,FINDLAST,...). Here you have to check out your code to see where you would better use the new commands.

beetlestone

Thanks, Alain.
I get the feeling though, that in order to make sure that SQL is behaving optimally, I'll need to get someone in to manage the server/database. And it would need to be someone with more than just SQL Admin skills and exams I guess.

The retail system in place here, is HEAVILY bespoked around 3.60. I think that the Microsoft course you mention - Fine tuning Navision for SQL is the best step that I can take, if we are going to move to SQL.

What problems do you, or anyone else, see when using Navision Server with a database size of 145Gb!!

Cheers

kriki

You don't need a SQL-specialist that doesn't know Navision. But rather a Navision specialist (which fields are filtered on and when) that knows how SQL thinks, knows how to improve performance by redesigning the SQL-indexes and SIFT-tables.

bbrown

Kriki makes a very good point. You may even find that this is 2 people working as a team. One person that knows SQL very well and another person that knows Navision very well.

I don't see these as long term positions, but rather a project team whose task is to fine tune your system for SQL (document & train you on what was done) plus develop a maintence plan that can be documented for someone to handle on a daily basis. Many of the maintence task can be automated. Being the DB Admin for a ~132 GB SQL database is not a fulltime job.

Based on your system description, and the rest of your postings, I think moving to SQL is something you should consider. However, you must plan this move. Migrating a system of this size to SQL is not a matter of simply installing SQL and moving the database.

A few areas to consider. These are all equally important:

1. Hardware.

Select the right hardware. A system that is good for the native db may not be the right one for SQL. This doesn't mean go out a just get the most expensive server you can find, but understand your budget and put together the best system for that budget.

2. System Integration.

This is the task of configuring the hardware, operating system, and SQL. A poor install can impact performance of even the best servers.

3. Application/Database design.

This is the part of tuning the indexes o achieve performance.

4. Maintenance

You must maintain the database to continue peak performance.

SimonW

Hi,
As long as your version of the replication software (Data Director) is newer than 1.95, you don't need to upgrade the stores at the same time.

This is because the communication is between data directors not between databases.