Keeping Your ERP Server Files Fit — Part II
When someone asks us, “How do you stay so fit?” we might talk about our diet and exercise regimen. The news is full of what to eat, what to avoid, and how much exercise we need. We thrive on these snippets, but the moiling reality is that to make a difference, we have to be consistent versus doing “yo-yo” dieting to maintain health.
In part one of this article, I talked about how to use server redundancy to keep your company healthy, wealthy and wise. To help your servers respond instantly, some consistent “nutrition and exercise” is also required. In part two of this article, we’ll explore “nutrition and exercise” tips for keeping a database server “healthy”: Organizing, Balancing, Archiving, and Participating.
Have you ever emptied a full closet, re-organized everything, and then put it all back, to happily discover that your things are easier to find, and the closet now seems half-empty? Windows Server and SQL Server both have utilities to do this, and the more frequently these utilities are used, the more organized the data is on a regular basis.
Windows includes a utility called “Disk Defragmenter.” This utility finds pieces of data stored separately but go together, and moves them so they are stored together. This is a typical problem in a supermarket when you don’t have many big open places to put a new SKU. First you might need to slide other SKUs down to consolidate free space, or move some items to a new aisle all together. By doing this, it will be easier for everyone to find this SKU than if it were stored in three or four separate places just because it did not all fit in one place.
The good news is that just about any Windows system lets you schedule the Defragmenter to re-organize your data (a typical frequency for this is weekly). The bad news is that on a SQL Server, this job will not re-organize the files that matter most: the SQL Data files and log files that are locked for update whenever SQL Server is up and running.
Therefore, on SQL Servers, we need to make sure the system is organized before we install and build the SQL database. We need to pre-grow the database to its expected size. Lastly, we set the database to grow in bigger leaps. All of this keeps the database from growing too frequently in tiny pieces, since these tend to scatter all around, making it take longer for the computer to find things quickly.
When a tree needs to get water from the roots up to every leaf, it’s faster if the tree has main branches, smaller branches and then twigs reaching the leaves. On the other hand, a very long “vine” has to carry the water an awful long way to water the furthest leaves down at the end. This is the problem with SQL databases too.
As you add more and more customers, inventory, orders, or other data to the database, it tends to build “vines” rather than a “balanced tree,” and so you’ll start to get delays when you are doing lookups. SQL Server comes with several utilities that should be used on a regular basis. These utilities re-organize these long vines into balanced trees. This is called “Index Rebuilding.” If you never ran this utility, your database server would still function, but responses might get very slow over time.
SQL Server helps you set up and schedule these utilities by providing a tool called the “Maintenance Plan Wizard” that can be used to do your balancing. This can also cause problems when your database is too out of balance, because the Wizard tries to process the entire database. There may not be time to do that overnight, causing extra locking delays the following morning. When you’re signed up for Morse Data’s SQL Server Health Monitoring Service, we take this a step further by monitoring which parts of the database need the most attention and tune these areas most frequently — allowing the maximum amount of balancing you can get.
If you continuously add new inventory, customers, orders, and other data to your database every day, your database will grow every day. This means you are slowly increasing the work that the computer needs to do to find something and the amount of extraneous data that might be listed when you are looking for something. Additionally, it increases the work the computer needs to do when you are re-organizing or balancing your database.
To keep your system fit, it makes sense to regularly archive any old data that you don’t need anymore. I use the term “archiving” to mean that you might run a report, or make a full database backup, and then purge off the old data. This way you can save that report or backup copy for a fixed period of time in case you ever need to refer to it. The assumption is that this data is old enough that you probably won’t need it anymore.
For credit card-related data, the PCI requirement 3.1 doesn’t provide specific lengths of time that this data should or should not be stored, but it does state you must establish some sort of retention policy and then follow it. It makes sense to set this policy for all types of data you’re storing, and then set up this schedule on the server to follow this policy.
Microsoft and Morse Data are both constantly finding new ways to improve their software, and developing fixes as problems are reported. By participating and applying these service packs and updated versions regularly, we’re able to take advantage of the best possible operating environment for our organizations when it matters the most.
Is it better to skip or delay these updates and keep going happy just the way you are? I agree that upgrades can sometimes be painful, but delaying them for too long only builds them up and ultimately causes greater pain.
Did you like these two posts on how to keep your SQL server in tip top shape? If so