SQL Server – SafePeak “Logon Trigger” Feature for Managing Data Access

Lately I received an interesting question about the abilities of SafePeak for SQL Server acceleration software:

Q: “I would like to use SafePeak to make my CRM application faster. It is an application we bought from some vendor, after a while it became slow and we can’t reprogram it. SafePeak automated caching sounds like an easy and good solution for us. But, in my application there are many servers and different other applications services that address its main database, and some even change data, and I feel that there is a chance that some servers that during the connection process we may miss some. Is there a way to ensure that SafePeak will be aware of all connections to the SQL Server, so its cache will remain intact?”

Interesting question, as I remember that SafePeak likes that all traffic to the database will go thru it. I decided to check out the features of SafePeak latest version (2.1) and seek for an answer there.

A: Indeed I found SafePeak has a feature they call “Logon Trigger” and is designed for that purpose. It is located in the user interface, under: Settings -> SQL instances management  ->  [your instance]  ->  [Logon Trigger] tab. From here you activate / deactivate it and control a white-list of enabled server IPs and Login names that SafePeak will ignore them.

Click to Enlarge

After activation of the “logon trigger” Safepeak server is notified by the SQL Server itself on each new opened connection. Safepeak monitors those connections and decides if there is something to do with them or not. On a typical installation SafePeak likes all application and users connections to go via SafePeak – this way it knows about data and schema updates immediately (real time). With activation of the safepeak “logon trigger”  a special CLR trigger is deployed on the SQL server and notifies Safepeak on any connection that has not arrived via SafePeak. In such cases Safepeak can act to clear and lock the cache or to ignore it.

This feature enables to make sure SafePeak will be aware of all connections so SafePeak cache will maintain exactly correct all times. So even if a user, like a DBA will connect to the SQL Server not via SafePeak, SafePeak will know about it and take actions. The notification does not impact the work of that connection, the user or application still continue to do whatever they planned to do.

Note: I found that activation of logon trigger in SafePeak requires that SafePeak SQL login will have the next permissions: 1) CONTROL SERVER; 2) VIEW SERVER STATE; 3) And the SQL Server instance is CLR enabled;

Seeing SafePeak in action, I can say SafePeak brings fantastic resource for those who seek to get performance for SQL Server critical apps. SafePeak promises to accelerate SQL Server applications in just several hours of installation, automatic learning and some optimization configuration (no code changes!!!).

If better application and database performance means better business to you – I suggest you to download and try SafePeak. The solution of SafePeak is indeed unique, and the questions I receive are very interesting. Have any more questions on SafePeak? Please leave your question as a comment and I will try to get an answer for you.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQLAuthority News – The Best Quotes of “Who Wrote This?” Contest

I am a frequent reader of Brent Ozar PLF, it is one of my favorite blogs. A recent post announced a “Who Wrote This?” contest to see if readers could tell their three contributors apart based on some writing samples. Here are my favorite lines from the sample paragraphs, from each of the three “mystery authors.

Topic 1: Working with Bad Managers

Mystery Author A – “Working with bad managers means working against my own happiness, and I’ve come to learn that there’s no changing bad managers.”

I love this line because, as anyone who has had a bad manager knows, often a lot of self-doubt rises up. We all have to remember that sometimes the problem is out of our control.

Mystery Author B – “Mentor your manager just like you would mentor a junior DBA.”

Having a bad manager can be extremely depressing, and we often feel out of control. But we all need to remember that our work is a two-way street, and that sometimes we can subtly influence those above us.

Mystery Author C – “The trick to working for all bad managers is to remember that they aren’t your parent. Take charge of your career.”

We all also need to learn not to play the blame game. Would you rather stay in a place where you are unhappy, or would you rather take charge of your life? I hope most people would pick the latter.

Topic 2: Working with Remote Teams

Mystery Author A – “Like almost anything else the key is to make sure that everyone on the team has an understanding of how and when communication will occur.”

Communication is so important. I cannot over emphasize how much. And this one line captures how I feel and even communicates the idea clearly!

Mystery Author B – “The key to remote team success is verifiable trust: feeling confident that invisible team members are doing the right amount of the right thing at the right time.”

I think this line not only captures the key aspects of remote work – verifiable work and trust – but there were so many lines that followed that I loved and could not fit here. The whole paragraph is a list for successful remote work. Everyone could benefit from reading it.

