SQLKitten

Holy Blocking Chains, Batman – I’m Speaking at PASS Summit!

Posted on July 29, 2017 | 2 Comments

For the first time ever I will doing a general session at PASS Summit this year.

Waiting for an email with news one way or the other last week was angst ridden, to say the least. After receiving no notification with the first wave on Tuesday, I got even more anxious and tried to occupy myself with work and other things. I didn’t want to get excited over something that wasn’t even a thing yet.

Then late Friday – acceptance.

Yeah, that’s right. ME!

It didn’t sink in right away. I had myself so convinced I wasn’t going to get selected. Maybe my head and heart were still bracing for the disappointment that was not to happen. Why would I be thinking this way? This is the third time I have submitted and the first time I have had a general session accepted. I did have the opportunity of doing a lightning talk last year. I also got to compete in Speaker Idol, which was an amazing and valuable experience that is only available to those that have not done a general session before. More on this later.

The first year I submitted I had been writing abstracts the same way I normally did for SQL Saturday events, not knowing there was more involved for a PASS Summit abstract. I logged in and was about to fill out everything for each of my sessions when I saw all these fields to fill out that I didn’t know about. I quickly got things written for them, along with my main abstracts, and submitted.

But I didn’t get selected.

The next year PASS offered a program for feedback on Summit abstracts. I thought “Yes, this is great! I will write my abstracts, get feedback, and I will be speaking at Summit this year!”

But I didn’t get selected for a general session.

Both times I was disappointed. Both times I did a fair amount of sulking and grumbling, but I am only human. In these cases, my best was beat out by others that were better. Kind of a hard pill to swallow, but I think these challenges are what make us who we are – IT professionals that are constantly striving to improve our own skills while creating content that will convey knowledge and better the SQL Server community.

Last year however, I was selected for a lightning talk and I finally got to participate in Speaker Idol. Doing my lightning talk on How to Keep Your DBA from KILLING YOU was fun and a great experience.

Speaker Idol? Wow…how do I put this? If you are a new speaker, and have not spoken at PASS Summit before (general session or better), throw your name in the hat for this! If selected, you will have the opportunity to get up and present something in five minutes, and get immediate feedback from a panel of judges, who also happen to be well known and experienced speakers in the SQL community. When this was first done in 2014, I heard about the brutality of the first round, so I made sure I was there for the subsequent rounds. Your audience for this? Mostly speakers or aspiring speakers. Don’t be surprised if the final round is standing room only. Or if there are acrobatics (lookin’ at you, Rob).

Moving on to 2017…

Earlier this year it was announced that the Summit selection process was changing yet again. There would be no feedback to speakers and you were also limited to submitting three sessions. There was also a list of topics, with some marked as “hot topics” or topics that PASS really wanted content on. It was also stated that they wanted new content. That session you had been peddling to every SQL Saturday that would have you? Not new content.

I took all this info and combined it with a ramped-up abstract writing and review process. What is ramped-up? Reading the sessions that were selected last year, and having a reviewer that is a seasoned PASS Summit speaker. It also helped that my reviewer was super critical. Once it was announced that submissions were being accepted I logged in and grabbed all the sections that I would need to fill out and got started on my abstracts, outlining details before writing anything. There was even one session I outlined while on a plane, that I looked at later to write up and decided it was COMPLETE GARBAGE…AND WHAT WAS I THINKING???

What did I learn from the PASS Summit submission and selection process this year?

Review, review, review. Write them early, review, come back later and review again. Have other experienced PASS Summit speakers review your abstracts. Don’t like their feedback or think they are being mean? Ummmm….they are trying to help you get your abstract to a level that is worthy of PASS Summit. LISTEN TO THEM!!! And buy them a beer.
Nothing is guaranteed. In a previous blog post I told y’all how to pull all the submitted session into a table and even gave you some PowerShell code to do it. I was able to compare that to the list of speakers selected and there were some amazing people that have spoken and PASS Summit before that didn’t make the cut this year. Sessions are reviewed and selected by human beings. This is a gamble, and regardless of how good of an abstract you have, you are at the mercy of the session review committee and what they feel is good enough for Summit. This is a monumental task, but they hunker down and get it done so that all of us can have an amazing selection of sessions to pick from.

I hope I get to see everyone at PASS Summit this year, whether or not they are speaking. Out of the conferences that I have been to over the years, I have found PASS Summit to not only be the best of them, but it was where I discovered that the #SQLFamily thing is real. If you are open to it, you can get hooked into groups that will welcome you and include you all because you are there for the same reason they are – to expand your knowledge and career in the area of SQL Server.

See y’all in October!

→ 2 Comments

Posted in Professional Development, SQL PASS, SQL PASS Summit, SQL Server, Uncategorized

Tagged dennycherry, passsummit, sqlfamily, sqlpass, summit17

Speaking – SQL Saturday Baton Rouge!

Posted on July 25, 2017 | Leave a comment

This Saturday will mark the fourth year that I will be speaking at SQL Saturday Baton Rouge at LSU. Out of all the SQL Saturday events I have participated in since 2014, Baton Rouge is one of the few that I have been to every year since. Houston is another one…and actually, these may be the only ones only because they have had an event every year.

Not only am I speaking on Automation with PowerShell and Deadlocks and Blocking, but I am also participating in a panel discussion on Careers in IT. I’m excited to be invited along with some of the other speakers to be a part of this. Looking back on my own school days, I knew I would have a career in IT, but little did I know I would detour from a path in development to the world of SQL Server, and becoming a DBA.

