SQL Server Read Scale Availability Groups (AKA Cluster less AGs)

What are Read scale Availability Groups ? (Also referred as Cluster less availability groups many times)

In this blog post let’s see what read scale AGs are and few scenarios when they can be useful for us and how to actually create them. So what exactly is a read scale AG? Well, Microsoft introduced these AGs in SQL 2017 just to serve a single purpose which is to scale our read work loads. These AGs do not offer HA capabilities which we get with a typical AG deployment running on a failover cluster. Since there is no cluster behind the scenes, there is no concept of health checks and hence no automatic failovers. To keep it very simple, consider them as Not Always On AGs 🙂

Well, when can I use Read Scale AGs?

Think of a scenario where all you want is to just isolate your read workload for a given database(s) which doesn’t have any HA requirements and your users and applications are okay connecting directly to your secondary replica for read/reporting work loads, do you really need clustering here? If you are absolutely sure that your AG is not being used for making your database highly available, why to have a cluster and why deal with it?

Note: Read scale AGs do not offer high availability capabilities, This is not for your mission critical database(s) which require HA with automatic failover capabilities. If you have to throw HA in the mix, just stick to traditional AGs.

If not for HA, How about Disaster recovery?

we can certainly get the DR capabilities with Read scale AGs. The reason I say that is, we can have synchronous commit setup which pretty much guarantees the same RPO as a traditional AG would do. Even in a traditional setup when ever I think DR, it’s a manual failover for me….YMMV.

Aaaalright, let’s get into action. I have two standalone SQL Server 2019 Instances joined in the same domain which are not participating in any kind of clustering…you know, just two simple VMs joined to my domain running Windows/SQL, nothing fancy.

First thing first, I enabled AG feature by going to SQL config manager(I did this on both servers), you can see it says “This computer is not participating in a failover cluster”. Duuuh!

I have a database (Not_AO_DB) on which I want to scale my read/reporting work load. well, I went ahead and restored the database manually with norecovery on my second Instance as part of preparing the DB to join in my AG later on.

Nothing new so far right, Now….from SSMS, I choose to use AG creation wizard, gave my AG a name and notice the Cluster type selection in below screenshot, that should be NONE.

Proceed further and select the database(s) that you want to join in AG, I have “Not_AO_DB” database in my case.

Now notice the highlighted Failover mode, you can see there is only one option(Manual) which can’t be changed(Remember, no clustering means no built in Automatic failover). Set your desired Availability mode to either Synch or Asynch.

It is important to remember, All the good old rules for endpoints like TCP port communication and account permissions etc etc still needs to be in place. The actual SQL Server AG mechanics are same here with read scale AGs.

Time for rules check…Next…Next…Finish!

That’s it folks! That’s pretty much what it takes to create a read scale Availability group. Below is how my dashboard looked like after successful creation of my AG.

In the next post, let’s see how to failover a read scale availability group, Listener GOTCHAS and learn how it is different from a traditional AG.

Happy holidays!

Configure/Manage Error logs – SQL Server on Linux.

How to configure and manage SQL Server Error logs in Linux environment:

In this blog post let’s see how to configure and manage SQL Server error logs on an Instance running in Linux environment. I am sure most of you are already recycling (re-init) your error logs (exec sp_cycle_errorlog) on regular basis by scheduling a job and controlling the number of logs as needed on SQL@Windows. Well, If you are not doing so, please go ahead and do yourself a favor…configure your error logs. By default on SQL@windows, it keeps 6 archives (128 in SQL@Linux). Imagine where your SQL got restarted multiple times(let’s say 6 times) in short duration for whatever reason, Oops! your precious logs are already gone. See below screenshots on how to do that for SQL@windows,
Using object explorer in SSMS:


Using T-SQL:
USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 20
GO

Now let’s move on to SQL@Linux. If you notice the above T-SQL code, SSMS is basically updating windows registry values behind the scenes which isn’t possible in Linux. (Registry is an exclusive windows OS thing…Duh…..!) See below screenshot, you don’t even get configure option in SSMS under SQL Server logs (BTW, Did you notice the tiny Penguin!).

On my lab server with default settings, You can notice that I have 82 error logs as of now.(By Def, max 128 in SQL@Linux and 6 in SQL@Windows)!

Let’s say I want to set it to 20,  what I have to do is, use mssql-conf utility and specify the desired number.

sudo /opt/mssql/bin/mssql-conf set errorlog.numerrorlogs 20

The changes will take effect after the service restart and you can validate by reading the contents of mssql.conf file as shown below.

Note: Old error log files should be manually removed or moved as needed.

Hope this helps, Happy Monday!

SQL Server 2019 – VLDB struck in “In Recovery”!

Getting straight to the point, Chances are very high that you will run into the issue that we are going to see in this blog post when you try to restore a database larger than 30 TB running on SQL Server 2019 CU1.

Environment:

SQL Server 2019 CU1
DB1 – 6 TB.
DB2 – 30+TB.

Behavior:

Restoring 6 TB database – Everything goes normal as it should.
Restoring 30 TB database – Database stays in “In Recovery” state indefinitely. Infamous “Non yielding scheduler” stack dump gets created when the recovery process enters roll forward(REDO) phase. To give you an idea, look at below log entries.

Message

