SQL questions and answers-database mobility, performance optimization, backup, and mirroring

New array mobile day

Q: our current RAID will soon be filled up, so we need to move some SQL Server 2005 databases to other locations. The new array is ready and I have been preparing for the mobile database. I just found that one of the databases is the transaction replication Publishing Server. I know that this means that I cannot move the database. What should I do?

A: There is good news for you-only SQL Server 2000 and earlier versions have the following limitations: restrict moving the publishing database without re-initializing transaction replication or directly changing various system tables.

For SQL Server 2005 and SQL Server 2008, There is a recorded process, you can follow it to move the database, without performing any operations on transaction replication, however, the database must be connected to the same SQL Server instance. The database file cannot be moved when it is online. The process is as follows:

First, use the following code to take the database offline. If a user connects to the database, the user needs to be disconnected before the process can be successful:

 
 

  
  
ALTER DATABASE MyDatabaseName SET OFFLINE; 
 
 

Next, copy the data file to the new location. Use replication instead of moving. You can perform quick rollback in case of any errors. Otherwise, you must perform restoration ). Then, use the following code to notify SQL Server of the new location of each file

 
 

  
  
ALTER DATABASE MyDatabaseName   
  
  
MODIFY FILE  
  
  
   (NAME = N'LogicalFileName',  
  
  
   FILENAME = N'pathname\filename'); 
 
 

After all files are physically copied and the file location in SQL Server is updated, run the following code to restore the database to online:

 
 

  
  
ALTER DATABASE MyDatabaseName SET ONLINE; 
 
 

Disable page lock

Q: I have questions about some performance optimization concepts. I read several times to prevent page lock issues. I don't know what "page" or "locking" means, or why page locking is even a problem. Can you explain all these questions?

A: All data in the SQL Server database is stored in data files. Internally, these files are organized into a data block sequence of 8 KB, which is called a page. Pages are the basic storage and I/O units that SQL Server can manage. Pages are usually stored in data files on the disk. Before processing any query, the SQL Server cache is called a buffer pool.

SQL Server uses various pages to store different types of relational data, such as rows in tables, rows in non-clustered indexes, or text/LOB data ). There are also some pages that store the internal data structure required for the SQL Server organization and access pages that store relational data.

Locking is a lightweight internal mechanism that SQL Server uses to synchronize access to a page in the cache. You need to pay attention to two types of page lock-General page lock and page I/O lock. If the SQL Server thread must wait to obtain one of the locks, it indicates a performance problem.

When SQL Server is waiting for a certain part of the data file to be read from the disk, it may cause page I/O lock wait. If the page I/O lock lasts for a long time, it usually indicates that the underlying disk subsystem has a performance problem, that is, the subsystem is overloaded ).

When multiple threads in SQL Server attempt to access the same 8 KB data file page in the memory, there is a contention for access to this page, which may lead to page lock wait. The most common case involves a large number of temporary small objects in the tempdb database.

A more in-depth description of how to monitor and reduce page lock wait does not fall within the scope of this topic, but you can find more information in the following documents:

The "SQL Server Wait Statistics object" section in SQL Server 2008 books online describes how to use system monitor to monitor Wait Statistics.

The "sys. dm_ OS _wait_stats" section in SQL Server 2008 books online lists common SQL Server wait types and their meanings, and describes how to monitor wait statistics from SQL Server.

The White Paper "Troubleshooting of performance problems in SQL Server 2008" provides various troubleshooting queries and technologies, including waiting for statistical data.

Querying database snapshots

Q: I just discovered a database snapshot. Now I want to use them as an alternative to full recovery mode and log backup. I will create a snapshot every hour, so that when an error occurs, I can pull back the damaged data. This seems to be a much easier and faster restoration method. Do you think this change will cause any problems?

A: there will be problems. Database snapshots are not a practical or feasible alternative to comprehensive disaster recovery policies. In terms of disaster recovery, database snapshots do not have the same features as transaction log backup. A database snapshot does not contain copies of all pages in the database. It only contains copies of pages that have been changed since the first database creation. This means that, if the database is damaged, the database snapshots without the underlying database will be of no use. It is only a set of different pages in the database and cannot be used for restoration.

You can use a database snapshot to pull back data that is accidentally deleted from the database, provided that the database is still available. For example, if the table deleted from the database still exists in the snapshot, you can use the snapshot to recreate the table.

This is because of potential performance problems. Creating too many database snapshots is not a good idea. Before you can switch the database page, see the answer in the "Close page lock" section). You must first copy the page to all existing database snapshots that do not contain the page version. As more database snapshots are created, more page copies are generated, resulting in performance degradation.

Another reason not to create too many database snapshots is that each database snapshot contains a copy before the database page changes. Each copy will increase as the changes in the database increase. This may cause disk space and performance problems.

Database snapshots are not designed to replace frequent log backups. You can go to the Database Snapshot Performance Considerations Under I/O-Intensive Workloads White Paper to learn more about the Performance impact of Database snapshots.

In addition, if you want to use the full recovery mode and transaction log backup, it is obvious that you are interested in restoring to a disaster point and/or a point in time at most. For instructions on restoring to a disaster point and a time point, see my articles "learn about SQL Server backup" and "SQL Server: use Backup for disaster recovery ".)

Images

Q: I was asked to set up a database image for the database, but I am worried that the database image will not help solve our problem. Our SAN has some damage problems, so we plan to use database images to prevent us from being damaged. Will the damaged image be automatically sent to the image? How can database images help us solve this problem?

A: This is a problem that will cause a lot of confusion. Any technology that provides redundant database copies seems to be vulnerable to damages from the principal to the mirrored database to use the database image term. But this does not actually happen.

The key to the problem lies in understanding how to maintain the image database. If the underlying synchronization mechanism copies the full database page from the subject to the image database, the corruption will certainly be transmitted to the image. The damaged pages in the subject are then placed in the image.

However, database images avoid this situation because they do not copy the database pages in one database to another. Database mirroring completes the process of copying transaction logs from the primary database to the image. Transaction log records indicate physical changes made to the database page, which do not contain the actual page itself. For a complete description of transaction logging, logging, and recovery, see my article, "Learn about the logging and recovery functions in SQL Server", published on September 10, February 2009 .")

Even if the database page is damaged by the underlying I/O sub-system of the primary database, the damage cannot be directly transmitted to the image database. The worst case may be that if SQL Server does not detect page corruption because page checksum is not enabled), corrupt column values will be used to calculate the values stored in the database. The generated incorrect results will be propagated to the image database, resulting in Secondary damages. As mentioned above, if page checksum is enabled, when the page is read from the disk, this damage will still not be detected and no secondary damage will occur.

This behavior also explains why the consistency check on the main database does not generate any information about the consistency status of the image database, and vice versa. They are two different databases that are synchronized by transmitting a description of physical changes to the database rather than the actual database page.

Original article address

Source: Microsoft TechNet Chinese site