Infrastructure at your Service

Tag

AlwaysOn Archives - Page 2 of 4 - Blog dbi services

Microsoft Team

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
Microsoft Team

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
Microsoft Team

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
Microsoft Team

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
Microsoft Team

Windows failover cluster 2016 : new site awareness feature

By | Database Administration & Monitoring, Operation systems | 5 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
Microsoft Team

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

By | Development & Performance | 2 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
Microsoft Team

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
Microsoft Team

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

By | Database Administration & Monitoring | 4 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
Microsoft Team

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
Microsoft Team

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