Boost Replication Snapshot delivery performance by modifying the MaxBcpThreads


The MaxBcpThreads argument defaults to 1 for the Distribution Agent and for the Snapshot Agent. This means that when the Snapshot Agent generates a snapshot it exports the data from tables to flat files using a single thread and when the Distribution Agent applies that snapshot to the subscription database at the subscriber a single thread is also used. We can get a significance performance improvement by increasing this value for both agents but most impact would be gained from the Distribution Agent as loading data from flat files to tables is a much slower operation than the opposite...
no comments

Restore a database from a backup set


I have recently carried out a consolidation project in which I consolidated three SQL Server instances into a single instance. The three instances were all clustered (known as FCI instances – Failover Cluster Instances) part of a two node Windows Server Failover Cluster (WSFC) where both cluster nodes are virtual machines enjoying the HA capabilities provided  by the VMware infrastructure. The goal of the project was to move to a standard SQL Server instance which is not clustered (not an FCI instance) and consolidate all into a single instance. A Windows Server Failover Cluster (WSFC) role is to...
no comments

AlwaysOn Availability Groups and Backup jobs


When implementing a backup solution with AlwaysOn Availability Groups you can use the built in feature of the preferred backups. I have decided to keep control under my hands and keep on using the same way I backup my servers ever since and this by using SQL Server Agent jobs with my Backup_Native stored procedure. All I had to do is add a logic that detects if the database we process belongs to an Availability Group and act accordingly. While deploying this solution I came across the function fn_hadr_group_is_primary from this post which is a UDF (User Defined Function)...

AlwaysOnSynchronizer 1.7.2 is now available for download


For some background information see the very first post of AlwaysOnSynchronizer here. For additional details about setup and configuration of AlwaysOnSynchronizer see this post. To download the latest release of AlwaysOnSynchronizer click here.   This release adds support for Regular Expressions to be used with the configuration option “ScriptJobsToExclude”. It can be helpful in environments where there are many jobs with the same pattern in the job names. The bellow example of my Servers.config file has the job named “Regex-Test” in three variations. Here is the outcome when using Regex. When “^” is used (“^Regex-Test”) this job only...

Take advantage of the “Blocked Process Report” event


If you are new to SQL Trace “server side traces” you may want to read this post first.   Recently I have been working on a project where the customer has hired me for performance tuning. Analyzing the system it turned out the degraded performance experienced is mostly due to high contention that the system suffers from,  and based on experience I was, in my mind, already making the assumption that when I get rid of these blocking activity the system's performance will be in a different place. One of the great challenges was to address a severe contention...
no comments

Trace Flag 1448 – Replication with Always On Availability Groups


  Working with Replication in an environment where Always On Availability Groups provides high availability brings a few challenges unique to this environment. To begin with when setting replication with Always On you should follow this kb providing a detailed description as per the steps required and configuration.   One important point to be aware of is that there are some situations where your log reader can stop reading transactions from the published database t-log resulting in your subscribers being way behind real time. This is where Trace Flag 1448 comes in to help. See Trace Flag 1448...

AlwaysOnSynchronizer 1.7.0 is now available for download


  For additional details about AlwaysOnSynchronizer see this post. To download AlwaysOnSynchronizer click here.   The new release includes some minor bug fixes: 1. The “ScriptLogins” option did not script a login deleted on the primary replica 2. The “ScriptJobs” option did not script a job that had a new schedule added In addition there is a major change in the configuration file (Servers.config) that now requires us to define all replicas that should be synchronized where as in previous versions we only had to define the Primary replica and the tool issued a query to retrieve...

Transactional Replication Customized Profiles–LogReader Agent


In this post I introduce a stored procedure that is used to enforce a customized profile for a log reader agent in Transactional Replication, Peer-2-Peer and CDC (Change Data Capture). Before you go please see this post which has more details about the topic of Replication Agents Profiles and how I implement them.   The MonitorLogReaderProfile stored procedure does the following: 1. Creates a customized log reader agent profile named “CustomizedLogReaderProfile” 2. Set the “CustomizedLogReaderProfile” to be the default profile for log reader agents 3. Verify that the current parameters of any existing profile named “CustomizedLogReaderProfile” match the values...

Transactional Replication Customized Profiles–Distribution Agent

When working with Transactional Replication you may want to use a different profile for any of the agents involved (i.e. distribution agent or the log reader agent) than the default profile you get at first just following your setup. An agent profile is a way to define a set of parameters that you pass to the replication agent (i.e. logread.exe, distrib.exe). The values defined in the agent profile are the values passed as parameters to the executable which in the case of the Distribution Agent it is distrib.exe. So if for any reason we would like to work with...