TSQL Tuesday #59 – My hero(es) – The Quiet Achievers

I decided to resume my effort at consistent blogging again..after more than a year..with this week’s TSQL Tuesday Invite – this invite is by Tracy McGibben and is on ”Heroes”. There are many heroes in the SQL world who have inspired me and continue to. But there are two among them whom I wish to single out for this post.

1 Joseph Sack – I met Joe during a Sqlskills Immersion Event at Florida. I had known him to be the former director of MCM program before he joined SQLSkills, and I had a mental image of him to be atleast twice as older than he really was. Joe was much younger, very smart and thorough in what he knew, kind, funny and very easy to be around with. Soon after that event Paul Randal put out the yearly mentoring program that they do, and the candidates each of them had picked to mentor. I was long looking for a mentor – and was a bit saddened that I did not make it. I sent out a personal request to Joe – asking if he would mind mentoring me. Honestly did not expect to hear anything in the affirmative but he responded immediately – yes, he would be happy to. I spent a year on mentoring program with Joe – talking every month on things that mattered to me and asking for his advice, suggestions and like. After a few months we would up good friends who shared each other’s cares and worries rather than just a mentor and mentee. What impressed me about him initially are the same qualities that impress me still – humble,  down-to-earth, hard working and always willing to lend a helping hand. (Unfortunately Joe is no longer on social media or blogosphere for me to add any links). Proud to know you and be your friend, Joe.

2 Wayne Sheffield - I met Wayne as a speaker for one of my SQL Saturdays. That particular SQL  Saturday was not really a great event for me and my team. Two of my most senior volunteers had to depart to attend to some personal duties – I was very short on help and struggling to pull together with many things falling apart. One of the things that ‘fell apart’ were speaker shirts, which were misplaced. I was unable to find Wayne’s shirt – he had come with expectation that he would get one and needless to say, it was not the best experience for either of us(The shirt was found later). But the year after – I went on SQL Cruise Alaska, and joined Wayne and his family the day before for a tour of Seattle underground. During the cruise I got to know him much better and we are good friends. I have since followed his journey towards the MCM and various job changes too.He has  always been there to help me – with SQL advice, professional help, or just lend a listening shoulder. Like Joe – the qualities I admire with Wayne are his patience, humility, hard work, down-to-earthiness and willingness to help.

‘A person is known by the friends they keep’ is an old saying – so if people know me by my friends such as these, am sure I am known well :) As a last word – would like to recommend everyone to find  people they vibe well with, not just people who are celebrities or big names. Find people who understand who you are, and are willing to support, encourage and motivate you. And do the same for them. Attend programs like SQL Skills or the SQL Cruise - they are not just for learning, I  have found some of my best friends this way.

Finding new goals…

The sudden and sad demise of the #sqlmcm program had me thinking on many levels..particularly on future goals and aspirations, and on how to market myself.

To give an introduction of some sort – in my younger years I was not particularly in favor of degrees or certifications. I was strongly of the belief that experience, interest to learn and creativity were the ‘real’ stuff that got you places. I dropped out of a high tech engineering program where I was 1 out of 300 people selected from among nearly 60,000 people who took it, and chose to pursue my life and career based on what I believed I had – interest in technology, creativity and hard work. Needless to say, it was not an easy journey. Particularly in a country like India where degrees and certifications were almost a cultural obsession. After a few years of working poorly paid jobs I went back to school for my masters. My masters degree and the process of doing it taught me many positive things. One, that having a hard goal – such as an exam or someone to rate me gave me better focus than learning on my own. Two, degrees helped you get through visas and other places where you do not have personal interaction with whoever is handling your stuff, and three, it helped you find community among others who had similar degrees. I never went crazy about degrees or certifications, but I did learn their place in the world, the hard way.

In the sql server world, there are many paths to progress. The most common one, by far, is by speaking/blogging/becoming a technical evangelist. There are many without MCM who have gone this way and been remarkably successful. But speaking and blogging unfortunately are not everybody’s goal nor does everyone have the time needed for it. To me – as someone who has a lot of food allergies, travelling intensively is really not a good goal to have. I have tried blogging but my time for it is rather limited and also not had success coming up with something creative to write about, I guess again that needs a lot of experimentation. That is part of the reason I picked the MCM as a possible goal. I was also interested in being differentiated from the average brain dump MCITP down the street by getting to be an MCM – and I did find this a worthy goal. Even saying you are pursuing an MCM at an interview got a lot of appreciative nods and interest. It also got employers to pay for good training programs such as those offered by #sqlskills, which they otherwise would not understand the value of.