How do they make this happen? Work. Lots of hard work. After helping with SQL Saturday Dallas 2015, joining the NTSSUG board in 2016, and then having an organizing role in 2016, I found out how much goes into the planning of these events. If you have attended a SQL Saturday or you are going to in the future, be sure to say “THANK YOU!” to all the organizers and sponsors. If you want to get more involved in the SQL Server community, SQL Saturday is a great way to do that – just show up at the event, find an organizer and tell them that the SQL Kitten sent you to be their humble servant volunteer for the day…or you could just say you want to volunteer and leave out the other stuff because it might make it weird.

→ Leave a comment

Posted in Database Administration, DBA, NTSSUG, Performance, Powershell, Professional Development, SQL Saturday, SQL Server, Uncategorized

Tagged automation, database administration, DBA, powershell, sqlsat628, SQLSatBR, SQLSaturday, sqlserver

(UPDATED) PASS Summit 2017 Session Data (You know you want it…so go get it!)

Posted on June 3, 2017 | 7 Comments

UPDATE #1 –
The following changes were made to the PowerShell script for the PASS Summit 2017 session scrape:

Abstract field is now varchar(max). ‘Cause precons get all the chars.
Added SubmittedSession column.
Upper bound increased to include precons.

UPDATE #2 –
Before I could get update #1 posted, it appeared all the general sessions and lightning talks were removed from the submissions page and replaced with submitted precons. Don’t fret – the PowerShell script will still grab all the submitted sessions (for now). There were also some more precons added so the upper bound has been changed yet again.

The deadline for PASS Summit submissions came and went. Either you got yours in or you didn’t. What does this mean? Time to gather the data.

Go get it! You know you want to!

Never having done a web scrape before, this was the perfect subject for my first time – grabbing all the sessions submitted to PASS Summit 2017…and doing it with PowerShell! Here is the script I used for this. I have accounted for the following:

Apostrophes (aka single quote). They will break your insert unless you have two of them, and for some reason, people seem to use them all over the place.
Formatting the string data for insert. No, your data will not magically come out right in your insert with single quotes so you need to add them.
Additional ID and deleted fields.
Speaker URL and ID. Will be using this to scrape speaker details later.
Accurate lower and upper bounds. These were arrived at by trial and error (you’re welcome), as well as the clean up of the data I scraped. More on this later.

Now for the code –

<#
Name: summit_sessions_scrape.ps1
Author: Amy Herold
Date: 27 March 2017
Purpose: Pull down all the session submitted to PASS Summit 2017. Because data. And because you want to.

14 June 2017
Updates: 
Abstract field is now varchar(max). Cause precons get all the chars.
Upper bound to include precons.
Added SubmittedSession field.

Note: You will need to create the following table in a database to write all this to - 

CREATE TABLE [dbo].[SubmittedSessions8](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](500) NULL,
	[Speaker] [varchar](100) NULL,
	[Duration] [varchar](50) NULL,
	[Track] [varchar](200) NULL,
	[Abstract] [varchar](max) NULL,
	[webid] [int] NULL,
	[url] [varchar](1000) NULL,
	[deleted] [bit] NULL CONSTRAINT [df_deleted8]  DEFAULT ((0)),
	[speaker_url] [varchar](1000) NULL,
	[speaker_id] [int] NULL,
	[SubmittedSession] [bit] NULL
) ON [PRIMARY] 


http://www.pass.org/summit/2017/Speakers/CallforSpeakers/SubmittedSessions.aspx

#>

#-----------variables you need to set-----------------
$server = 'SERVER\INSTANCE';
$database = 'Summit2017';
$lower = 65066
$upper = 66731
#-----------------------------------------------------

for ([int]$i = $lower; $i -le $upper; $i++)
{
    $url = 'http://www.pass.org/summit/2017/Speakers/CallForSpeakers/SubmittedSessions/SessionDetails.aspx?sid='+$i.ToString();
    $url;

    #----------------go scrape the web page------------
    $webresponse = Invoke-Webrequest $url;

    #---------------save the innerText to a variable for parsing--------------
    $elements = $webresponse.AllElements | where {$_.class -eq 'pass-orator-session-details'} | select -First 1 -ExpandProperty innerText

    #----------------get the link to the speaker page and the speakerid--------------
    $speaker_url = $webresponse.Links  | Select-Object -ExpandProperty href | Where-Object {$_ -like '*/summit/2017/Speakers/CallforSpeakers/SubmittedSessions/*'} | select -First 1
    $speakerid = '';
    $speakerid = $speaker_url.split("=")[1]
    
    #---------------clear and fill $resultsArray with the remaining data-----------
    $resultArray = @()

    foreach($element in $elements.Split("`n")){
        if($element.Length -gt "2")
        {$resultArray += $element}
    }

    #--------------clear and populate $blurb with the session deatils-----------
    $blurb = '';
    for ([int]$k = 4; $k -le $resultArray.Count; $k++)
    {
        $out = $resultArray[$k]+"`r`n`r`n"
        $blurb += $out
    }

    #--------------clear $sqlstmt and populate the rest of our variables with information from the session page----------------
    #--------------replace instances of on single quote with two so inserts with these will work-------------------------------
    $sqlstmt = '';
    $title = $resultArray[0] -replace "'","''"
    $speaker = $resultArray[1] -replace "'","''"
    $duration = $resultArray[2]
    $track = $resultArray[3]   
    
    #--------------do some formatting and fix the single quotes in the abstract--------------
    $title2 = "'"+$title+"'"
    $speaker2 = "'"+$speaker+"'"
    $duration2 = "'"+$duration+"'"
    $track2 = "'"+$track+"'"
    $webid = $i.ToString();
    $blurb2 = $blurb -replace "'","''"

    #-------------create our insert statement and run it-----------
    $sqlstmt = "insert SubmittedSessions (Title, Speaker, Duration, Track, Abstract, webid, url, speaker_url, speaker_id) values ($title2,$speaker2,$duration2,$track2,'$blurb2',$webid, '$url','$speaker_url',$speakerid)";
    Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $sqlstmt

}

