Restore a database from a backup set

16/06/2018

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 (WSFCWSFC) 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

02/06/2018

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

13/01/2018

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...
2 comments

Take advantage of the “Blocked Process Report” event

27/12/2017

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

14/07/2017

  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

24/06/2017

  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...
4 comments

Transactional Replication Customized Profiles–LogReader Agent

16/06/2017

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...

Distribution Agent (sp_MSupdatelastsyncinfo) blocking when using subscriptionstreams

11/06/2017

Today I received a few notification emails from a client who's production environment I monitor and maintain. I received a “Query timeout expired” message coming from MSrepl_errors and since I have a few tens of agents in this system there were many messages… in addition I also received notification about contention indicating a that a process is waiting on a resource held by another process for a long term and so it is blocked and cannot proceed until the resource it is waiting for is released. I used my LeadBlockers query (see the very bottom of this post) to...