Server Sizing for about 125 clients

Tetos

Hello Experts.

We are in the design stages of implementing MS Dynamics Nav 5.0 with LS Retail. We have about 100 WAN sites intending to connect thru citrix to the Head office and run Navision for their day to day operations. At the sites, we would have a local site server (native db) connected to IBM POS machines. Sales and Master data will transfer between the HO and Sites through Replication. But all inventory transactions would be done only at HO. There are about 25 users in the HO who are mainly MIS users who generate reports etc.

What would be the recommended configuration for a SQL Sever and TS to be able to cater to these clients.

The likely transaction counts are as follows.
Per site
Average Sales Transactions per month = 30,000
Average Number of items sold per month = 50,000
Average Number of Purchase Invoices per month = 700
Miscellaneous Transactions (Adj, Phys. Inv, Transfers) = 2000

Assuming that the primary data retention is for about 3 years. With the above information, we have done some calculations. However, would like to hear from experts in this group about their experiences.

Thanking you all in advance.

Tetos.

kriki

[Topic moved from 'Navision Financials' forum to 'NAV/Navision Classic Client' forum]

Tetos

Sorry - I couldnt get to read the reply. (Topic moved from 'Navision Financials' forum to 'NAV/Navision Classic Client' forum). Is there a way to view the reply?

kine

Tetos wrote:

Sorry - I couldnt get to read the reply. (Topic moved from 'Navision Financials' forum to 'NAV/Navision Classic Client' forum). Is there a way to view the reply?

There is no reply, it is just post about moving the original post from one forum to another... ;-)

kriki

