Administering "10+TB's of monster databases" is where it pains the most

BACK IT ALL UP :

Performing a daily complete backup for databases that are 10TB in size can pose significant challenges, particularly for an OLTP database. Even with improved hardware, the backup process would typically take approximately five to seven hours. Hence, it is crucial to establish an effective backup strategy that prioritizes both availability and maintenance.

Considering the time and resource costs involved, it would be prudent to implement a weekly full backup regimen supplemented with daily differential backups and 15mins/30 mins or hourly transaction backups.

Opting for third-party tools to execute the database backup is a preferable choice. These tools not only assist in reducing the backup duration but also compress the backup files, thereby reducing their size."

ONE EYE ALWAYS ON THE INDEXES:

Performing routine index maintenance tasks on a significantly large database differs from doing so on a database of regular size. Rebuilding indexes on tables with extensive indexes and substantial data can be excessively time-consuming. Additionally, this process can lead to server blocking, negatively impacting the performance of other applications.

To effectively maintain indexes on such a massive database, the preferable approach is to reorganize them. Rebuilding indexes should only be considered when there is index corruption or an explicit need to rebuild a specific large index!(Warning here!)

When creating indexes for such a sizable database, it is important to consider using the 'WITH SORT_IN_TEMPDB' option in the statement. This option forces the index building process to occur in the tempdb, and once completed, it is then written to its designated filegroup. This particular option is often overlooked, despite its potential to significantly reduce index building times.

An additional benefit of reorganizing indexes is the ability to pause the operation if it overlaps with business hours. This pause does not result in the index going into a 'ROLLBACK' state.

DATABASE CONSOLE COMMANDS (aka DBCC)

The DBCC command serves the purpose of verifying the consistency and integrity of a database. Its execution ensures the databases are in a healthy state, and in case any issues or corruptions are detected, this command assists in identifying and resolving them.

Executing the DBCC command consumes significant system resources, putting constraints on both memory and disk usage. Running the DBCC command on a large-scale database entails risks, as it may not complete within the allocated time frame. If an attempt is made to terminate the execution process, it enters a 'ROLLBACK' state. This not only prolongs the process but also poses a threat to the overall consistency of the database. Consequently, running this command on a substantial OLTP database is not a practical option!(Keep a note of this)

The duration of the command's execution relies heavily on the available memory and the type of RAID configuration used for hosting the tempdb database.

Alternatively, other options can be utilized, such as executing the DBCC CHECKTABLE command against individual tables or groups of tables in a rotational manner. Another option is to employ DBCC CheckDB WITH PHYSICAL_ONLY, which restricts the processing to verifying the integrity of the physical structure of page and record headers, along with consistency between the pages for the allocation structures (data and indexes).

The most recommended and optimal approach is to have a standby server, restore a backup of the production database on that server, and then execute the DBCC command. If the consistency checks on the standby database are successful, it provides assurance that the production database is also in a good state since it serves as the source for the standby. In case the standby database reports any corruptions, appropriate DBCC checks or other corruption tests can be performed against the production database(This is one of the most practical implementation in real time projects).

Hope you enjoyed learning something new today. Thanks for reading!