In this article, we will review Transparent Data Encryption (TDE) in AWS RDS SQL Server. AWS RDS supports TDE on SQL Server Enterprise edition of 2012,2014,2016 and 2017 editions.
Read more »
Features
Azure SQL Server auto-failover groups
July 18, 2019In this article, we will review how to set up auto-failover groups in Azure SQL Server and how failover group is different from active geo-replication in Azure. Auto-failover group is an Azure SQL database feature that replicates one or a group of databases to the secondary Azure SQL server in the cross-region. We cannot have a secondary server in the same region. This feature is used to failover all the databases in the failover group in case of disaster and the failover is automatic.
Read more »
Azure SQL database Geo-Replication
July 15, 2019In this article, we will review how to set up Geo-Replication on Azure SQL databases. Geo-Replication is an Azure SQL database feature that allows you to create a readable secondary database in the same region or cross-region. We can failover to the secondary database in case of an outage for a long time on the primary database server. We can also use this feature to migrate a database from one server to another server in the same or cross region with minimal downtime. Geo-replication uses the Always-on feature to replicate committed transactions to the secondary database asynchronously.
Read more »
Applying Transaction Logs to the Secondary Replica in SQL Server Always On Availability Groups
July 12, 2019Introduction
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 »
SQL replication with a Publisher Database in Always On Availability Groups
July 8, 2019In this article, we will review how to setup SQL replication with publisher database in Always On availability groups so that replication continuous to sync even after failover to the secondary server in the availability group.
Read more »
A walk-through of moving database files in SQL Server Always On Availability Groups
July 8, 2019SQL Server Always On Availability Groups feature provides high availability (HA) and disaster recovery solutions. We can add multiple databases in a single Always on Availability Group on SQL Server Enterprise edition. Before we add a database in the AG group, we need to use the following checklist.
Read more »
Azure SQL Data Sync – Replicate data and schema changes between Azure SQL databases
July 5, 2019In this article, we will review how to configure the sync group to replicate data between Azure SQL databases using Azure SQL Data Sync.
Read more »
Isolation levels behavior in SQL Server Always On Availability Groups
June 18, 2019In my previous article, Data synchronization in SQL Server Always On Availability Groups, we explored the internal data synchronization for both Asynchronous and Synchronous data commit mode. In the following image, on the secondary replica, we can see two important processes.
Read more »
Automatic Seeding in Always On Availability Groups
June 18, 2019Introduction
In SQL Server Always On Availability Groups, we can add a database into an existing availability group using different methods. We get the following option in the Initial Data Synchronization wizard in SSMS.
Read more »
Read Scale Availability Group in a clusterless availability group
May 27, 2019In this article, we will explore configuring Read Scale Availability Group that does not require a failover clustering configuration. It helps to scale read-only connections to the secondary replica in a cluster less configuration.
Read more »
SQL Server Statistics in Always On Availability Groups
May 27, 2019Introduction to SQL Server Statistics
SQL Server Statistics are an essential part of query performance in SQL Server. They help the query optimizer to prepare a query plan using the distribution of values in participating rows. If we do not have updated statistics, it might lead to resource intensive query execution plan. For example, for a large table having a billion records, SQL may choose to have an index scan instead of an index seek.
Read more »
How to Rebuild a Database with a Corrupted or Deleted SQL Server Transaction Log File
May 15, 2019This 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, 2019If 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 Always On Listeners
May 13, 2019This article on SQL Server Always On Listeners includes an overview and various connection configurations for specifying MultiSubnetFailover including SSMS, ODBC, ADO.NET, as well as MultiSubnetFailover limitations and how to connect to all IP addresses via RegisterAllProvidersIP.
Read more »
SQL Server Always ON Availability Group Log Backup on Secondary Replicas
May 10, 2019In a high OLTP environment, we may observe strain on CPU and IO due to frequent backups. It might include further complexity due to backup compressions. SQL Server Always On Availability groups provides the capability to perform database backups from the secondary replicas. This article explores Log backups in SQL Server Always On Availability Group.
SQL Server Always On Availability Group Data Resynchronization
May 9, 2019In my previous article Data Synchronization in SQL Server Always On Availability Group, we described a scenario where if a secondary replica goes down in synchronous data commit mode, SQL Server Always on Availability group changes to asynchronous data commit mode. It ensures that users can get their transaction commit irrespective of waiting for a secondary replica to come online.
Read more »
Data synchronization in SQL Server Always On Availability Groups
May 3, 2019This article describes the data synchronization process on SQL Server Always On Availability Groups in both Synchronous, and Asynchronous data commit mode.
Read more »
SQL Server Transaction Log Administration Best Practices
April 22, 2019In 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, 2019In this article, we will cover SQL Server Transaction log backups, truncate and shrink operations with an overview and examples covering everything discussed
Add new articles, drop the article, change the snapshot folder path and Data filter rows in SQL Server Replication
April 2, 2019In 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.
SQL Server Transaction Log Growth Monitoring and Management
April 1, 2019In 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, 2019Both 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 »
SQL Server Replication on a mirrored database
March 26, 2019This article will review on how to configure SQL Server Replication along with mirroring on a database.
SQL Server database migration to Azure SQL Database using SQL Server Transactional Replication
March 22, 2019In this guide, we’ll discuss more about migrating a SQL Server database to Azure SQL Database using SQL Server Transactional Replication.
Accelerated Database Recovery and Long Running Transactions with Transaction Log Growth
March 21, 2019In 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 »