SQL Saturday - Baton Rouge # 234 - 2013

I went to (and spoke at) SQL Saturday #234 in Baton Rouge this past weekend.  It was incredible - yet again!  I wanted to list the sessions I went to and a little blurb about each.

8:40 - 9:40 AM == I attended a class taught by Thomas LeBlanc - "Execution Plan Basics - Beginners".  When I learned about how active and friendly the SQL community was last year, I started a 5 year plan to get my name known and help others.  I finally broke down and joined Twitter.  Tom was the first person to follow me, which had me very excited to meet him.  I walked in with very little knowledge of the execution plans.  I knew they existed and I knew I could use them to find what the SQL Engine does to get and return the data.  His session taught me about several little tricks to see more performance tuning data, and to be able to read an execution plan.  He started by giving an overview of the history of execution plans, including showing us the text that used to be the only way of reading an execution plan - now there is an easier to understand GUI.  He showed many examples and demos and was very easy to understand.  I was really glad I attended.  During his presentation he also gave many pointers on where to look for more information.  Here is the link to the abstract and his slidedeck on the SQL Saturday website.

9:50 - 10:50 AM == The next session I attended was taught by Ryan Adams - "SQL 2012 AlwaysOn Quickstart".  I went into this class not expecting to learn all about it, but hoping to learn enough to be able to research on my own.  I had no doubts in Ryan's teaching abilities, but I have heard a lot of very brief things about AlwaysOn and knew it was a very big topic.  I also knew that it was something I didnt think I had enough overall SQL knowledge to understand quite yet.  I was pleasantly surprised when the session was over, because Ryan explained everything in a simple enough manner that I feel I have a very good understanding of the entire concept of AlwaysOn.  I know I have a lot to learn about the specifics, but overall I was thrilled to be able to have thought of 3 distinct ways it would help us out at my current job.  I saw a presentation of his at last year's SQL Saturday, also in Baton Rouge, and even though he had to play charades a bit due to the projector difficulties, I still walked away with a couple key points I had learned.  This year was even better.  Here is the link to the abstract and his slidedeck on the SQL Saturday website.

11 AM - 2:30 PM (except for the lunch break) == I am going to talk about the next 2 sessions I attended together.  They were taught by Sean McCown - "Beginning PowerShell for DBAs 1.0 and 1.5".  I have to put a disclaimer here that I was SUPER excited about meeting him and his wife this year.  I had first learned about the SQL Community in general when I learned about the SQL Saturday event in Baton Rouge last year.  This was right-up-my-alley and I had a great time.  I met Sean and Jean at sessions last year, but I was a no-body so they wouldnt have known about me.  I found out about their website and weekly webshows - so I started attending the shows regularly.  By the time I got to meet them this year, I knew they would at least recognize my name.  I like the way Sean teaches beginner sessions and since I have been chomping at the bit for learning PowerShell, it was perfect opportunity.  His sessions taught me a lot and now when I go onto the midnightdba.com site to learn more, I have a very good context to pull from.  He stepped through the very basics of PowerShell and emphasized the point that whether you are working with the OS, SQL, Exchange, etc., it is all the same - same cmdlets, same syntax, etc.  He showed the use of PowerShell and went into examples of applying those scripts to multiple servers or DB instances very easily.  All in all, I had high expectations for this class, and as usual, his knowledge and ease of teaching exceeded those expectations.  Here are the links to the abstracts on the SQL Saturday website: link for the 1.0 class; link for the 1.5 class.

2:40 - 3:40 PM == This is the session where I was the speaker (so I kind of HAD to attend).  I taught "The ABCs of SSIS".   Here is the link to the abstract and slidedeck on the SQL Saturday website, you can also access it on this website at this link.  I have given this presentation online before, but this was my first live technical presentation.  I was very impressed with the way everything was handled.  There was plenty of notification before the event via email and on the phone.  I knew that anything I needed would be answered promptly.  They gave me all the information I needed though, and since I had been an attendee before, I had a very good understanding of what to expect during the event.  What I didnt know though, was how much fun the people who run it are!  As an attendee you dont see all the camaraderie that I was privvy to.  It was great to see the hard work that was put into it, but also the way the event leaders, speakers, sponsors, and volunteers interacted.  I sincerely hope I get to speak at the next year's event.  What a wonderful first experience!  I will be constantly comparing other events to this one - it was just that good. :)

 

