I can speak English, I learn it from a book

Posted on February 16, 2012 by

Many many years ago when I was still in short trousers I started to take an interest in Movies. My parents had completely different sleeping patterns and my Father was an irritatingly early riser -a fact that would irritate me right into my adolescence and beyond. My mother was the opposite and (like me) very much a night owl. This resulted in there being a period of around 3 to 4 hours before my Mother went to bed, where the Television would be switched to whatever channel she wanted to watch -in other words not Sport.

I come from Barcelona!

As I approached the age that staying up beyond 9 pm. was acceptable I tended to sit with her and watch whatever she was watching. Her tastes were very varied and cosmopolitan and we would tend to watch different film genres over seasons. One of the first genres that I remember watching with her were the 1930s and 1940s horror Movies where together we absorbed every Frankenstein (starring Boris Karloff), every Dracula (starring Bela Lugosi) and every Wolfman (starring Lon Chaney Jr.) not to mention the sequels starring Abbott and Costello!

Over a very long time we watched an enormous and varied collection of films and since her tastes where not just limited to English language films; we watched films from France, Belgium, Germany, Japan and any other country we could get on our Gogglebox. Over time reading the subtitles whilst watching each film became as natural as the spoken word to us.

To this very day I meet people who refuse to watch anything that is not spoken in their own language (for whatever reason) and fail to realise just how many amazing films they are missing out on. A long time before Japanese Horror film remakes by Hollywood was the “norm”, I sat with a friend who held the believe that subtitled films were impossible to watch but convinced them to join me for the evening to watch a certain Japanese Horror Classic. The evening was a rather scarily enjoyable experience and the film in question was called Ringu -you will by now know its remake as “The Ring”. Let me tell you that even if you have seen the somewhat half-hearted Hollywood effort, the original film in its natural form is quite something to behold. Possibly one of the scariest films of all time and guess what? I watched it years and years before most Westerners (and perhaps even now the majority will not have watched the subtleties of the original).

Time to start running

I am sure by now you have been asking yourself “What is the point to all this?”  and “How does it relate to SQL Server?”. Well let me tell you…

I have just been speaking privately with Rob Farley (blog|twitter) and have found out about the plans for the next iteration of 24 Hours of PASS. What he has told me is that each session will have Live Closed Captioning in 15 different languages!!! Can you believe that? This means that all the sessions will be accessible to people who speak other languages other than English and makes 24 Hour of PASS a truly international event for the very first time. As Rob put it “if someone in China asks you a question in Chinese… you’ll be able to read it in English and they’ll read your answer in Chinese”.

If you are one of those non-English language SQL professionals that has ’till now not been able to view the English language 24HOP (you are obviously reading this blog using Google Translate :) ) and want to have access to valuable, technical, up to date material -then come and join the party. And remember my story, please don’t forget to introduce a friend!

To register for all 24 sessions go click here without delay, and don’t forget to join me for my session “Moves like Jagger – Upgrading to SQL Server 2012“. You should also check out Rob’s post about these excellent changes by clicking here.

Posted in 24HOP, humour, publicspeaking, sqlpass, SQLServerPedia Syndication | 2 Comments

What are the impersonation rights for my database users?

Posted on January 30, 2012 by

From time to time I get thrown the odd request to provide various bits of information from the SQL Server environment and most recently I was asked about a group of databases that required migrating to another instance. For some reason there had been a little bit of a panic about the impersonation rights in each of the databases and their configuration. The request was *ahem* kindly passed to me. The number of database users was huge in each database and performing a manual lookup through the SSMS GUI got rather tiresome very quickly (after about the second user).

My reply was that as long as we ensured each database was migrated with associated logins and mapped correctly (to prevent orphaned users) we didn’t really need to worry about the impersonation, since it would be contained within the databases and unaffected by the move. Unfortunately, there was no getting away from it, they still wanted a report.

I first decided to perform a web search for the term “list impersonate rights” which led my to quite a useful post by Kendal Van Dyke (blog|twitter) called “Hey Mr. DBA, What Permissions Do I Have On This Database?” but I was already familiar with the examples listed, having previously come across the fn_my_permissions and fn_builtin_permissions functions in Books Online. They did what I wanted, but really the wrong way around. To use them I would have to write a cursor based solution which would be a little long winded.

