Transactional Replication - Deep dive

Transactional Replication is most widely used form of SQL Server replication. It’s more full featured than snapshot replication but much easier to set up and manage than merge replication. There is common belief that merge replication is only replication type that supports bidirectional data replication. That’s not the case as both snapshot and transaction replication offer immediate and queued updating subscriptions.
SQL Server implements transactional replication via snapshot agent , log reader agent , and the distributor agent. The snapshot agent prepare the initial snapshot of a transactional publication. The log reader agent scans the T-log on the publisher and detects the changes made to the data after the snapshot has been taken and records them in the distribution database. The distribution agent reads the changes recorded in the distribution database and applies them to subscriber.
Each modification to a published table causes the Log Reader agent to write at least one row to MSrepl_commands. Unlike snapshot replication here data in MSrepl_commands is not stored in human readable form. You have to use sp_browsereplcmds.This sp returns readable version of replicated commands stored in distribution database.

The log reader agent uses the extended procedure sp_replcmds (implemented internally by SQL Server) to retrieve log records produced by DML statements in the published database's T-log. Each publisher database participating in transactional replication will have only one log reader agent regardless of howmany transactional publications it contains. Each database published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

If one publication calls sp_replcmds for a db then log reader is considered for that database until it disconnects. Other publications attempting to run sp_replcmds before the first disconnects will get error stating that ' Another log reader is replicating the database'. One other reason that why only one log reader agent is permitted for each database is that scanning the log for changes can impact performance. Each time the log reader agent invokes sp_replcmds, it causes log reader code within the SQL Server process to scan the published database's transaction log for changes that need to be replicated. When it does this , the log reader agent changes the typical sequential method SQL Server uses to access the log into something more random. While the server writes new entries to the end of the T-log as changes are made to the database, the log reader agent may be reading a different section of log in order to write replication commands to MSrepl_commands and MSrepl_Transactions.This can cause resource contention for the T log and impact the performance of the server.
SQL Server maintains a global chache of article metadata known as the article cache. This cache stores metadata from sysarticles and syscolumns for the replicated article. SQL Server consults this chache when it requires metadata for a particular article.
Once the log reader agent finishes calling sp_replcmds and writing new entries to MSrepl_commands and MSrepl_Transactions, it calls sp_repldone to indicate that the specified log records have been successfully replicated. This allows SQL Server to purge log records as necessary. Log records for articles cannot be purged utill they are replicated to distributor.

 

SQL Server History

How SQL Server writes data to disk ?

The transaction log is a serial record of all modifications that have occurred in the database. SQL Server writes changes to the log before it writes changes to the actual data file.

SQL Server maintains a buffer cache into which it reads data pages when data must be retrieved. Data modifications are not made directly to disk, but are made to the copy of the page in the buffer cache. The modification is not written to disk until a checkpoint occurs in the database, or the modification must be written to disk so the buffer can be used to hold a new page. Writing a modified data page from the buffer cache to disk is called flushing the page. A page modified in the cache, but not yet written to disk, is called a dirty page.

 At the time a modification is made to a page in the buffer, a log record is built in the log cache that records the modification. This log record must be written to disk before the associated dirty page is flushed from the buffer cache to disk. If the dirty page is flushed before the log record is written, the dirty page creates a modification on the disk that cannot be rolled back if the server fails before the log record is written to disk. SQL Server has logic that prevents a dirty page from being flushed before the associated log record is written. Log records are written to disk when the transactions are committed.

When users change data, SQL Server doesn't write that change directly to the data. Rather, SQL Server locates the appropriate data and then loads it into a special area of RAM called the data cache. Changes are made in RAM. Then, SQL Server copies changes waiting in RAM to the transaction log. Only then does SQL Server write changes to the actual data file.

This is called a write-ahead log because SQL Server writes changes to the log before it writes changes to the actual data file. This approach is quite a bit faster than writing directly to the data file.

Checkpoint in SQL Server

What is checkpoint?