SQL Sat #234I cant imagine all the work that goes into the event each year, but this is yet another year where things look flawless from the end user perspective. :) The coordinators and volunteers didn't show the panic behind the scenes at all - in fact they looked like it was just a barrel of laughs. I look forward to going next year!

ABCs of SSIS

This presentation shows the basics of SSIS to help with automating database tasks, such as maintenance, importing and exporting data, or ETL transactions. The discussion will start with an understanding of when SSIS should be used vs. when a database object, such as a view or stored procedure, should be used. You will learn about the concepts of syncronous vs asyncronous and non-, semi- and full- blocking transformations.  You will also see how to create a basic package, and how to use the built-in logging and configurations. Lastly, we will talk about the importance of organizing the overall SSIS structure.  This presentation gives you a great start on how to use SSIS, gives you something to think about and provides resources for where to continue learning or researching.

You can get to the presentation slide-deck by clicking here.  There are helpful notes on most of the slides.

This session is also available via a recording, which can be found by clicking here.  It was presented to the Virtual DBA Fundamentals PASS chapter.  ** Note: Please right click and choose save as, then watch it from your local PC.

Presented At:

  • November 2, 2013 - SQL Saturday - Dallas, TX (Abstract)
  • August 3, 2013 - SQL Saturday - Baton Rouge, LA (Abstract)
  • December 4, 2012 - DBA Fundamentals PASS Virtual Chapter  ** recording available (Meeting Archive)

TSQL Tuesdays # 42

It is time for TSQL Tuesday - Wendy Pastrick (blog | twitter) is hosting this month and the topic is "The Long and Winding Road". Wow, this is a great topic - I cant wait to read many of the other posts over the course of this week! :) This is my first time writing for TSQL Tuesdays, and I cant think of a better way to start.

To start off with, computers have been a part of my life since I was "knee high to a grasshopper". I remember playing on the ATARI and the Commodore. That was back in the days when you put the cartridge for Frogger in, went to have dinner, and then by the time you finished, it was loaded and you could play. I also remember when the game BOMB SQUAD came out and it TALKED!! Holy smokes it was totally AWESOME at the time! :) (I don’t do a good impression of those words, although I try, but they are definitely etched into my brain for all time.) I remember thinking, at that young of an age, how smart and creative you would have to be to come up with stuff like this. I dabbled in basic for a bit, and I am proud to say I wrote my first code at a tender young age - I could print on the screen "Hello Dad, this is cool!" and make it repeat X number of times.

Skip ahead a few years (ok many… but who is counting?) - I was an administrative assistant at an HMO in Florida. I always had a knack for picking up on how software worked by playing around, and as an Admin Asst, it came in handy to be able to help others. I got in good with the IT guys, I mean after all, you should always make friends with IT. That is the first time I was introduced to databases. I worked in the Pharmacy department and we had a vendor that tracked the utilization of medications for our members that we needed to better analyze. So, I used the tools I knew, Microsoft Office and came up with a fabulous solution. I imported the csv file they sent monthly into an Access database (please don’t cringe, the story gets better - I promise :)). Then, scrubbed and manipulated the data, and finally spit it out to Excel. Excel took over and I ran a few macros, and voila - the end result was a formatted report that was used as is. Everything was automated and I LOVED it!

I should have taken the hint even back then, but it took several years and a few jobs in between for me to figure out my passion - data and database management. Right now, I work with SQL 2008 R2, but I have worked with a number of database platforms (MS Access, FoxPro, Visual FoxPro, mySQL, SQL Server 2000/2005/2008/2008 R2 and now delving into 2012). I have loved the intricacies of each, but like working with SQL Server most. I am in a 2.85 person BI team, and while I think what they do on the analysis side is cool, nothing compares to digging my hands into the database directly. I like giving accurate data into the hands of my colleagues and letting them do whatever they wish with it. I also love SSIS! I don’t know what I would do without it at the moment.

While SQL Server is what is taking me to new places right now, I have to be honest. I cherish the memory of when I made my first integrated BI app using an Access database and Excel macros. I don’t know where I would be without that experience. I truly hope to have that kind of moment in SQL Server. I am studying to get my certifications in SQL Server 2012. Hopefully along the way I will have a chance - with such interesting characters in this industry, it is probably almost guaranteed! :)

