SQL 20114 - AlwaysOn vs. Standalone

AlwaysOn availability groups has been around since SQL 2012 and they've been used to build highly available and disaster resilient solutions.
But how much performance may you lose when placing your database into an availability group?

The alwaysOn availability groups depend on the Windows Server Failover Cluster (WSFC) which in turn depends on Active directory.
Thus, for my test I need an environment like this

Two servers (Node1 & Node2) are nodes in a WSFC and they have SQL 2014 installed.
On Node1 I have two databases used for testing.
Both databases are in Full recovery mode and before running each test I backed up the log file.

The test script was executed from the Srv1 server. At the beginning all tables were dropped and recreated and then the script launched 2, 5, 10, and 15 concurrent clients running the same test (the description of the test is here).

The results are presented in this chart and they show approximately 50% drop in number of transactions the SQL server is able to process when it has the test database configured as a part of the availability group in the synchronous-commit mode.

In the test the both servers (Node1 & Node2) were connected to the same virtual network switch and your results will very depending on your hardware and network between the nodes.

Comments

Create 3-Node Windows 2012 Multi-subnet Cluster

Environment There are two Data centers connected via a WAN link. Two Windows 2012 Servers (called SQLDEV1 and SQLDEV2) are located in the Primary Data Center (on the IP subnet 192.168.79.0/24) and the third server is placed in the Secondary Data Center with the 192.168.69.0/24 subnet. We’ll be creating a three-node Windows cluster with no shared storage on the multi subnet network with a file share witness at the Primary Data Center. We’ll be using a file share witness to protect from the cluster failure in a situation when the network between the Data Centers is unavailable and one of the servers in the Primary Data Center is also down (or being rebooted). The final state will look like depicted above: - Two Virtual IP’s will be assigned (192.168.76.218 and 192.168.69.134) to the cluster - The servers at the Primary Data Center will have a vote (Vote=1) and the ...

Joining Windows 10 to Azure AD Domain

As of October 2016 to join Windows 10 computers to Azure AD Domain service requires these steps: Create a VNET in the classic portal . The VNET must be placed to a region where Azure AD domain service is available (( https://azure.microsoft.com/en-us/regions/services/ ) In the classic portal go to Directory -> Configure and enable the domain service. And wait for ~ 30 min When completed the IP address will be populated Go back to the VNET configuration and add a DNS server with the IP (10.0.0.4 in this case) Create the "AAD DC Administrator" administrators group (again in Directory -> Group). Members of this group are granted administrative privileges on machines that are domain-joined to the Azure AD Domain Services managed domain. Add to the group your users who are supposed to have the administrative access on a Windows 10 computer go to Settings -> Accounts (this is true for Windows 10 version 1607) then select 'Access...

SQL 2012 AlwaysOn: Synchronous vs. Asynchronous commit. Performance impact

Recently I've had a chance to build a 3-server AlwaysOn environment distributed between the primary and secondary data centers. The configuration looks like this: Primary Data Center Secondary Data Center SQLDEV1 SQLDEV3 SQLDEV2 The availability group was crated with synchronous commit replicas on SQLDEV1 and SQLDEV2 and the replica on SQLDEV3 was configured for asynchronous commit. The link between the data centers was not great and when I pinged SQLDEV3 from SQLDEV1 I got these results Approximate round trip times in milli-seconds: Minimum = 39ms, Maximum = 63ms, Average = 42ms I also created a very simp...

legi, intellexi

Search This Blog