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