So how does recovery know what to do?

 All recovery processes depend on the fact that each log record is stamped with a log sequence number (LSN). A log sequence number is an ever-increasing, three-part number that uniquely defines the position of a log record within the transaction log. Each log record in a transaction is stored in sequential order within the transaction log and contains the transaction ID and the LSN of the previous log record for the transaction. In other words, each operation that is recorded as part of the transaction has a "link" back to the operation that immediately preceded it.

For the simple case of a single transaction being rolled back, the recovery mechanism can easily and quickly follow the chain of logged operations from the most recent operation back to the first operation and undo the effects of the operations in the opposite order from which they occurred. The database pages that were affected by the transaction are either still in the buffer pool or on disk. In either case, the image of the page that is available is guaranteed to be one where the effect of the transaction is reflected on the page and must be undone.

During crash recovery, the mechanism is more complicated. The fact that database pages are not written to disk when a transaction commits means that there is no guarantee that the set of database pages on disk accurately reflects the set of changes described in the transaction log—either for committed or uncommitted transactions. However, there is one final piece of the puzzle that I haven't mentioned yet—all database pages have a field in their page header (a 96-byte portion of the 8192-byte page that contains metadata about the page) that contains the LSN of the last log record that affected the page. This allows the recovery system to decide what to do about a particular log record that it must recover:

• For a log record from a committed transaction where the database page has an LSN equal to or greater than the LSN of the log record, nothing needs to be done. The effect of the log record has already been persisted on the page on disk.
• For a log record from a committed transaction where the database page has an LSN less than the LSN of the log record, the log record must be redone to ensure the transaction effects are persisted.
• For a log record from an uncommitted transaction where the database page has an LSN equal to or greater than the LSN of the log record, the log record must be undone to ensure the transaction effects are not persisted.
• For a log record from an uncommitted transaction where the database page has an LSN less than the LSN of the log record, nothing needs to be done. The effect of the log record was not persisted on the page on disk and as such does not need to be undone.

Crash recovery reads through the transaction log and ensures that all effects of all committed transactions are persisted in the database, and all effects of all uncommitted transactions are not persisted in the database—the REDO and UNDO phases, respectively. Once crash recovery completes, the database is transactionally consistent and available for use.

Suspect Database

The suspect_pages table is used for maintaining information about suspect pages, and is relevant in helping to decide whether a restore is necessary.

The suspect_pages table resides in the msdb database and was introduced in SQL Server 2005.

Configuring Transactional Replication -SQL Server 2008



 There are 3 steps involved in setting up Transactional Replication.

1. Configuring the Distribution database.
2.  Creating the Publisher.
3. Creating the Subscriber.

Scenario:

Here I am taking two SQL Server instances ( Roli-w7\rolit & Roli-w7\roliSQL) installed on same server. I will create Distribution database on 'rolit' instance and Publisher on roliSQL instance. A contact table which belongs to Person schema in the Adventureworks Database is replicated to rollsubs database present on same server.

Configuring the Distribution database

Step1. Connect to the Microsoft SQL Server 2008 R2 Management Studio.
Step2. Right Click on the Replication node and Select Configure Distribution.



Step3. A new window appears which holds only information about the wizard. Click Next.

 

 

 

 

 

 

 

 

 Step4. A new window will appear where you can make choice whether you want your distribution database to reside on same server or some other server will host the distributor.

 

 

Note : Distributer database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance.

Step5. Next window will appear. Select the first option, i.e. Yes, configure the SQL Server Agent service to start automatically and click on the Next> button as shown in the screenshot  below.

 

Step6. In below screen, you are asked about location of  the Snapshot folder.

 

Step7. The new screen will display information such as what will be the distribution database name, the location where the data and the log file will reside. Make required changes if you want and click Next.

  Step8 : Click Next.

Note here you get option of adding publisher.

 

Step 9: Choose to configure distribution database.

Step10. Click Finish.

 

Step11.  Snapshot folder is created & Distributor database is configured.

 

After this a new database named distribution gets created. You can confirm this by expanding the System Database node and you shall be able to view the distribution database, please refer below screenshot.

 

 2. Creating the Publisher

Step1: Right Click on Local Publications and select New Publication.

 

Step2: New Publication window will appear. Click Next.

 

 

Step3: Choose the Publication Database and Click Next.

 

 

Step4: Choose ' Trasaction Replication' in Publication type.

 

 

Step5: You have to choose here what you want to publish

 

 

Step6: I have choosen only one table for replication.

 

 

 

Step7: If want to add filter you can add filter here however I did not add any filter in this example. Click next.

 

 

 

Step8: Choose when you want to run snapshot agent.

Step9: Click Security Settings

 

Step10:  Here specify the domain or machine account under which snapshot Agent process will run.

Step11: Click the check box of - Create the publication. and proceed to the next screen.

 

Step12: Give publication name. I have named my publication as RollRep.

 

 

Publication is created and now we will move ahead to create subscriber. 

Creating the Subscriber

Step 1: Open SSMS and go to Replication. Click new Subscriptions.

 

 

Step 2 : A new Subcription wizard will appear click Next.

 

 

Step 3 : This step will allow you to choose the publication for which you want to create subsciptions. You can create one or more subscriptions.

 

 

Step 4: Now choose the type of Replication you want to create Push or Pull.

After choosing the type of subscriptions click next.

 

 

 Step5 : Choose Subscriber and click next.

 

Step 6: Choose connection to Dstributor and Subscriber.

 

 

 

 

Step 7: Specify Distribution agent security and mode of connection to distributor and subscriber.



Step 8: Choose the Synchronization schedule.

 

Step 9: Initialize subscriptions 

Step 10: Choose Create Subscription and click Next.

 

Step 11: New Subcription is successfully created.

 

 To ensure that you replication is working fine keep a close eye on replication monitor.

 

 

 

Replication monitor is used for measuring Latency and Validating Connections for Transactional Replication.Transactional replication provides the tracer token feature, which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher, Distributor and Subscribers. A token (a small amount of data) is written to the transaction log of the publication database, marked as though it were a typical replicated transaction, and sent through the system, allowing a calculation of:

• How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor.
• How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber.

From these calculations, you can answer a number of questions, including:

• Which Subscribers take the longest to receive a change from the Publisher?
• Of the Subscribers expected to receive the tracer token, which, if any, have not received it?

Replication Monitor displays performance quality values for transactional replication and merge replication in the Current Average Performance and Current Worst Performance columns for publications and the Performance column for subscriptions. The values are:
Excellent
Good
Fair
Poor
Critical (transactional replication only)

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