SQL Server 2016 SP2 got released :)

Microsoft released SQL Server 2016 Service Pack 2 today. Visit https://support.microsoft.com/en-us/help/4052908/sql-server-2016-service-pack-2-release-information for all the details you need. There’s a looooong list of number of bugs fixed and enhancements made. Couple of DMVs and features from SQL 2017 were back ported as well.

Make sure you go all the way to the bottom of the page regarding GOTCHAS with uninstalling 2016 SP2 if needed. This is the section which I would like you guys to focus on if you are not on Enterprise Edition.

Cheers!

Advertisements

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.

How to create a SQL Server Instance on Amazon RDS

In this blog post let’s see how to create a SQL Server Instance on Amazon RDS (Relational Database Service). I will be using defaults for most of the part in this screenshot tour to keep it simple. This post doesn’t cover what a VPC is, What a Security Group is etc, Instead the focus of this post is to create a SQL Server Instance(Express) which is Free-Tier eligible, so that you can get your hands dirty playing withΒ  your cloud databases.

First and foremost login to your AWS console. You can sign up for 1 year free tier services.
Navigate to Services and locate Database->RDS.

1

Just to avoid any costs, check to display only free tier eligible services. As you can see SQL Server Express Edition is Free Tier eligible.

2

After selecting, Pick your version, License model, Instance Class(t2.micro is pretty basic 1 GB, 1 vCPU machine which is what we get for free) etc as shown below.

3

As I mentioned earlier, I am leaving all the values to default with publicly accessible as YES.

4

Choose your Backup retention period. I changed it to 0 from 7 (I don’t want any backups for this LAB).

5

Click Launch DB Instance and wait for few minutes(It took about 15 mins in my case for AWS to prepare my Instance)

6

Violaa! That’s it. It’s ready as you can see below.

7

How to connect to this RDS Instance using SSMS?

Copy the ENDPOINT without the port number, That’s your Instance name which you should be using to connect from SSMS or any other client tool. Select SQL Server Authentication and provide the user name and password which you’ve provided earlier while provisioning Instance.

8

As you can see now I am connected from my SSMS and If you are curios, the account which we did provided during setup has been created as a SQL login with below shown Server role membership.

9 10

Recently, Amazon has announced that we can directly restore the databases to RDS Instances using Backup files from On-premise SQL Servers. Just place the backup files in your S3 bucket(If you are new to AWS, just think S3 as your Backup Storage. S3 is not the actual storage for servers from which disks are carved out…S3 is just to store raw files) and restore from there. This makes DBA job so painless, instead of fighting with data migration services.(Hope Azure guys are listening to me πŸ™‚ )

That’s it for now guys! I would recommend you guys to spun up something in cloud in your leisure and have fun!