Transaction log

SQL Server Transaction Log Architecture

January 31, 2019 by Ahmad Yaseen

This article will cover SQL Server transaction log architecture including file topography, basic overview, review of LSN and MinLSN, and log truncation

SQL Server Transaction Overview

January 8, 2019 by Ahmad Yaseen

In general, a Transaction is a single unit of work consists of multiple related tasks that should succeed or fail as one atomic unit. To make the concept of the transaction more familiar and why it should go all or none, imagine one of the most critical transaction examples in our daily life, which is withdrawing money from the ATM.

SQL Server transactional replication: How to reinitialize a subscription using a SQL Server database backup

September 13, 2018 by Prashanth Jayaram

A workload management is considered as a critical aspect of SQL Server transactional replication. Replication is the oldest of the high availability technologies in SQL Server and it is available since the inception of SQL Server. As a very mature technology, SQL Server transactional replication is also very robust and, in most cases, very straightforward to set up and manage.

SQL Replication: Basic setup and configuration

September 12, 2018 by Prashanth Jayaram

This is article is a continuation of the previous: SQL Server replication: Overview of components and topography.

By now, you’re familiar with the components of replication. So far, we’ve seen a lot of theory about replication. It’s a time for practical walkthrough of setting up a basic transactional SQL Replication system. The best way to get a feel for how SQL Replication is implemented and how it works is to see it in action.

How to setup a custom SQL Server transaction replication model with a Central Subscriber and Multiple Publisher databases

September 12, 2018 by Prashanth Jayaram

In this data-driven era, replication is often a critical requirement for achieving a modern, agile database management environment. It is believed designing an enterprise-grade dataset is the to achieving this requirement but building datamarts from datasets always presents certain challenges

In this article, we’ll discuss what it takes to setup “central subscriber with multiple publishers” replication model, to create an aggregate dataset from multiple sources, and you’ll also see how to scale with the data.

SQL Server replication: Overview of components and topography

September 11, 2018 by Prashanth Jayaram

The volume of data retained, managed, and accessed today is unprecedented. Businesses expect the IT department to keep data online and accessible indefinitely, putting intense pressure on the databases required to store and manage it. To meet today’s needs; we need to replace outdated and inefficient legacy processes with new, more agile techniques. SQL Server Replication is one of the techniques to accommodate such demands.

How to setup cross-platform transactional SQL Server replication for database reporting on Linux

August 31, 2018 by Prashanth Jayaram

In most cases, an organization can either use the existing out-of-the-box database replication features offered by their database software provider or invest in custom solutions to execute and manage database replication processes. The latter option sometimes allows greater flexibility to create data replicas across multiple types on multiple platforms.

How to setup custom SQL Server transactional replication with a central publisher and multiple subscriber databases

August 29, 2018 by Prashanth Jayaram

In this article, you’ll learn how to setup a simple, custom distributed database replication system.

Introduction

In general, a typical setup of transactional replication model of a central publisher with multiple subscribers includes the creation of a replica database(s) which may serve multiple purposes including:

How to Add/Drop articles from existing publications in SQL Server

August 24, 2018 by Prashanth Jayaram

This article discusses the challenges of meeting the availability, and performance requirements of high ended transactional replication environments. In addition, you’ll learn a new innovative approach that can be used to add/drop articles from an existing replication environment while maintaining replication system up and running.

How to continuously audit transaction log file data directly into a SQL Server database

August 23, 2018 by Prashanth Jayaram

Business transformation requires solid tools to automate complex integration to seamless deployments. In today’s modern data-rich world, nothing is more important than data management, making it critical to know how to safeguard and meet compliance requirement is very critical and the key to the business success.

How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog

August 23, 2018 by Prashanth Jayaram

Outline

In this article, we’ll discuss how to read SQL Server transaction logs. This article should provide and insight into how the workload is being captured in the transaction log files. We are going to see how to get the transaction meta-data details and the history of the data will give us a clear idea of how the system is working and helps to decide peak usage hours, also helps to log information about who is doing what?

How to set up a DDL and DML SQL Server database transactional replication solution

August 22, 2018 by Prashanth Jayaram

In the article How to setup SQL Server database replication for reporting Server we discussed all about setting up a simple SQL Server transactional replication environment. In this article, we’ll simulate a more robust system that replicates faster and includes both DDL and DML statements. We’ll walk-through the steps to setup a dynamic transactional replication.

SQL Server database migrations with zero data loss and zero downtime

August 15, 2018 by Prashanth Jayaram

The growing importance and complexity of data migration, in an era of exploding data volumes and ever-changing business requirements, means that old approaches will no longer get the job done. We are in a world where everything needs to run instantly. Every Database Administrator or Developer would have definitely heard about database migrations with zero downtime and with zero data loss.

Using transactional data replication to replay and test production loads on a staging server

