SQL Server 2017, SSMS

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!

Advertisements
SQL Server 2008/20008R2, SQL Server 2012, SQL Server 2014, SSMS

Reviewing SQL Server Error Log – A Better approach!

How many of you have a habit of reviewing Error Log(s) of the SQL Server(s) which you support on a daily basis(At least on regular basis, if not daily)? If you are not doing that, go ahead and start making it as a practice from “Right now”. You can thank me later for this suggestion 😀

If you’re already doing that,then you are awesome…! But, let me ask this. How many times you get annoyed reviewing your error log(s), especially if you are looking for a specific error. Well, you can apply filter or you can simply query your error log from T-SQL. But what if you are only looking for all the severe Errors, total error count, Errors by frequency all at one place? Yes, you can write your own query and use SSRS for generating a fancy report. But Wait……What if I say SSMS already has this cool report inbuilt for us?

In this blog post I will show you a better way to decipher your current Error Log for Errors by using that hidden report.
It’s called “Number Of Errors” Report and you can get to this report by right clicking on “Management” Node -> Reports -> Standard Reports->Number of Errors in your SSMS as shown below.a

How it looks? See below…

1
As you can see, at a glance I know I’ve got 6 Severe Errors(Sev 18 or above) and 5 Moderate errors in my current error log. See below screenshots for how neat the info is once I expand those nodes.

Severe Errors:
2

You can see I’ve got the same 824 Error repeated 6 times. ( Well, I tried attaching the same corrupted ldf file 6 times for this demo)

Moderate Errors:
3

All errors by Frequency:
4

Is in’t nice? Personally, I liked this report…and thought of sharing what I’ve discovered with you folks 😀
Please ignore if you are already aware of this nifty report, if not…well, you know it now!

SSMS

Obtaining Pipe Delimited Results from SQL Server using SSMS

In this short Blog post let’s see how to fetch results in a Custom/pipe delimited format file directly from SSMS without any fancy SSIS Packages involved. In fact you can use SSMS for fetching result set as with any delimited value.

As you all know with all the default settings of your SSMS without any options enabled/disabled, we’ll get results of any SELECT Statement in a grid fashion as shown below.

In the above screenshot I pulled few columns from “AdventureWorks” Database with the default Settings. You can copy the results including column headers and paste into excel and create a delimited file. Or if you are looking for CSV or Tab Delimited file, you can achieve without much pain by just right click in the result set and choosing “Save results as” and selecting the required file format. Let’s assume the requirement is to create a Pipe(|) delimited file with column names as the first row in the file, or some other custom delimiter in the file. You can’t see those options while saving the results by default. But, it’s very simple to achieve this just by turning on few options in your SSMS.

Go to Tools->Options->Query Results->SQL Server->Results to Text as shown below

In the top right corner drop down the list and select Custom delimiter as shown below.

Once you choose Custom Delimiter, you’ll have an option to enter the Delimiter as you can see below.(I entered “|” for Pipe Delimited files in this example)

Click Ok and now the trick is to select “Results to Text” in your SSMS while executing the Query and you get a result set with a clean pipe delimiters as you can see below:)

Or choose “results to file” and execute the same to save the results directly to a file. You can see in the below Screenshot where I’ve selected the required option in my SSMS and saving the file as “Pipe”.

Now, I’ve navigated to that folder and opened the file with notepad where I got the file exactly as I was expecting to be as you can see below 🙂

As you can see we don’t need any fancy tools to get custom delimited results…All it takes is exploring our own SSMS  🙂

Hope this helps!

 

 

 

SQL Server 2012, SSMS, Windows

SQL Server 2012 RC0 on Windows 8

Today I got some leisure time and thought of Installing SQL 2012 on my Win8 machine and sharing my experiences at a very high level. Well,  I’ve Installed SQL Server 2012 RC0 on my Windows 8(Consumer Preview – Evaluation Copy, Build 8250) Machine.

To be honest, I was expecting few bugs and annoying errors. But let me tell you this, My Installation went Flawless. FYI, I’ve Installed only basic components(DB Engine – with Replication, SSIS,Complete Client Tools, SDK Connectivity).

Just a screenshot of how it looks like on Win8. Well, Technically there is no difference in how SSMS looks and how it behaves, but as most of you are already aware of…there is no START Button on my Menu bar on my Desktop! See below on the bottom Left corner.

So, where did I open SSMS from?? If you said “Metro Screen” yes, you are correct. See the below Screenshot for how Windows8 organized programs on my Metro Screen.

Hmm…this is something which really disappointed me. Basically, It created bunch of Apps for each exe! Imagine how ugly your Metro Screen gets if you Install all the components of SQL Server!! I Wish it was smart enough to create at least folders/Groups and place related exe’s accordingly.

Btw, if you want to Run SSMS as Admin, just right click on the App and you will see the available options in the bottom of your Screen as shown below. ( This applies to all the Executables)

SQL Server 2012, SSMS

How Intelligent is SQL 2012 IntelliSense??

I’m assuming whoever reading this post is already aware of SQL Server 2008 SSMS brand new Enhancement “IntelliSense”. Being said that, was “IntelliSense” really Intelligent enough upto SQL Server 2008R2? IMHO, It’s not really smart enough to read our minds! But WHY?? Because, It’s not smart enough to recognize a word in middle of your Object name! For Example, if you need information regarding DB Mirroring Endpoints, you’ve to actually start your typing with  “SYS.DATABASE_MIRRORING_*******”. With all the new hundreds and hundreds of DMO’s available, it’s really difficult to remember each and every starting word of a DMO. It would be really nice if SSMS is smart enough to return all the Objects if it finds a word anywhere in the Object name. I mean, in the above example It should display a list of all DMO’s even though we just type “SYS.MIRR” irrespective of where the word “MIRR” actually present.