Now with that certification gone we are back to the world where there is really not much by way of proof to tell an average employer how different you are from brain dump MCITPs. If you are like me into doing community work such as organizing sql Saturdays or running user groups you may want to use that to some extent but not everyone is highly impressed by community work to hire you, or even give you pointers to jobs (including people in the community itself). And to many of us it is not just about career growth or progress, it is also about acceptance among people you respect and acknowledge.

There is a scene in one of my favorite movies ‘A Beautiful Mind’ – where Professor John Nash – played by Russell Crowe, gets accepted and acknowledged for his intelligence after a nobel nomination. The other professors walk over slowly to him and hand him pens as a token of their respect and his acceptance into their club. I have a pen with the ‘Microsoft certified master’ printed on it – a little gift from the #sqlskills class I attended some years ago. It was my goal and the goal of many others I know to be accepted like John Nash was into the elite #mcm community. It is a sad day to have that taken away from us. It is my sincere hope and wish that all of us will find bigger and better goals to pursue.

SQL Saturday 2013 Louisville – A Recap

This year was SQL Saturday #5 in a row for us at Louisville. It was by far the best event we have had and we greatly enjoyed hosting it. Below are some things that worked and did not work this time:

What worked:

1 The location – ideally situated, no parking hassles, walking distance from the discounted hotel, all rooms on one floor, a very friendly and cooperative staff – in short everything we wanted in a location came through this time with this one. Our sincere thanks to Indiana Wesleyan University for hosting us (in particular to Leigh Ann Black) and our volunteer Dave Mattingly for finding them for us. We hope to have the next event(s) at the same location.

2 An enthusiastic crowd – although our turnout was not as high as usual (190 compared to 220+) we found most of the audience interested, appreciative and many stayed until the very end.

3 Our loyal speakers – over time we have grown a crowd of  speakers who attend our every event unfailingly – these include Kevin Edward Kline, Louis Davidson, Allen White, Tim Chapman, Dave Fackler and Craig Utley. Our thanks are due to these speakers (and  everyone else) whose knowledge and willingness to share it made the event a grand success. It may also be worthy of mention that Kevin unfailingly gets voted as the best speaker, and Tim has been the first guy  who made it upto the MCM from this town.

4 Sponsors – We put the sponsors in a separate room along with snacks and sodas. Almost all of them were very appreciative of the audience they got and promised to return again.  It may also be noteworthy that other than Quest/Dell Software and Confio and our local VSoft Consulting, all the other sponsors were first timers for us. Some like Embarcadero, Imperva  and BI Tracks did their first ever SQL Saturday sponsorship with us and wanted to return again. We consider this a huge victory not just for us but for the bigger SQL Saturday  community as well.

5 Post Event party – We did not have  post event parties at older events. The main reason for this is because we are a small volunteer crowd and all of us were very tired at the end of the day. Coordinating  another party was too much of an effort to pull off. This time VSoft Consulting stepped in as sponsors and coordinators, and we were able to host the first ever post event party. While it was not hugely attended, it was a good start and we will surely consider doing this again.

What did not work:

1 Pre Cons – We started with announcing 4 pre cons and ended up cancelling all of them. Three pre cons were cancelled at the behest of the speakers, who  were busy  with other work and did not find attendance big enough to book their time in advance. The last one had to be cancelled because the count of students was too small  for us to coordinate it. This made a few attendees unhappy and we were sorry that we had to do that. But some self examination helped us understand that pre cons were too much effort in addition to the sql Saturday – we are probably not going to have them again.

2 Speaker Shirts – We ended up with some shirts that were not of desired quality. The main reason  was that our order was placed in the last minute with a vendor who did a mix and match of suppliers. Our decision was to go with the Florida based SQL Saturday supplier that was used by many events , from next time.

3 Speaker Room – We had insufficient tables and chairs in the room that resulted in many speakers relocating to the lunch/registration room for chats and discussions . More attention to be paid to this later.

4 Speed Pass – We had a printer with us  for those attendees who did not show up with their speed pass printouts. Surprisingly this time it was quite a large crowd (roughly around 30-40 %). That resulted in the printer chugging along as fast as it could and two volunteers working the registration table non stop from morning until the last attendee strolled in after lunch. We also learnt by show of hands during the raffle that most attendees were first timers. This might be one of the reasons. From the next time we plan to work around this by having something different – like perhaps a $1 fee for those who forget their printouts.

5 Lower attendance than normal – We had about 20% less people than usual. We attributed the main reason for this to be our choice of date for the event – one week away from July 4th and a time when most people take time off for summer vacations. We will work better at finding a better date.

