Infrastructure at your Service

AlwaysOn Archives - Page 2 of 3 - Blog dbi services

David Barbarin

SQL Server AlwaysOn : the registry is also important for availability groups

By | Database Administration & Monitoring, Database management | No Comments

A couple of months ago, we experienced with my colleague Nathan Courtine a weird issue at one of our customers. This issue concerned an AlwaysOn availability group and more precisely to the WSFC cluster layout. I can’t say strongly enough that the AlwaysOn availability group runs on the top of the WSFC and having a good understanding of the internal stuff can help a lot for troubleshooting. First of all, let me introduce the issue…

 
Read More
David Barbarin

Windows failover cluster 2016 : new site awareness feature

By | Database Administration & Monitoring, Operation systems | No Comments

After my first two blogs about cloud witness and domain-less dependencies features shipped with Windows Server 2016, it’s time to talk about another pretty cool feature of the WSFC : site awareness that will also benefit to our SQL Server FCIs and availability groups. But before to talk further about the potential benefits of this new feature, let’s go back to the previous and current version of Windows Server that is 2012 R2. Firstly, let’s…

 
Read More
David Barbarin

Availability groups : readable secondary replicas and impact on the storage layer

By | Development & Performance | No Comments

Do read-only capabilities of an availability group have a performance impact for databases? The quick response is yes but let’s see why in this blog post. First of all, when we want to use our secondary replicas as read-only, we enable and we use implicitly the SNAPSHOT isolation level regardless either the transaction isolation level or the hint used in your query. So as you know, the use of the SNAPSHOT isolation level has an…

 
Read More
David Barbarin

SQL Server 2016 : availability groups and performance improvements

By | Development & Performance, Hardware & Storage | No Comments

During the last SQL Saturday in Paris, I had the opportunity to co-present the new SQL Server 2016 features and one of my topic concerned the availability group’s enhancements. I mentioned the replication throughput improvement but without giving more details and this is the idea of this blog post. First of all, let’s remind that the network throughput is part of the synchronous replication process and it is directly tied to the application transaction performance….

 
Read More
David Barbarin

SQL Server AlwaysOn : readable secondary replicas and index rebuild online vs offline

By | Database Administration & Monitoring | 2 Comments

A couple of weeks ago, I was involved in a discussion on the French forum developpez.com about SQL Server AlwaysOn and availability groups, index strategies and the impact on the readable secondary replicas. During this discussion, one of the forum member stated that rebuilding an index online had less impact than rebuilding an index offline on a readable secondary replica but I disagree with this affirmative. Is it really true? Let’s verify in this blog…

 
Read More
David Barbarin

SQL Server 2016 : availability groups and the new ssis_monitor role for SSIDBB catalog

By | Database Administration & Monitoring | No Comments

During my investigation about the new AlwaysOn features, I wrote a blog post about the new SSISDB support for AlwaysOn. You can find it here: > SQL Server 2016 : availability groups and the new SSISDB support Just as a reminder, for those who have dealt with the SSISDB catalog in the previous version, some extra works were mandatory to be “AlwaysOn” compliant. Fortunately, the new version of SSIDB catalog will make easier the DBA…

 
Read More
David Barbarin

SQL Server AlwaysOn: number of databases and the hadr worker pool

By | Database Administration & Monitoring | No Comments

Often, when I’m in charge to implement an SQL Server AlwaysOn infrastructure with availability groups, customers ask me if it exists some best practices and recommendations about the maximum number of databases in an availability group planning. In fact, we have to keep in mind the relation between databases and the SQL Server worker pool in this architecture. Remember that the SQL Server AlwaysOn and availability group feature is an extension of the SQL Server…

 
Read More
David Barbarin

SQL Server AlwaysOn and availability groups : how to isolate the replication traffic from other networks?

By | Database Administration & Monitoring | 12 Comments

A couple of days ago, I was involved in a project about implementing availability groups with SQL Server 2014. My customer wanted to isolate the replication traffic from the public network. The idea behind was to get a better control of the network bandwidth as well as to be sure that the replication can be done without any latency issues in case of heavily network traffic issued by other applications or backup activities for instance….

 
Read More
David Barbarin

Dealing with SSRS subscription schedules in AlwaysOn environment

By | Database management | 11 Comments

A couple of days ago, I had the opportunity to work with AlwaysOn 2014 infrastructure that included a report server (SSRS) in native and scale-out mode. As you know, dealing with SSRS and availability groups is not an easy task depending on the used features. This is particularly true when scheduling and subscriptions are used by customers. Here’s a brief description of the concerned architecture: 2 SSRS servers in scale-out mode and an availability group…

 
Read More
David Barbarin

SQL Server 2016 : availability groups and the new SSISDB support

By | Hardware & Storage | 7 Comments

This blog post is focused on the new supportability of SSIDB catalog on AlwaysOn architecture. Others studies are available here: New database issue level detection for automatic failover Potential features in standard edition Automatic failover enhancements New load balancing features with read-only replicas New clustered columnstore index support A couple of weeks ago, I was involved in an SSIS infrastructure project with SQL Server 2014. As you know, the SSIS architecture has fundamentally changed since…

 
Read More