Applying Transaction Logs to the Secondary Replica in SQL Server Always On Availability Groups July 12, 2019 by Rajendra Gupta Introduction SQL Server Always On Availability Groups provides HADR solutions for the SQL databases. Here HA refers to high availability and DR refers to disaster recovery. The priority of this feature is to keep the database highly available and then provide Disaster recovery. Due to this reason, if the secondary replica goes down in a synchronous data commit mode, SQL Server changes commit mode to Asynchronous so that users can continue run the transactions and a secondary replica can be in sync later once it bought up. SQL Listener also points to the primary replica and continues redirects connection to the primary replica. Read more »
How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File May 15, 2019 by Ahmad Yaseen This is the last article, but not the least one, in the SQL Server Transaction Log series. In this series of articles (see the TOC below), we described the Transaction Log concept from four different aspects. Read more »
Recovering Data from the SQL Server Transaction Log May 14, 2019 by Ahmad Yaseen If this is your first visit to this SQL Server Transaction Log series, I recommend you first review the previous articles (see the TOC below), in order not to be familiar with the concepts that we will discuss in this article. Reviewing the previous articles, you will have a good idea about the following: Read more »
SQL Server Transaction Log Administration Best Practices April 22, 2019 by Ahmad Yaseen In this article, we will go through a number of SQL Server Transaction Log recommendations and best practices that each database administrator needs to follow in order to enjoy his holidays and have a pleasant sleep without nightmares or calls during the night. Read more »
SQL Server Transaction Log Backup, Truncate and Shrink Operations April 16, 2019 by Ahmad Yaseen In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed
SQL Server Transaction Log Growth Monitoring and Management April 1, 2019 by Ahmad Yaseen In the previous articles of this series on the SQL Server Transaction Log, we discussed the importance of the SQL Server Transaction Log and the role that it plays in maintaining the database consistency, by ensuring that the committing transactions data will be preserved and the failed transaction will be rolled back. It also helps to recover the database to a specific point in time in case of system or hardware failure. This is achieved by writing a log record to the SQL transaction log file before writing the data pages to the physical data file, using Write-ahead Logging process. Read more »
Log shipping on a mirrored database March 26, 2019 by Ranga Babu Both log shipping and mirroring are high availability and disaster recovery options available in SQL Server. This article will review on how to configure log shipping on a mirrored database. Read more »
Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth March 21, 2019 by Rajendra Gupta In my previous article in this series Accelerated Database Recovery; Instant Rollback and Database Recovery, we talked about a potential DBA painkiller to resolve long waiting times for database recovery and rollback scenarios using Accelerated Database Recovery. In this article, we will look at one more painful challenge for DBAs, Long Running Transaction with Transaction log growth. Read more »
SQL Server Transaction Log and High Availability Solutions March 21, 2019 by Ahmad Yaseen In the previous article of this series on the SQL Server transaction log, we discuss the SQL Server database recovery models, Full, Simple and Bulk-Logged, and the how the recovery model option of the database affects the way the SQL Server Engine works with the transaction logs. In this article, we will discuss the different types of high availability and disaster recovery solutions and the role of the SQL Server transaction log in these technologies.
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping March 5, 2019 by Rajendra Gupta This article is the continuation of the SQL FILESTREAM series. Read more »
Transaction log backups in a SQL FILESTREAM database March 5, 2019 by Rajendra Gupta In the continuation of our SQL FILESTREAM article series, we’ll be covering transaction log backups In SQL Server, we take transaction log backups regularly to have a point-in-time recovery of a database. It is essential to define the backup policy with the combination of full, differential and transaction log backups. A standard configuration of backups for large databases in the production database environment is as follows. Read more »
SQL Server Transaction Log and Recovery Models February 27, 2019 by Ahmad Yaseen In the previous articles of this series (see TOC at bottom), we discussed the main concept of the SQL Server transaction, the internal architecture of the SQL Server Transaction Log and finally the Virtual Log File and the best practices in managing the VLFs. In this article, we will go through the relationship between the SQL Server Transaction Log and the three database recovery models. Read more »
Auditing by Reading the SQL Server Transaction Log February 25, 2019 by Ahmad Yaseen In the previous articles of this series, we discussed the reasons behind auditing the different SQL Server instance and database events, how to audit the SQL Server instance manually and using the Extended Events and Triggers methods. In this article, we will show how to audit your SQL Server instance by reading the content of the SQL Transaction Log file of the user database. Read more »
What are SQL Virtual Log Files aka SQL Server VLFs? February 18, 2019 by Ahmad Yaseen In this article we’ll review SQL Virtual Log files aka SQL Server VLFs, providing an overview and review topics related to size, performance, monitoring and troubleshooting. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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: Read more »
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. Read more »
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. Read more »
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? Read more »