August 9, 2018 by Prashanth Jayaram

Outline

In this article, you’ll see how to simulate production loads on a test server with a “record and replay” type situation using the transaction log, batch scripting, PowerShell and a SQL Server agent job.

We’ll be walking through the scenario in the following steps

Record the production load and write the transactions to disk by generating a timestamped replay script
Create a batch file to automate the task at an interval of every 1 minute
Create a SQL Server agent job to schedule the batch file
Replay the production workload to the target/test database by running a PowerShell script to open and execute the scripts at the same interval as they were created, every 1 minute
Validate the data between the source and the target databases to make sure our job works
Monitor the load with a monitoring tool, solution of your choice

How to setup SQL Server database replication for a reporting server

August 7, 2018 by Prashanth Jayaram

In this article, we’ll discuss the purpose of database replication and show how you can implement Replication using ApexSQL Log, a SQL Server transaction log reader.

Reading the transaction log in SQL Server – from hacks to solutions

July 3, 2018 by Nesha Maric

The SQL Server transaction log is akin to a ‘Black box’ in an airliner. It contains all of the records of transactions made against a database. This information is a proverbial goldmine for database audits, recoveries etc but it was never meant to be exposed to end users let alone visualized in an easy to read manner nor used for DBA tasks. As such, utilizing this information can be a challenge, to say the least.

Logical SQL Server data replication 101

May 4, 2018 by Thomas Mattei

Introduction

Data replication has been around for many decades. There are two primary types of data replication, logical and physical. This article covers a high-level view of logical replication, the differences between logical and physical replication, and the specifics of SQL Server transactional replication.

How to set up SQL Server Log Shipping on Linux

November 13, 2017 by Prashanth Jayaram

Log shipping is a high-availability configuration that perhaps most of us are familiar with. It’s one of the oldest techniques wherein we ship transaction logs from a Primary database to a Secondary database. Log Shipping is still a vital feature used in case of applications that use warm standby for Disaster Recovery. We can see many articles which discuss the process of configuring Log shipping using T-SQL or SSMS.

What is the SQL Server Virtual Log file and how to monitor it

April 26, 2017 by Musab Umair

Microsoft SQL Server has many important features when it comes to file structures. Generally speaking, DBAs often ignore file structures and growth configurations. More specifically, the SQL Server Log files are often left at default values and never touched, even when they begin to cause problems caused. These Log files are important and critical part of the database, especially when it comes to large systems.

Managing SQL Server transaction logs

May 13, 2016 by Ahmad Yaseen

SQL Server databases consist of two physical files types; the data file in which the data and the database objects such as tables and indexes are stored, and the transaction log file in which SQL Server stores records for all transactions performed on that database.

How to take advantage of the SQL Server’s transaction log?

April 11, 2016 by Jefferson Elias

Introduction

SQL Server keeps track of all database modifications and every database transaction. This is done in a file called the transaction log or TLOG. This transaction log is particular to a SQL Server database and there is, at least, one transaction log per SQL Server database.

As explained on MSDN, the transaction log is a critical component of the database and, if there is a system failure, the transaction log might be required to bring your database back to a consistent state. The transaction log should never be deleted or moved unless you fully understand the ramifications of doing this.

Peer to peer replication

February 18, 2016 by Rajendra Gupta

Nowadays many customers are spread globally and need to manage the requirement for users to connect from/to any location, perform an activity (insert, update, delete), and the databases should be kept synchronized across multiple sites.

Understanding the distribution scale of transactional and snapshot replication

January 25, 2016 by Timothy Smith

Background

If an environment chooses to use snapshot or transactional replication, one useful exercise is to ask the technical end user (or client) what they think replication does. If you have access to a white board, you can even ask them to demonstrate what they think replication will do for their data. Generally, these technical end users will plot something similar to the below image, where we see a table with data being copied to another table with data.

SQL Server Transactional Replication Moving Distribution Database – step-by-step guide

November 27, 2015 by Miroslav Dimitrov

SQL Server replication is a relatively old high-availability solution part of the Microsoft world. In fact, there have not been any significant changes to this solution in the recent versions SQL versions, but it is still a widespread mechanism for distributing objects from one database to another and synchronize them. Replication is very useful when you have remote and mobile users accessing your data. Let’s cut to the chase and give you more details about our specific case. 🙂

SQL Server Transaction Log – Part 3 – Configuration Best Practices

May 4, 2015 by Miroslav Dimitrov

It has been a while since the last transaction log article has been published, so I hope you do remember where this series is heading to. In the former posts, we have examined the Log Structure and Write-Ahead Algorithm (part 1) and the Top Reasons for Log Performance Problems (part 2). Taking into consideration this knowledge, we will review some best-practices for transaction log configuration in order to decrease the chance of experiencing log bottlenecks.