You, as the executive or IT professional in charge, have a significant risk if you do not manage the items in this document. If you don’t have the time or understanding to make good decisions, then hire someone, either an employee or consultant who can take appropriate actions. Don’t let the fate of your company rest on chance or blind trust that your system is prepared. Parts of this document target the business owner or manager. Other portions are for the person responsible for managing SQL on an on-going basis. The article is a short read; use it as a framework to discuss these topics with your IT support providers to improve your business system.
Microsoft SQL is a powerful, resilient database engine that can provide a stable platform for your business system. Like any sophisticated program, proper hardware, setup and maintenance are critical for success. Use this document as your “general best practices” guide based on our current knowledge level.
The discussion in this document is not some esoteric, theoretical information. Proper SQL maintenance and monitoring can prevent catastrophic failure. We setup automated maintenance for all our customers. We have found that these jobs fail for no specific reason. When these jobs fail, your company cannot work and you may lose data.
We created this document to provide your company the best performance and data integrity, since SQL contains the data for your operations and customer data. This document contains general recommendations; it is not a how-to guide. We will cover Server Selection, SQL Setup and Regular Maintenance. We also tell you other areas that you should consider.
Our first recommendation for ‘best practices’: Question any suggestion, no matter what the source and validate all assumptions, including your own. The practical conditions encountered in each different location, continuing software changes, and hardware/software advances could greatly alter these recommendations at any time.
Our perspective is that of a vendor who supports ERP/accounting systems that may have a CRM (Customer Relationship Management) component or a standalone ERP or CRM system. Our main concerns are internal security, stability, reliability and reasonable performance to support the business operations and reporting requirements of the company. Your circumstances may be different and may have unique recommendations. Hardware, operating systems, applications, security requirements, Web access and other business requirements will affect these recommendations.
This discussion does not include virtualization as an infrastructure or disaster recovery method. Anecdotal evidence shows degradation of performance with virtualization of 20 to 50%, depending on the configuration and disk access. If you want to use virtual servers, discuss the pros and cons with your network and ERP vendor. Use highly qualified technical support to configure the hardware, virtual and physical environment to achieve satisfactory performance. The same advice holds if you are using a SAN as your server disk storage. Both of these are highly complex environments and can cause significant performance issues if configured incorrectly. Each one adds several layers of software that may cause problems.
We have not included an enterprise backup discussion here. You need one and SQL must be a part of the plan. Discuss this with your network support vendor. Then check for special implementation considerations with your ERP support group.
We have not included a discussion of good design and programming techniques here. Software and reporting design significantly affect performance. Poorly designed reports will take far more resources and time than well-designed reports. A poorly designed report may take hours to run, while a well-designed report may run in minutes. This difference dramatically affects both actual and perceived performance for all users on the system.
This is a general framework for determining the specifics of the requirements for servers for your specific situation. Our intent is to provide the business manager or owner with the understanding to make a technical decision without becoming a technical guru.
- Separate Servers: Do not combine your SQL server with Exchange, Domain Controller, Active Directory Server. Depending on load, use separate IIS server, SQL Reporting Services and other SQL options. This means separate hardware, not virtual servers.
- Operating System: 64 bit operating Windows Server 2008 R2 for your SQL server. Make sure that your selection of operating systems includes consideration for the lowest common denominator for your applications. I.E. this recommendation is invalid if one application does not support 64 bit Windows Server.
- Microsoft SQL Version: Use the highest version of SQL supported by your applications. You may consider having multiple instances of SQL on the same or different servers if a particular application does not support Microsoft 64 bit SQL. Consider these versions of SQL for your ERP or CRM application:
1. SQL Express: Very small company, limited number of users, limited transactions; simple reporting and automation requirements
2. SQL Standard: Small to Mid-size company, more users, more transactions, more reporting and automation requirements
3. SQL Enterprise: Small to Mid-size company, high availability, low tolerance for any data loss (since last backup), more complex reporting and automation requirements
- Hard Drives: Databases are typically I/O bound (the speed of the hard disk first, the network second and the processor third). Disk speed, size and type matter more than anything else in your SQL server does. Disk drives are the slowest piece in the system.
1. Speed – pick the fastest drives you can afford. SATA drives are not as nearly as good as Serial Attached SCSI (SAS) drives. Consider using Solid State Drives for high-performance applications.
2. Use RAID 10 or Mirrored drives. Avoid any other type of RAID or disk configuration.
3. Three separate disk subsystems for operating system, database and log files are better than one.
4. If using a SAN, make sure your hardware support team knows how to configure it properly.
5. Many smaller drives are better than one large drive as this provides more throughput for reads and writes.
6. Quantity and Configuration Recommendations:
a. Operating System – Mirrored, serial attached SCSI drives
b. Database – RAID 10, serial attached SCSI drives
c. Transaction Logs – RAID 10, serial attached SCSI drives
d. Other volumes and systems – for backup, temporary files, as necessary
- RAM: More is better. For maximum performance, 4 GB more than largest database size. SQL will use RAM as cache for database.
- Network Interface:Use at least one Gigabit network interface per server, more with heavy demand. Make sure that your switches are Gigabit for your servers, preferably for users too.
- Processors: Current processors generally provide more than ample performance for SQL. That being said, don’t skimp, as you may need more processor tomorrow.
1. Separate the Program, Data and Log files as described above.
a. Beware of ‘virtual’ environments for now—anecdotal evidence suggests 20-25% performance loss through virtualization.
b. In high performance circumstances, consider moving your temp files to another physical drive.
2. Make sure virus scan leaves SQL and its data alone, including SQL backup folder locations.
3. Add a new user to control your application. Do not use SA as the user controlling your application. Creating a unique user will increase your flexibility and control over the database you will be using. This user should be the dbo (database owner) of your application database. This technique allows you to have a SQL administrator who does not have access to your ERP, Manufacturing or CRM data.
4. Remember that new databases take their location from the ‘Model’. Check after the creation of any new database to ensure that all aspects of the new database are set correctly. You can adjust the ‘model’, but it is often more trouble than it is worth because it is a single-user task.
a. Physical location
b. Type (Full or Simple)
i. Sage Pro: PROTEMP and PROSAMPLEDATA should be Simple and all others Full
ii. Microsoft Dynamics NAV: Full
iii. Sage Accpac: Full
iv. Sage CRM: Full
v. (A short digression – you may decide that you do not need to be able to restore to a point in time after your last backup. For example, your server crashes at 3:30PM and your last backup was at 1:00AM. A full database allows you to restore to the last completed transactions prior to the server crash (assuming you can get it off the disk drive). A simple database does not allow you to do so. The restore to 3:30 may not be a good idea as you may have many partially completed transactions. You might use it to find out what you did for the day and then restore the 1:00AM and reenter the transactions to make sure you enter all transactions.)
c. Collation – unless you have changed the Model, take the default. Improper Collation settings will cause very unpredictable results.
5. Defragment your data and transaction log drives before and after setting up your databases. Your SQL performance will be better on a defragmented drive. If you defragment in conjunction with growing your files sizes, you will not have to defragment these drives for a long time. Take steps to limit physical disk page fragmentation:
a. Increase database and log file sizes to deal with long-term needs.
i. Consider how large the log gets during re-indexing and how fast the data will grow. We frequently see log files one to three times the size of the data.
1. Database size 10GB with 7 years data; setting database size to 15 GB will provide approximately 3.5 years data without “growing” the database. Consider setting database size at 20GB.
2. Set transaction log size at 10 GB to 15 GB depending on number of users and types of activities performed.
b. Detach databases in SQL and shutdown SQL services (all)
c. Defragment all server drives (several times needed to get true defragmentation). Consider using a special purpose defragmentation software program rather than the defragmentation utility Microsoft provides.
d. Reboot server
e. Re-attach databases
Automated Database Maintenance
1. Create many separate Maintenance Plans and create a SQL Job to run them. Create one or more Jobs as appropriate for your system, user schedule and hardware capacity. Schedule these Jobs at times when the Job will not conflict with users or each other. This may seem to be more difficult to manage one Maintenance Plan, but in the end, it will provide better flexibility and provide more assurance that SQL completes some tasks, even though some may fail.
2. Backup – Do it or Die! We suggest backing up to a local drive, and then back it up to another device. This gives you quick access to restore a database even if your latest backup is on a device that is now offsite or is on an Internet backup plan.
a. Setup a SQL backup Maintenance Plan for your databases. This will create .bak files. Backup these as a part of your Disaster Recovery Plan. Get a copy offsite on a regular basis; consult the Disaster Recovery Plan. If you do not have a Disaster Recovery Plan – develop one. The health of your business is at stake.
i. System databases: backup on a regular basis, at least monthly, more often if frequent changes are made to users
ii. Company databases: back up at least nightly.
iii. Static ERP files: (such as Sage Pro ERP PROTEMP and PROSAMPLEDATA) backup monthly, could easily include these with the system tables.
c. Transaction logs
i. Setup a separate Transaction log backup that runs at least twice. The full backup above also backups up the transaction logs. However, SQL does not delete the transaction records until the next backup. As such, the log files have an opportunity to grow to great sizes if you do not add another specific job to backup the transaction logs AGAIN.
ii. Functions such as Sage Pro ERP indexing, recalculating, period closings, etc. create large log files. Manually run this SQL Transaction Plan after these actions to keep log files from growing.
iii. Note: We have encountered times where the SQL did not truncate the logs until the third backup.
3. Other Maintenance Tips
a. Delete old backups for databases (.bak) and transaction logs (.trn). Make sure you set the retention period so that at least three backups are always in the hopper (this will depend on your available drive space, do not run out of disk space because you fill the drive with backups).
b. If you have large database sizes and you are not regularly working on the weekend, don’t run backups on weekends in order to provide enough time for other maintenance (re-indexing, database integrity, etc.) to occur.
a. Do you rebuild or reorganize? Rebuild defragments the index by dropping (deleting) the index and building it from scratch. Reorganize makes sure that the index is current and has all the data included in it. Consider jobs to do both at appropriate times.
b. Rebuild – do this monthly or more frequently with high transaction volume. (If you have SQL Enterprise addition, you have the option to leave indexes online while rebuilding the index.)
i. Consider this option when you can assure yourself that you can get exclusive use.
ii. The job will fail if it cannot get exclusive use and any other tasks following the rebuild will fail (depending on your settings). This is why we recommend that you create a Maintenance Plan for each task rather than many tasks in one Maintenance Plan.
c. Reorganize – Do this every night.
d. Update Statistics: include this task with either rebuild or reorganize to ensure the SQL engine has up-to-date information required to develop execution plans.
e. DO NOT FORGET THE SYSTEM TABLES!
5. Check Database Integrity
a. This task requires exclusive use. Schedule this task for a time that will not conflict with user access.
b. Suggestion: include as step or separate job after index reorganization discussed previously
a. The Sage Pro ERP PROTEMP database will sometimes contain orphans. Typically, environmental issues like workstation settings cause this. Any file with a creation date over a day old is probably not relevant. Manually delete tables on a monthly basis—more often if seen as a problem. Investigate workstation setups if there are often many temporary files left in PROTEMP.
b. Setup email alerts for system problems, failed jobs, etc.
Do not rely on automated systems as though they were bulletproof. They will fail for no apparent reason. (There always is a reason, it just is not apparent and investigation will determine why it failed. For example, an index rebuild will fail if someone is logged into the database when the job runs. You will have to adjust your timing or ensure that the “offender” follows company procedures and logs out at prescribed times.)
- SQL Maintenance Records
a. Did everything run successfully?
b. When did it last run?
c. Is it scheduled to run again? When?
d. Are all databases included in appropriate maintenance plans (including new databases)?
- SQL database checks
a. Is there adequate space in databases?
b. Are transaction logs staying near “empty”?
c. Are your indexes fragmented?
d. Do you have excessive Page fragmentation?
- Drive Space availability
a. Is there enough free space on the volumes?
b. Are the drives fragmented?
c. Is the RAID degraded?
d. Are backups deleted per schedule?
- Hardware/system check
a. Is the RAM over utilized?
b. Are there any warnings?
c. Are system updates overdue?
d. Is your virus scanner up-to-date and set correctly?
e. Do you see degradation of network traffic?
Backups are not of any value unless you can restore from them and have valid data after the restore. If you see a SQL backup file on a disk drive or get a message from your backup software that a backup completed successfully, consider this only a ‘quick’ check. You must verify that you can restore the data and that the data you restore is valid.
WARNING: make sure that you or the person you assign this to knows what to do. You run the risk of over-writing your live system with bad or corrupted data and programs if the backup is bad. Test your restore on a test server. (Your test system can be a virtual server.)
- Test Restore
a. Restore both programs (from your system backup) and last night’s data (from your system backup and your local SQL backup) to your test system.
b. Have a user run the application, enter some data and run some reports. Make sure that these reports match reports run from the live system.
- Check PC/Server Setups
a. Are settings (see our Configuration Guidelines) in conformance to software vendors recommendations?
b. Are virus scanners and settings up to date?
c. Is the hardware showing signs of age or deterioration?
d. Are users putting files on local drives that should be on public drives for backup and security purposes?
e. Are there updates to your ERP or CRM that should be installed?
f. Is SQL up to date?
- Update your management team
a. System status
i. Drive space issues
ii. Purchases required
iii. Software updates & changes
b. Restore time
i. Is it acceptable?
ii. Are there system changes that can facilitate faster recovery in the event of a system or software failure?
SQL generally runs very well when setup properly. It is not maintenance free. If you are not comfortable with your SQL setup or with doing the maintenance and weekly checks, we provide this service on a contract basis. (Send us a message, bautomation.com/contact or give us a call 877-571-8580 or 763-571-8580) Do not risk your company’s data and livelihood by ignoring this task until it is too late.
For a .pdf version of this article, click here.