Thankfully most SQL Server problems are relatively easy to solve by applying a very small amount of grey matter to them. I decided to turn my attention to the system catalog views and started off with sys.database_principals. Using the Microsoft SQL Server 2008 R2 System Views poster which is helpfully stuck to my office wall I focused in on this view and my attention was drawn to the sys.database_permissions catalog directly underneath. Surely this would be the most likely candidate for the IMPERSONATE grants?

Wall posters can come in handy!

After a very quick query of several of the databases provided to me I noticed that in one of them under the column permission_name, several rows contained the entry “IMPERSONATE” with a state_desc of “GRANT”. Bingo! That was just what I wanted. Time to write the query….

So we can test the query, let us first create a new database, several logins, several users from the logins and grant impersonation rights to some of the users to some of the others:-

CREATE DATABASE Impersonation;
GO
USE Impersonation;
GO

CREATE LOGIN login1 WITH PASSWORD = 'Password1';
CREATE LOGIN login2 WITH PASSWORD = 'Password2';
CREATE LOGIN login3 WITH PASSWORD = 'Password3';
CREATE LOGIN login4 WITH PASSWORD = 'Password4';

CREATE USER user1 FROM LOGIN login1;
CREATE USER user2 FROM LOGIN login2;
CREATE USER user3 FROM LOGIN login3;
CREATE USER user4 FROM LOGIN login4;

GRANT IMPERSONATE ON USER::user4 TO user1;
GRANT IMPERSONATE ON USER::user4 TO user2;
GRANT IMPERSONATE ON USER::user1 TO user3;
GRANT IMPERSONATE ON USER::user1 TO user2;

Next we should connect under login1 and change context to our Impersonation database; then attempt to impersonate user4 to test that everything works:-

user4 impersonation works

USE Impersonation
GO
EXECUTE AS USER = 'user4';
SELECT USER_NAME();

OK so we know all this works and we know how the rights are assigned. Lets write our query:-

USE Impersonation
GO
SELECT DB_NAME() AS 'database'
	,pe.permission_name
	,pe.state_desc
	,pr.name AS 'grantee'
	,pr2.name AS 'grantor'
FROM sys.database_permissions pe
	JOIN sys.database_principals pr
		ON pe.grantee_principal_id = pr.principal_Id
	JOIN sys.database_principals pr2
		ON pe.grantor_principal_id = pr2.principal_Id
WHERE pe.type = 'IM'

This query produces a rather simple result set as you see in the next picture:-


And there we have it -all impersonation rights are listed for the database in question. I was able to go onto query the five databases in question and provide a very simple report which would have taken me a very long time to perform using the SSMS GUI.

Whilst nothing I have demonstrated in this article is particularly difficult, it does highlight the multitude of different ways to perform operations in SQL Server. Just make sure you choose the easiest route!

Posted in database, dmv, reporting, security, SQLServerPedia Syndication, tsql | Leave a comment

SQLRally and beyond

Posted on January 25, 2012 by

I am almost ashamed to say that I have let over a month pass since my last post and truth be told I have come close a few times without never actually pressing that submit button.

I shan’t bore you with my excuses or reasons for my inactivity but what matters is I’m back!

I’ve been seeing an increasing amount of noise about SQLRally Dallas and I felt that I wanted to share my own personal experience with you about this rather unique event (in Orlando) -for it quite literary changed my life.

Six years or so ago I joined a company with big hopes and promises and after starting a small family (and growing it further after joining) I was looking for a period of stability and a platform to learn, improve and share my skills. In short I knew that come what may, as good or bad as things might get, I was there for the long haul.

Not long after joining, my skills and talent for other technologies were recognised and when the need for a specialized “crack” team supporting a critical troublesome in-house application, I was identified as their man. This position (I was told) would be permanent and was absolutely vital to the running of the company operation. There was one small problem -I didn’t want it and regretfully declined. My reasons were many but I had joined the company as a SQL Server SME and saw the very broad set of skills required to support one single application platform as a regression in my ever growing expertise in SQL Server (and increasing knowledge in Oracle) and did my best to explain this.

