Tag: transaction log

SQL Server Transactional Replication Configuration

Raja Jegan Leave a comment

Transactional Replication in SQL Server is one of the most commonly used Replication techniques to copy or distribute data across multiple destinations. In previous articles, we discussed SQL Server Replication and how Replication internally works. Now our goal is to see how to Configure Transactional Replication in SQL Server using the Backup approach and how to add or remove Articles to Replication properly. Without proper measures, we risk invalidating the snapshot and face the necessity to reconfigure Replication.

CodingSight - SQL Server Transactional Replication Configuration
Read More

SQL Server Transactional Replication Internals – Part 2

Raja Jegan Leave a comment

SQL Server Transactional Replication is one of the most commonly used replication techniques used to copy or distribute data across multiple destinations. In the previous article, we discussed SQL Server Replication, Types of Replication, and the Basic Internals on how the Transactional Replication works. Now, we are going to dive into Advanced Internals of how SQL Server Transactional Replication works.

CodingSight - SQL Server Transactional Replication Internals - Part 2
Read More

Basics of SQL Server Transaction Log

Kenneth Igiri Leave a comment

What is a Transaction Log?

There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties of transactions. The system must ensure that if a sudden crash happens, the transaction can be replayed. SQL Server fulfills this requirement by capturing all transactions in a physical file called a transaction log file.

In essence, every time a transaction is committed, SQL Server records changes produced by that transaction in a transaction log. Even if the transaction has not been persisted in the data file, it is available in the transaction log and can be replayed in the event of a sudden crash.

CodingSight - Basics of SQL Server Transaction Log
Read More

Configure SQL Server Log Shipping

Nisarg Upadhyay Leave a comment

This article explains the step by step deployment process of the SQL Server Log shipping. It is the database-level disaster recovery solution that is easy to set up and maintain.

The log shipping involves three steps:

  1. Generate the log backup on the primary database.
  2. Copy the backup to the network location or the specific directory on the secondary server.
  3. Restore the log backup on the secondary server.

The log shipping technology performs the steps described above by using SQL Server agent jobs. During the configuration process, the log shipping wizard creates those jobs on primary and secondary servers.

Read More

Using Trace Flag 3226 to Suppress Log Backup Logging

Kenneth Igiri Leave a comment

Introduction

Every backup operation in SQL Server is written to the SQL Server Error log. This includes Transaction Log Backups even when they occur as part of a Transaction Log Shipping Configuration. Sometimes logging the entire Log Backup can be a nuisance in the SQL Server Error Log and needs to be managed. Trace Flag 3226 is used to suppress such logging and we will demonstrate how this can be done in this article.

Read More

Importance of transaction log in SQL Server

Prashanth Jayaram Leave a comment

Transaction logs are a vital and important component of database architecture. In this article, we’ll discuss SQL Server transaction logs, importance, and their role in the database migration.

Introduction

Let’s talk about different options for taking SQL Server backups. SQL Server supports three different types of Backups.
1. Full
2. Differential
3. Transaction-log

Before jumping into transaction-log concepts, let’s discuss other basic backup types in SQL Server. Read More

Fixing Data Loss Using Log Shipping with Delayed Recovery

Kenneth Igiri Leave a comment

Introduction

Transaction Log Shipping is a very well-known technology used in SQL Server to maintain a copy of the live database in the Disaster Recovery Site. The technology depends on three key jobs: the Backup Job, the Copy Job, and the Restore Job. While the Backup job runs on the Primary Server, the Copy and Restore jobs run on the Secondary Server. Essentially the process involves periodic transaction log backups to a share from which the Copy Job moves same to the Secondary Server; subsequently, the Restore Job applies the log backups to the secondary server. Before all this starts, the Secondary Database must be initialized with a full backup from the Primary server restored with NORECOVERY option.

Read More

SQL Server Database Backup Encryption

Andrey Langovoy Leave a comment

To manage the data security that has been backed up by the file system as a database backup files, SQL Server provides the backup encryption feature. In this article, we will talk about encryption options available in SQL Server for database backups. We will have a closer look at the usage details, benefits, and recommended practices for encrypting SQL Server database backup during the backup process. Read More