Database images and log transmission work together to achieve high availability and disaster recovery

The database has now become the cornerstone of the enterprise, so it is necessary to ensure the high availability of the database. At the same time, it is also necessary to prevent unexpected incidents from causing problems in the entire data center (such as addresses and floods ). Therefore, data center high availability and disaster recovery must be considered at the same time. Using SQLServer's database mirroring and log shipping functions can help us complete

The database has now become the cornerstone of the enterprise, so it is necessary to ensure the high availability of the database. At the same time, it is also necessary to prevent unexpected incidents from causing problems in the entire data center (such as addresses and floods ). Therefore, data center high availability and disaster recovery must be considered at the same time. Using the database mirroring and log shipping functions of SQL Server can help us complete

The database has now become the cornerstone of the enterprise, so it is necessary to ensure the high availability of the database. At the same time, it is also necessary to prevent unexpected incidents from causing problems in the entire data center (such as addresses and floods ). Therefore, data center high availability and disaster recovery must be considered at the same time.

Using the database mirroring and log shipping functions of SQL Server can help us achieve the above objectives.

Scenario: Data Center A has two database servers: SQL2K8 and R2, and data center B has SQL28 (different from region A, to prevent unexpected unavailability of the entire data center ), the database to be configured is AuditDb.

The configuration is as follows:

1. Perform full backup and log backup for AuditDb of SQL2K8.

2. Restore the backup created in 1 to R2 and SQL28 (restorewith norecovery ).

3. Right-click AuditDb in Management studio and select the image created from SQL2K8 AuditDb (select monitoring server ).

4. After the image is configured, select create Log shipping to SQL28 AuditDb (select monitoring server) in the properties ).

5. After the above steps are completed, high availability and disaster recovery can be achieved.

When the monitoring server detects a problem with SQL2K8, it will automatically Failover to R2, the application can automatically access R2, reducing the Down time (the application DBConnection needs to do the configuration: http://technet.microsoft.com/en-us/library/ms175484.aspx ). When a problem occurs in the entire data center, we can still change the server of data center B to available (Logshipping may cause some data loss Depending on the backup and restoration settings ).

The good news is that you can use Always On in SQL Server 2012 to implement the above functions without combining the two functions.

In fact, the combination of cluster, database image, and log transmission can produce a variety of combined solutions, which can basically achieve zero data loss and high availability of 99.999%. (Solution: A data center cluster + B data cluster image, A data cluster image + B data cluster log transmission, A data cluster + B Data Center log transmission ).