Snapshot vs. LogShipping vs. Mirroring vs. Replication vs. Failover Clustering in Sql Server 2008R2

1) Snapshot is a static read only picture of database at a given point of time. Snapshot is implemented by copying a Page (8KB for SQL SERVER) at a time. For e.g. assume you have a table in your DB, & you want to take a snapshot of it. You specify the physical coordinates for storing snapshot & when ever original table changes the affected rows are pushed first to the the snapshot & then changes happen to the DB. (N.B. There is also something called as Snapshot Isolation Level which is different from Database Snapshot).

Usage Scenario: You have a separate DB for report generation, and want to ensure that latest data for that is available. You can periodically take snapshot of your transactional database.

2) Log Shipping is an old technique available since SQL SERVER 2000. Here the transactional log (ldf) is transferred periodically to the standby server. If the active server goes down, the stand by server can be brought up by restoring all shipped logs.

Usage Scenario: You can cope up with a longer down time. You have limited investments in terms of shared storage, switches, etc.

3) Mirroring which was introduced with 2005 edition, works on top of Log Shipping. Main difference is the uptime for the standby server is quite less in mirroring. Standby server automatically becomes active in this case (through help of a broker server which is called as Witness in SQL SERVER parlance), without having to restore logs (actually logs are continuously merged in this scenario – no wonder it’s called Mirror :) ). Additional advantages of Mirroring include support at .NET Framework level (read no switching/routing code – requires ADO.NET 2.0 & higher) plus some new features like Auto Page Recovery introduced with SQL SERVER 2008.

Usage Scenario: You want very less down time and also a cost effective solution in terms of shared storage, switches, etc. Also you are targeting a single database which easily fits in your disks.

4) Replication is used mainly when data centers are distributed geographically. It is used to replicate data from local servers to the main server in the central data center. Important thing to note here is, there are no standby servers. The publisher & subscriber both are active.

Usage Scenario: A typical scenario involves syncing local / regional lookup servers for better performance with the main server in data center periodically, or sync with a remote site for disaster recovery.

5) Failover Clustering is a high availability option only (unlike others above which can be used for disaster recovery as well) used with clustering technology provided by hardware + OS. Here the data / databases don’t belong to either of servers, and in fact reside on shared external storage like SAN. Advantages of a SAN storage is large efficient hot pluggable disk storage. You might see DR options like Mirroring used quite frequently with failover clustering


Cons and Pros :

Log Shipping:

It automatically sends transaction log backups from one database (Known as the primary database) to a database (Known as the Secondary database) on another server. An optional third server, known as the monitor server, records the history and status of backup and restore operations. The monitor server can raise alerts if these operations fail to occur as scheduled.

Mirroring:

Database mirroring is a primarily software solution for increasing database availability.
It maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine.

Replication:

It is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Components
Log Shipping:Primary server, secondary server and monitor server (Optional).
Mirroring::Principal server, mirror server, and witness server (Optional).
Replication::Publisher, Subscribers, Distributor (Optional).

Data Transfer


Log Shipping:T-Logs are backed up and transferred to secondary server.
Mirroring::Individual T-Log records are transferred using TCP endpoints.
Replication::Replication works by tracking/detecting changes (either by triggers or by scanning the log) and shipping the changes.

Server Limitation


Log Shipping:It can be configured as One to Many. i.e one primary server and many secondary servers. Or Secondary server can contain multiple Primary databases that are log shipped from multiple servers.
Mirroring:It is one to one. i.e. One principal server to one mirror server.
Replication:

    Central publisher/distributor, multiple subscribers.
    Central Distributor, multiple publishers, multiple subscribers.
    Central Distributer, multiple publishers, single subscriber.
    Mixed Topology.

Types Of Failover
 
Log Shipping:Manual.
Mirroring:Automatic or manual.
Replication:Manual.

DB Access


Log Shipping:You can use a secondary database for reporting purposes when the secondary database restore in STANDBY mode.
Mirroring:Mirrored DB can only be accessed using snapshot DB.
Replication:The Subscriber Database is open to reads and writes.

Recovery Model


Log Shipping:Log shipping supports both Bulk Logged Recovery Model and Full Recovery Model.
Mirroring:Mirroring supports only Full Recovery model.
Replication:It supports Full Recovery model.

Restoring State


Log Shipping:The restore can be completed using either the NORECOVERY or STANDBY option.
Mirroring:The restore can be completed using with NORECOVERY.
Replication:The restore can be completed using With RECOVERY.

Backup/Restore


Log Shipping:This can be done manually or
through Log Shipping options.
Mirroring:User make backup & Restore manually.
Replication:User create an empty database with the same name.