[The reason I moved your question to another forum is that it wasn't a Navision Financials question]

ara3n

In your Title you mention 125 users, and in HQ only 25 people who will be using the HQ db. How many stores that will replicate the data to HQ?

Here is a basic HW that you can start with.

You will need at least

SQL Server.
NAS Server.
Citrix/RDP Server.


The SQL Server should at least have the follow configuration.

OS: Windows Server 2008 Standard Edition 64-Bit with SP2
Processor: 2 x 2GHz
RAM 16+ GB
Internal Storage 8 x 146GB, RAID 1/0
Internal Storage HBA Multi Channel SAS/U320
Network Interface (NIC) 1 - Dual Port Gigabit, Teamed


Logical Drive Configuration
C: OS & SWAP (100GB)
SQL DATA (500GB)
E: SQL Logs (100GB)

SQL Server 2008 Standard Edition 64-Bit with SP1

stryk

ara3n wrote:

In your Title you mention 125 users, and in HQ only 25 people who will be using the HQ db. How many stores that will replicate the data to HQ?

Here is a basic HW that you can start with.

You will need at least

SQL Server.
NAS Server.
Citrix/RDP Server.


The SQL Server should at least have the follow configuration.

OS: Windows Server 2008 Standard Edition 64-Bit with SP2
Processor: 2 x 2GHz
RAM 16+ GB
Internal Storage 8 x 146GB, RAID 1/0
Internal Storage HBA Multi Channel SAS/U320
Network Interface (NIC) 1 - Dual Port Gigabit, Teamed


Logical Drive Configuration
C: OS & SWAP (100GB)
SQL DATA (500GB)
E: SQL Logs (100GB)

SQL Server 2008 Standard Edition 64-Bit with SP1

Hmm ... I do not fully agree with this ...
First of all: the sizing depends on the number of processes/users, the transaction volume and estamated db size and growth. Thus, we just know about the number of users we could just give some "key figures" which have to be discussed ...

I'd recommend this:

SQL Server:

Architecture: x64 (64bit - of course)
OS: Windows Server 2008 x64 (maybe R2 once officially supported for NAV by MS) Enterprise The price-difference between STD and EE is not that much, but with EE you have no limites and you are more scalable; e.g. STD supports max. 32GB RAM, once you need more you need EE
SQL: SQL Server 2008 x64 (maybe R2 when officially supported for NAV) SP1+. Regarding the edition you should compare STD vs. EE and decide on basis of your requirements regarding the features
CPU: 1 Socket per 100 processes. 150 Users = 2 Sockets = 2 x QuadCore (total 8 CPU) or 2 x HexCore (total 12 CPU)
RAM: depends on db size on tranaction volume; consider 16GB as minimum
Disks: strongly depends on db size. With a db around 100GB you need something like this (proposal):

C:\ 2 x SAS 15k rpm RAID1 OS, Programs, Page File, etc
\ 2 x SAS 15k rpm RAID1 SQL Server, master, model, msdb, tempdb (6-8 files)
E:\ 16 x SAS 15k rpm RAID10 NAV DB (mdf/ldf)
F:\ 4 x SAS 15k rpm RAID10 NAV Log(ldf)
G:\ n x SAS 15k rpm RAID10 Backup & Misc

LAN: 1 x Gigabit Ethernet for User-Connections, 1 x dedicated Gigabit for Server-to-Server connections

NAV Application Server (NAS):

Architecture: x86 (32bit)
CPU: 1 x QuadCore
RAM: 4GB
Disks: 1-2 x SAS 15k rpm [optional RAID1]
LAN: 1 x Gigabit Ethernet


Citrix/Terminal Server:

Architecture: ?
CPU: 1 CPU per 10 Users; hence 1 x Quad supports up to 40 Users (this strongly depends on WHAT the users are doing - NAV and other applications!)
RAM: 64MB per User, hence with 40 Users = 40 x 64MB = 2560MB (2.5GB); plus 1GB OS (= 3.5GB), plus requiremets of other applications (so consider 4GB as absolute minimum! be careful!)
Disks: 500MB per User; with 40 Users = 40 x 500MB = 20000MB (20GB) SAS 15k rpm plus OS requirements and other applications
LAN: Gigabit Ethernet

Again: this all needs to be discussed thoroughly ... :-k

Tetos

Thank you experts for your posts.

ara3n wrote:

In your Title you mention 125 users, and in HQ only 25 people who will be using the HQ db. How many stores that will replicate the data to HQ?

@Rashed - There are 25 HO users who will be accessing Navision thru LAN (thru nav client) and about 100 Store users who will be accessing the HO db thru Citrix. That makes it a total of 125 users. The 100 stores will be replicating data to and fro HO (mainly sales data from stores and master changes from HO)

I am a bit puzzled to see that my local partner who is implementing Navision for us hasnt recommended the NAS server at all. Is the NAS server an important component in this type of an implementation? Could there be any reason why the partner hasnt recommended this to us?

stryk wrote:

First of all: the sizing depends on the number of processes/users, the transaction volume and estamated db size and growth. Thus, we just know about the number of users we could just give some "key figures" which have to be discussed ...

Yes. I fully agree with you. The rough estimate of the average number of transactions expected from each store can give some idea. When we did our calculations using these averages, (going by the record size of each transaction table), we estimated the database to be over 2TB in 3 years. Is this normal? However, the partner has mentioned that compression routines can be used to reduce the size of the database periodically.

Thanks in advance for all your inputs. There is a lot of clarity now in terms of HW Sizing.

kine

es. I fully agree with you. The rough estimate of the average number of transactions expected from each store can give some idea. When we did our calculations using these averages, (going by the record size of each transaction table), we estimated the database to be over 2TB in 3 years. Is this normal? However, the partner has mentioned that compression routines can be used to reduce the size of the database periodically.

The size could be big e.g. because number of used dimensions - do not forget that each used dimensions means many entries for each document line/journal line posted. Item tracking and big count of document lines could make the DB very big quickly. The mentioned compression routines are dangerous. Try to not use them if possible. You can see many posts about this on MiBuSo. In most cases the database is not growing linearly but quickly first year and than the grow is smaller (the initial grow is because all the indexes on the tables are growing, but because it is tree structure, it is not growing so quickly after that). All depends on which areas in NAV you are using, how the DB optimized etc.

David_Singleton

2tb for 100 stores over 3 years seems way too much. How many POS units do you have at each store.

How many LS Retail implementations has you partner done. There are a lot of LS Retail implementations in UAE, so its not a problem to find some reference customers to compare to.

stryk

Regarding NAS:
The "NAV Application Server" is actually some kind of NAV Client without GUI, used for automatic processing. You probably need one for the LS replication etc..

Regarding Growth:
700GB per year is definitely not normal. As Kamil suggested, you should review your setup and maybe business processes. I think it is also crucial to develop a feasible "data- clean-up & archiving solution" ASAP to get rid of the data.
With such an estimated growth/volume you should definitely use SQL Server 2008 ENTERPRISE Edition - most important here is the data compression (e.g. PAGE compression) feature: this is quite useful to compress Ledger Entry tables or Posted Header/Lines or Archived Header/Lines down to 20%-30% of their original size (caution: you must not compress everything, there are important guidelines to follow!).
Hence, you could save physical and memory space, also reducing I/O, avoiding trouble.

David_Singleton

Tetos wrote:

There are 25 HO users who will be accessing Navision thru LAN (thru nav client) and about 100 Store users who will be accessing the HO db thru Citrix. That makes it a total of 125 users. The 100 stores will be replicating data to and fro HO (mainly sales data from stores and master changes from HO)

Tetos, where is that spread sheet from. Those numbers are very bad, and show the the system is set up wrong, AND has a lot of bad customizations. Is this your database, or is this some other database that your partner used to base on?

Tetos

David Singleton wrote:

Tetos, where is that spread sheet from. Those numbers are very bad, and show the the system is set up wrong, AND has a lot of bad customizations. Is this your database, or is this some other database that your partner used to base on?

The spreadsheet is created from Database-information-tables - copied to excel. So the columns B, C and D are picked up from Navision. This is our existing Database. We have around 6 sites running on Navision with 2 POS machines in each site. They are currently connecting thru TS.

"Bad Numbers" did you say? Is it bad on the columns B, C and D or the others?

stryk wrote:

Regarding NAS:
The "NAV Application Server" is actually some kind of NAV Client without GUI, used for automatic processing. You probably need one for the LS replication etc..

Got it. We are having a client which is running the Scheduler to replicate data. Im told that NAS is not required as this setup is already existing.

David Singleton wrote:

2tb for 100 stores over 3 years seems way too much. How many POS units do you have at each store.

Average 2 POS units in each site.

kine

"Bad Numbers" did you say? Is it bad on the columns B, C and D or the others?

As I already wrote - you cannot just multiply the numbers. If the table is small (right now in your database), the overhead part of the record size is much bigger than when the table will be filled in with more records. E.g. when you have one record in table, the overhead size could make more than 90% of the size (if there are many indexes etc.). But if you fill the table with thousand records, the record size could be smaller because this overhead will be much smaller.

Tetos

@Kine - I understood your point that to arrive at the size estimate, mere multiplication doesnt work. I had a feeling of this when performing the analysis. But since David mentioned about the numbers, I wanted to check if the initial record size itself was alarming.

David_Singleton

Tetos wrote:

David Singleton wrote:

Tetos, where is that spread sheet from. Those numbers are very bad, and show the the system is set up wrong, AND has a lot of bad customizations. Is this your database, or is this some other database that your partner used to base on?

The spreadsheet is created from Database-information-tables - copied to excel. So the columns B, C and D are picked up from Navision. This is our existing Database. We have around 6 sites running on Navision with 2 POS machines in each site. They are currently connecting thru TS.

"Bad Numbers" did you say? Is it bad on the columns B, C and D or the others?

Its two things, firstly the actual values are wrong. If you are using 3.6k for value entries, then they have done something wrong. I didn't understand Kine's first post, but I see now what he means, but by 10,000 records the numbers will have stabilized, so I think the issue is a badly designed system, with too many keys and too many fields. Some values like G/L entry and Dimension look OK, but how can the value entyr have more detail than the purchase line?

Secondly you can't have more Item ledger entries than Value Entries. Its a many to one relationship the other way. Then you have 30,000 purchase lines and 50,000 Item ledger entries? That does not add up. These numbers are not usable to extrapolate database size, and in addition BEFORE you ramp up to a big system you need to figure out what mess your system is in and fix it.

Tetos

David

That is a very useful information. Im sure there is some explanation to be done on those mismatching records. I will try to investigate more and post the results.

Thank you all for the useful information. Will post back any new updates.

Cheers.

rmpatel22

Got it. We are having a client which is running the Scheduler to replicate data. Im told that NAS is not required as this setup is already existing.

@Tetos,

I think one scheduler scheduler server is not enough for your setup. It depends on many parameters

Do you have any server at stores?

If no, Have you installed Navision database as Server on one POS machine and another POS machine is accessing as client? (Case 1) OR do you have seperate databases on both POS machines? (Case 2)
If yes, (Case 1)

If you have (Case 1), for HO scheduler, you have to consider 100 locations for data transfer and if (Case 2) then you have to consider 200 locations

Now if one scheduler is pulling sales from POS machines (Trans. Sales Entry, Transaction Header, Trans. Payment Entries and many other tables, you know that) and pusing masters(Item, Barcode, Unit of measure, Sales Price, POS staff, Tenders, Promotions etc) to POS and if one machine takes 5 min (Again it depends on your connection in store, if connection is slow, it will take long time) to replicate these tables, it will take 8 hrs for next processing in Case 1 and 16 hrs for Case 2.

So you need to divide this activities on different replication servers or process different location on different replication servers.

Do you have VPN in stores?
ADSL connection in all stores?
Your company is in which type of business (Sure its Retail, but its in Garment/Supermarket/other)?

Tetos

Patel,

rmpatel22 wrote:

Do you have any server at stores?

Yes. There is a server (Trx server) and 2 POS machines connected to it at each store. They are all on individual native databases. The Trx Server pulls sales info from the POS and push masters to the POS. On the other side, the Trx server replicates sales information to HO thru VPN and masters go from HO to the Trx server in the same way. All other inventory related transactions are performed thru the TS connecting to the HO.

rmpatel22 wrote:

Your company is in which type of business (Sure its Retail, but its in Garment/Supermarket/other)?

Supermarket / Convenience stores at petrol stations.

With this setup, do you still feel one scheduler is not enough.

rmpatel22

Hi Tetos,

If your stores are Supermarket/Convenience stores (like EPPCO/ADNOCstores on petrol stations), i think you need more replication servers. If configuration is low, it will be ok, but servers are more then you can reduce time to update data in stores and HO database (We have 20+ stores, we are using more then 2 replication servers). You can discuss this with your implimentation partner.