In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed
In the last articles, we have learned Configuring Snapshot and Transactional SQL Server replication and Configuring Peer to Peer and Merge SQL replication. Now, once we configured SQL Server replication, there will be some instances where we need to modify the SQL Server replication configuration as per the project updates. In this article, we will learn a few modifications in the existing SQL Replication such as Add new article, drop an article and change the Snapshot folder path and data filter in the current SQL Server replication.
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 »
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 »
This article will review on how to configure SQL Server Replication along with mirroring on a database.
In this guide, we’ll discuss more about migrating a SQL Server database to Azure SQL Database using SQL Server Transactional Replication.
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 »
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.
In this article of the series on SQL Server replication, we will explore ways to configure SQL Server Snapshot replication and Transactional replication step by step.
This article will cover SQL Server replication configuration including Peer to peer replication and merge replication, initial configuration, adding nodes and data verification.Read more »
In this article, we are going to see how to create a multi subnet cluster spanning across multiple subnets for lab purposes. Creating such an environment should help creating Availability groups simulating a far replica residing in a different Data Center (Subnet) acting as a disaster recovery site in your lab for learning/experimenting real world scenarios.Read more »
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 »
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 »
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 »
In this article we will discuss about SQL Server Merge Replication Parameterized row filter issues while replicating incremental data changes post initial snapshot.Read more »
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 »
This article will cover corruption and recovery scenarios in the context of SQL Server FILESTREAM including missing data, incompatible files types, DBCC checks, orphan files and garbage collection
Corruption is a looming thought through every administrator’s mind, from sysadmins to database administrators there is always a monster hiding in the shadows. SQL Server Database corruption happens all the time around the world and while most of us have been lucky enough to avoid it, we should still be prepared. While there is no way to prevent the corruption from happening, we must work hard to implement practices that minimize damage caused by the corruption. This means good backups and of course running DBCC CHECKDB.Read more »
This article will cover SQL Server transaction log architecture including file topography, basic overview, review of LSN and MinLSN, and log truncation
This article will review performance issues in replicating schema changes on tables involved in SQL Server Replication (Merge).Read more »
This article will show how triggers, indexes, constraints and foreign keys will replicate in snapshot and after snapshot in SQL Server replication.Read more »
This article will cover SQL bulk insert operations deterministic outcomes and responses covering not allowing any bad data to allowing all data to be inserted, regardless of errors.Read more »
This article will focus on the various ways to disable triggers in SQL Server so they won’t interfere with certain operations like bulk inserts.
One of the challenges we face when using SQL bulk insert from files flat can be concurrency and performance challenges, especially if the load involves a multi-step data flow, where we can’t execute a latter step until we finish with an early step. We also see these optimization challenges with constraints as well, as fewer steps to complete a data flow results in saved time, but possibly less accurate data.Read more »