This backend version is not supported to design database diagrams or tables. (MS Visual Database Tools)

This morning one of our developers contacted me regarding this error message he got when he tried to design a table from his SSMS.

1

Also, he reported that his SSMS goes to “Not Responding” state when he tries to edit rows using his SSMS. So…What’s happening?

He was trying to edit a table within a database which is running on SQL Server 2014 version using his SSMS 2012.  Now, all he needs is upgrading his client tools to 2014. Remember, you can manage/manipulate older versions of your SQL Servers using your 2014 SSMS, However be prepared to face these annoying issues if you are trying the other way.

Happy weekend folks!

 

Importance of reviewing your SQL Server Error Logs.

Few weeks ago I posted a poll here asking for how often do you review your Error logs. As promised, here comes the follow up post:)

Unfortunately, 45.45% out of 11 people said “Never and I’ll open Error log only If I want to investigate something” . IMHO, this is not a good practice(Especially if you want to be an outstanding DBA). Please don’t get me wrong…Am not saying that you are not an awesome and outstanding DBA! ( Well, People call you a DBA because you are already outstanding 🙂 )

Okay…….let  me say this guys, Start reviewing your Error logs daily as the first thing when you get into your cubicle/desk/office. Trust me, there will be lot of surprises(at least a few) based on number of Servers(Instances I should say) you manage. SQL Server Error log is really one of the best buddies a SQL DBA has got for free besides default trace 🙂 . I simply love these two things in SQL Server which provide so much useful information in case of investigating something(At no additional cost/overhead).

A simple real life use case: SQL error log records all the failed login attempts by default, if you keep an eye(or even Just glance over it) you should know if there are any unwanted/unsafe login attempts(Which are failed) hitting your SQL Instance. For example…I see quite a few times, incoming connections from a particular host attempting to connect as “sa”. Error Log records IP address of the machine and the time stamp of course. I can just do a NSLOOKUP and check with respective owner of the machine on what’s happening. This is just one useful scenario, there are tons of use cases which you can come up with!

Believe me guys, there will be lot of surprises for you and lot of things you’ll discover about your own SQL Server Instance which you don’t know, just by reviewing your error logs on daily basis for a week or two!

Most of the times, I hear people saying ” I am responsible for managing multiple Instances, I really don’t have time to get into each of my SQL Server and review error log. It will eat up my entire day“. Well, you really don’t have to do this. All you have to do is just setup a job and automate which basically reads all the error logs from all of your servers and generates a fancy report and sends you an email.(All you need is to just create a simple SSIS package and SSRS for fancy reports) or may be Powershell could help you.

If you just have handful of SQL Instances to manage, see this blog post.

Basically, This will allow us to be “Proactive” and not being “reactive”.  Preventing something bad happening to your database as opposed to fixing will really make you stand tall in front of your management and your peers. Am not saying, by reviewing Error logs daily you can literally prevent any issue from being happening. But you will be definitely able to reduce number of “OOPS…” moments!
IMHO, Even though part of DBA job is to troubleshoot/fix database related issues, our prime time goal as a DBA should be  trying to prevent those issues from occurring at the first place!
Let me know if you need any help automating this process. I will be glad to point you in the right direction.

Cheers! Happy friday 🙂

Buffer Pool Extension – SQL Server 2014

With SQL Server 2014, Microsoft is bringing a lot of features/enhancements into the wild, one of them is “Buffer Pool Extension” AKA BPE. ( Enterprise edition Only). With RTM, MSFT announced that even Standard edition has this feature.

so, what is BPE?  The name itself is pretty much self explanatory…It’s an extension for your Buffer pool. yes, with this you can use any non volatile storage device(SSD’s preferred) as an extension for your SQL Server Buffer Pool. Basic idea here is to eliminate requirement of “adding more physical RAM”, Instead you can mount a new Drive and tell SQL Server to treat that newly created drive as Buffer Pool. Sounds very interesting right? But there are few limitations Per Microsoft SQL Server Team’s Blog which we should keep in mind.

Limitations:
Enterprise Only Feature.
Might not be useful for OLAP/DSS Work loads.
Even in OLTP workloads, improvements are very limited for Write-heavy work loads.
If your server got more than 64 GB of RAM, don’t expect much from this.

Personally, for me these limitations are really frustrating. On one hand, this is Enterprise only feature and on the other hand, they are saying don’t expect much from this if your SQL Server has got already more than 64 GB of RAM allocated. What are the chances, any DBA would build a new SQL Server 2014(Enterprise) with less than or equal to 64 Gigs in 2014 or 2015? I would say almost “NIL”.

It Offers:
Performance Gain on Read heavy OLTP Work Loads.

MSFT recommendations:  (From SQL Server team Blog post)
Use High throughput SSD for better results.
Start from 4x-10x of the Memory available to SQL Server.

How to Enable BPE :
Step 1:
I’ve created a new Drive(BPE) for this demo purpose as shown below.

1

Step 2:
Before enabling BPE, Let’s check max memory setting I’ve got on this Instance.(512 MB as shown below)

2

Step 3: 
I’ve tried enabling BPE with 500 MB which actually failed. Reason – Buffer Pool extension size must be larger than current memory allocation threshold.(See below screenshot)

3

Well, now I increased the size to 600MB and now I got a different Error message(See below)!

4

Am not sure why this happened, but I’ve created a new Drive (F$) with 2 GB size and it worked this time without any issues as you can see below!!!…I will investigate further why this happened some time later.

5

6

Examine from DMV:

7

How to disable BPE: 

8

Can We alter the size of BPE?
Nope, we can’t. We have to basically disable and recreate it with new size.

Caution: When you disable BPE, chances are….you will get into significant Memory Pressure situation  and hence  increased IO Pressure.

Bottom line: IMHO, this idea sounds interesting, but I don’t think I would implement this on a production SQL Server considering the limitations it has(YMMV). I would rather convince  my management for purchasing real Memory which has no hand cuffs and no limitations!

After all….What are the chances my client doesn’t have money to purchase more DRAM for the sake of true performance boost when they’ve enough money to purchase High Performance SSD’s just for the sake of BPE(which promises me only limited performance gains)? . Remember folks…..now a days physical RAM is very cheap.

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!