Replication Terminology

Replication is a way to automate data distribution from a source to one or more destination database. SQL Server replication follows the Magazine Publishing terminology. A publisher (source instance) publishes articles (publications – SQL Server objects) to which one or more subscribers (destination instance/s) can subscribe to get updates.

Replication can be used for disaster recovery, scalability, reporting, data warehousing etc.

Publisher:- a server which makes data available for other servers i.e. subscribers. The articles (SQL Server Objects) which are to be replicated are defined at the publisher.

Subscriber:- a server which gets data from the publishers. A subscriber can subscribe to one or more publications.

Distributor:- a server which distributes data from publishers to subscriber. It contains the Distribution database. The distribution database keeps track of the changes to the publication which are to be replicated to subscribers. A distributor can be a separate server or publisher itself.

Publication:- a set of articles or SQL Server objects which are published by a publisher and to which a subscriber subscribes too.

Articles:- a SQL Server object such as Table, view, function, stored procedure etc. which is replicated.

Replication Agents

Replication is performed by the replication agents. These are external applications configured as SQL Server Agent Job.  These are listed below

The Snapshot Agent is used to synchronize or initialize initial schema in Merge/Transactional replication and to replicate data in Snapshot replication. This is done by snapshot.exe.

The Distributor Agent takes the snapshot/T-log data from the snapshot/log reader agents and makes it available to the subscribers. It is done by Distrib.exe

The Log Reader Agent extracts the appropriate transactions from the publisher’s log which are to be replicated. It then sends these logs to distributor (Distribution database) in proper sequence. It is done by LogRead.exe.

Merge Agent applies the initial snapshot to the subscribers and also applies the incremental data changes at the publisher to subscribers. It is done by Replmerg.exe.

Queue Reader Agent applies the messages in SQL Server queue or Microsoft Message Queue to the publisher when queuing is specified.

Publication 4 types

Standard transactional publication

Appropriate for topologies in which all data at the Subscriber is read-only Standard transactional publications are created by default when using Transact-SQL or Replication Management Objects (RMO). When using the New Publication Wizard, they are created by selecting Transactional publication on the Publication Type page.

 Transactional publication with updatable subscriptions

-Each location has identical data, with one Publisher and one Subscriber.  -It is possible to update rows at the Subscriber -This topology is best suited for server environments requiring high availability and read scalability.

Peer-to-peer topology

- Each location has identical data and acts as both a Publisher and Subscriber. - The same row can be changed only at one location at a time. - Supports conflict detection  - This topology is best suited for server environments requiring high availability and read scalability. 

Bidirectional transactional replication

Bidirectional replication is similar to Peer-to-Peer replication, however, it does not provide conflict resolution. Additionally, bidirectional replication is limited to 2 servers. 

DBA's Queries

List of all 'open tran' to stop deadlocks

SELECT

 [tst].[session_id] AS SessionID

 ,[des].[login_name] AS LoginName

 ,DB_NAME (tdt.database_id) AS DatabaseName

 ,[tdt].[database_transaction_begin_time] AS TransactionBeginTime

 ,[tdt].[database_transaction_log_bytes_used] AS LogBytesUsed    

 ,[mrsh].text AS QueryText

 ,[ph].[query_plan] AS QueryPlan

FROM sys.dm_tran_database_transactions [tdt]

JOIN sys.dm_tran_session_transactions [tst]

 ON [tst].[transaction_id] = [tdt].[transaction_id]

JOIN sys.[dm_exec_sessions] [des]

 ON [des].[session_id] = [tst].[session_id]

JOIN sys.dm_exec_connections [dec]

 ON [dec].[session_id] = [tst].[session_id]

LEFT OUTER JOIN sys.dm_exec_requests [der]

 ON [der].[session_id] = [tst].[session_id]

CROSS APPLY sys.dm_exec_sql_text ([dec].[most_recent_sql_handle]) AS [mrsh]

OUTER APPLY sys.dm_exec_query_plan ([der].[plan_handle]) AS [ph]

ORDER BY [TransactionBeginTime] ASC

Top 5 slowest quries

SELECT TOP 5 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1 ) AS Query ,qp.query_plan ,qs.execution_count ,qs.total_logical_reads ,qs.last_logical_reads ,qs.total_logical_writes ,qs.last_logical_writes ,qs.total_worker_time ,qs.last_worker_time ,qs.total_elapsed_time ,qs.last_elapsed_time ,qs.last_execution_time FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC

Tables without Primary Key

SELECT OBJECT_SCHEMA_NAME(tables.object_id,db_id()) AS SchemaName,
tables.name As TableName
FROM sys.tables tables join sys.indexes indexes
ON tables.object_id=indexes.object_id
WHERE indexes.is_primary_key=0
GO

Detach or Take Database offline

select name, state_desc from sys.databases where name='AdventureWorks2014'

USE [master]

GO

ALTER DATABASE [AdventureWorks2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'AdventureWorks2014'

GO

USE [master]

GO

CREATE DATABASE [AdventureWorks2014] ON 

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks2014_Data.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.DMSQL2014\MSSQL\DATA\AdventureWorks2014_Log.ldf' )

 FOR ATTACH

GO

ALTER DATABASE AdventureWorks2014 SET OFFLINE WITH ROLLBACK IMMEDIATE

ALTER DATABASE AdventureWorks2014 SET ONLINE WITH ROLLBACK IMMEDIATE


% Fragmentation

select TableName=object_name(dm.object_id)
 ,IndexName=i.name
 ,IndexType=dm.index_type_desc
 ,[%Fragmented]=avg_fragmentation_in_percent
 from sys.dm_db_index_physical_stats(db_id(),null,null,null,'sampled') dm
 join sys.indexes i on dm.object_id=i.object_id and dm.index_id=i.index_id
 order by avg_fragmentation_in_percent desc