SQL In Linux – MSSQLTREK

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!

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!

Managing SQL Server Services in Linux Environment

In the previous blog post we have seen how to Install SQL Server on Linux(CENTOS). In this post, let’s see how to manage SQL Server Services in Linux environment. After all as a DBA, we should know how to manage our database services 🙂 Let’s get started…

In Linux world, to manage services, we’ll be using command called “SYSTEMCTL“. We can check the status, stop,start,disable,enable any service running on a linux box using that command.

Syntax is very simple to remember: systemctl action ServiceName. Check this documentation for more details.
https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/7/html/System_Administrators_Guide/sect-Managing_Services_with_systemd-Services.html

Verify the current status of SQL Server services:

Syntax: systemctl status mssql-server

As you can see SQL Server Service is enabled and is running. Okay, let’s stop and disable them.

Stop and Disable SQL Server services:

Syntax: sudo systemctl stop mssql-server
sudo systemctl disable mssql-server
Once this is done, check the status of SQL services(See below screenshot for all the three steps)

Now, Let’s start SQL services(Since we have disabled the services as well besides stopping, let’s enable and then start SQL)

Enable and Start SQL Server Services:

Syntax: sudo systemctl enable mssql-server
sudo systemctl start mssql-server

It successfully created syslink right after enabling the service. Then I started SQL service, Let’s check the status of the service again.

Happy SQLing on Linux 🙂 Fun Fun….

Microsoft Loves Linux – Installing SQL Server on Linux

In this blog post, let’s see how to Install SQL Server on Linux. It’s been a while since Microsoft announced SQL on Linux, but I thought of waiting for a while before I set this thing in my lab. With the release of SQL Server 2017 CTP 2.0, It’s about time. Okay ,so let’s get started!

Note: I’ve Centos installed in my lab. That’s where I started learning/exploring Linux. I am installing SQL Server on CentOS (Which is exactly same as RHEL). Every single command shown in this blog post should work on RHEL as well.

Step 1: Connect to Linux machine and download config/repo file using CURL command and verify if it downloaded the file.

As you can see in the above screenshot, I logged in as root user. It’s not required, in fact not recommended to ever login as root user to perform any sort of activities on linux box.(Well, I guess It’s okay sometimes If you are Linux admin and/or you absolutely understand what you are doing 🙂 ). You can just make use of sudo instead. ( As long as you are listed in sudoers file or a member of wheel group, you should be able to use sudo)

Step 2: Now we know that the file exists, lets try installing SQL using YUM Package. I logged out of root and I am using my regular account “Sreekanth”. See below, It didn’t let me Install SQL because I didn’t have rights to use sudo.

As I mentioned above, I will be adding “Sreekanth” to wheel group by logging in as root again.

Now…logging in as “Sreekanth”, my regular user account, Let’s try it again…

Well, It works. If you notice, it says “Run sudo /opt/mssql/bin/mssql-conf setup to complete setup”…Okay let’s do that. That’s our next step.

Step 3:

This is the step where we accept license terms, set “sa” pwd …all that good stuff.

Done. That’s it guys! I am really impressed how easy it was actually to install SQL Server on Linux. It’s actually way faster than installing SQL on windows box. This entire process took less than 3 minutes for me! Wooohoooo!

Let’s check few other things before wrapping up. Let’s check the status of SQL Server service on our Linux box.

Okay…that confirms SQL service is actually Up and Running. Now, let’s open port 1433 in firewall.

Step 4: Let’s connect to this sql server instance from another client machine. (connect to IP address using the sa account which we provisioned earlier)

I see Agent XPs are disabled. Okay…let’s turn it on.

Give it a shot setting up in your own test area. Trust me, This reaaaaally feels good once you have SQL Server running on your Linux box 🙂 That’s it folks for today! Hope this blog post helps…Later.