Hereunder is the brief Client Database Structure :
Currently Operating 100 stores with a target of 300 stores within 1-2 years.
Each store has its own Navision native data base in which Primarily retails invoice are being generate. Statements are being created in day end and replicated to HO database daily.
HO Database is a Navision Server with SQL server Option. Day end statements from all 100 stores are being replicated to HO database daily and statements are being posted at HO which actually created ledger entries at HO. Later on Item ledger entries are again being replicated to Stores.
Currently HO & Store database has following Hardware Configuration:
Existing HO Server Configuration
Server HP Reliant ML570 G4 Server
Processor 2 X 64 bit Dual-Core Intel Xeon Processor 3.20GHz
RAM 1 X 6 GB
Hard disks 6 X 146 GB
Array Smart Array P400 Controller
1 Logical Array
SQL installation path : C:\Program Files\Microsoft SQL Server
Data files path :
Log files path :
Networking 2 X Dual NC371i multi-function Gigabit NICs
Operating System Windows Server 2003 R2 Standard Edition(SP2)
Database SQL Server 2000(SP4)
Existing each Store Server Configuration
RAM 512 MB
Hard disks 40 GB
Problems faced by the client :
1- Table locking of Item Ledger Entry while posting transfer Orders,statements.
2- Navision procedures getting slow (i.e. Scanning of item in transfer Orders) when some posting is being done.
Kindly suggest me whether the Hardware configuration is sufficient, or Upgradation is required, and How to handle Table locking in SQL and performance improve.