Infrastructure at your Service

AlwaysOn Archives - Blog dbi services

David Barbarin

SQL Server AlwaysOn – Distributed availability groups, read-only with round-robin capabilities

By | Database Administration & Monitoring | No Comments

  This blog post comes from a very interesting discussion with one of my friends about the read-only capabilities of secondary replicas in the context of distributed availability groups. Initially, distributed availability groups are designed to address D/R scenarios and some migration scenario types as well. I already discussed about of one possible migration scenario here. However, we may also take advantage of using secondary replicas as read-only in Reporting Scenarios (obviously after making an…

 
Read More
David Barbarin

SQL Server 2016: Distributed availability groups and Cross Cluster migration

By | Database Administration & Monitoring | No Comments

How to migrate an environment that includes availability groups from one Windows Failover Cluster to another one? This scenario is definitely uncommon and requires a good preparation. How to achieve this task depends mainly of your context. Indeed, we may use a plenty of scenarios according to the architecture in-place as well as the customer constraints in terms of maximum downtime allowed for example. Among all possible scenarios, there is a process called “cross-cluster migration…

 
Read More
David Barbarin

SQL Server 2016 : Basic availability groups may fit to your HA needs

By | Database Administration & Monitoring | No Comments

In this blog let’s talk about SQL Server and availability groups in standard Edition. I had some customer questions about existing limitations and this is a good opportunity to review them on this blog post. First of all, let’s say that Microsoft has already documented the restrictions here and from my side, I already written a blog post on the subject at the time of the CTP version of SQL Server 2016. In the meantime,…

 
Read More
David Barbarin

SQL Server AlwaysOn: troubleshooting improvements with new service packs

By | Database Administration & Monitoring | No Comments

As promised in my latest blog, I will talk about improvements in the detection of the availability group replication latency. As a reminder, replication latency between high available replicas may be monitored in different manners so far. Firstly, in my experience I had the opportunity to use different performance counters as “Database Replica:Transaction Delay” and “Databas Replica:Mirrored Write Transactions/sec” in order to get a good picture of the latency over the time after implementing an…

 
Read More
David Barbarin

SQL Server AlwaysOn: new services packs and new diagnostic capabilities

By | Database Administration & Monitoring | No Comments

As you certainly know, the SQL Server 2014 SP2 has been released by Microsoft with some interesting improvements that concern SQL Server AlwaysOn and availability groups feature. In fact, all of them are also included into SQL Server 2012 SP3 and SQL Server 2016 (update 24.09.2016: not yet released with SQL Server 2016). Among all fixes and improvements that concern AlwaysOn, I would like to focus on those described in the Microsoft KB3173156 and KB3112363. But in this…

 
Read More
David Barbarin

SQL Server 2016 AlwaysOn: Direct seeding and backup considerations

By | Database Administration & Monitoring | No Comments

As promised in my prior blog post, we’ll talk now about direct seeding feature and backups. We found an interesting LIMIT_CONCURRENT_BACKUPS value for current state of the hadr_automatic_seeding_state_transition event. As said last time, this value is meaningful by itself. So let’s try to perform a backup while the database is concerned by an active seeding session. In fact, my backup was blocked (as I supposed) and here what I found from the sys.dm_tran_locks and sys.dm_exec_requests…

 
Read More
David Barbarin

SQL Server 2016 AlwaysOn: Direct seeding and performance considerations

By | Database Administration & Monitoring, Development & Performance | No Comments

This blog post follows the previous one about new direct seeding feature shipped with SQL Server 2016. As a reminder, I had some doubts about using direct seeding with large databases because log stream is not compressed by default but I forgot the performance improvements described into the Microsoft BOL. I also remembered to talk about it a couple of months ago in this blog post. So let’s try to combine all the things with the…

 
Read More
David Barbarin

SQL Server AlwaysOn: is my database ready for failover?

By | Database Administration & Monitoring | No Comments

In this blog post I just want to clarify values that we may find from the is_failover_ready column of the sys.dm_hadr_database_replica_cluster_states DMV. A couple of days ago, I had an interesting discussion with one of my customers during which he explained he was not confident about the availability group state after facing some unavailability issues. The main source of uncertainty was the result from the following query that he used to check if everything was…

 
Read More
David Barbarin

MVP Data Platform renewed and next SQL Saturday event at Paris

By | Technology Survey | No Comments

The last 1th April 2016, I got my 6th MVP Data Platform renewal (not an april’s fool for sure). This is definitely a good news and I would like to thanks again Microsoft for its interest. So the story continues and I want to share with you the next sessions I will have the opportunity to animate in the next SQL Saturday in Paris on the 25th and 26th June 2016.   First session (precon)…

 
Read More
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