SQL questions and answers: Backup and settings

XXXL transaction log

Q: Our products use SQL Server to store data. We will release new product versions from time to time, including upgrade scripts for running databases. As we test our latest upgrade script in a typical test database, the size of the transaction log file has increased to more than 40 GB. We want to prevent log files from growing so large. Which solution can we choose? For the purpose of disaster recovery, we need to continue using the full recovery model.

A: First of all, I am glad to know that you are using typical customer data for testing. I have repeatedly discovered that hierarchical application vendors use small datasets to test such scripts, which are then put into release and available to customers, and customers encounter various problems in the production process. If you are a user, I will answer your questions. Then you can apply my answers based on the customer's actual situation.

You said you need to continue using the full recovery mode. This means that you have backed up transaction logs, and you have not encountered any common problems such as transaction log growth out of control. This is good because transaction log backup is the only operation that can clear transaction logs after the transaction is committed. For background information about this issue, see references .)

Therefore, the frequency of transaction log backup determines the speed at which transaction logs are cleared to prevent them from increasing. For example, if your regular backup job executes a transaction log backup every 30 minutes, the transaction log file must be sufficient to save the maximum transaction log data volume generated within 30 minutes. Otherwise, the data volume will increase.

If your upgrade script runs for 60 minutes and generates 20 GB transaction logs every 30 minutes, the transaction log file size should be 20 GB. The file may be too large, so you need to increase the frequency of transaction log backup when running the upgrade script. In this way, transaction logs can be cleared more frequently to prevent excessive growth. We have encountered similar problems in our customer offices. As a result, they need to execute a transaction log backup every minute within several hours when running similar scripts in large databases.

One thing to remember is that these "extra" transaction log backups constitute part of the log backup chain and are necessary for disaster recovery. Make sure that their names are meaningful and not deleted.

In addition, consider the following: as part of the upgrade process you designed, what is the largest single transaction? It may be too simple to clear transaction logs only when logs are recorded from committed transactions. For more information, see the previous article ). This means that long-running transactions cannot clear logs, even if the transaction log backup does not back up the generated transaction logs.

If your upgrade script contains a transaction that requires 15 GB of log space, the transaction log file requires at least 15 GB to save the entire transaction before committing the transaction. In this case, the transaction log will not be cleared no matter how often you back up the transaction log. In this case, the only solution is to split large transactions into smaller ones if possible.

Remember that the transaction log size required to run the upgrade script depends on the transaction log backup frequency and the maximum size of a single transaction you have created.

Configuration difficulties

Q: We are configuring some new direct connection storage for one of our database servers. We want to ensure that we understand all the options and configure them correctly. Can you explain the different configuration settings for SQL Server?

A: policy settings and configuration options are required for storage configuration. Therefore, I prefer to be responsible by a dedicated storage administrator. The SQL Server administrator must pay attention to some options to ensure correct settings.

The first is the underlying RAID level. When performance and redundancy problems are involved, the trade-offs of various RAID levels are different. For example, the cheapest RAID configuration that can still provide certain redundancy is RAID-5, but this configuration can only be used to handle a single drive failure unless RAID-6 is used or hot backup is configured ), based on the number of drives in the array, it sometimes compromises the performance of a large write workload.

RAID-10 provides the best redundancy, but is more expensive. The total capacity of the array is up to half of the total capacity of the drive. For more information about RAID levels, see appendix A of the physical database storage design in the TechNet White Paper.

Other major factors to consider are the RAID strip size, NTFS allocation unit size, and cluster size), and disk partition alignment. If the settings are incorrect, all of the above factors will cause a significant reduction in performance. The most important factor is the disk partition alignment of the disk volumes created using Windows Server 2003. The default alignment is 31.5KB, but this does not match the size of commonly used RAID strip of 64KB or the size of multiple strip. Therefore, each I/O actually needs to read or write two RAID records to satisfy IO requirements. Obviously, this will cause a sharp reduction in performance.

By default, Windows Server 2008 adopts the 1 MB alignment mode. Alignment of any volumes created and upgraded to managed by Windows Server 2003 on Windows Server 2008 does not change, so they may still be affected. To solve this problem, you must reformat the volume. As this can improve performance, it is worthwhile.

The detailed discussion of these issues is obviously beyond the scope of the topic of this column, however, you can read my blog post to see if your disk partition offset, RAID strip size, and NTFS allocation unit settings are correct ?, To learn more details, including links to related posts ).

When configuring any new storage, it is best to perform stress testing and performance testing before the application production load starts. Stress Testing allows you to eliminate any configuration problems that may cause downtime or data loss. Performance testing helps you verify whether new storage provides the I/O capabilities required by your workload. Microsoft provides free tools to help with these operations. For more information, see the White Paper Pre-Deployment I/O best practices.

Images

Q: I am confused about the nature of the witness server when setting the database image. How powerful does the witness server need? Does it depend on the number of databases that perform failover? In which data center will the witness server be placed? I want to ensure that the image database has the highest availability.

A: The Role of the witness server is the most common misunderstanding in any database image system. The only purpose of the server witness in the synchronous database image configuration is to help promote automatic failover when the main server becomes unavailable.

The principal server continuously sends Transaction log records to the backup server rather than the witness server. As part of the automatic fault detection mechanism, the main server, the backup server, and the witness Server ping each other every second. If, for any reason, the backup storage determines that it cannot communicate with the master server, the backup storage cannot start automatic failover unless the witness server agrees that it cannot communicate with the master server. If the two servers reach an agreement, arbitration will be formed and automatic failover will be initiated by the backup storage. If the witness server does not exist, arbitration cannot be formed and automatic failover cannot be started.

Therefore, the sole purpose of the witness server is to assist in arbitration. It does not start failover or assume any role in the hosted image database. Generally, such arbitration exists between the master server and the backup server.

Because the witness server does not perform any of the above operations, it does not need to be very powerful. It can host any version of SQL Server, including free SQL Server Express Edition. For a specific instance of SQL Server that can serve as the witness Server, there is no limit on the number of database image sessions.

It is best to place the witness server in a data center different from the primary server or backup storage. However, most companies do not have three data centers, so the question is whether to place the witness server and the backup server together with the master server.

If only two data centers are available, always place the witness server and the master server together. This is related to the formation of arbitration. If the witness server and the mirror server are placed together, when the master server loses the network connection, the witness server and the mirror server will form arbitration and start failover by the mirror server.

In this case, the subject server may have no problems. If no arbitration is formed, it will take the subject database offline. It assumes that the image performs failover in this case. To prevent such problems, the subject server and the witness server should be placed together, so that the subject server can be maintained and the witness server can be arbitrated in the case of a network failure. So that the main database remains available.

The witness server is completely optional, but automatic failover is impossible if the witness server does not exist. Therefore, the maximum availability of the image database cannot be guaranteed. For other methods, database image operations are the same. If the witness server is configured but is unavailable for some reason, the image function will not be affected except for the automatic failover function.

You can also configure two witness servers for each database image session. The only way to add higher redundancy to the witness Server role is to host the witness SQL Server instance in the Failover group. For more information about database image configuration, see database images in the TechNet White Paper SQL Server 2005.

Original article address

View more articles

Edit recommendations]