Feature Request - Inline integrity check
EdVassie
Posts: 15
Description
Include the processing normally done by a DBCC CHECKDB inline with running a backup.
Most of the time required to do a CHECKDB is the time needed to read the DB files. Likewise most of the time needed to do a backup is the time needed to read the DB files.
The backup should have an option to read the DB files once and do perform consistancy checking before writing the backup.
Justification:
Finding a window to do a CHECKDB and a backup is not always easy. Being able to run one process that did both functions would save a lot of time.
Notes:
This type of functionality has already been released by BMC and CA for their mainframe DB2 products, so there may be patent issues to beware of.
Experience with the DB2 products is that doing an inline consistance check only added about 5% to the backup execution time. Most DB2 sites now use this functionality for all their backups due to the time saved.
Include the processing normally done by a DBCC CHECKDB inline with running a backup.
Most of the time required to do a CHECKDB is the time needed to read the DB files. Likewise most of the time needed to do a backup is the time needed to read the DB files.
The backup should have an option to read the DB files once and do perform consistancy checking before writing the backup.
Justification:
Finding a window to do a CHECKDB and a backup is not always easy. Being able to run one process that did both functions would save a lot of time.
Notes:
This type of functionality has already been released by BMC and CA for their mainframe DB2 products, so there may be patent issues to beware of.
Experience with the DB2 products is that doing an inline consistance check only added about 5% to the backup execution time. Most DB2 sites now use this functionality for all their backups due to the time saved.
Comments
http://connect.microsoft.com/SQLServer/Feedback
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
When BMC and CA did this for DB2, they did it on their own. There was no vendor API to help.
My understanding from when I worked at BMC is they worked out what a valid page should look like, and highlighted any inconsistencies. Different pages (data, index, space map, etc) each have their own formats. Where relevant, pointers to next/previous pages (eg for spacemaps, etc) were checked to ensure the target page was of the correct type. CA must have done something similar for their product but which did not violate the BMC patents, as these 2 companies were and still are very serious rivals.
The BMC product had various levels of integrity check the DBA could ask it to do. The most expensive checked that all index RIDS pointed to the right data, and that all off-page pointers went to whatever was valid.
Most installations settled on the option do do page consistency and spacemap (etc) chain checks. This was the option that added only about 5% to elapsed time. The little feedback I had about the more complex options was they were shelfware.
After about 10 years, IBM as DB2 vendor also thought that inline consistency checks were a good idea and added it to their own backup product.
The moral of all this is Red-Gate could add inline consistency checks to SQL Backup without any additional help from Microsoft. However, I have posted this request on Connect so vote for it if you want: https://connect.microsoft.com/SQLServer ... kID=340720