Name: summit_sessions_scrape.ps1

Author: Amy Herold

Date: 27 March 2017

Purpose: Pull down all the session submitted to PASS Summit 2017. Because data. And because you want to.

14 June 2017

Updates:

Abstract field is now varchar(max). Cause precons get all the chars.

Upper bound to include precons.

Added SubmittedSession field.

Note: You will need to create the following table in a database to write all this to -

CREATE TABLE [dbo].[SubmittedSessions8](

[id] [int] IDENTITY(1,1) NOT NULL,

[Title] [varchar](500) NULL,

[Speaker] [varchar](100) NULL,

[Duration] [varchar](50) NULL,

[Track] [varchar](200) NULL,

[Abstract] [varchar](max) NULL,

[webid] [int] NULL,

[url] [varchar](1000) NULL,

[deleted] [bit] NULL CONSTRAINT [df_deleted8] DEFAULT ((0)),

[speaker_url] [varchar](1000) NULL,

[speaker_id] [int] NULL,

[SubmittedSession] [bit] NULL

) ON [PRIMARY]

http://www.pass.org/summit/2017/Speakers/CallforSpeakers/SubmittedSessions.aspx

#-----------variables you need to set-----------------

$server = 'SERVER\INSTANCE';

$database = 'Summit2017';

$lower = 65066

$upper = 66731

#-----------------------------------------------------

for ([int]$i = $lower; $i -le $upper; $i++)

{

$url = 'http://www.pass.org/summit/2017/Speakers/CallForSpeakers/SubmittedSessions/SessionDetails.aspx?sid='+$i.ToString();

$url;

#----------------go scrape the web page------------

$webresponse = Invoke-Webrequest $url;

#---------------save the innerText to a variable for parsing--------------

$elements = $webresponse.AllElements | where {$_.class -eq 'pass-orator-session-details'} | select -First 1 -ExpandProperty innerText

#----------------get the link to the speaker page and the speakerid--------------

$speaker_url = $webresponse.Links | Select-Object -ExpandProperty href | Where-Object {$_ -like '*/summit/2017/Speakers/CallforSpeakers/SubmittedSessions/*'} | select -First 1

$speakerid = '';

$speakerid = $speaker_url.split("=")[1]

#---------------clear and fill $resultsArray with the remaining data-----------

$resultArray = @()

foreach($element in $elements.Split("`n")){

if($element.Length -gt "2")

{$resultArray += $element}

}

#--------------clear and populate $blurb with the session deatils-----------

$blurb = '';

for ([int]$k = 4; $k -le $resultArray.Count; $k++)

{

$out = $resultArray[$k]+"`r`n`r`n"

$blurb += $out

}

#--------------clear $sqlstmt and populate the rest of our variables with information from the session page----------------

#--------------replace instances of on single quote with two so inserts with these will work-------------------------------

$sqlstmt = '';

$title = $resultArray[0] -replace "'","''"

$speaker = $resultArray[1] -replace "'","''"

$duration = $resultArray[2]

$track = $resultArray[3]

#--------------do some formatting and fix the single quotes in the abstract--------------

$title2 = "'"+$title+"'"

$speaker2 = "'"+$speaker+"'"

$duration2 = "'"+$duration+"'"

$track2 = "'"+$track+"'"

$webid = $i.ToString();

$blurb2 = $blurb -replace "'","''"

#-------------create our insert statement and run it-----------

$sqlstmt = "insert SubmittedSessions (Title, Speaker, Duration, Track, Abstract, webid, url, speaker_url, speaker_id) values ($title2,$speaker2,$duration2,$track2,'$blurb2',$webid, '$url','$speaker_url',$speakerid)";

Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $sqlstmt

}

There will also be some webids that error out – this means the session doesn’t exist for that specified webid. You will see an error in the output to the PowerShell window, but the script will keep running.

Once you run the script and it completes, you will have your table of session data. You will notice that there are more sessions there than have been submitted, according to the website. If you do a count of the sessions by speaker you will also find that there are more than three sessions (the maximum allowed) for quite a few speakers. Why is this? You are pulling from a data source that contains sessions other than the ones that were submitted to Summit (apparently). This means you will have to clean up the data – I included the deleted field for this purpose. Look at the duration and filter for the 75 minute and 10 minute sessions and update your deleted field accordingly.

After this, you will have sessions that meet the duration, but you will find the speaker is not listed on the submission site when doing an advanced search. Clean the data some more.

