Database high availability (HA) and disaster recovery (DR) are critical for any organization. There is essential work to be done to ensure that online services and applications always have access to data in order to operate.
- SQL Backup and Restore :
Database and log backups are the foundation of any HADR implementation. It’s the basic blocking and tackling of any database implementation. Every SQL Server should be backed up periodically in addition to any other HADR solutions you may have implemented.
Backing up is important. But it’s really the ability to restore that important. Periodically restore selected databases to another system and run integrity checks on those databases. Document the process so that in a true disaster recovery scenario, you’ll have a proven protocol.
- SQL Server Log Shipping :
Log Shipping works by automatically sending the primary database log backups to one or more secondary server instances. It applies these log backups on the secondary databases to keep them up to date with the primary database. This process is similar to the MySQL binlog or the PostgreSQL Write Ahead Log (WAL). Additionally, you can deploy a monitor server to record the backup, restore history, and raise alerts if the secondary copy is behind the primary database with a predefined threshold.
- SQL Server Replication :
SQL Server replication is used to distribute filtered data among multiple target servers. It can synchronize data between databases while maintaining data consistency and integrity.
SQL Server supports the following replication types:
-- Snapshot Replication: Snapshot replication takes a snapshot of the entire database and applies it to the subscriber database. It is helpful for initial database synchronization or cases where your source database does not change frequently.
-- Transactional Replication: Transactional replication replicates continuous changes from the primary database (publisher) to the secondary database (subscriber) with minimal latency. It is one-way replication from publisher to subscriber databases.
-- Merge Replication: Merge replication can replicate data from a publisher to a subscriber database and vice versa. It is bidirectional replication typically used in the server to the client environment for synchronizing data across database servers when they cannot be connected continuously.
-- Peer-to-Peer Replication: Peer-to-Peer replication can replicate data to multiple subscribers distributed across the globe. Data can be changed in any database server and replicated to servers participating in Peer-to-Peer replication.
- Failover Clustered Instances (FCIs) :
Failover Clustered Instances takes your HADR game up several levels. Leveraging Windows Server Failover Clusters (WSFC), a single instance of SQL Server can be installed across multiple WSFC nodes. Should one of the nodes experience a hardware or software failure, key services such as SQL Server, are automatically shifted to another node.
There are two types of failover clustering:
-- Active-Passive Cluster: Active-Passive cluster configuration uses the secondary server in case of any failover. It is a popular choice since it does not affect performance. However, your passive server remains idle until resources are failed over to it.
-- Active-Active Cluster: The Active-Active cluster runs different SQL Services (instances) on both servers. Each server has its own SQL Service and storage. If any of the servers fail, the other server takes over and runs both SQL instances.
- SQL Server Always On Availability Groups :
With Always On Availability Groups, transaction logs are synchronized from the primary replica to one or more secondary replicas. Should the primary replica become unavailable, either due to a failure or scheduled maintenance, a secondary replica transitions to the primary role. Clients, which connect to a listener, are unaware of any changes to the actual location of the primary replica.
SQL Server Always On supports two modes.
-- Synchronous Data Commit: In synchronous mode, the transaction must commit on the secondary database followed by the primary database. It ensures that you always have a synchronized database copy without any difference between the primary and secondary databases. It is suitable for the critical database where you cannot afford any data loss.
-- Asynchronous Data Commit: In asynchronous commit mode, the primary database does not wait for acknowledgment from the secondary. It is suitable for optimized performance and disaster recovery solutions.
No comments:
Post a Comment