Some general tips on how to setup and configure the database landscape (for B1)

[Johan_H]

So...

 

A while ago Abhinav Bannerjee wrote an excellent article (http://scn.sap.com/community/business-one/blog/2012/08/09/performance-best-practices-for-sap-busines... .) about best practices for setting up and configuring the MS SQL Server landscape for use with B1. Unfortunately it was deleted or moved for some reason, so I am going to try and give you my version of the same. Please bear in mind that all of the following is mostly hearsay, and my own personal conclusions from experience.

 

Hardware

 

B1 is a database driven application.That means that whatever the application does, it is actually the database (usually MS SQL Server) that does it. As such when deciding on a hardware configuration for your B1 installation, consider what a database needs for optimal performance.

 

Here are some rules of thumb:

• RAM, RAM, RAM. The database server (usually MS SQL Server) is basically optimized to use hardware as efficiently as possible. In practice that means that it will load data from the database into RAM memory, so it can be accessed much faster than when it would have to read it from a hard disk. What data is loaded is determined by the available amount of RAM and by how popular it is. For example customer information is used throughout the business process (sales order to customer X = delivery to customer X = invoice to customer X), whereas information about human resources is accessed maybe once a day. If the amount of RAM is smaller than the size of the (company) database, SQL Server will load only the most popular data into memory, and access everything else by reading it from the hard disk at the moment it is needed. This is also the premise of HANA. Just take a look at the hardware requirements and you will see it is not a miracle database system. It is simply a database that is allowed to load more data into RAM memory, and optimized to access data that way. MS SQL Server will actually do the same, it is merely less optimized to do this with SAP software like Business One.
  
  Tips:
  - Compared to the rest of the server hardware, RAM is cheap, and has by far the greatest impact on performance.
  - The more RAM available, the more data can be loaded for fast(er) access.
  - Determine the average size of your company database during the expected life time of the server hardware. MS SQL Server runs on Windows. Windows needs RAM too. Preferably you want to get an amount of RAM the size of your company database + the recommended amount of RAM for your Windows Server version (32 bit = 4 GB, with 64 bit it depends on the Windows Server version). For example with an average company database size of 10 GB and Windows Server 2012 (64 bits), get at least 10 GB + 8 GB = 18 GB RAM.
  - If you use a Remote Desktop landscape on this same server, add another 1 GB of RAM for each concurrent user.

 

• Storage, a.k.a. hard disks. A slightly more complicated topic. B1 requires enough hard disk space to fit the company database at least twice. Once for the company database, and once more as free space. The free space is needed for system upgrades (maybe SAP is working on changing this, or has done so already, I don't know), and obviously the system needs free space to work, and the database space to grow (even if only temporarily). A very important principle to understand about databases is that we are talking about reading operations (for example running a sales analysis report) and writing operations (for example adding a new sales order in B1). Writing to a disk is always slower than reading from it, although with the newest SSD disks the difference is becoming more and more negligible. HANA's exceptional speed for example is really only reading speed. Adding a sales order will take just as long as MS SQL Server.About RAID:
  Old school says: RAID1 with a separate physical set of hard disks for the (company) database, as it allows for the fastest possible reading and writing speed. This is and remains sound advice. However, server hard disks can get very expensive at sizes larger than 146 GB, so if your company database is nearing that 100 GB size...
  Also in the development of current hard disk technology a lot of focus has been put on the hard disk controller (the hardware card that connects the hard disks to the rest of the server), and in newer servers these controllers have their own caching system. That means that when you create a sales order in B1, it will really be written into the hard disk controller's RAM memory, from which the controller will write it to the disk(s). As in point 1 above, writing and reading with this controller cache RAM is much faster than reading and writing with hard disks directly. As such with newer servers, it is fine to use other RAID configurations as well. At any rate, using a separate physical set of hard disks for the company database is a good idea.About NAS:
  Network Attached Storage is becoming more and more commonplace, even for smaller companies, because of the advantages of easier scalability, and maintenance.
  With databases the general rule of thumb is: every layer slows the system down. Network Attached storage is an extra layer.Tips:
  - The faster the physical hard disk, the better
  - Use RAID1 if possible and your budget allows for it, at least on older server hardware
  - Invest in a good hard disk controller, rather than in faster hard disks.
  - Once server quality SSD hard disks become affordable, consider them.
  - Use local hard disks for your company database and MS SQL Server installation. Use NAS only for backups, and that sort of thing.
  - If you insist on using NAS, or in separating your hard disks from the server in any way, try to use fiber optic network cabling.


• The rest. After you have allocated the necessary amount of your budget to RAM, and then to storage, invest whatever is left in the CPU and network adapter.
  In general, if it fits in your budget, go for redundancy: two power sources, two CPU's multiple physical disks, etc. Server downtime can be very costly.

 

Software

 

As I mentioned before, it is really all about RAM. However do not forget your network.

 

Some rules of thumb:

• MS SQL Server memory settings. Although the database does all the work, it itself runs on an operating system (usually Windows). The operating system needs memory (RAM) to operate. A common oversight is to not limit the Maximum Server Memory (in MB) of the MS SQL Server installation. As a result, SQL Server can monopolize all server RAM, during peak loads. This in turn smothers Windows, which then smothers SQL Server. In other words, you allow SQL Server to hang itself.
  So however much RAM you have in your server, limit the amount of memory MS SQL Server may use. Ideally, total RAM minus the recommended minimum amount of RAM for your Windows version, but at the very least some 3 - 4 GB. So for example, you have a Windows 2012 (64 bit) server with 16 GB RAM, and a company database of 10 GB. Then you should set Maximum Server Memory to 120,000 MB at the most.
  Of course this is not quite so black and white. You should carefully consider your scenario. How large is your company database, how much RAM do you have? Can you add RAM (is it expensive, does it fit)? How is your B1 installation mainly used? Are you creating transactions at a steady pace, or in peaks? Are you running a lot of demanding queries or reports (also) during business hours? Do you run any addons? Does any of these addons run a lot of demanding queries, or create a lot of transactions ? Based on the answers to these questions you can increase or decrease the maximum amount of RAM the SQL Server may use. Are you running other software on your server and/or database?

 

• Database maintenance. Setting up a maintenance job, will prevent your system from slowing down over time. How much effect such a job has, will depend mainly on the way you use your B1 system. Specifically on the average amount of transactions you perform in relation to the quality of your server hardware. On a high-end server it will take a lot more transactions for the system to slow down than on a low end one.
  A maintenance job can include several steps (shrink, reindex, check integrity, etc).There are some conflicting opinions out there on what step you should include in a job and what not. In my experience it depends on the circumstances, and there is no solid answer that goes for all situations. I recommend using our good friend Google, to read as many articles on the subject as you can, and to then make an informed decision. Remember, you can always delete a job, and create a new one, or tweak the settings of the job.
  A simple way to implement maintenance jobs is to use the Remote Support Platform. The RSP contains several maintenance jobs set up and supported by SAP. All you need to do is determine the schedule. You may also set the maintenance job up yourself in the SQL Management Studio.

 

• Database files. Your company database consists of two files (see the Files section in the database's properties): Rows Data, and Log. You can improve performance by saving these files on physically separate hard disks (also see the paragraph About RAID). However the increase of performance to cost of separate disks ratio is not enough to warrant purchasing multiple disks for this purpose alone. I recommend this only if you happen to already have separate disks.
  The Autogrowth setting is best left to a percentage setting. MS SQL Server reserves hard disk space for the database to use, depending on how much is needed. This is a fast process, but reserving space by x static increments of a certain number of MB, is slower than a single increase of 10%. The percentage setting uses more space on your hard disk, but this is simple to counter by using the shrink operation in your maintenance job. If you have so little free space on your hard disk that an increase of 10% causes problems, then it is time to install a bigger hard disk (see also the paragraph about storage above).

 

• Remote Desktop Services. Using this is smart, though there are one or two pitfalls.
  What is good about it? With RDS you only have to install, maintain, update, and troubleshoot, a single Business One client.
  If your scenario requires users to access Business One from a remote location (from home, or a business partner's premises), it is much easier to access; just start a VPN connection and double click the Business One client shortcut. Incidentally, it is also faster. This is because only the visual screen is transmitted, not the actual data.
  You will be running the client on server hardware. That should improve performance in itself, assuming that your server hardware is stronger than your workstation's hardware, as is usually the case.
  What are the pitfalls? Printing is not as easy, as the Business One client via RDS, will only see the printers installed on the server. That means that you need a printer with a network card, and printer settings are the same for all users.
  The amount of times you can access an RDS application per user is limited. Users must log off (close the application) correctly / normally for RDS to register that a session is no longer active. If your environment is prone to sudden power outages, or a workstation is shutting down unexpectedly a lot (for whatever reason), or you rely on WIFI, and one of your Access Points is acting up, or you have a sloppy user, who likes to shut down their workstation without closing the RDS application properly first, you have to purposely log on to the server to close sessions that are left in limbo this way. If not, users will not be able to start the application after a while because there are too many active sessions "open".

 

• Virtualization. Using a virtual server is great for server maintenance, but it is not ideal for running databases. As with NAS, virtualization is an extra layer.
  RAM configuration is also more complex: physical RAM > virtualization platform server > virtual servers / machines > allocated RAM > SQL Server RAM setting. According to the same principles as a stand alone server, the virtual platform server needs a minimal amount of free RAM to operate optimally, then each virtual server needs a minimum amount of free RAM to operate optimally, and each database needs an amount of RAM as large as possible to operate optimally.
  As one of the premises of a virtual landscape is load balancing, any peak usage of physical resources (RAM, CPU, etc.) by any virtual server / machine, will immediately hamper all other virtual servers / machines. Along the same line of logic, if you assign the full amount of physical RAM to your virtual server, it may strangle the virtual host at peak usage.In conclusion, avoid virtualization if at all possible for database (B1) purposes. Otherwise, we are back to RAM. Make sure that you have enough physical RAM as if all virtual servers / machines were operating at peak load at the same time. Alternatively, find an engineer with expertise in both virtualization and databases, for an optimal setup of memory in your virtual landscape.

 

In Conclusion

 

If I have missed something, or if I got something horribly wrong, please let me know, and I will try to add it or correct it.