If you run Microsoft Dynamics on premise then all but the smallest user counts will one day come across performance issues. As data volumes and user counts grow this becomes something we have to pay more attention to if the snappy instant speed users become used to initially is to be maintained. This is not restricted to Dynamics applications, but is true for any database system. The principles I outline for keeping the system optimal apply to any such application, especially if based on Microsoft SQL Server database platform.
So the Dynamics family are primarily database applications used to store lots of data about all aspects of your business. The primary constraint for speed is going to be how fast they can get the data you need now off the disk it stored on and how fast they can write new data to the correct place. The most important part of your system for this is your SQL server – so that's what we will concentrate on.
So by far the best option are the newish SSD (solid state disks) that are made up of electronic chips. The trouble is that these chips only support a number of writes to the same place before they burn out, and while this will be years on a normal laptop or PC, on a database server with lots of people feeding revised data all the time – it can happen very quickly. For this reason the SSD’s suitable for database servers are expensive basically because they have to have lots of redundancy, so when an area burns out it can move the information to that spare area.
The older type still use mechanical spinning disks to encode magnetic data. These have read/write heads that go across the disk to the correct track as the sector spins round. That means you have what we call latency, as the disk gets itself into the right position to read or write the data you need.
This latency means that performance is much better, having lots of smaller disks rather than a few large ones. The read/write heads cannot be in multiple places at the same time, so having two on two disks rather than one big one gives twice as much chance of it being feted to get the data you need. Similarly buying faster spinning disks that spin 15,000 times a minute means that data comes round under the heads quicker than the 7,200rpm slow ones.
What those disks are connected to is also critically important. There will be times when the communication channel to the disks will be saturated so the faster and wider it is the better. If the disks are the carparks then the disk subsystem are the roads from the car parks to the motorway. A road system that allows all the carparks to both park and leave without getting is each other's way and at motorway speed is what you need. That means either a fast SCSI or better still a saturate area network based system.
Best of all though is that the system doesn’t have to get it off the disks at all. This happens when your SQL Server has enough memory to be able to store the data there is what we call cache and so it doesn't have to get it for disk. No SQL Server can have too much memory, buy as much as you can afford, you'll not regret it.
The final bit of the SQL Server performance jigsaw are the processors. These however on a SQL server are not the priority. Don't get me wrong they need adequate power but it's not critical to have cutting edge. The money is better spent on memory and the disk subsystem.
I'm often asked we are about to buy this hardware, is it suitable? The trouble with that question is suitable for what? What you need now for the peak twenty minutes a week? What you might need in the future because it's got to last at least four year? Why take what will be a gamble?
Outsource the lot to the cloud and specifically Microsoft Azure. That way if performance is slow you can turn it up without having wasted money on hardware you don't need any more. If the business need change the platform can change. You only pay for what you need today. If your communications aren’t good enough and reliable enough don't you need to change that anyway?
Having a fast Dynamics system won't be much good if you phones and email are down for long will it? How much longer will your business prosper without that fibre connection? How attractive will your premises be if it's doesn't have fast reliable comes? Spend the money, dig up the road, put the fibre in and never regret it.
