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
Declare @T table(saname varchar(100),sarole varchar(500),saenabled smallint)
 — SET NOCOUNT ON added to prevent extra result sets from
 — interfering with SELECT statements.

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″>’ +   
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))
–ORDER BY [name]   
FOR XML PATH(‘tr’), TYPE) AS NVARCHAR(MAX) ) +    N'</table>’  


 EXEC msdb.dbo.sp_send_dbmail    
@recipients =
@subject =
‘SYSADMIN ROLE MEMBERS -VENDOR’,    @importance = ‘High’,    @body =   @tableHTML,    @body_format = ‘HTML’
N'<table border=”1″>’ +   
N'<tr><th>No records found</th>’


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.




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 @loginname VARCHAR(20)

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


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
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


— Close and Deallocate Cursor
CLOSE UserDatabases
DEALLOCATE UserDatabases

SQL Cruise 2013

I had the opportunity to go on yet another SQL Cruise in 2013. This time our trip was to the Carribean Isles – St Maarten, US Virgin Islands and the Bahamas. In addition to some great opportunities for training and networking the cruise was a relaxing and enriching experience with great food and great opportunities for sightseeing in new land(s). Below is my summary of the experience – if you want to skip my rambling notes and get to what I got out of it please scroll at the way down to the last paragraph :)

Day 0- 25th January 2012: I landed at Miami late Friday evening. The cruise crowd met up with local sql server user group members and had a small get together by the pool at the hotel where we stayed.We were also given our sql cruise swag (big bag of goodies from various sponsors).It was a good opportunity to warm up to the cruise and get to know fellow cruisers.

Day 1 – The first day was marked by a breakfast get-together at the hotel we stayed in. It was a good meal and an opportunity to get to know new cruiser Mickey Stuewe and also catch up with Kevin Kline, Bruce Sacrisante and several others. Kevin was also kind to sponsor our ride to the dock in a taxi. The check in was crowded and the ship was much larger than the one I had been in Alaska. We were finally in by noon and met up again for lunch. That was followed by room check in. I took the evening off to nap and settle in – and missed the ‘search the ship’ contest which is a lot of fun. But I simply could not find energy to do it after a long day.

Day 2 – The first day at sea was marked by breakfast followed by Kevin Kline’s class on communication ‘Influence vs Authority’.  Kevin has always been one of my favorite teachers in the community particularly in areas of professional growth and this class was no exception. I learnt a lot about using different styles of communication with different people (so different from the default same cut for all that most techies are used to).I was also impressed by Kevin’s suggestion to keep a printout of various styles so that one may refer to them constantly and make it a habit. That is something I have adopted and has helped in atleast two situations so far.After a great lunch the afternoon session was Allen White’s ‘Powershell 101′. I have attended this session a couple of times before but always find it uniformly interesting and inspiring to get to learn Powershell. This session was followed by  ‘How to be an Enterprise DBA – Part I’ by Sean Mccown. Sean discussed automation strategies, communication strategies and various situations faced by DBAs who handle several servers in large shops. I found it very interesting and useful. The evening was marked by networking hour on the deck followed by formal dinner. The dinner discussion at the table I was centered around mid life challenges with finding jobs, and being a generalist versus a specialist. It provided many insights.

Day 3 – This day was again at sea and marked by a continuation of Sean Mccown’s session on being an Enterprise DBA. It was accompanied by an invigorating discussion on many challenges faced in handling DBA work in enterprise shops. After lunch the class continued with Ryan Adams teaching Active Directory terminologies and usage as applicable to SQL Server. I learnt more on many terms that I did not know in great detail about. The evening was again marked by networking – also called ‘Office Hour’. I was impressed and happy with the fact that cruisers had some time to themselves in the evening to network on their own or explore the huge ship at leisure.

 Day 4 – We docked at St Maarten today. I took a guided tour to a Butterfly Farm followed by a short exploration of the beach and some shopping. The Butterfly farm was the backyard of a canadian scientist – a sunny garden full of flowers, with only a thin mosquito net for protection and hundreds of butterflies gracing the space. It was a truly spectacular and interesting visit. The multi cultural nature of the tiny island (partly owned by the French and partly by the Dutch), and the lifestyle of the local people (very little fresh water or agriculture, tourism main industry) was interesting to observe. After a long day we met up for networking again and then retired early.

Day 5: We docked at St John’s, an island part of the US Virgin Islands today. It was a gorgeously beautiful summer day. I was torn between joining the group on their trip to a private beach or going on the tour I planned — the eco hike of the island, and finally ended up doing the latter. The hike was a short 3 mile walk through the rocky island landscape, with many gorgeous views of the beaches. We also spent two hours at Honeymoon beach, a small beach with smooth white sand and the bluest beautiful waters ever. I regretfully made my way back to the boat around 2 pm. St John’s is definitely among the most beautiful scenic places I have seen in my lifetime. We met up again for networking and dinner in the evening, most conversation centered around our sightseeing experiences that day.

Day 6: This was a day at sea. Our class started with Neil Hambly teaching Memory Management. I liked Neil’s style of teaching and learnt many tips from the presentation. It was followed by Kevin Kline’s session on SQL Server Internals. Although I have attended the session before it was a great refresher on things one needs to know. This was followed by Ryan Adams teaching Policy Based Management. In the evening we met up for a semi formal dinner at the same restaurant. My memories of the dinner center around the gluten free, sugar free peach cobbler ordered by Mickey Stuewe and shared by some of us. It was truly a memorable guilt free dessert experience :)