SQL Saturdays are team organized events, they are not one person effort no matter how motivated the organizer is. The success of this event is due to the hard work of our volunteers – Brian Carter, Dave Ingram, Dave Mattingly, Karen Schuler, Deana Ritter, Kenney Snell and newbies Scott Drake and James King. We hope to remain a team going forward and bring more events to the community. Our thanks again to speakers ,sponsors,volunteers and attendees who made it a grand success.

SQL Saturday Richmond – 2013

My friend Karen and me decided to drive out to Richmond and attend their SQL Saturday this past week. We have had several people from Richmond and the Washington DC area attend our events – we wanted to go there too. The other reasons were that the speaker line up was very attractive and there were several friends I had known via SQLCruise and PASS Community in general who were going to be attending. We left Louisville the morning of Friday the 8th and reached Richmond by around 6 pm in the evening. It was a pleasant, scenic drive through the mountains and weather was cooperative to the extent it could be for winter.

After a good night’s rest and dinner we arrived at the event location by 8.00 am the next day. There were several signs posted that made it very easy to locate the building in the campus of University of Richmond. Check in was very quick and easy with speed pass printouts – we were handed our swag bags with event schedule. There was coffee and bagels available for a breakfast.

My first class of the day was ‘Building a virtual lab’ by Matt Velic. I have been struggling with labs for many days and I found the session useful – although Matt had lots of material to cover and time did not seem to suffice. I was encouraged to know that he had made a book out of the process – it is on my list of things to download and work further with.

The next two classes I attended were ‘Windowing Functions in 2012′ by Kevin Boles and ‘Branding yourself for a dream job’ by Steve Jones. I have been wanting to attend Steve’s session for a very long time and go to do it finally. He gave some great tips on prudent use of social media and its importance in networking – also on using networking to find the next job. I greatly enjoyed it.

Lunch included many selections including two for vegetarians – and came as a neatly wrapped box of pasta salad, sandwich,fruit salad and cookies. For $5 it is about the best bargain I have seen so far and was very tastefully done. I ate lunch at the Women in Technology session on ‘Breaking the glass ceiling’ – moderated by Kevin Kline. The panel included Karen Lopez, Melissa Coates and Stacia Misner. I have attended many WIT sessions before but this one was definitely memorable and greatly useful for the many insights provided by the panelists – particularly Karen Lopez, on salary negotiation and behavior models of women in the male dominated techie world. Kevin also raised the question of numbers of women in technology across the world – and it was one I could provide some insight on.

I spent an extra hour on networking in the afternoon – something I have felt very necessary to do, especially at an event where I knew so many people. It was a valuable experience and made me understand the need to rethink the habitual pattern of running from class to class. I attended two sessions – one by Michael Corey of Ntirety on Virtualizing SQL Server, and the other by John Welch on Big Data. Both were packed with information and made for worthy use of my time.

The day ended with raffles and closing remarks. A mini slide show highlighted the volunteers and sponsors. Many prizes were raffled off including one for those who provided event feedback. We said goodbye to many friends including organizers,speakers and attendees and left for home, greatly satisfied.

As an organizer of many sql saturdays – am well aware of and always appreciative of the efforts that go into  making an event successful. I wish to communicate my hearty congratulations to the Richmond team for providing a memorable event for 2013 – hope you get some much needed rest until another event comes along!

Finding users who are Sysadmins

We had a requirement on a server supporting a vendor application to monitor users who are given sysadmin rights. This was because vendor personnel would need these privileges every now and then – and it was our job to monitor when they are active and see that they are disabled on time. To achieve this objective I was asked to write a stored procedure based job that would get a list of people who were SA’s and send an email to the DBA team manager with the list. Below is the procedure I wrote to achieve this objective.

/*********************************************************************************************
Authored by: Malathi Mahadevan 2/27/2013

Tested on : SQL Server 2005 and 2008 only

Version 1.00
**********************************************************************************************/

CREATE PROCEDURE usp_getsanames
AS
DECLARE @tableHTML  NVARCHAR(MAX)
Declare @T table(saname varchar(100),sarole varchar(500),saenabled smallint)
BEGIN
 — SET NOCOUNT ON added to prevent extra result sets from
 — interfering with SELECT statements.
 SET NOCOUNT ON;

INSERT INTO @T(saname,sarole,saenabled)
    — Insert statements for procedure here
SELECT  mem.name,mem.type_desc,mem.is_disabled
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS rol ON rol.principal_id = srm.role_principal_id
INNER JOIN sys.server_principals AS mem ON mem.principal_id = srm.member_principal_id
WHERE rol.name = ‘sysadmin’
and mem.type_desc <> ‘SQL_LOGIN’

