sreekanth bandarla

SSMS – Security Vulnerability Assessment(VA)

In this blog post, let’s explore one of the fantastic features added to our good old friend SSMS by Microsoft, Security Vulnerability tool. I am currently running SSMS 17.6, but this feature which we are going to see has been introduced in version 17.4. Okay….So, let’s see what this is all about.

I have a database named “ChickenNuggets” which I would like to know what are the potential security risks it has. Well, All I need to do is connect to SQL Instance from SSMS 17.4 or above and Right click on the database and go to tasks and navigate to VA and do a scan. See below screenshot for what I am talking about.

Give it a path and Wait for few seconds and voila…..you get something like this.

As you can see, it reported 10 failed checks for this DB. Also, You can select any item and approve as baseline if that’s normal in your environment, That way SSMS doesn’t include that particular item under failed list as it has been included in baseline.

As you can see it’s giving a detailed explanation along with what exact Query it ran for this particular check and the best part is it gives you remediation script as well.

Now, What If I want to scan something at Instance level, I could perform the same on Master Database.

That’s all good so far right? But there is still huge room for improvement. What if my instance has 100 databases? I hope you already guessed where I am going with this….Yes, That’s correct, I have to repeat this 100 times at individual database level. Well that sucks. Also, I don’t see anyway to export these results to different formats. It would be really nice if Microsoft gave us an option to export this to PDF format or HTML. But hey…this is a start and I am positive Microsoft is listening to community and these will be made available to us very soon in future releases of SSMS.

BTW only SQL Server 2012 Instances and later are supported, and of course Azure SQL Database 🙂 .This is extremely light weight tool which you can run at any time without impacting your workload. It just runs bunch of DMVs…Shouldn’t block anything. But hey, don’t trust anyone and anything posted online when it comes to your Production Databases. After all that’s our Bread and Butter!

Happy weekend folks.Cheers!

AOAG – SSMS 17.4…Surprise!

Howdy folks! So, I was installing SQL Server 2017 on my new Cluster lab setup and found something interesting with SSMS 17.4 when creating AG. Before going there, let me show you guys what has been changed with SQL 2017 setup.

Note: This is not a step by step setup walk through.

As you can see there is no option to install SSMS/SSRS from original SQL media.

Well, so I downloaded the latest and greatest version of SSMS as of today(which is V17.4).

Note: On my server, SQL DB Engine installation was completed in 3 minutes, where as it took about 17 minutes to Install SSMS!!

Well, now I was all set to create an Availability Group(from SSMS) and found something interesting in the wizard(It has a Read-Only Routing tab 🙂 ). Not sure when this was introduced, but initial releases of SSMS 2017 didn’t had this IIRC.

If you are curious, Prefer secondaries is the default backup setting.

So, below is the screenshot of how it looks like if we navigate to ROR tab. We can setup load balancing as well right here from this wizard which is an awesome addition IMHO.

We are not going to create routing lists here in this blog post, I will show you how to do that using GUI in one of my future posts.

Modern Servicing model – SQL Server

Microsoft made it official at last, starting SQL 2017 there won’t be any Service packs.

No more worrying about baseline build(s) for installing respective CUs. All we get is a single baseline build(which is RTM) starting SQL 2017.

FMI https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-the-modern-servicing-model-for-sql-server/

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.