Are you done cleansing your data yet? You might think so, but you aren’t. You see sessions that look like they were submitted for Summit but are not on the site. These appear to be sessions that were created and saved, but not officially submitted. You can rule out quite a few of these if you look for speakers that have more than three sessions. Get those counts, check the website for those speakers, and clean your data.

“Good lord, am I done yet?”

No. No you’re not.

There will be speakers that have three sessions in your table but only submitted one or two. This sounds worse than it is, but it is a little painful. How do you do this? You’re going to search the speakers with the advanced search on the website. Do these in groups based on the number of sessions a speaker has, ordering by speaker name. You can exclude the the speakers you have already reviewed in the previous clean-up steps, deleting their names from your list as you go. When you find a speaker with three sessions in your list and fewer sessions submitted, mark the session that was not submitted as deleted. Next, do the speakers with two sessions as they may have only submitted one. Finally, look at the speakers with one session as there may be ones that didn’t officially submit anything.

“But what about the code for the clean-up?”

No. You know how to do this, and may write it differently (and better) than me. My code was ugly and I don’t feel like cleaning it up and posting it here. I gave you the script to grab it all. Stop complaining or I will send you to time-out.

The speaker info grab will be a separate blog post. With the script in this post you might be able to use the syntax and craft your own PowerShell script for this. Or you could wait for my blog on it.

Yeah…I haz the datas!

→ 7 Comments

Posted in DBA, Powershell, SQL PASS, SQL PASS Summit, Uncategorized

Tagged passsummit, sqlpass, summit2017

SQL Saturday NEW YORK CITY? Get a rope!

Posted on May 19, 2017 | Leave a comment

This weekend I will be speaking at SQL Saturday New York City. My last time here was two years ago for the last SQL Saturday here and I had a great time. If you are in the area come on out for some awesome speakers and a great day of learning!

Anyone remember this? No? Just me, huh? Ok.

→ Leave a comment

Posted in SQL PASS, SQL Saturday, Uncategorized

Tagged database administration, DBA, sqlfamily, sqlsat588, SQLSaturday

Blogging reboot and PowerShell – Get System Information

Posted on May 19, 2017 | 1 Comment

After whining about not blogging enough, I am going to do something about this. Whether it is PowerShell or SQL, simple or complex, I know that others can benefit from my knowledge and expand their skill sets.

And you are like “That’s great Amy…where’s the PowerShell we are here for?” Ok, Ok….keep your drawers on! 😉

Recently I was tasked with gathering the system information from all of the servers at a client. Another opportunity for some PowerShell dominance.

<#
Name: get_sysinfo.ps1
Author: Amy Herold
Date: 03 May 2017
Purpose: Get the system information from a list of computers/servers, doing one machine at a time and outputting server name
when you can't connect. Output information to a NFO file with the name of the machine.

Notes: Get your list of servers and update the $servers variable below. Make sure they are also formatted the same way as in the sample.
Update the $path variable with where you want to save the NFO files.
#>



<#------------variables you need to change---------------#>
$servers = @('server1','server2');
$path = 'C:\Where_You_Want_The_Files\system_info\';
<#-------------------------------------------------------#>

foreach ($s in $servers)
{
  #------as long as we can connect to the machine, get the system info--------
  if((Test-Connection -Cn $s -BufferSize 16 -Count 1 -ea 0 -quiet))
  {
        $filepath = $path + $s +'.NFO';        
        $cmd = "C:\windows\system32\msinfo32.exe";
        $args = "/computer $s /nfo $filepath /categories +all";

        #if you can connect to the server, gather and save sysinfo
        Start-Process $cmd $args -Wait; 

  }
  else
  {
        "Cannot connect - $s"
  }
    
}

Name: get_sysinfo.ps1

Author: Amy Herold

Date: 03 May 2017

Purpose: Get the system information from a list of computers/servers, doing one machine at a time and outputting server name

when you can't connect. Output information to a NFO file with the name of the machine.

Notes: Get your list of servers and update the $servers variable below. Make sure they are also formatted the same way as in the sample.

Update the $path variable with where you want to save the NFO files.

<#------------variables you need to change---------------#>

$servers = @('server1','server2');

$path = 'C:\Where_You_Want_The_Files\system_info\';

<#-------------------------------------------------------#>

foreach ($s in $servers)

