SQL Server DBA thirty-one Q & A (question 7th)

A few days ago I saw an article titled "SQL Server DBA thirty questions", which is very good and pertinent. These questions can be answered only after some operation experience is required. Many of these questions can be found through books online. I am also very interested in some questions because I have also handled similar problems, so I was very impressed. Now I have written my answers, hoping to help others. If there is a better solution, of course I would be happy to learn.

Question:7. In the online system, a table has 50 million records. Now you need to import 20 million records to a table on the other server. After importing the 20 million records, you need to delete the data.Besides, what are your advantages and disadvantages; 

My answer: (first, my environment is sqlserver 2000, and assume that it has a primary key ID)

 

) Step 2: first create a table, t_update_log, record the primary key ID of the updated record, record the modification and deletion records. The specific role of this step is shown in the following steps;

 

) Step 2: Export 2nd of the data to another table in another database. I name it t_2000w. I use DTS for processing and the efficiency is very good. If there is no large field, 20 million can be exported in about 3 hours. If there are large fields such as varchar, text, and ntext, it takes a long time, the specific time depends on the data size.

 

) Step 2: delete the 3rd exported from the current database. I name the current database t_5000w. In this way, create a table with the same structure as the source data table and name it t_3000w, import the remaining 30 million data records, use sp_rename to modify the two tables, change t_5000w to t_5000w_bak, and change t_3000w to t_5000w (that is, the source table). This is probably the case. It's easy.

 

Don't worry. The first step of preparation is useless, and there are still many details.

Q1: how to ensure data accuracy? What if someone updates the data while importing it?

Q2: minimize the impact on users. If it takes a long time to modify the table name in sp_rename and insert is required for data, what should I do?

My solutions:

A1: The t_update_log table in step 1 is used, and updated records are re-imported to the new table t_2000w. The deleted ID is found in the t_update_log table and deleted from t_2000w;

A2: Modify the insert operation interface for table t_5000w. You can create a new table with the same structure as t_5000w and name it t_6000w. If there is an auto-increment ID, set the current value of the auto-increment ID to 6000 W, and then insert the newly added data to t_6000w. If all your database operations are performed in a unified manner through the stored procedure, you are lucky, you only need to modify the stored procedure. NetProgramYou can even upgrade without stopping the service. Here, the superiority of the stored procedure is fully reflected. If you do not use the stored procedure but SQL statements, and there are many calls, we recommend that you suspend the service during the upgrade.

 

Of course, all the above operations need to be performed again in the test environment, record the time required for each part, and restore to the previous state in time when the failure is completed. Of course, we recommend that you suspend the service during the upgrade, because most people can suspend the service, but cannot accept data loss.