Checkpoint is a process to write drity pages to disk. Dirty pages are pages which are in buffer pool and modified but not yet written to disk.

Syntax :

Checkpoint [ checkpoint duration ]

Check point duration is advanced option. Checkpoint duration is time is seconds and it's value should be interger and greater than zero. If you mention any value in checkpoint duration then SQL will ensure to maintain the amount of dirty pages in disk.
If SQL services restart then all the databases should recover with in the time which is mentioned in the checkpoint duration. If you write nothing in checkpoint duration then SQL Server will issue automatic checkpoint. What does automatic means ? If no value is mentioned in checkpoint duration then SQL Server will issue the checkpoint when data in dirty pages is greater than what SQL can recover in the recovery interval. SQL will ensure that databases should be online after restart in specific amount of time which is  mentioned in recovery interval in sp_configure.

What check point do ?

Batch up write processes to improve performance.
Reduces time required for crash recovery.

How checkpoint improves performance?

If data is written to disk each time pages get's modified hence write I\Os will increase on the server which in result will bring down performance on whole. Checkpoint batches up write processes and writes them to disk at certain duration.

How checkpoint reduces crash recovery ?

SQL keep monitoring the pages in buffer pool and checkpoint is issues as soon as number of dirty pages increases beyond what can be covered in specified recovery interval.

SPID & KPID

SPID is the SQL Server Process ID number and is assigned by SQL Server to each new connection. It starts with one and is globally unique. SPID 1 through 50 are reserved for system uses and are not used for any user connections.

KPID is the kernel-process ID. Under SQL Server for Windows this is the thread ID number, also known as "ID Thread," and is assigned by Windows when the thread is created. The Thread ID number is a system-wide identifier that uniquely identifies the thread. KPID is visible by querying the KPID column of master..sysprocesses. It is only filled in for spid numbers four and higher. You can also get KPID/ID Thread from Windows Perfmon using the "Thread" object and the "ID Thread" counter.

Query to find how many threads and open transactions SPID is running we can run this query.

SELECT spid, kpid, status, cpu, memusage, open_tran, dbid FROM sysprocesses WHERE spid= 'enter spid value'

Tools for troubleshooting

In today's post I will walk you through some of the tools you can use to troubleshoot.

SQLDiag.exe is a valuable troubleshooting  tool for any SQL Server instance. It gathers all teh SQL Server error logs and configuration settings. You can use SQLDiag.exe to gather information such as event logs, a profiler trace and performance monitor log for SQL Server.

The Cluster log is not specific to SQL Server , it contains information about when any resource fails the LooksAlive or IsAlive check and tracks when resources are brought online and offline. All the times in cluster.log is GMT in Windows2000 and Windows2003. Cluster.log is present in the cluster directory under the systems folder.

The Event logs can be good source of information. The event logs are collected by SQLdiag in SQL Server 2005 and PSSDiag in SQL Server 2000.

If you are getting some sort of "file not found" error, it could indicate that a file or registry key no longer exists or the process does not have sufficient permissions for the file or key. You can doenload toos such as Filemon or RegMon from http://www.sysinternals to help troubleshoot these types of issues.

Sometimes you need to check the settings of various registry keys such as location of the master
data and log files. Regedit.exe is a useful tool in these situations. However keep in mind that in a cluster , extrasteps are necessary to change the checkpointed keys.

Sometimes the SQL Server error logs give you a clue as to why you are seeing problems.Always check SQL error logs.

SQL Server Profiler is a great tool for seeing exactly what queries are being sent to SQL Server and how long they take to execute.

System Monitor ( also called performance monitor , Perfmon, or sysMon ) is often used to monitor general performance as well as SQL Server specific components.

Cluster Adminstrator is used for operations such as manually failing over to another node , reviewing and changing configuration settings , taking resources or resource group offline or bringing them online and changing cluster resource dependencies. There is also the command line cluster.exe to perform these operations.

Which TCP/IP port does the SQL Server run on? How can it be Changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number, both on client and the server.