Implement effective management of multiple databases on one server

Setting a single database for each instance or server makes database management more convenient. However, this will soon increase the cost of your database solution because you need to purchase a new server or a new SQL server license for each database you want to host.

To cope with this expensive setting, people generally host multiple databases (that is, multiple applications) on one server or instance ). Although this will reduce the cost of hosting all these databases, it increases the complexity of managing these systems as you now have to handle multiple service-level protocols and maintenance windows.

When you decide to host multiple databases on the same server, the first thing you need to consider is whether these systems have complementary maintenance windows. If one system cannot slow down or go offline at night, and the other system cannot slow down or go offline during the day, these systems are not suitable for sharing one server, you do not have an effective maintenance time window when you need to patch the system or make the system offline for other reasons.

The next deciding factor you need to examine is the service-level protocols of these systems. Systems that require 99% boot time can be arranged together, because you may create a more powerful environment for these systems (maybe cluster solutions) than non-important task systems. This saves you extra costs because you do not need to purchase any high-end systems. Systems with higher service-level protocols may have the same maintenance time window. Therefore, these systems complement each other at the beginning.

Workload

It is time to maintain the SQL server hosting multiple databases.

Of course, the most important question to consider when multiple databases are concentrated on one SQL Server is, whether there are enough CPU and memory resources to process the workload that these customer programs add to this database server. If a single server cannot provide the required CPU and memory resources, it is not a good choice to concentrate these databases on that server.

How do you keep these systems healthy and still run during peak hours after you complete the decision-making process and put these databases on the same server? Like any other database solution, you still need to process your own backup, index fragmentation and reconstruction, and use patches for operating systems and SQL servers.

It is time to maintain the SQL server hosting multiple databases. You need to ensure that your maintenance tasks can be completed within the maintenance time window of all the database plans managed by the SQL server. The maintenance work outside the maintenance time window of any database will cause the database to run slowly, because the hard disk and CPU resources are currently occupied by maintenance activities, rather than processing normal database queries.

Reindexing

One technique that has proved useful is to fragment your index more frequently than to run the re-Index Command normally. Fragmented commands have more benefits than new index commands. First, the index fragmentation command is an online operation, and re-indexing is an offline operation (unless you run SQL Server 2005 Enterprise Edition or an updated version ). Second, if you frequently run the index fragmentation command, the workload is relatively small each time you run this command.

For example, if you check index fragmentation once every week, it shows that the fragmentation is 70%. In this way, you can run an index reconstruction command to clear these indexes.

However, what happens when you check index fragmentation the next day? It may be about 8% to 10% fragments. Therefore, if you run an index fragmentation command every day instead of an index fragmentation command every week, there is very little work to be done every day, and this work can be completed more quickly, it may be completed within the daily maintenance time window.

Even if you cannot take the system offline within the time window, because the fragment operation is an online operation, the system will continue to play a role during the time of the fragment operation, the response speed is a little slower than normal.

Database Backup

Backup is another key issue that needs to be solved when multiple databases are hosted on one server.

Each database has its own backup requirements. Backing up a database may be the most complex task that can be executed on the SQL server. This is not because this backup requires a lot of CPU and memory resources (the resources occupied by this task are usually very low, unless you compress the database during Backup ), instead, backing up a large database requires a lot of hard disk resources.

During full backup, the entire database must be read from the hard disk. If your hard drive system is very busy, this backup will cause serious performance degradation. The best solution for this backup is to choose the right time. You can also find a third-party tool that allows Database Backup compression while backing up. Because this will increase the CPU workload on the SQL server, it usually takes only a small amount of time to complete the backup, because there is little data to be written into the backup device.

Conclusion

Only a few technologies can help maintain database servers when running multiple databases on one server. When you consider database integration projects, we hope you can find these tools useful.