Mystery Author C – “What seems clear, precise, and specific in one time zone comes across as vague, soupy, and just plain weird in another.”

You know what? I just love this description. The author is right – sometimes vague e-mails really do seem soupy and weird!

Topic 3: Working with Your Nemesis

Mystery Author A – “Every job is temporary, but your reputation stays with you.”

Everyone needs to remember this. The workplace is meant to be a professional arena, and many people have the opinion that work is temporary and disposable. No one wants to work with co-worker like that.

Mystery Author B – “Unhealthy conflict is going to lead to leaving three week old tuna fish sandwiches in someone’s desk drawer.”

Sometimes humor really is the best policy!

Mystery Author C – “Oh no, it’s that guy.”

This might seem like a weird phrase to choose as my favorite from an entire paragraph. But the whole piece was written in the form of a story of co-workers getting drunk and plotting against a nemesis. It was too funny to overlook, but too long to post here. A must read!

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Function: Is Function – SQL in Sixty Seconds #004 – Video

Today is February 29th. An unique date which we only get to observe once every four year. Year 2012 is leap year and SQL Server 2012 is also releasing this year. Yesterday I wrote an article where we have seen observed how using four different function we can create another function which can accurately validate if any year is leap year or not. We will use three functions newly introduced in SQL Server 2012 and demonstrate how we can find if any year is leap year or not.

This function uses three of the SQL Server 2012 functions - IIF, EOMONTH and CONCAT. When I wrote this function, this is the sortest function I ever wrote to find out leap year. Please watch the video and let me know if any shorter function can be written to find leap year.

More on Leap Yer:
Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function
Date and Time Functions – EOMONTH() – A Quick Introduction
Script/Function to Find Last Day of Month 

I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Detecting Leap Year in T-SQL using SQL Server 2012 – IIF, EOMONTH and CONCAT Function

Note: Tomorrow is February 29th. This blog post is dedicated to coming tomorrow – a special day :)

Subu: “How can I find leap year in using SQL Server 2012?

Pinal: “Are you asking me how to year 2012 is leap year using T-SQL – search online and you will find many example of the same.”

Subu: “No. I am asking – How can I find leap year in using SQL Server 2012?

Pinal: “Oh so you are asking – How can I find leap year in using SQL Server 2012?

Subu: “Yeah - How can I find leap year in using SQL Server 2012?

Pinal: “Let me do that for you – How can you find leap year in using SQL Server 2012?

Indeed a fun conversation. Honestly, only reason I pasted our conversation here is – it was fun. What he was asking is that how to do it using new functions introduced in SQL Server 2012. Here is the article I have written which introduces all the new functions in SQL Server 2012 Summary of All the Analytic Functions – MSDN and SQLAuthority and 14 New Functions – A Quick Guide.

There are many functions written to figure out to figure out if any year is Leap Year or not. The same I have written using T-SQL function over here.

CREATE FUNCTION dbo.IsLeapYear (@year INT)
RETURNS INT
AS
BEGIN
RETURN
(IIF(DATEPART(dd,(EOMONTH(CONCAT(@year,'0201')))) = 29,1,0))
END
GO

What I really like is that I was able to use three newly introduced function in SQL Server 2012 in above script. You can read more about them here. IIF, EOMONTH and CONCAT.

You can validate above query by running following script.

SELECT dbo.IsLeapYear('2011') 'IsLeapYear';
SELECT dbo.IsLeapYear('2012') 'IsLeapYear';
GO

You will get result 1 if the year is leap year and 0 if year is not leap year.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Identifying guest User using Policy Based Management

If you are following my recent blog posts, you may have noticed that I’ve been writing a lot about Guest User in SQL Server. Here are all the blog posts which I have written on this subject:

One of the requests I received was whether we could create a policy that would prevent users unable guest user in user databases. Well, here is a quick tutorial to answer this. Let us see how quickly we can do it.

Requirements

  1. Check if the guest user is disabled in all the user-created databases.
  2. Exclude master, tempdb and msdb database for guest user validation.

We will create the following conditions based on the above two requirements:

  1. If the name of the user is ‘guest’
  2. If the user has connect (@hasDBAccess) permission in the database
  3. Check in All user databases, except: master, tempDB and msdb

Once we create two conditions, we will create a policy which will validate the conditions.

Condition 1: Is the User Guest?

Expand the Database >> Management >> Policy Management >> Conditions

