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 »
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. Read more »
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. Read more »
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 Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
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. Read more »