SET @tableHTML =    N'<H1>SYSADMIN ROLE REPORT</H1>’ +   
N'<table border=”1″>’ +   
N'<tr><th>Name</th><th>Role</th><th>Status</th>’
+   
CAST ((
SELECT  td = CAST(([saname]) AS nvarchar(100)),”,
td = CAST((sarole) as nvarchar(15)),”,
td = CAST((CASE  [saenabled]
            WHEN 0 THEN ‘ENABLED’
            WHEN 1 THEN ‘Disabled’
            END) AS NVARCHAR(50))
FROM @T
–ORDER BY [name]   
FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX) ) +    N'</table>’  
      

IF @@ROWCOUNT > 0
BEGIN

 EXEC msdb.dbo.sp_send_dbmail    
@recipients =
‘dbamanager@mycompany.com’,
@subject =
‘SYSADMIN ROLE MEMBERS -VENDOR’,    @importance = ‘High’,    @body =   @tableHTML,    @body_format = ‘HTML’
 end
ELSE
BEGIN
SELECT @TABLEHTML =  N'<H1>SYSADMIN ROLE REPORT</H1>’ +   
N'<table border=”1″>’ +   
N'<tr><th>No records found</th>’
END

END

Tail of the log has not been backed up

This is the second post in my attempt to blog one little thing every day. Today I ran into this message when trying to restore a database over an existing database. I forgot to check the box that says ‘overwrite existing database’ and got this rather informative error. I love this error message since it tells exactly what the user should do

‘The tail of the log of the database <mydatabase< has not been backed up – Meaning there are transactions in the log that need to be backed up.

‘USE BACKUPLOG WITH NORECOVERY’ if you want to backup the log and it contains work you don’t want to lose. ‘- It tells exactly what should user do in case he needs the older copy.

‘USE WITH REPLACE OR WITH STOPAT CLAUSE OF THE RESTORE STATEMENT TO JUST OVERWRITE THE CONTENTS OF THE LOG’ – in other words if am using the GUI I better check the box to tell sQL Server to overwrite the existing database, if that is what I intend to do.

This message tells me what is wrong and also evaluates the two possibilities – I want to keep or discard the old copy of the database, and tells me what to do in both situations. It is an excellent example of user friendly error messages.

blogpost

 

 

Re attaching logins…script

I am posting this as first in the series I plan to do on my favorite scripts. This is also an attempt to get into blogging seriously – an idea suggested by Tim Ford – to blog atleast one thing learnt per day(#learn365). Am starting with very mundane/basic things as there are many of them.

As a DBA I often have a need to do activities across user databases on a server. One of them is to re attach all SQL Server Logins when a Development or QA environment is restored. There are many ways of doing this…and many scripts available online. Below is the script I authored and use. I also use the same framework to perform any activity across user databases. It works on SQL Server 2005, 2008 and 2008 R2. I have not tested it on SQL Server 2012.

The script checks if login exists on the database so that it does not have to run on databases that do not have the login and produce an error.  It looks really simple but when I looked for a script I could not find one that met my needs and therefore wrote my own.

/*********************************************************************************************
Authored by: Malathi Mahadevan 2/15/2013
**********************************************************************************************/
— Define variables needed
DECLARE @DataBase NVARCHAR(128)
DECLARE @Command NVARCHAR(500)
DECLARE @loginname VARCHAR(20)

–Provide login to be reattached
SELECT @loginname = ‘mylogin’

DECLARE UserDatabases CURSOR FOR

SELECT name FROM master..sysdatabases
where name not in (‘master’,’tempdb’, ‘model’, ‘msdb’, ‘distribution’)

– Open Cursor
OPEN UserDatabases
— Get First database
FETCH NEXT FROM USerDatabases INTO @DataBase

– Process until no more databases
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @command = ‘USE [' + @database + '];IF EXISTS(SELECT 1
FROM sys.database_principals princ
LEFT JOIN sys.database_permissions perm
ON perm.grantee_principal_id = princ.principal_id
where name = ”’ + @LOGINNAME + ”’and type_desc = ”SQL_USER”) ‘
+ ‘EXEC sp_change_users_login ”update_one”,’ + ”” + @LOGINNAME + ”’,’ + ”” + @LOGINNAME + ”';’
EXEC (@command)
— Print command to be processed
PRINT @command
— Get next database
FETCH NEXT FROM UserDatabases INTO @DataBase

END

– Close and Deallocate Cursor
CLOSE UserDatabases
DEALLOCATE UserDatabases

Previous Older Entries

Follow

Get every new post delivered to your Inbox.