{

#------as long as we can connect to the machine, get the system info--------

if((Test-Connection -Cn $s -BufferSize 16 -Count 1 -ea 0 -quiet))

{

$filepath = $path + $s +'.NFO';

$cmd = "C:\windows\system32\msinfo32.exe";

$args = "/computer $s /nfo $filepath /categories +all";

#if you can connect to the server, gather and save sysinfo

Start-Process $cmd $args -Wait;

}

else

{

"Cannot connect - $s"

}

With this script you can generate system information files and save them to a specified location. It makes sure a connection can be made to the server first, and then outputs the file. The files are created one at a time, so if you pass in a longer list of servers, you shouldn’t crash your machine. From my testing, this will take some time to run as these files don’t output quickly. Despite that, the output is worth it. This can be modified to pull your list of servers from a file or from a Central Management Server (CMS) instance.

Hope you find this useful. Happy PowerShellin’ 🙂

→ 1 Comment

Posted in Database Administration, DBA, Powershell, SQL Server, Uncategorized

Tagged database administration, DBA, powershell, sql server

Podcast with SQL Data Partners

Posted on March 3, 2016 | Leave a comment

Recently I had the pleasure of meeting Carlos Chacon with SQL Data Partners and found out he had a podcast (I know…I live under a rock…with cats). He asked if I was interested in being on said podcast – ummmm…yes, please! Click here (Episode 33) if you wanna go listen to the podcast or here if they get all up in your grill about streaming stuff at the office and you wanna just read the transcript. Thanks to Carlos for the opportunity! Enjoy! 🙂

→ Leave a comment

Posted in Media, Professional Development, SQL Server

Tagged database administration, DBA, DevToDBA, podcast, SQLDataPartners, sqlfamily, sqlserver

Cannot drop the database because it is being used for replication – the Right Fix and the Long Way

Posted on January 27, 2016 | 2 Comments

One of the things I am still trying to get past when it comes to writing about tech is the fact that I can’t always be completely original. I can write about different experiences and scripts but in many cases someone will have already written about the same thing. Part of tech blogging is realizing that even if someone else has already written about something you want to write about WRITE ABOUT IT ANY WAY. Maybe you had a task that was slightly different from what others have written about or maybe you can embellish on something they left out. Bottom line, you can be creative and reach people.

Another part of tech blogging is the fact that sadly there are people blogging about the wrong other ways to resolve certain issues. Your parents told you not to believe everything you read on the internet – are they liars or is that “expert” telling you the best way to do backups is with maintenance plans the one serving up the bull?

I had been asked to detach a database. The person doing it couldn’t and tossed it to me. Cool. No big deal. Then I ran the script to detach and got the error message 3724 – Cannot drop the database because it is being used for replication.

Well, isn’t that special.

Was there replication? Yes. Were there any publications associated with this database? There used to be but not any more (at least not visible at the publisher server in SSMS). There were some other issues that needed resolving but those had nothing to do with this situation. I pop over to my other screen and start looking for the right scripted solution (I figure I will have to do this again so better that I save it off to a script with some notes).

First, I want to show that the database is enabled for replication – sp_helpreplicationdb is going to give me this information.

Ok. Now what?

Based on the results from sp_helpreplicationdb , I now have confirmation that my database is at least enabled for replication. The next thing I need to do is turn off replication for this database with sp_removedbreplication.

Fixed – WITH ONE LINE OF CODE!!!

After ran this I was able to detach the database. And all was right with the world. On Saturday night. Did I mention this was on Saturday night? I’m a party animal.

Anyways, back to the tale of the long way to do this…after my search I had opened a few of the results in new tabs in my browser. One of them was here. After a little more searching based on this link I had what I needed with the right syntax. I resolved the issue but when I was done I still had to go back and close the new tabs I had just opened. It was then that I saw a completely different solution. While this other solution may have allowed you to drop the database, it required far more steps than the one I presented above. Also, in my case, I did not want to drop the database, just detach it. Restoring the database from a backup that contains no data wipes out my database, and was not part of the requirement. So essentially, to be able to detach the database I would have to run sp_helpreplicationdb and sp_removedbreplication OR I would have to take a backup, validate that all the data that was needed was in the backup, create the other database, backup that database, copy the backup file, restore the backup with replace over the database I am trying to drop, and then detach or drop the database. In this case, the desired end result was to migrate the database files to a new server and attach them. This would be changed to restoring from the backup of the original database. If you have a case where you are having to do this for multiple databases, this would be multiplied.

Frustrated after finding this, I took to twitter.

Always interesting to find a blog post with a bassakwards, heavy handed answer to a SQL problem with the right solution in the comments.

— Amy (@texasamy) January 24, 2016

I decided I would do the right thing and give the internet one more page with the RIGHT SOLUTION. While we can’t stop people from posting the longer, less practical solutions to things we can post the right ones and keep posting them.

They might have a few pieces of candy left.

Now that we have established the internet lies to you, go call your mother.

→ 2 Comments

Posted in Uncategorized

T-SQL Tuesday #73 – Those Darn Elves

Posted on December 8, 2015 | Leave a comment

Since I have not blogged in a while, and I saw it was T-SQL Tuesday, I thought I would participate in this FOR THE FIRST TIME EVER! This month SQLBalls asks us if our SQL Servers are on the naughty or nice list. Since I have recently transitioned to a new role, I am still getting familiar with the servers and the environments I am working with. The servers of my past are but a memory, but a fresh one. I cannot even begin to think of all the different “naughty” things that were done on those servers, and how some days it felt like a losing battle. How can you fight a security or code change when you have been over ridden by management, only to have that change come back and bite you weeks or months later? The “I told you so” you might feel like popping off will fall on deaf ears and you will just be stuck fixing the problem…until it gets to be too much and you decide it is time for action.

I have done this before. The day had been a long one and I thought I was finally going home when I got drug into a call on a data issue – something was changed that shouldn’t have been. When and by whom? I didn’t know or have any way of finding this information (days later when I tried to get a backup that was several months old to validate this data from when it was deployed; there was no backup – all all – but that is a completely different issue for another time). I had my suspicions but no proof. Could have been a naughty developer elf logging in with an elevated SQL account they knew the password for. Changing the password? “Out of the question” they say. It is everywhere.

If this were the only event that had happened THAT DAY. It wasn’t. This one was production (hence the conference call). The others (yes, more than one) were pre-production. Messes that had to be cleaned up because the developer elves thought they knew better than the DBA and that they could do it on their own. This production issue was the last straw. Elves were running a muck and had to be reigned in, and they weren’t going to like it.

All the elevated permissions in the pre-production servers – gone. I didn’t care if it was a DEV server. Am I the meanest DBA in the world? So says some. Scrooge? Well, if you are into name calling and want to go there, then ok, but I get to call you names too. In this case I did not care – I was fixing things that were only broken because someone abused a privilege. It should also be said that there was some relation in the names off all the tables involved with all the issues that occurred on this day.

If I had to wag my finger and any naughty part of the SQL Server instances it would have to be at security…and I am partially to blame. It can be difficult to keep up with all the changes that happen across a large environment when it comes to assigning permissions, and if you have more than one DBA, the situation is compounded by the fact that you might not always know what the other is doing and vice versa. They might grant something that you would otherwise veto for cause. You might take care of a permissions issue one way when they would handle it differently.

While I worked on some Powershell code to pull back users from specific AD groups and incorporate alerts for some of those groups, sadly the bandwidth was not there to fully roll this out. I did however create some triggers that would send email alerts when a change was made at the server and database levels, and I made them nameless and encrypted.

/*************ALERTS FOR SERVER ROLES***************/
ALTER TRIGGER [ ] ON ALL SERVER
WITH execute as &#039;sa&#039;,encryption
FOR ADD_SERVER_ROLE_MEMBER
AS 
BEGIN

DECLARE @event XML, @rolename VARCHAR(50),@user_added VARCHAR(50),@adding_user VARCHAR(50)
,@mailbody VARCHAR(8000)
,@mailsubject VARCHAR(200)
,@servername VARCHAR(50)

SET @event = EVENTDATA()
SELECT @rolename = @event.value(&#039;data(/EVENT_INSTANCE/RoleName)[1]&#039;, &#039;SYSNAME&#039;)
,@user_added = @event.value(&#039;data(/EVENT_INSTANCE/ObjectName)[1]&#039;, &#039;SYSNAME&#039;)
,@adding_user = @event.value(&#039;data(/EVENT_INSTANCE/LoginName)[1]&#039;, &#039;SYSNAME&#039;)

SET @servername = @@SERVERNAME
SET @mailsubject = &#039;USER ADDED TO ROLE - &#039;+@servername
SET @mailbody = &#039;The following change has been made to the &#039;+@rolename+&#039; on &#039;+@servername+&#039;:&#039;+CHAR(13)+CHAR(10)
+&#039;User Added: &#039;+@user_added+CHAR(13)+CHAR(10)
+&#039;Added by: &#039;+@adding_user+CHAR(13)+CHAR(10)

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = &#039;DBMailProfile&#039;,
 @recipients = &#039;alert@yourcompany.com&#039;,
 @body = @mailbody,
 @subject = @mailsubject;

END


/*************DATABASE LEVEL ALERTS**************/
CREATE TRIGGER [ ] ON ALL SERVER
WITH execute as &#039;sa&#039;,encryption
FOR DDL_DATABASE_SECURITY_EVENTS
AS 
BEGIN

DECLARE @event XML, @rolename VARCHAR(50),@user_added VARCHAR(50),@adding_user VARCHAR(50)
,@mailbody VARCHAR(8000)
,@mailsubject VARCHAR(200)
,@servername VARCHAR(50)
,@event_type varchar(100)
,@database varchar(50)
,@datetime varchar(100)
,@command varchar(4000)

SET @event = EVENTDATA()
SELECT @adding_user = @event.value(&#039;data(/EVENT_INSTANCE/LoginName)[1]&#039;, &#039;SYSNAME&#039;)
,@event_type = @event.value(&#039;data(/EVENT_INSTANCE/EventType)[1]&#039;, &#039;SYSNAME&#039;)
,@database = @event.value(&#039;data(/EVENT_INSTANCE/DatabaseName)[1]&#039;, &#039;SYSNAME&#039;)
,@servername = @event.value(&#039;data(/EVENT_INSTANCE/ServerName)[1]&#039;, &#039;SYSNAME&#039;)
,@datetime = @event.value(&#039;data(/EVENT_INSTANCE/PostTime)[1]&#039;, &#039;SYSNAME&#039;)
,@command = @event.value(&#039;data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]&#039;, &#039;SYSNAME&#039;)

SET @servername = @@SERVERNAME
SET @mailsubject = &#039;DATABASE SECURITY CHANGE - &#039;+@servername
SET @mailbody = &#039;The following change has been made to the &#039;+@database+&#039; database on &#039;+@servername+&#039;:&#039;+CHAR(13)+CHAR(10)
+&#039;Change Made By: &#039;+@adding_user+CHAR(13)+CHAR(10)
+&#039;Event Type: &#039;+@event_type+CHAR(13)+CHAR(10)
+&#039;Server Name: &#039;+@servername+CHAR(13)+CHAR(10)
+&#039;Database Name: &#039;+@database+CHAR(13)+CHAR(10)
+&#039;Date/Time: &#039;+@datetime+CHAR(13)+CHAR(10)
+&#039;SQL Statement: &#039;+@command+CHAR(13)+CHAR(10)

EXEC msdb.dbo.sp_send_dbmail
 @profile_name = &#039;DBMailProfile&#039;,
 @recipients = &#039;alert@yourcompany.com&#039;,
 @body = @mailbody,
 @subject = @mailsubject;


END

/*************ALERTS FOR SERVER ROLES***************/

ALTER TRIGGER [ ] ON ALL SERVER

WITH execute as 'sa',encryption

FOR ADD_SERVER_ROLE_MEMBER

BEGIN

DECLARE @event XML, @rolename VARCHAR(50),@user_added VARCHAR(50),@adding_user VARCHAR(50)

,@mailbody VARCHAR(8000)

,@mailsubject VARCHAR(200)

,@servername VARCHAR(50)

SET @event = EVENTDATA()

SELECT @rolename = @event.value('data(/EVENT_INSTANCE/RoleName)[1]', 'SYSNAME')

,@user_added = @event.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

,@adding_user = @event.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

SET @servername = @@SERVERNAME

SET @mailsubject = 'USER ADDED TO ROLE - '+@servername

SET @mailbody = 'The following change has been made to the '+@rolename+' on '+@servername+':'+CHAR(13)+CHAR(10)

+'User Added: '+@user_added+CHAR(13)+CHAR(10)

+'Added by: '+@adding_user+CHAR(13)+CHAR(10)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBMailProfile',

@recipients = 'alert@yourcompany.com',

@body = @mailbody,

@subject = @mailsubject;

END

/*************DATABASE LEVEL ALERTS**************/

CREATE TRIGGER [ ] ON ALL SERVER

WITH execute as 'sa',encryption

FOR DDL_DATABASE_SECURITY_EVENTS

BEGIN

DECLARE @event XML, @rolename VARCHAR(50),@user_added VARCHAR(50),@adding_user VARCHAR(50)

,@mailbody VARCHAR(8000)

,@mailsubject VARCHAR(200)

,@servername VARCHAR(50)

,@event_type varchar(100)

,@database varchar(50)

,@datetime varchar(100)

,@command varchar(4000)

SET @event = EVENTDATA()

SELECT @adding_user = @event.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

,@event_type = @event.value('data(/EVENT_INSTANCE/EventType)[1]', 'SYSNAME')

,@database = @event.value('data(/EVENT_INSTANCE/DatabaseName)[1]', 'SYSNAME')

,@servername = @event.value('data(/EVENT_INSTANCE/ServerName)[1]', 'SYSNAME')

,@datetime = @event.value('data(/EVENT_INSTANCE/PostTime)[1]', 'SYSNAME')

,@command = @event.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'SYSNAME')

SET @servername = @@SERVERNAME

SET @mailsubject = 'DATABASE SECURITY CHANGE - '+@servername

SET @mailbody = 'The following change has been made to the '+@database+' database on '+@servername+':'+CHAR(13)+CHAR(10)

+'Change Made By: '+@adding_user+CHAR(13)+CHAR(10)

+'Event Type: '+@event_type+CHAR(13)+CHAR(10)

+'Server Name: '+@servername+CHAR(13)+CHAR(10)

+'Database Name: '+@database+CHAR(13)+CHAR(10)

+'Date/Time: '+@datetime+CHAR(13)+CHAR(10)

+'SQL Statement: '+@command+CHAR(13)+CHAR(10)

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBMailProfile',

@recipients = 'alert@yourcompany.com',

@body = @mailbody,

@subject = @mailsubject;

END

The lack of a name for each of these is intentional, as is the encryption. The last thing I wanted was someone seeing these ans what they were doing, and if they had permissions to do so, disabling or dropping them to avoid having their nefarious behavior tracked. Even better would have been to put additional triggers in place to prevent the dropping of these no matter what, but I decided not to go there.

Note there is nothing there for the name for each of these – this is courtesy of the devious mind of Rob Volk. He might have too much time on his hands but this is pretty darn crafty. What you name these triggers is up to you but you have to MAKE NOTE OF WHAT THE NAME ARE!!! When I did these they were a combination of a few tabs and spaces – like “space space space tab tab” but with those actual characters. The result looks like this:

It should go without saying use this code at your own risk and always thoroughly vet and test anything before applying it to a production environment.

If this helps further cement my meanest DBA creds then I guess I am doing it right. Sometimes the elves developers can get out of hand and it is up to Santa the DBA to make sure they know they are being watched.

→ Leave a comment

Posted in Challenges, SQL PASS, SQL Server, T-SQL Tuesday, Uncategorized

Tagged DBA, security, sqlpass, SQLSaturday, sqlserver, tsql2sday

My First Time Submitting to SQL PASS Summit – Part 1

Posted on March 20, 2015 | 2 Comments

I have now officially submitted to SQL PASS Summit 2015. Five sessions. All me (well, and a few others on the panel I submitted). The hard part is over…but how did this all happen?

My story starts back in the year 2013. My first time to attend a SQL PASS Summit. It was in Charlotte, NC. I didn’t know these were normally in Seattle. It didn’t really seem to matter too much either. I was a first timer. And they labeled me as such.

I didn’t mind. I knew I was a newb. I embraced it. I signed up for and watched the webinar for first timers put on by Denny Cherry. I also got a first timer buddy (aka Someone I can follow around like a puppy until I am comfortable enough and have found other suitable, like minded, people that will welcome me and allow me to accompany them to places that sell beer).

I met so many new people while at Summit that year and out of all the conferences I had been to in the past, this was by far the best experience I had ever had. One this that happened numerous times when talking to people was hearing the words “Have you thought about presenting?” I’m all “Wow…these people are nice…and encouraging…wait…presenting? Say what?”

Did they need fresh meat? A new crop of willing yet unsuspecting folks to throw their hat in the ring, to hopefully be selected, and dive head first off that cliff into something that might become an obsession that would challenge them in ways they had never thought of before? Maybe. Maybe they did. Maybe it was a little cultish. Well, pass the kool-aid.

I came home and gave it some thought. I really wanted to do this but did not know where to start. I felt like I had stuff to say. Stories to tell. Experiences to share. But where to start? I decided I would do a panel. I rounded up some experienced speakers for a particular SQL Saturday, created my session and submitted it. That was the easy part. I then decided we would have weekly internet video chats so that everyone could get acquainted, and I could get their take on how they saw the panel going. They were also able to provide me much needed guidance with my slide deck (something else I had never done before) and how the session needed structure. I took all this in and did my slides accordingly.

The time finally came for the SQL Saturday event and my panel session. I made the trip out there and met up with everyone. I attended my very first speaker dinner. I could not believe I was there and in the same room with some of the brightest and well known minds in the SQL community. This was it – my first taste of what would consume that entire year.

The panel went well. My fellow panelists and other patted me on the back for a job well done. Feedback was good. I was elated. I was hooked. I thought I was ready to handle what was up next – my first solo session. Just me and my slides. Another SQL Saturday. Another city.

To be continued…

→ 2 Comments

Posted in SQL PASS, SQL PASS Summit, SQL Saturday, SQL Server, Uncategorized

Tagged billfellows, dennycherry, sqlfamily, sqlpass, sqlpasssummit, SQLSaturday, sqlserver, summit13, summit15

Code Upload – Pragmatic Works webinar

Posted on March 19, 2015 | Leave a comment

For those that attended my webinar last week with Pragmatic Works I have uploaded the code…finally. For what it is worth, the weekend was busy and I was working on my SQL PASS Summit submissions. Then, just as I was getting this together on Monday night, I was not feeling right and realized I was making mistakes and that is no good. Tried to go to work on Tuesday but even they knew something was wrong with me. Went home and found out later my nephew was also sick. No need for any further details. Just trying to get better. Thanks for your patience. 🙂

Download Now!

The lovely people at Pragmatic Works sent me some of your questions from the webinar. Without further adieu…

Do you have a suggestion for BEGINNERS learning books to get a basic understanding of PS??

Great question! While I have a TON of books on all kinds of things (SQL Server, C#, web dev stuff, etc.) I do not have a single book on Powershell. How did I accomplish all this then? Simple – using this pesky thing we call the internet. I have also attended some great Powershell sessions at SQL Saturday events.

Did they leave any beer bottles?

The roofers? Thankfully no.

Do not criticize the king of soda pop, Dr. Pepper!

Preach it!

When will VBScript stop functioning for SQL Server or Windows Server. As in will there be a total switch?

VBScript? Hmmmm…great question. No clue. This might be a Microsoft cockroach. Like replication.

why we need a PS-Drive – why we can just use an arraay directly?

You mean passing in the server name from the array to the Invoke-SqlCmd? Yeah, you could do that. That’s just not how I wrote this. 🙂

what is her email again?

Leave a comment here if you would like to email me and I will get back to you.

does the ( $servers | Measure-Object).count syntax do?

That counts the number of items in the array.

What is the purpose of the New-PSDrive?

New-PSDrive is a method of connecting to a SQL Server instance. Another method would be to declare a connection with SMO (Server Management Objects).

Does it work on windows 2003 as well?

I have not tested this against Windows Server 2003.

so, ps-drive is the equivalent of object explorer in SSMS?

Well, kinda. Maybe. More the equivalent of browsing a drive in a cmd.exe window.

what city in Austin?

Ummmm….Austin is a city.

What permission you require on AD for this excercise

To make the AD changes, I have no clue. Not my area and I also intentionally created the Azure VM so I could make all the changes I needed to without worrying about a silly thing like permissions. As far as reading from AD (which is the main Powershell script for this part) I am not sure about this either but I am assuming you need some level of read permissions (which is all that script is doing).

There are some things where I may tend to run something, and if anyone says anything, ask forgiveness later. Security is pretty tight where I am – I have gotten dinged on xp_cmdshell but so far nothing on reading from AD. If for some reason you can’t run anything against AD, I would find the person over that and find out why, and let them know what you are trying to do.

Thanks again to everyone who attended. If you have any other questions leave a comment. 🙂

→ Leave a comment

Posted in Powershell, SQL PASS, SQL Saturday, Uncategorized

Tagged activedirectory, database administration, powershell, pragmaticworks, SQLSaturday, sqlserver

Holy Blocking Chains, Batman – I’m Speaking at PASS Summit!

Speaking – SQL Saturday Baton Rouge!

(UPDATED) PASS Summit 2017 Session Data (You know you want it…so go get it!)

SQL Saturday NEW YORK CITY? Get a rope!

Blogging reboot and PowerShell – Get System Information

Podcast with SQL Data Partners

Cannot drop the database because it is being used for replication – the Right Fix and the Long Way

T-SQL Tuesday #73 – Those Darn Elves

My First Time Submitting to SQL PASS Summit – Part 1

Code Upload – Pragmatic Works webinar

Recent Posts

TexasAmy

Recent Comments

Archives

Categories

Meta