Shulou(Shulou.com)06/01 Report--

Truncated log classification for highly available disaster recovery environment

Logshipping: truncates the log

Replication-subscription: log will not be truncated

Mirror: log will not be truncated

Always on: log will not be truncated

Summary

Logshipping:

Because the log will be truncated, so the database has logshipping, so it is no longer necessary to do backup log

The primary instance of logshipping does not have a job for backup log, unless there is no database for building logshipping on the primary instance.

Mirror:

Backup log can only be performed in the database of the primary node. After the backup, the log is truncated, and the truncated log information is automatically synchronized to the database of the secondary node.

The primary instance node of mirror must have backup log jobs, unless each data of the primary instance node has mirror and logshipping

About the choice of mirror and logshipping

If you encounter that the log generated by the database in a short time is very large, for example, 500MB is generated within 15 minutes, then mirror is not as good as logshipping, because mirror consumes more memory.

So generally, large databases choose logshipping, and small databases choose mirror.

Always on:

Backup log can be performed in the database of primary and secondary nodes. Any node backup completes will synchronize the truncated information to other nodes, but primary and secondary nodes cannot backup log at the same time. At the same time, one of the nodes will wait for the backup of the other node to complete before starting the backup, and the waiting event is HADR_BACKUP_QUEUE.

Primary and secondary instance nodes of always on may have backup log jobs, because any node backup log will synchronize truncation information to other nodes, so in order to reduce the pressure on primary, you can only create backup log jobs on secondary nodes.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.