You know what, with SQL 2012 that is how it works 🙂 This Simply Rocks!! It makes our DBA life so simple…Yeyyyyyy 🙂

See below Screenshots for better understanding what I meant in the above paragraph.

SSMS 2008R2:

As you can see in the above Screen cap, it’s no where close to Mirroring DMV’s 😦 In other words, I’ve to remember the actual starting words in that DMV!!

SSMS 2012:

Here you can see a list of all the DMO’s which contain a word “Mirr” any where in the Object! This is what I call it as really “Intelligent”. This is something which is really really Cooooooool!!!

SQL Denali CTP3, SSMS

Denali Databases Status in Object Explorer

Howdyyy…Hope all is well folks! It’s been a long time since I wrote something….Was quite busy with some crazy stuff going around!! Anyways this would be a another small/Quick Blog post regarding a small enhancement msft offered in DENALI SSMS.

Okay, let’s jump into the content! I’ve created few SUSPECT databases on one of my SQL Instances and let me show you how DENALI CTP3 SSMS is showing in it’s Object Explorer and how SQL Server 20008R2 SSMS shows in it’s Object Explorer.

SQL Server 2008R2 SSMS:

SQL Server DENALI CTP3 SSMS:

As you can see, in DENALI the Database(s) are appended with (Recovery Pending) status which is more self explanatory where as in 2008R2 SSMS, the Databases are shown as nothing! Even though this is not great enhancement, it’s good to see this in our OE 🙂

SQL Server 2008/20008R2, SSMS

SSMS(SQL Server Management Studio) Standard Reports and my Personal Favs!!

If you are already working on SQL Server 2008 since decent amount of time, you might have already known the inbuilt standard reports which we can popup Just by right clicking on an object in our Object Explorer.

In this Blog post, i would like to show some interesting In Built Reports which you(we) as a DBA might(Should) be interested! Before going any further, please see the below Screenshot for what i’m actually referring to if you are new to SQL Server.

Server Dashboard: This is a very useful report especially for a DBA to understand his Instance Configurations, Settings at a very high level in a single glance. I really like this guy and would recommend you to open this fab report as frequent as you can on your Server.

As you can see, at a single glance it is giving my Server(Well, It’s actually SQL Instance) startup time, Instance name, Version, Stand alone or clustered, AWE Settings, memory settings, CPU’s visible to SQL and many more….My personal favorite in this report is “Non Default Configuration Options“. Basically this tiny section is giving me a clear picture of settings which are deviating from default SQL Server settings, which is really reallyyyyyyyyyyy useful for a DBA. Let’s assume a scenario where a DBA from your team is leaving and he/she is doing a Knowledge Transfer to you. Just by pulling this info, you can ask him/her “Hey i’m seeing the following Non Default Configuration Options on this Instance, Can you pls. explain why??…”

Transaction Log shipping Status: This is a single place where you can get all the cool information regarding all the thresholds, last backed Up t-log, copied t-log backup, restored t-log backup(with File names and LSN details as well…Huhuuuuuuuuu That’s awesome!!) Note: For accurate and more meaningful info, you’ve to pull this report from both your Prod and DR Servers, to see if your DB is out of sync.

Schema Changes History: You can see the DDL Changes being made on your Databases. For example If some one dropped a Table and if you as a DBA are looking for an option to track him/her down….Well, this is one of the places 🙂

Note: The above information will be pulled from SQL Server Default Trace. So the Data will be not stay for ever! You can see only the info what has been captured by your DEF trace. very Imp….!

Sooo…are these the only Inbuilt Reports(which you are seeing in the first Screenshot) we have in SSMS?? Nope!!  If we Right click on the Database in Object Explorer, you’ll get completely different set of reports! See the below Screenshot.

My personal favorites are

Backup and Restore Events: Please see the below Screenshot!

It gives me the Avg time it is taking for backup Operations! You can use this as a Benchmark for your backup duration (well, there is much better way to pull that info from MSDB, I’m Just talking about basic Benchmarking here ;))  If you expand Successful Backup/Restore  Operations, it tells you what was backed up/restored, the location of files, when it was performed and who(the Login) performed the operation…Well one more way to track these operations!!

Disk Usage:

You might think that it only Shows, the sizes of your Data and log files on your Disk. Well, there is one of the Most important hidden gem in this report. Auto Shrink/Auto Grow Events as you can see below. Tadaaaaaaaaaaaa!

This gives me a clear picture that my log file has auto grown several times on 9/7/2011, Which means as a DBA I’ve to consider changing my File Growth settings on my database. This is the Most important factor which might be hindering performance of your Database( For the sake of  this example I’ve changed the autogrowth options to 1 MB on data and 10% on my log file and i inserted 10000 rows. Well this is just my test lab, but imagine the same happening on your Live Production database!!!! Really Really bad).

Note: If you see any Auto Shrink Operations in the above report, ha ha you are gone. It’s time to take action immediately!! I’m not going to deal with what Shrinking does to a Database in this post, for now…Just remember, Setting Auto Shrink option turned ON on any database is the worst possible thing you could potentially do to your Database!!

I recommend to explore all these Inbuilt, completely free and most fabulous reports and get familiarize with what each report can do for you as a DBA. Hope this Post helps!