Home » High Availability

High Availability

sql server DBA 999

SQL Server Database mirroring modes and patner timeout

The Two modes of Database Mirroring in SQL Server for HA/DR Solutions are

  1. Synchronous mirroring( High Availability / HA )
  2. Asynchronous mirroring( Disaster Recovery / DR )

Synchronous mirroring :

In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database’s log buffer in memory, and then flushed to disk (or ‘hardened’) as quickly as possible. In database mirroring, as the principal server writes the principal database’s log buffer to disk, it simultaneously sends that block of log records to the mirror instance.The mirror receives the log records in mirror database’s log buffer and then hardens them to disk … Read the rest

sql server DBA 999

Administration of Transactional Replication

Administration of Transactional Replication

There are 3 types of Replication Types in SQL Server:

1. Snapshot Replication
2. Transactional Replication
3. Merge Replication

Replication of data can be configured between same server or can be different servers connected by Network. SQL Server replication uses a publisher, distributor and subscriber for replication to work
.
Publisher is the database that sends its data to another database which can be located in same or another server.

Subscriber is the database that receives data from another database which can be located in same or another server.

Distributor is the server that manages the flow of data through the replication system. This server contains the distribution database.

There are

Read the rest
sql server DBA 999

Transactional Replication

Transactional Replication in SQL Server 2008:Transactional replication will start with a snapshot of the publication database objects and data which is called as initial snapshot. Subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time). The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher; therefore, within a publication, transactional consistency is guaranteed.
By default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that allow updates at the Subscriber.
Transactional replication is implemented by the SQL … Read the rest

sql server DBA 999

Log Shipping in SQL Server 2008

Log Shipping in SQL Server 2008
Log shipping allows us to automatically send transaction log backups from a primary database server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. There is option to include another server instance known as the monitor server which will record the history and status of backup and restore operations and be configured to raise alerts if these operations fail to occur as scheduled.
Log shipping consists of three operations:
1. Back up the transaction log at the primary server instance.
2. Copy the transaction log file to the secondary server instance.
3. Restore the log backup on the … Read the rest

sql server DBA 999

Database Mirroring in SQL Server 2008

Database Mirroring in SQL Server 2008:
In database mirroring we can opt to either synchronous or asynchronous DB mirroring.
In asynchronous DB Mirroring the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance.
In synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes as below;
1. In High-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
2. In … Read the rest