Day 7: Our last day of the cruise began with Allen White teaching ‘Powershell for Performance’. I was very impressed by scripts he used to do performance monitoring and display graphs as reports. The ship docked at the Bahamas for the afternoon. I spent the afternoon on a swimming experience with dolphins at the resort we were close to. It was truly unforgettable but left little time for anything else. The weather at the Bahamas was also chilly due to a storm front coming in. Our last evening was marked by a great get together after dinner with plenty of jokes and good humor. I took leave of the party with many warm feelings, friendships made and gratitude for a great week of learning and networking.

There are a few things I learnt from the cruise. First of them perhaps is that it has given me an opportunity to make friends easily – I am one of those people who is often perceived as a strong introvert, although am really not that much of a shy or reserved person. My personality type is more of an ‘ambivert’ or ‘centrovert’ as they call it – I take time to warm up to people though and am not an ‘instant mixer’. To add to that is all the complexities of having spent half a lifetime in another culture and the strong stereotype around asian women. I would probably not have much to contribute in a conversation that centers strongly around american food, or alcohol or music but I do like jokes of any kind and enjoy conversations where people give room for differences and have time to listen. SQL Cruise allows for that, and has helped me make many good friends among the people I have cruised with so far. I have been enriched by their stories and their experiences and hopefully they learnt some from mine too. I am also always someone up for checking out new lands and exploring new places – it is just the trip for someone interested in all that .I was able to add 3 new places to my list and that makes me greatly thrilled. Lastly is the invaluable experience of technical learning and being around people who have been successful and good at what they do, for extended periods of time. There are many conferences/sql saturdays and various events but none that give you the opportunity to do all of this in one week. 

I would highly encourage any person interested in growing their career in SQL Server to try SQL Cruise. You will want to come back for more, I promise. I want to thank the sponsors – SQL Sentry, Idera, Red Gate and Confio for making this possible for the SQL Community – in addition to Tim and Amy Ford, for their great organizing skills and also for the fun and enriching company of their entire family.

SQL Saturday 154 – St Louis

I attended SQL Saturday 154 at St Louis last weekend. I was particularly keen to attend this event since it was organized by my friends at St Louis (Sanil Mhatre,Julie Bloominquest, Kathie Kellenberger, and others) who have been regular attendees at all of our events at Louisville. It was also their first sql saturday. I arrived late on friday and checked in at Crowne Plaza, the hotel assigned to the event. It was very comfortable,reasonably priced and close to the event location – which helped me not rent a car. The next day morning I finished breakfast at the hotel and walked to the event. It was a short and pleasant walk. I had left my speedpass printout at home but the team was quick to print me a copy, and I was set with my badge and swag bag in a few minutes. I was also able to get a good cup of coffee and settled in for my first session of the day – Dan Guzman’s ‘Maximising SQL Server Insert Performance’ – I learnt a few things particularly on benchmarking and measuring performance. The next session I attended was Arie Jones’s ‘Pwned..Security,SQL Server and you’. AJ is one of those people who can keep you listening for hours with the content and stories – and he was totally in his element today. I greatly enjoyed the presentation and learnt many new things about hacking techniques and security mechanisms to prevent them. After this session I had to head back to the hotel to check out, and returned in time for lunch. There were no lines and was able to get my sandwich easily – it was recommended to sit in classrooms, eat and listen in on vendor presentations. This was a great idea but for those of us who could not attend the post event, this was also the only time to socialize. I spent this hour socializing with many people I knew, and also with vendors. The only post lunch session I was able to attend before rushing to the airport was Kevin Boles’s ‘ Common TSQL mistakes’ –  another brilliant presentation packed with tips for every day use. I thoroughly enjoyed the session and left the event very satisfied with a good day’s learning.
As an organizer of 5 sql saturdays I happen to know the many pains and huge effort to pull off an event – and the effort put in by the team was obvious. Finding a space to host such an event for free is a huge challenge, but again bigger spaces are the only way events can grow – so perhaps a bigger space would help next time. Also, as Kendal Van Dyke pointed out in the SQL Saturday news letter – the most common feedback events get are around signage in and around the building. We learnt to make investments in signage and reuse them every year – something I have never regretted doing as an organizer – this could be considered.  A boxed lunch with all sides in one box would be better – in return for $10. Other than these small issues I felt the event was very well organized and look forward to attending the next one!!

Thank You To an Awesome Sponsor

There are 3 kinds of people who keep our user groups and sql saturdays going. They are loyal speakers, sponsors and volunteers. We took extra care to reward our speakers this time – particularly those who have been returning to us every year for the past five years. For sponsors – it is difficult to offer any ‘rewards’. Sponsors are the reason behind our free lunches, our swag, our giveaways…This post is a humble thank you to one of the best sponsors there is. Idera Software has kindly sponsored our user group lunches for 4 years now, and all our 5 sql saturdays as Gold Sponsors. Without them it would be really really hard to keep both the user group meetings as well as sql saturdays going. We are proof and testimony to their commitment to community and we greatly appreciate it. This picture is our volunteer team expressing our thanks.On behalf of all volunteers and attendees of Louisville SQL Saturday – THANK YOU IDERA, YOU ROCK!

Previous Older Entries Next Newer Entries


Get every new post delivered to your Inbox.