Better management of multiple databases on the server

Source: Internet
Author: User

Intermediary transaction http://www.aliyun.com/zixun/aggregation/6858.html ">seo diagnose Taobao guest cloud host technology Hall

Each of these instances, or a single database per server Setup, can make the database advisor more convenient. However, doing so will soon increase the cost of your database solution because you will need to purchase a new server or a new SQL Server license for each of the databases that you expect to host.

In order to deal with this expensive setting, people typically host multiple databases (and many applications) on this server or instance. While this reduces the cost of hosting all of these databases, this increases the complexity of the consultants ' systems because you now have to organize multiple service-level agreements and maintenance windows.

When you decide to host multiple databases on the same server, the first thing to consider is whether these systems have complementary maintenance windows. If the system does not slow down or go offline at night, and the system cannot slow down or go offline during the day, these systems are not suitable for sharing the server, because you don't have a valid maintenance window when you need a patch for the system or other reasons to get the system offline.

The next decision you need to investigate is the service level agreement for these systems. The system that requires 99% boot time can be arranged in a common, because you may be able to build a more powerful environment for these systems (perhaps cluster solutions) than the non significant task system. This can save you extra money because you don't need to buy any high-end systems today. Systems with higher service-level protocols may also have the same maintenance-time window. As a result, these systems are complementary in one turn.

Take on the amount of work

The number one difficulty in maintaining a SQL Server hosting multiple databases is timing.

Of course, the most important question to consider when putting multiple databases on this SQL Server is whether there is enough CPU and memory resources to defragment these guest programs to add to this database server. If a single server does not provide the CPU and memory resources that are required, then it is not a good choice to concentrate these databases on that server.

How do you keep these systems healthy and running at peak hours after you've gone through all the decision-making processes and put them on the same server? Like any other database solution, you still need to organize your own backups, index defragmentation and rebuilding, and consume patches for your operating system and SQL Server.

The number one difficulty in organizing the maintenance of SQL Servers hosting multiple databases is timing. You need to ensure that your maintenance tasks can be completed within the Maintenance window of the entire database program hosted by this SQL Server. Maintaining jobs outside of any database Maintenance window will cause the database to run slowly because hard disk and CPU resources are now being consumed by maintenance activities, rather than collating normal database queries.

Re-indexing Jobs

This technique has proven to be useful in defragmenting your index more frequently than normal running indexing instructions. The instructions for defragmenting are more beneficial than indexing instructions. First, an index defragmentation instruction is an online operation, and rebuilding an index is an offline job (unless you run the SQL Server 2005-pack or newer version). Second, if you frequently run index defragmentation instructions, you will have less time to work on each run.

For example, you measure the index fragment once a week, and it shows that the fragment is 70%. This allows you to run this index rebuild command to clear these indexes.

But what happens when you measure the index fragment again the next day? It could be about 8% to 10% fragments. So, if you run this index defragmentation every day instead of running the index defragmentation instructions every week, there will be very little work to do every day, and the job will be completed more quickly, possibly in the window of every day's maintenance.

Even if you can't take the system offline at the moment, because the defragmentation operation is an online operation, the system will continue to function at the moment of defragmentation, only slightly slower than normal information.

Database backup

Backup is another key issue that solves the time requirement of hosting multiple databases on a single server.

Each database has its own backup requirements. Backing up a database may be the most complex task that can be implemented while the SQL Server is running. This is not because this backup requirement takes up a large amount of CPU and memory resources (this task consumes a very low resource, unless you compress the database at the time of backup), but because backing up this mega database requires a large amount of hard disk resources.

When a public backup is carried out, all databases must be read from the hard disk. If your hard drive system is very busy, this backup can cause a significant performance drop. The best solution to this backup is to choose the right time. You can also look for Third-party tools that allow compression of database backups while they are backed up. Because this will increase the amount of CPU work on the SQL Server, it usually takes only a few moments to complete the backup because there is very little data to write to the backup device.

Do picture station contact me I'll show you the management of www.55mm.cc background server data

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.