How to Fix and Optimize My Database?

Access database, SQL Server database, MySQL database - basically any database!

Databases are the most important part of your software since they store all your business data and structures.
It's no wonder that if the database stops working properly, it significantly affects all company productivity.

Database Issue Types

• Crash - database completely doesn't work
• Failure - database generally works, but sometimes goes down or stops responding
• Consistency/Integrity - database works, but data is poorly structured, invalid, inaccurate, incomplete, duplicate, or there are broken references
• Performance - database works, but it is too slow
• Security - database works, but it is not secure

Let's take a closer look at these issues.

Database Crash

When a database crashes it could be caused by:

• OS failure
• Hardware failure (power, server memory, server disk corruption)
• Network failure (server network card, routers, firewall, cables)

How to solve:

• Always have backups! Restoring from a backup onto a new server is the fastest way to get your database up and running in the case of a hard crash.
• Instead having a single db server, consider setting up a database cluster (for example, if you are using AWS cloud - switch to RDS with High Availability Multi-AZ deployment.)
  • This allows you to keep your work going when one of the database servers crashes.
• Check that the database server is actually powered on and booted up.
• Check connectivity to database server, if you cannot ping or login on the server itself - it could be network infrastructure issue.
• If connection is fine and the server works, try to login to the OS (remotely or via terminal.)
• If you can login to the server - check OS logs and DB server for any errors, especially disk issues. Try to reboot and perform memory tests.
• If hardware and OS works, sometimes it might help to save DB data files and re-install software, but this could take time.
• In a worst case, if you can't get the server to work and can't restore it from backup, it might still be possible to recover data by physically moving disks to another server. (Turn it off first.)
  • For SQL Server you can attach old database files (.mdf/.ldf)
  • For MySQL just change data directory to old one
  • In case of MS Access - copy .mdb or .accdb files to a working server shared directory

Database Failure

Software failures or glitches are frustrating as they can occasionally happen and it's often hard to find the root cause.

Common Causes:

• Hardware issues
• Software misconfiguration - current database configuration, while working properly before, currently does not match your needs after your db usage pattern has changed (much more data being processed, more simultaneous users)
• Too high load - this failure happens when db is overloaded

How to solve:

• Check server OS/DB logs to find out if there are any errors, if it's hardware-related - run tests and replace that part
• If you have a specific database error message when failure occurs - google it, there is a good chance somebody has already found a solution
• Check DB server configuration
  • Ensure DB server has enough memory and is not using swap file
  • If there is not enough memory/cpu/disk resources for the DB - consider moving the database to a separate dedicated DB server
• Apply latest updates to the OS and DB server
• For high load issues - see "Performance" section
  • If many simultaneous users are working with the database, failures can be caused by locking issues:
    • For SQL Server - consider changing transaction isolation levels to levels that allows better parallelism, for example "READ COMMITED SNAPSHOT"
    • For MySQL - switch to InnoDB tables instead of MyISAM
    • For Access - move your data to SQL Server and use linked tables

Consistency/Integrity Issues

Such issues are related not to DB server itself, but to the way the database structures where designed.
You can tell if your database has these issues when:

• There are orphaned data, which should be linked to some other information that was deleted
• There are duplicates (same client appears more than once)
• Not all data required for your business logic are gathered
• Some data is stored using wrong format
• There are invalid data or "garbage"
• Data is stored in an unstructured way and cannot be processed automatically
• Same data needs to be entered in more than one place
• Existing fields are re-used to store information of a different type than the field is supposed to have

Some of these issues could be solved on the database level, but usually it will require your software to be updated/rewritten since database structures can be significantly changed.

How to fix these issues:

• Re-design your database using Database Design Best Practices
• Make sure your database is at least in 4NF (fourth normal form)
• Make sure all fields in tables have proper type according to the stored data
• Use transactions where necessary to keep data integrity
• Add validation layer to your application, so only correct data will be allowed to save
• Add unique indexes on key fields to avoid duplicates
  • This also requires you to add some logic on application level to help users easily search and select already existing records
• If new data needs to be gathered and stored - add new fields/tables, don't re-use existing fields

Performance Issues

Good database performance is required to successfully serve client needs.

What could cause bad database speed:

• Hardware (old processor, small memory, ancient HDDs, slow network)
• Inadequate DB server configuration
• Poorly designed database structures
• Wrong type of database chosen (i.e. MS Access instead of SQL Server, Relational vs NoSQL)
• Absence of required indexes
• Business logic on database level (i.e. triggers)
• Not using optimal SQL queries
• Too high of a load

Common ways to optimize database:

• Upgrade your hardware, seriously - it's the quickest/cheapest way if you can't change the application or database
  • Replace HDD with SSD, add more memory, get a new server, or migrate to the cloud
• Check DB server configuration
  • Ensure your server is a dedicated database sever and nothing else is eating the server's resources
  • For example, MySQL could be fine tuned to perform much better than the default configuration (or switch to InnoDB from MyISAM if you are still using it)
  • In case of Access - move your data to SQL Server instead of storing it in .mdb/.accdb
• If possible - review your database design, ensure it's normalized, check best practices
• Add indexes, but make sure you are adding the right ones, extract top several slow queries and test them:
  • For SQL Server - use Management Studio ability to "Display Estimated Execution Plan"
  • For MySQL - use "EXPLAN query" command to analyze what fields should be added to the index
• If you are able to modify your application queries - take a closer look at them, test and rewrite, avoid full table scans, complex joins, and transactions (lock waits kill performance)
• Review if there is "hidden" business logic on database level - in triggers or stored procedures. Business logic hidden in triggers could have a negative impact on performance. Move such logic to application level.
• Offload your database - move non-relational data to other storage. For example, don't save images/files right into database.
  • Some types of data are better to be stored in NoSQL databases (key/value pairs, documents, graphs, column-oriented)
• In cases of really high load you may be getting everything you can from your DB server and optimized queries, however it's still not enough. Next step would be to split your database into several servers, this will also require changes to how your application works with your database:
  • If your load is mostly reading data, not modifications - just add "read replicas", so clients will write on one master server, but read from one of the many, this will help spread load
  • If your data can be logically split by some entity (for example, by user) - put different users on different servers (make "shards,") so one server will have just a small number of users with less load
  • De-normalize your database, put different entities to different servers
  • Use a distributed database instead of a "usual" one

Database Security

Data security is very important since nobody wants their data leaked into the wrong hands.

Common security issues:

• Database server is open to the public
• Connection or data is not encrypted
• Too many people have a common login/password which grants full access

How to secure your database:

• Limit physical access to the server.
• Encrypt disks.
• Ensure backups are stored in a secure place.
• Limit the number of users who have full access (usually only Database Administrators.)
  • Each user should have their own login/password.
• Avoid remote access directly to DB server, DB server should only be accessible from internal network, only users can access database via applications.
  • If remote connections are really necessary - use VPN or limit by particular IP addresses.
  • Turn on encryption on database connections.
• In the applications that work with the database - have different access levels for different users, i.e. don't allow everyone to see everything.
• Encrypt sensitive fields with application-level keys.
  • For example, in a medical database, client names usually get encrypted, so even if the database is leaked, personal data cannot be read.
• Establish notifications if something goes wrong or there is suspicious activity

As you can see database issues can be complex to solve. If you need to fix or optimize your database - Contact Us, we are here to help!