A long time ago in a galaxy far, far away, I had to troubleshoot interesting performance issue in SQL Server. Suddenly, the CPU load on the server started to climb up. Nothing changed in terms of workload. The system still processed the same amount of requests. The execution plans of the critical queries stayed the same. Nevertheless, the CPU usage grew up slowly and steadily by a few percent per hour.
Eventually, we nailed it down. The problem occured in very busy OLTP system with very volatile data. We noticed that system performed much more I/O (logical and physical) than before. It was very strange, because nothing should have changed that day. Finally, we found that we have large number of deleted rows in the database that had not been cleaned up by ghost cleanup task.
Read on to learn what caused this mess.
Simon Su has an interesting tool available:
Now I develop a tool to analyze AG log block movement latency between replicas and create report accordingly.
Click through for more info and check it out on Github.
The other part I needed to set up was read-only routing, this enables SQL Server to reroute those read only connections to the appropriate replica. You can also list the read only replicas by priority if you have multiple available or you can group them to enable load-balancing.
Although this seems to be setup correctly so that connections that specify their application intent of read only will be routed to the secondary node I wanted to prove it.
Read on to see how Jess is able to prove it.
High Availability (HA) – Keeping your database up 100% of the time with no data loss during common problems. Redundancy at system level, focus on failover, addresses single predictable failure, focus is on technology. SQL Server IaaS would handle this with:
- Always On Failover cluster instances
- Always On Availability Groups (in same Azure region)
- SQL Server data files in Azure
Disaster Recovery (DR) – Protection if major disaster or unusual failure wipes out your database. Use of alternate site, focus on re-establishing services, addresses multiple failures, includes people and processes to execute recovery. Usually includes HA also. SQL Server IaaS would handle this with:
Always On Availability Groups (different Azure regions)
Backup to Azure
Click through for more details.
AlwaysOn Basic Availability Groups (BAGs) are available with SQL Server 2016 and 2017 Standard edition. The functionality is generally the same as database mirroring (which has been deprecated). This feature replicates transactions to a database on a secondary server, and is useful for disaster recovery should something happen to the primary server.
If you have a database that requires an extra layer of protection or ‘BAG of tricks’, deploying a Basic Availability Group is useful for providing disaster recovery and high availability for the one database. Also there is major cost savings since it is not necessary to purchase SQL Enterprise edition…this can be done in Standard edition of SQL Server.
BAGs provide a failover environment for only one database, and there can only be two replicas in the group. Replication can be synchronous or asynchronous, and there is no read access, no backups and no integrity checks on the secondary. The secondary replica remains inactive unless there is a failover, and Basic AGs can remain on-premises or span from on-prem to Azure.
Read on for the two methods.
In addition to the existing checks, the new implementation has the following additional checks.
The new implementation stores and uses a historical snapshot of the database state information to decide if a failover should be initiated. The health check routine caches the database state and associated error information, for the last two executions, which is then compared with the state information from the current execution of the health detection routine. If the same error condition (for the below mentioned error codes) exists in three consecutive runs of the health detection routine, a failover is initiated. This implementation is intended to provide safeguards against transient errors and issues which can be fixed by the auto page repair capabilities of the availability groups.
The new implementation checks for following additional errors. Majority of these errors are indicative of a hardware issues on the server. Please note, that this is not an exhaustive list of errors which could impact the database availability. There is an outstanding item to include error 824 to this list.
Great news from the Tiger Team.
Recently a customer reported an interesting issue, while querying against recently added readable replica, SELECT statement is shown as suspended and session is shown as waiting on HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
Upon more investigation, it appeared to be waiting on with a wait type HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
The behavior is by design as mention in the SQL Server product documentation and applicable to all version of SQL Server that supports availability group.
Read on for the explanation.
A Read-Scale Availability Group is a Clusterless Availability Group. It’s sole purpose and design is to scale out a read workload. More importantly is what it is not. It is NOT a High Availability or Disaster Recovery solution. Since this design has no cluster under it, you lose things like automatic failover and database level health detection. For example, You have reports that run for customers that are in your DMZ that is fire-walled off from your internal network. Opening up ports for Active Directory so that you can have a cluster means opening a ton of ephemeral ports and ports with high attack vectors. Remember the Slammer worm? This solution removes those dependencies.
Click through for the setup scripts as well as a video Ryan created of him putting it all together. As long as you recognize the trade-offs involved, this can be a nice solution to certain problems.
Yes, you may have an availability group – well done – and you may have installed SSRS on both servers. But you’ve only set up the reporting application to point to one of those? And you’ve given the link
https://<<Listener_Name>>/reportsout to the users? Head/desk. I told you at the time that SSRS doesn’t play nicely with AGs. [Nearly misposted as SSRS doesn’t play nicely with SSRS, which, while valid, isn’t the point here…]
Here’s what you need to do to fix this / make sure it doesn’t happen:
Click through to learn what you need to do to make sure there are no problems.
Microsoft released Availability Groups (AG) as a feature in SQL Server 2012. Prior to SQL Server 2016, there were two methods of adding a database to a new AG replica.
- You could provide the Add Database to Availability Group wizard a file share accessible by the primary and secondary replicas. SQL Server would run FULL and LOG backups of each database to the share and use them to restore the database(s) to each replica.
- You could manually run a FULL and LOG backup of each database, copy the backup files to each replica, and restore the databases WITH NORECOVERY.
With SQL Server 2016. Microsoft has provided a third option, Automatic Seeding. With Automatic Seeding, you specify the databases and the replicas and SQL Server will begin transferring data to each replica. The duration of the seeding process depends on the size of the database and the network bandwidth available between primary and secondary replica.
Automatic seeding isn’t perfect, but it’s quite useful.