Unfortunately for me, the Technical Director at the time (let’s give him a fictitious name of Dennis) told me in no uncertain terms that this would be the last time I ever refused him. Three months into my new job, everything had turned sour overnight simply due to me being good at what I do.

Years would pass and with each new year would bring a new job offer somewhere else for an ever increasing Salary, but I would always find an excuse not to take it -probably the thought of working for someone else again and repeating the same mistake was always the biggest reason to avoid saying yes.

Then something happened.

Although I have been helping in forums and the like on and off for a long period of time which you can read a little more about in this post (Standing upon the shoulders of Giants) I always felt that I had more to give, more to share, more to say and more to learn. I decided to submit my first ever public presentation to SQLBits 7 and surprisingly managed to secure a speaking slot! As daunting as the whole thing was, I think I managed to pull it off -and if nothing else I know that I learnt LOADS during my weeks and weeks of hard graft of preparation putting it all together.

About a month or so later I took a week off work and self financed a trip to the SQLPASS 2010 Summit (my very first time) and met some absolutely amazingly talented people which is probably another story for another time.

Whilst in Seattle I decided that should the opportunity arise, I wanted to attempt a presentation in America and would submit and hope for the best. SQLRally Orlando was announced and my submissions went in. Although I didn’t make the first cut, my submission received the joint highest votes for the runners up and quite incredibly I was eventually selected as a wildcard! Totally brilliant and I was thrilled!

This time (unlike Seattle) I thought I would approach Dennis and see if my company would like to contribute in any way, since there would be obvious technical benefits to the organization. The response did not surprise me. He said that my speaking and attending sessions at SQLRally (or anywhere else) had absolutely no benefit to the company at all. I have never forgotten the impact that sentence made upon me. The situation was made even more ironic when a few days later I was assigned to an important scalability project (for someone had personally requested that I should be the resource).

…… The title of my presentation : “Orders of magnitude-Scaling your SQL Server Data“.

Like the SQLPASS Summit, I fully financed my trip to SQLRally and used a week of my holiday entitlement and came to a decision. I realised that however much I tried to improve myself socially and technically (and help others), Dennis would never support my efforts in any way. A few days later I received a permanent job offer from another firm offering a substantial salary increase (almost double) and I ……….turned it down!

SQLRally helped me realize that the only person holding me back was myself and It was time for me make the jump. I quit and left for Orlando to give my presentation. On my return I secured work and now when I need to finance any speaking or training events, the only person I need to convince is myself.

But what of SQLRally? I loved giving my presentation, it was very hard work preparing for it but I learnt more than I can put into words at hopefully managed to communicate some of that knowledge across. From a non speaking perspective, if you have never been to a SQL Server event and can get to Dallas then you are going to LOVE it. SQLRally is smaller than the Summit and full of lots of first timers and many regulars who will embrace you and make you feel completely at home.

Attending SQLRally could be your first steps towards something amazing. I hope you take them, and if you do, I look forward to meeting you someday soon and hopefully share a nice cold beer.

You can read more about my exploits at SQLRally here and here.

What has happened to me since my return?

Going forward I am currently :-

Dennis, I think you made a mistake with me but I know you would never be able to admit it. I sincerely thank you for every single day I spent praying for deliverance. You made me realize that if I wanted something strongly enough I could go out there and get it. Through your neglect you encouraged me to meet people LIKE me. You have given me hope and for that I will never forget you.

Posted in humour, learning, personaldevelopment, publicspeaking, sql, sqlbits, sqlpass, sqlrally, SQLServerPedia Syndication, summit, summit_2011 | 4 Comments

T-SQL Tuesday #25 – The Best Tool You’ve Never Used

Posted on December 13, 2011 by

Wow, it’s been a long time since my last T-SQL Tuesday post and I have disappointingly missed it 5 times for one reason or another. Still, the important thing is that I am back people!

This month’s T-SQL Tuesday is brought to you by Allen White (blog|twitter) who many of you will know for his PowerShell, SMO and SQL Server expertise.

Ironically for this post (or the subject at least) I was saving up towards a larger Data Visualisation article that I was putting together, but when I read the subject of this months T-SQL Tuesday I just knew it was time to spill the beans.

Let me give you the backstory…

