Managing Multiple Databases on a Single Server

Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More .

Setting up a single database per instance or server makes database management a piece of cake. However it can very quickly drive up the cost of your database solutions as you now have to purchase a new server and new SQL Server license for each database you wish to host.

In order to combat this expensive setup, people will typically host multiple databases (and therefore multiple applications) on a single server or instance. While this will begin to ease the costs of hosting all these various databases, it increases the complexity of managing these systems as you now have multiple Service Level Agreements (SLAs) and maintenance windows to work with.

When you decide to host several databases on the same server the first thing to look at is whether these systems have complementary maintenance windows. If one system cannot be slow or offline in the middle of the night, and another cannot be slow or offline during the middle of the day, these may not be the best systems to share a server as you will have effectively no maintenance window to work with in the event that you need to patch the system or take the system offline for other reasons.

The next deciding factor you need to look at is the SLAs for the systems. Systems that require 99% uptime can reside together since you will likely build a much more robust environment for these (a clustered solution perhaps) than you would for systems that are not mission critical. This can save you some additional costs as you now have fewer high-end systems to purchase. The systems with the higher service level agreements also probably have similar maintenance windows, so these systems will probably be complementary to begin with.

Bearing the Load
Reindex Jobs
Database Backups
Conclusion
RELATED NEWS AND ANALYSIS

Bearing the Load

Of course the most important thing to consider when combining databases onto a single SQL Server is if there are enough CPU and memory resources to handle the load that the clients will be placing on the database server. If a single server cannot provide the required CPU and memory resources then combining the databases onto that server would not be a good choice.

After you have gone through this entire decision process and have put the databases onto the same server, how do we keep the systems healthy and running at their peak? Like any other database solution you will still need to handle your backups, index defrags and rebuilds, and patching of both the operating system and the SQL Server.

The biggest trick to handling the maintenance on SQL Servers with several databases on them is timing. You need to make sure that your maintenance tasks can all be performed within the scheduled window of all your databases being hosted on the SQL Server. Running the maintenance tasks outside of the maintenance window for any of the databases will cause all the databases to run slower as disk and CPU resources are now being taken up with maintenance activities instead of handling normal database queries.

Reindex Jobs

One technique that has proven useful is to defrag your indexes more often than you would normally run your reindex commands. The defrag commands give you a few advantages over reindex commands. First the index defrag commands are an online operation, while rebuilding your indexes is an offline operation (unless you are running SQL Server 2005 Enterprise Edition or newer). And second, if you run index defrag operations more often there will be less work do to each time the command is run.

For example let’s say that each week you check your index fragmentation and it shows 70% fragmentation. So you run an index rebuild to clean up the indexes.

However what happens if you check the index fragmentation the next day? It will probably be about 8-10% fragmentation. So if you run the defrag job daily instead of running the reindex job weekly there will be much less work to do each day and the job will complete much faster, possibly within the daily maintenance window.

And even if you can’t take the system offline during that window, as a defrag operation is an online operation the system will continue to function just with a slower response rate than normal while the defrag operation is running.

Database Backups

Backups are another key issue to address when dealing with multiple databases on a single server.

Each database may have its own backup requirements. Backing up databases is probably the most brutal task that can be performed on a live SQL Server. Not because of the amount of CPU power or RAM it takes (which is typically very low unless you are doing compression on the database while it is being backed up) but because of the massive amount of disk resources which are required to backup up a large database.

When performing a full backup the entire database must be read from the disk. If you have a very busy disk system, the backup could bring performance to its knees. The best solution for this is proper timing. You may also need to look to a third party tool that allows for database backup compression while the backup is running. As this will increase the CPU load on the SQL Server, it will usually greatly decrease the time it takes to complete the backup as much less data needs to be written to the backup device.

Conclusion

These are only a couple of the techniques which can be used to help maintain the database server while running several databases on the single system. Hopefully you will find them useful while working on your database consolidation projects.

This article was first published on EnterpriseITPlanet.com.