Recovery of database ‘VLDB’ (8) is 0% complete (approximately 138 seconds remain). Phase 2 of 3.
Recovery of database ‘VLDB’ (8) is 22% complete (approximately 7 seconds remain). Phase 2 of 3.

***Unable to get thread context for spid 0
* *******************************************************************************
** BEGIN STACK DUMP:
* MM/DD/YY HH:MM:SS spid 4368
** Non-yielding Scheduler
** *******************************************************************************
Stack Signature for the dump is 0x00000000000001BC
External dump process return code 0x20000001.
External dump process returned no errors.
Process 0:0:0 (0x110c) Worker 0x000001B39D42C160 appears to be non-yielding on Scheduler 1. Thread creation time: 13234458222710.
Approx Thread CPU Used: kernel 0 ms, user 70375 ms. Process Utilization 16%. System Idle 79%. Interval: 70579 ms.
Process 0:0:0 (0x110c) Worker 0x000001B39D42C160 appears to be non-yielding on Scheduler 1. Thread creation time: 13234458222710.
Process 0:0:0 (0x110c) Worker 0x000001B39D42C160 appears to be non-yielding on Scheduler 1. Thread creation time: 13234458222710.
Process 0:0:0 (0x110c) Worker 0x000001B39D42C160 appears to be non-yielding on Scheduler 1. Thread creation time: 13234458222710.
Process 0:0:0 (0x110c) Worker 0x000001B39D42C160 appears to be non-yielding on Scheduler 1. Thread creation time: 13234458222710.
Process 0:0:0 (0x110c) Worker 0x000001B39D42C160 appears to be non-yielding on Scheduler 1. Thread creation time: 13234458222710.

Note: I noticed this exact behavior consistently on multiple servers irrespective of what method we use to restore this DB.
Native restore – Fails
Attach/Detach – Fails
Restored using EMC DDBoost – Fails
Restore virtual database by presenting virtual data copies using third party tools such as Delphix – Fails.

Fix:
Drop the database which got struck in “In recovery” and apply 2019 CU4 or above.

I am not sure at what size SQL 2019 CU1 starts to panic, but it was happy with 6TB DB and caused misery for 30 TB DB. BTW…At the time of writing this blog post, we have CU5 available which I strongly recommend if you are planning to patch.

 

Happy Monday!

 

Uninstalling CUs/Downgrading SQL Server in Linux(RHEL7)

Back in 2011 I wrote an article on how to uninstall service packs in version SQL 2008/R2 which was quite a popular post at that time based on my wordpress stats. (Well, SQL 2008 was the very first version that allowed us to do that) Fast forward to year 2020 where we can have SQL Server running on Linux in production environments, let’s see how to achieve the same if you ever want to uninstall a cumulative update. Did you notice, I said CU, not service packs this time. See this post for more info on MSM.

First thing first, let’s see what we have got on the box. Pulling that information is quite simple, Run “sudo yum info mssql-server” which should return something like this.

Of course you can get this info from SQL or several other ways in Linux. Okay, now we know we got SQL Server 2019 CU5 running on this server to work with. Let’s just assume CU5 broke something in my database and I want to go back to CU4. How do I do that?

Run “sudo yum downgrade mssql-server-<your_desired_version_number>.x86_64“. Okay, so how do I get those version number details? Microsoft has those details maintained and updated regularly in their release notes. Check below links for 2019 or 2017 based on your version.
SQL 2019 release notes.
SQL 2017 release notes.

Also, We can get this information directly using YUM super powers as shown below…
sudo yum list mssql-server –showduplicates

If you want additional details for individual packages as shown below, just replace “list” with “info”, the command would be “sudo yum info mssql-server –showduplicates

Now that we have all the information what we need, let’s go to the actual fun part.

Downgrading from SQL 2019 Cu5 to CU4:
Command:
sudo yum downgrade mssql-server-15.0.4033.1-2.x86_64

As you can see it clearly states that CU5 was removed and CU4 was downloaded and installed successfully 🙂

BTW, the basic golden rule still remains the same,  you can’t downgrade to a lower version(from SQL 2019 to 2017, –> Nope, that’s not how downgrade works)! You are allowed to do whatever you want as long as you are staying at same version.

Install and open/run Azure Data Studio in CentOS

In this blog post let’s see how to install Azure data studio in CentOS and where/how to locate the executable if you are unable to find in GUI. For this demo, I am using CentOS 7 as my operating system. Installing ADS in CentOS is pretty straight forward process as detailed in Microsoft’s documentation here. My personal preference is to download rpm package and issue “yum install”. See below for screenshot tour of ADS installation on my machine.

sudo yum install azuredatastudio-linux-1.18.1.rpm

Where is Azure Data Studio in CentOS?
How to open Azure Data Studio in CentOS?

Okay…now what? Where to locate the executable and how do I open Azure data studio in CentOS? GUI in CentOS is not as user friendly as you can see in some other Linux OSs (Mint for eg or few other Ubuntu flavors of Linux). In windows you can locate the program in start menu or even in few desktop experience Linux distributions it’s extremely easy to just search in application center, but that was not the case for me in CentOS 7.

Well, I am going to use “which” to locate azuredatastudio executable/command.

As you can see it’s already in my /usr/bin with a symlink to its original location. Okay, so all I have to do is type “azuredatastudio” in my console from where ever I want to and voilaaaaaaaaa 🙂

Happy Monday!