Around four years ago, week in and week out I used to religiously buy a Technology Publication just on the “off chance” that they would have something of use in their pages. Very occasionally a little gem would crop up and eventually one day I found what I was looking for. This particular little gem used a rather ingenious way to visualise folder and file space usage and most importantly it was free and fast.

One of the biggest problems I used to experience as a production DBA was the filling of various disk drives. From backups, log, data, tempdb and even system drive we would get it all and having a substantial amount of servers made it a common problem.

When these issues would occur I was very happy to receive them because I knew I could identify the problem area in minutes (if not seconds) and it was always very satisfying to whip out my secret tool (ooo errr missus!)

So why is it so good…

There are of course lots of other space analysis tools on the market, many commercial and many shareware or even free. But most of those I have seen have usually been slow, used poor visualisation and mostly required a local or remote installation. My tool is a simple Windows based executable that does not require installation and can live on your network home folder. This can be mapped across to any server you Remote Desktop into, so hey presto it is always available to you :)

One of the biggest usability points of the tool is the ability to zoom into out out of folders very easily and quickly. It is this ability which makes finding problem files and folders incredibly simple.

Imagine the following scenarios :-

  1. Your system drive is close to filling up, you are not sure why and your Windows Administrator is struggling to locate the problem folder.
  2. You have thousands of databases across many instances on a server and your: -
    1. Data drive is full
    2. Log drive is full
  3. Your Backup drive is getting very full. Backup and archive routines appear to be working.

… and some real-world explanations for those situations that I have uncovered in seconds using this awesome utility :-

  1. A DBA left a long running Profiler trace locally on the SQL Server. It transpired that even though the trace file can be saved on any drive, Profiler unfortunately generates a ever growing temporary trace file locally on the C: Drive until it is stopped (a recipe for disaster!).
    1. A mis-behaving bulk loading process caused a database data file to grow out of control.
    2. A long running transaction prevented the log file VLFs from being truncated and caused it to grow out of control.
  3. DBAs did not follow standards and practices and stored VLDB adhoc backups in random locations.

Remember fixing a SQL Server problem is usually pretty easy in most cases, the hard part is identifying where the actual problem lies. With the best Tool you have have never used, even identifying your space issues becomes a breeze.

Ladies and Gentlemen I give you SpaceMonger! Ensure that you download the old v1.4.0 version located on the Free Software tab since this is the superior release. Now imagine using this on a multi TB disk and being able to zoom into your problem. You will be astounded how fast and easy it is.

The more data you have the easier those problem files are to find!

Posted in database, reporting, SQLServerPedia Syndication, storage, t-sql tuesday, windows | 4 Comments

Lets keep it short – problem creating availability group listeners

Posted on November 25, 2011 by

Someone once said to me that he thought I should work as a GUI tester since I always seemed to find problems in the UI after I highlighted a problem to him that I’d found in Linux Gnome that he had never seen before after all his years working with the product. I guess there is an element of truth to that statement since I really do like clicking around and trying different things. However I suspect that my latest discovery won’t necessarily be one of those obscure problems that no-one else comes across since it is such an obvious problem and fairly easy to run into.

Whilst playing around with SQL Server 2012 (Denali) Availability groups, I discovered that there is a restriction set on the size of the listener name that you are able to enter through the GUI.

Let me demonstrate…

First I will create a new DNS host name called “readpastandfurious” which you should note is 18 characters long.

create our "longish" DNS host record

And of course there isn’t a problem creating this record since the restriction upon the size of the DNS host name is (I believe 24 characters).

DNS Server will now resolve our host record

Next I decide to create an Availability Group Listener via the SSMS GUI wizard and am very surprised when I can only type in 15 characters! Obviously this text field must have been programmatically restricted since by default the GUI text fields would not be limited.

oh dear we are out of space

My first thought was that it has been restricted on purpose perhaps for an odd NETBIOS reason -if for example NETBIOS was being used for resolution in any way in addition to DNS (daft but possible). Microsoft limits NETBIOS names to 15 characters, so this might have been a plausible explanation.

Next I decide to try and create the full 18 character availability group via T-SQL and I am a little surprised to find that it succeeded with no problems at all!

ALTER AVAILABILITY GROUP [READPAST & Furious Optimistic]
ADD LISTENER 'readpastandfurious' (WITH IP ( ('10.0.5.1','255.0.0.0') ) )