Add Location to Open File dialog window in SSMS

Recently I realized I kept going to the same folder over and over...  I have my "play" files on my C:\ drive, but I have a network share that I keep scripts and project related stuff that I really dont want to lose.  Most of my stuff is database development related, so the stuff on my C:\ drive is also in the DEV database (and I am lucky enough to be the main one who works in DEV - only 2 others really do and that is minimally).

I put a shortcut in the My Documents folder, but realized that it didnt help as much as I wanted.  Therefore, I found a way of putting a shortcut in the left hand pane of the Open dialog box. Look at the "home" option in the image below - that is custom :)

Oracle datetimestamp to SQL Server datetime

Why doesnt this work Arggg... I am a SQL Server worker... why do I need to know Oracle too??? That is what I thought when I came across a situtation where dates were throwing a fit. I was pulling data from Oracle into a SQL database.

I started using the LINKED SERVER, but switched to use OPENQUERY because they work differently. At first I didnt care why they did, but I am now coming back to learn much more about it and explain it. Honestly, I am hoping I will remember I am typing this so that when it comes up again, I will have a place to check! :)

This article is all about the dates, not about other data types (hence the name). I learned an interesting fact: SQL Server allows dates to go back to January 1, 1753, but Oracle allows dates earlier than that.

The problem was the date I was trying to bring into SQL was before that date and therefore threw an error:

Msg 8114, Level 16, State 8, Line 4 
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

OPENQUERY sends the query to the remove server to be executed there, rather than being executed on the server calling the query, which is what a LINKED SERVER does. To make myself remember it better, using the LINKED SERVER gets back all raw data from Oracle, then processes the query more, which means the dates we want are included and therefore throws an error. On the other hand, using OPENQUERY makes the Oracle database process the query, which means the dates are not included in the returned recordset. It also means that with OPENQUERY we can use the Oracle functions, which is very handy at times.

Word Wrap in Annotations

Well - one would think that word wrap would be something that isnt too difficult, but when I started typing in an annotation today (look at me - commenting!!), it didnt wrap my text.  I tried enter, to no avail.  If you are stuck - hit Ctrl-Enter and that will wrap your text for you.  Simple enough, but it took me a little bit to find it.  Hope it helps someone! :)

Backup Tune-up

SQL Saturday - Aug 4, 2012 - Baton Rouge

Instructor: Sean McCown (Midnight DBAs)

Backup Tune-Up

Contact Info:
webshow.midnightdba.com
DBAs@midnight - webshow

This post is going to be a copy of my notes which arent going to make a lot of sense, but when I review them at different times, I am hoping to pick up on maybe what he was saying when it is applicable. There is a lot of information on their website, including the demo that he performed

Restore >> Execution Plans >> log zeroing
- fill a full file with zeros
- then fills it in with SQL data

IFI - data files only

  • instant file initialization
  • turn on - local security policy >> user rights assignments >> 1) perform volume maintenance task and 2) restart SQL service

Trace Flags - change the normal behavoiur of SQL

  • 3014 - never gonna use - troubleshooting HEX info
  • 1806 - turns off IFI - current session
  • 3605 - send to SQL log
  • 3004 - zeroing data came from here
  • 3604 - send to client (not log)
  • 3213 - tells how many buffers using by default
  • 3212 - close to 3213 (sister to it)
  • There is a SQL Server Central post that displays a lot the known ones

Increase the speed of backups - best ways

  • compression - only works 2008 and above - enterprise and above
  • buffer count (# of files) - striping to separate drives (or same drive)

More things discussed/shown

  • buffers - memory to use
  • striping - threads to use
  • max transfer size - amount of data to write at a time - chunks of 64bytes (2MB is a bit excessive)
  • cant stop zeroing log files - growing log files (1 GB is ok)

Benchmarking

  • read and write
  • NUL - goal - cant do any better - goal is to get as close to these numbers as possible
  • Find out how fast you can do it with all the resources available
  • Good to know you can do it BUT - if you do it you will hurt production resoruces
  • But - if things are stopped due to issues, its nice to know the smallest amount of time it will take if all resources are available

Thank you for visiting!