Right click on the Conditions, and click on “New Condition…”. First we will create a condition where we will validate if the user name is ‘guest’, and if it’s so, then we will further validate if it has DB access.

Check the image for the necessary configuration for condition:
Facet: User
Expression:
@Name = ‘guest’

Condition 2: Does the User have DBAccess?

Expand the Database >> Management >> Policy Management >> Conditions

Right click on Conditions and click on “New Condition…”. Now we will validate if the user has DB access.

Check the image for necessary configuration for condition:
Facet: User
Expression:
@hasDBAccess = False

Condition 3: Exclude Databases

Expand the Database >> Management >> Policy Management >> Conditions

Write click on Conditions and click on “New Condition…” Now we will create condition where we will validate if database name is master, tempdb or msdb and if database name is any of them, we will not validate our first one condition with them.

Check the image for necessary configuration for condition:
Facet: Database
Expression:
@Name != ‘msdb’
AND @Name != ‘tempdb’
AND @Name != ‘master’

The next step will be creating a policy which will enforce these conditions.

Creating a Policy

Right click on Policies and click “New Policy…”

Here, we justify what condition we want to validate against what the target is.

Condition: Has User DBAccess
Target Database: Every Database except (master, tempdb and MSDB)
Target User: Every User in Target Database with name ‘guest’

Now we have options for two evaluation modes: 1) On Demand and 2) On Schedule

We will select On Demand in this example; however, you can change the mode to On Schedule through the drop down menu, and select the interval of the evaluation of the policy.

Evaluate the Policies

We have selected OnDemand as our policy evaluation mode. We will now evaluate by means of executing Evaluate policy. Click on Evaluate and it will give the following result:

The result demonstrates that one of the databases has a policy violation. Username guest is enabled in AdventureWorks database. You can disable the guest user by running the following code in AdventureWorks database.

USE AdventureWorks;
REVOKE CONNECT FROM guest;

Once you run above query, you can already evaluate the policy again. Notice that the policy violation is fixed now.

You can change the method of the evaluation policy to On Schedule and validate policy on interval. You can check the history of the policy and detect the violation.

Quiz

I have created three conditions to check if the guest user has database access or not. Now I want to ask you: Is it possible to do the same with 2 conditions? If yes, HOW? If no, WHY NOT?

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Standards Support, Protocol, Data Portability – 3 Important SQL Server Documentations for Downloads

I have been working with SQL Server for more than 8 years now continuously and I like to read a lot. Some time I read easy things and sometime I read stuff which are not so easy.  Here are few recently released article which I referred and read. They are not easy read but indeed very important read if you are the one who like to read things which are more advanced.

SQL Server Standards Support Documentation
The SQL Server standards support documentation provides detailed support information for certain standards that are implemented in Microsoft SQL Server.

Microsoft SQL Server Protocol Documentation
The Microsoft SQL Server protocol documentation provides technical specifications for Microsoft proprietary protocols that are implemented and used in Microsoft SQL Server 2008.

Microsoft SQL Server Data Portability Documentation
The SQL Server data portability documentation explains various mechanisms by which user-created data in SQL Server can be extracted for use in other software products. These mechanisms include import/export functionality, documented APIs, industry standard formats, or documented data structures/file formats.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – A Cool Trick – Restoring the Default SQL Server Management Studio – SSMS

“I do not know where my windows went!”

“I just closed my object explorer and now I cannot find it.”

“How do I get my original windows layout back in SQL Server Management Studio?”

“How do I get the window which was there in left side back again?”

Since last 2-3 years, every single day I receive more than 5 emails on SSMS and its layout. For the beginners it is very common to get confused when they attempt to change SQL Server Management Studio’s windows layout. They often change the layout and are not able to get the original layout back. Often people do not change the layout whole of their life, leading to uncomfortable feeling when they go to another’s computer where the windows are differently placed.

Today’s blog post is dedicated all the beginners in SQL Server. It is extremely simple to reset the SSMS layout to default layout. The default layout involves 2 major things 1) Object Explorer on left side 2) Query Windows on right side (80% screen estate). Personally I am so used to this as well that if there is any other changes in the same, I do not enjoy working on the environment.

Well, the solution to rest the SSMS layout is very simple. One can do it in split seconds.  To restore the default configuration, on the Window menu, click Reset Window Layout.

Have you ever used this feature? Do you feel uncomfortable when SSMS layout is not in default state? How do you address this situation?

Reference: Pinal Dave (http://blog.sqlauthority.com)