but as I suspected, TSQL is hunky dory

My conclusion then is that this behaviour is almost certainly a bug and was introduced by a programmer who either doesn’t understand the difference between NETBIOS and DNS or blindly assumed that 15 characters is enough?! In any event I believe this is a problem and have therefore raised a connect item for this issue here give it a vote up if you believe you might fall prey to this issue at some point in the future!

Posted in denali, error, gui, sql, SQLServerPedia Syndication | Leave a comment

Compatibility Matrix 1.2

Posted on November 17, 2011 by

A couple of months back I published my version of SQL Server’s Lock Compatibility Matrix under the post “Fancy a decent Compatibility Matrix?” since the one provided by MSDN is really very poorly designed and difficult for the average Joe to understand.This is one reason I believe that lock compatibility is a very misunderstood subject.

One thing that has bothered me about my version from the very beginning was the use of green and red to respectively indicate compatible or incompatible locks. The reason this has bothered me from the offset is because I was aware that those two colours could be a very big problem for colour blind DBAs. However, for me they really DID look great together and “told the story” of the compatibility between the locks without even needing words -for every non colour blind person understands and sees clearly the concept of GREEN -> GO (lock grant), RED -> STOP (lock wait).

Well the time has finally come to redress that issue and provide a matrix that is (hopefully) colour blind friendly. I have made the following changes :-

  1. Lightened the green so that it wasn’t so garish and made it slightly easier on the eye.
  2. Substituted the garish red for a deep blue. The reason I chose blue was for the “coldness” to hopefully still “tell the story” through colours.
  3. Minor changes to the Legends (for purely indulgent reasons).
  4. Changed the legend descriptions for green and red to “compatible” and “incompatible” since I believe they are better than “No Conflict” and “Conflict”.

I also have several MAJOR changes to the chart scheduled at some point in the future such as changing the cell sizes to squares -which should overcome the perceived distortion that the eye interprets when the chart is viewed at the wrong sizes. In order to accommodate that change I also need to make better use of the white space and I know how I will do it, but as everything in life it will take some time!

Without further ado, the Compatibility Matrix 1.2 can be downloaded from here. Enjoy!

p.s. Your comments or requests are very welcome…

Posted in Concurrency, sql, SQLServerPedia Syndication | Leave a comment

PASS the Dutchie

Posted on October 24, 2011 by

Anyone old enough to remember this song from the eighties will remember the catchy lyrics and tune:-

“pass the Duchie pon the left hand side <I say!> pass the Duchie pon the left hand side… It gonna burn… <give me music make me jump and prance>…”

…Ah yes, I’ve now got my foot tapping away remembering the good old days of my youth :) . The song (as some of you will be aware) is about passing a cooking pot around and sharing food with each other, but the more astute of you will know that it was based upon a track titled “Pass the Kouchie” -a ditty about smoking cannabis! *1

Food for the Community

Why am I telling you all this? Well over the weekend I was very excited to recieve an email welcoming my Cambridgeshire SQL Server User Group (Web|LinkedIn) as a new PASS chapter and I believe that the song very nicely sums up the way I feel about all of this. Not only is it fantastic news on the basis that being part of the larger community should help improve our ability to share the “food” in the cooking pot (knowledge) but will also enable the group to have more efficient lines of communication and rewarding social network allowing us all to smoke the metaphorical “Kouchie” together.

I am hoping to arrange the next meeting for the SQLCambs user group over the coming weeks, so please keep your eyes peeled. If you live in or around the area (or even further afield) and would like to join us then we will be very happy to have you there.

Finally I would like to thank SQLPASS for everything they have done for the SQL Community over all these years and I have been a big supporter of them from afar from the very beginning of their inception. Over the last few years I have had the pleasure of meeting several people who work tirelessly behind the scenes of the organization giving up huge amounts of their own time for the benefit of us all and words cannot describe how very pleased I am to be even more involved in this community.

Thank you all!

*1 Disclaimer: It goes without saying but just in case you are missing the humour, drug taking and working as a DBA (or any walk of life in fact) is (in my opinion) a very bad idea folks, please don’t do this!

Posted in personaldevelopment, sql, sqlpass, SQLServerPedia Syndication | 2 Comments