A Fine Slice Of SQL Server
There’s a misconception that if you have a synchronous replica, that replica has to commit any transactions that are passed to it by the primary before the primary can then commit those same transactions. This ensures that there is no possibility of data loss and that the data would always be available on the secondary.
With this misconception comes our problem.
Read on to understand the full issue.
Comments closedRyan Adams provides guidance on using distributed transactions against Availability Groups:
A paramount concept to understand is how to make the DTC highly available. We can see from the precedence order that SQL Server will use the local DTC out of the box. This makes it appear that everything is working, and it is, but it is not exactly highly available.
I see a lot of customers leave it configured this way because they either don’t know the ramifications or do not realize they are using the MSDTC (Linked Servers). Since it simply works out of the box, things get left this way until they end up with a suspect database and error messages that look like this:
“SQL Server detected a DTC/KTM in-doubt transaction with UOW {598B7EDD-F7A1-9DC1-8D3E-303A4C93AAB4}.Please resolve it following the guideline for Troubleshooting DTC Transactions.”
Read the whole thing. There are a lot of small areas between processes where things can fail, and the combination of DTC + AGs is no different.
Comments closedAllan Hirt makes a disappointing discovery:
This one is going to be a short post. One of the big capabilities of SQL Server 2019 – the ability to deploy AGs using containers – is not shipping when SQL Server 2019 is released.
Read the whole thing, which isn’t much longer than my snippet. But Allan does promise an update for when it does hit.
Comments closedThe configuration for a cluster rolling upgrade allows for mixed Windows Server versions to coexist in the same WSFC. This is NOT a deployment method. It is an upgrade method. DO NOT use this for normal use. Unfortunately, Microsoft did not put a time limit on how long you can run in this condition, so you could be stupid and do something like have a mixed Windows Server 2012 R2/2016 WSFC. Fire, ready, aim. The WSFC knows about this and you’ll see a warning with an Event ID of 1548.
Read on for a summary of what Allan has learned in doing this.
Comments closedLet’s list the basic known details for the possible solution(for the Enterprise Edition of the Sql Server that is):
– We can make the secondary replica readable and read the same data on it. (Not that you should do that by default, but if you really know what you are doing …)
– We can copy our object into the TempDB (yeah, your Multi-TB table is probably not the best candidate for this operation), or maybe into some other writable DB.
– We can write results in the shared folder between the replicas (let’s say in a text file into a File Share)
– We can export the BLOB object of the statistics out of the SQL Server
– We can import the BLOB object of the statistics into the statistics
Read the whole thing.
Comments closedDmitri Korokevitch walks us through the HADR_SYNC_COMMIT wait type:
The secondary nodes may be configured using asynchronous or synchronous commit. With asynchronous commit, transaction considered to be committed and all locks were released when COMMIT log record is hardened on the primary node. SQL Server sends COMMIT record to secondary node; however, it does not wait for the confirmation that the record had been hardened in the log there.
This behavior changes when you use synchronous commit as shown in Figure 1. In this mode, SQL Server does not consider transaction to be committed until it receives the confirmation that COMMIT log record is hardened in the log on the secondary node. The transaction on primary will remain active with all locks held in place until this confirmation is received. The session on primary is suspended with HADR_SYNC_COMMIT wait type.
Click through for the full story.
Comments closedDavid Fowler points out a few things to look at before failing over an Availability Group:
What about your SQL Agent jobs? Have you got jobs that perform actions on your data? If you have, do those jobs exist on the new primary? If they don’t then I’m happy to bet that whatever function that they were playing probably isn’t happening anymore.
One thing that I always want to make sure before I failover is, do I have all the relevant jobs ready to roll on the secondary server?
But what about the jobs that you’ve got on the old primary? There’s a fair chance that, if they’re doing any sort of data manipulation, they’re going to be failing.
Click through for additional considerations.
Comments closedJohn McCormack shows two ways to connect to an Availablity Group listener with read-only intent:
SQLCMD
The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.
There’s some good information in here for sqlcmd and for SQL Server Management Studio.
Comments closedAndy Mallon looks at a wrinkle Availability Groups adds to replication:
This has always worked super, and has been a go-to query for me for years. But when looking at a SQL Server 2017 distributor with the distribution database in an Availability Group, that wasn’t working. All the publications had a
publisher_idof 1, but insys.servers,server_id1 was some random linked server, and definitely not the publisher. But replication was working great. Maybe replication was set up on the other AG replica, andserver_id1 came from there.Nope. On the other replica, it was the same story.
Server_id1 was a random linked server, and nothing to do with replication at all, let alone the publisher. But replication was working perfectly. A teammate fooling around with it in dev confirmed that if he updated thepublisher_idto match theserver_idwe thought it should join to, replication stopped working. So, thatpublisher_idof 1 was correct. Or special. But also definitely different than what I’ve seen in prior versions of SQL Server.
Read on to see what Andy learned.
Comments closedPaul Randal walks us through a permissions issue after an Availability Group failover:
Jonathan was working with a client recently who experienced a CLR assembly failure after an AG failover and needed to figure out why. They’d been testing their AG disaster recovery strategy and ran into an unexpected problem with their application which relies heavily on SQLCLR and an UNSAFE assembly that calls a web service from inside SQL Server. When they failed over their AG to their DR server, the CLR assembly failed with the following error:
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65546. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘sqlclr_assemblyname, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fa39443c11b12591’ or one of its dependencies. Exception from HRESULT: 0x80FC80F1
Read on to see the root cause and what you can do to correct it.
Comments closed