Monitor/
Distributer/ Witness


Log Shipping:The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. . If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance.
Mirroring:Principal server can’t act as both principal server and witness server.
Replication:Publisher can be also distributer.

Types Of Servers


Log Shipping:All servers should be SQL Server.
Mirroring:All servers should be SQL Server.
Replication:Publisher can be ORACLE Server.

SQL Server Agent Dependency/Jobs


Log Shipping:Yes. Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.
Mirroring:Independent on SQL Server agent.
Replication:Yes. Snapshot agent, log reader agent & Distribution agent (transactional replication)
Merge agent (merge replication).

Requirements


Log Shipping:

    The servers involved in log shipping should have the same logical design and collation setting.
    The databases in a log shipping configuration must use the full recovery model or bulk-logged recovery model.
    The SQL server agent should be configured to start up automatically.
    You must have sysadmin privileges on each computer running SQL server to configure log shipping.

Mirroring:

    Verify that there are no differences in system collation settings between the principal and mirror servers.
    Verify that the local windows groups and SQL Server logins definitions are the same on both servers.
    Verify that external software components are installed on both the principal and the mirror servers.
    Verify that the SQL Server software version is the same on both servers.
    Verify that global assemblies are deployed on both the principal and mirror server.
    Verify that for the certificates and keys used to access external resources, authentication and encryption match on the principal and mirror server.

Replication::

    Verify that there are no differences in system collation settings between the servers.
    Verify that the local windows groups and SQL Server Login definitions are the same on both servers.
    Verify that external software components are installed on both servers.
    Verify that CLR assemblies deployed on the publisher are also deployed on the subscriber.
    Verify that SQL agent jobs and alerts are present on the subscriber server, if these are required.
    Verify that for the certificates and keys used to access external resources, authentication and encryption match on the publisher and subscriber server.

Using With Other Features Or Components


Log Shipping:Log shipping can be used with Database mirroring, Replication.
Mirroring:Database mirroring can be used with
Log shipping, Database snapshots , Replication.
Replication:Replication can be used with log shipping, database mirroring.

DDL Operations


Log Shipping:DDL changes are applied automatically.
Mirroring:DDL changes are applied automatically.
Replication:only DML changes to the tables you have published will be replicated.

Database Limit


Log Shipping:No limit.
Mirroring:generally good to have 10 DB’s for one server.
Replication:No limit.

latency


Log Shipping:There will be data transfer latency. >1min.
Mirroring:There will not be data transfer latency.
Replication:Potentially as low as a few seconds.

Committed /
Uncommitted
Transactions


Log Shipping:Both committed and uncommitted transactions are transferred to the secondary database.
Mirroring:Only committed transactions are transferred to the mirror database.
Replication:Only committed transactions are transferred to the subscriber database.

Primary key

Log Shipping:Not required.
Mirroring:Not required.
Replication:All replicated table should have Primary Key.

New Created Database&
Stored Procedure


Log Shipping:Monitoring and history information is stored in tables in msdb, which can be accessed using log shipping stored procedures.
Replication::Creates new SPs ( 3 Sps of one table).
Distribution Database.
Rowguid column will be created.

Individual Articles


Log Shipping:No. Whole database must be selected.
Mirroring:No. Whole database must be selected.
Replication:Yes. Including tables, views, stored procedures, and other objects. Also filter can be used to restrict the columns and rows of the data sent to subscribers.

FILESTREAM


Log Shipping:Log shipping supports FILESTREAM.
Mirroring:Mirroring does not support FILESTREAM.
Replication:Replication supports FILESTREAM.

DB Name


Log Shipping:The secondary database can be either the same name as primary database or it may be another name.
Mirroring:It must be the same name.
Replication:It must be the same name.

DB Availability


Log Shipping:In case of standby mode: read only database.
In case of restoring with no recovery: Restoring state.
Mirroring:In Recovery state, no user can make any operation.
You can take snapshot.
Replication:Snapshot (read-only).
Other types (Database are available).

Warm/ Hot Standby Solution


Log Shipping:It provides a warm standby solution that has multiple copies of a database and require a manual failover.
Mirroring:When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss).
Replication:It provides a warm standby solution that has multiple copies of a database and require a manual failover.

System Data Transferred


Log Shipping:Mostly.
Mirroring:Yes.
Replication:No.

--- Find tables rows count in a database--------------------------------

 Using sys.partitions table :

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO
 Using sys.dm_db_partition_stats table :

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
    sOBJ.type = 'U'
      --AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Using Count(*) and sp_MSforeachtable proc table (will be slow for large tables):

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

Using  Sys.tables and sys.Indexes:

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2