Maintaining Server Protection

Question

Maintaining Server Protection

Karim Hemani

SSCrazy

Points: 2063
More actions
December 26, 2005 at 7:22 am

#152680

Hi,
There is one statement made in Books Online that I need help in understanding.
A mission-critical online transaction processing (OLTP) system requires full recoverability but periodically undergoes bulk load and indexing operations. The recovery model for the database can be changed to Bulk-Logged for the duration of the load and indexing operations and then returned to Full Recovery. This increases performance and reduces the required log space while maintaining server protection.
How does switching the recovery mode of the database to bulk-logged help in maintaining server protection. Are we talking about SQL Server software here? How does such an operation help in protecting it? I would highly appreciate if somebody clarified.
Karim

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 1

If you read the entry, it allow recovery while increasing performance during bulk loads. There are some limitations in how the data is recovered, but assuming you do not fail during the bulk load operation, you have full recovery of all data. You also still have transactional recovery of non-bulk loaded data.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

It is my understanding ( and interpretation by the most of books) that

1. You have Full Recovery = Transaction Logging before the bulk load (BCP and Bulk Insert). So you can recover your data up to the this point

2. Bulk Insert and re-indexing are Reproducible Operations! If something fails then you can recover database to the point just before the bulk insert (see # 1) and then just repeat the load from whatever source you are loading.

3. After the Bulk Insert you need to: Change Recovery Model back to Full and assure the full database backup.

4. You are back to the Full recovery model.

What I don't understand by this statement is why do I have to switch the model at all? Yes, it is the latest Microsoft recommendation, see

http://msdn2.microsoft.com/en-us/library/ms191244.aspx

Bulk-Logged assumes that it does the full logging of everything except for the bulk operations, so theoretically I just need to assure a proper backup schedule, see

http://msdn2.microsoft.com/ms190692.aspx

But if switching models anyway, I usually recommend the following for my developers for the databases that are not 24/7: backup database and transaction log before the bulk operations, put the database in restricted access and set Simple recovery model, do the import, switch the model back to Full, make a full backup, make sure you have Transaction Log backups on schedule

Regards,Yelena Varsha

Karim Hemani SSCrazy Points: 2063 More actions · Answer 3

Thanks a lot, Yelena. Your point does make sense. Basically, if I am getting it right, you are saying that one should let the database stay in bulk-logged recovery mode and perform a full database backup after completing the bulk operation. This way, you can recover to the point the bulk finished. Since you would be making transaction log backups from that point (after having done a full database backup, of course), you don't need to be in full recovery mode at all. Makes a lot of sense. Thanks again.

Karim

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Karim,

There are some other restrictions on what is or what is not logged with Bulked-Logged model. I would read the article on Bulk-logged a couple of times and decide if i would want to keep it this way. Because "theoretically" sometimes is very different of "practically". What I would be afraid is that you may have user transactions for the time period you have bulk operations. Say, you bulk-load into Table1 and a user updates Table2 through the application. Then his transaction will not be logged if something happened during the load operation. By default for Bulk Insert for example the lock will be what is specified for the table:a table lock will allow another user to update another table. See

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

BULK INSERT article:

TABLOCK

Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance.

Also note:

Remarks

The BULK INSERT statement can be executed within a user-defined transaction. Rolling back a user-defined transaction that uses a BULK INSERT statement and BATCHSIZE clause to load data into a table or view using multiple batches rolls back all batches sent to SQL Server.

Regards,Yelena Varsha