Archive
T-SQL Tuesday #14 – Resolved to do better
This month’s #TSQL2sDAY is being hosted by Jen McCown (Twitter | Blog). The topic this month is about techie resolutions for this coming New Year.
Review – You suck!
I am my worst critic and I can usually talk myself out of most things. One of the hardest things for me to do last year was blog. I still consider myself a newbie to the whole blogging thing. I haven’t found my niche or what I am trying to convey. I am still feeling a bit random about what to post. My first blog post was my last year’s 2010 resolution [Blog].
Reflect – It was a year ago?
Wow, can’t believe a year just passed.
I just posted my 2010 Review [Blog] and if I was actually being graded on these, I would be taking summer school.
Resolved to do better
I am not so much into setting New Year’s Resolutions and as you can see from my 2010 review (link above) I did terrible. This year the plan is to be as vague as possible to achieve higher scores (not really). Instead of resolutions, I shall call these goals.
1. Take Microsoft SQL Server 2008 Cert Exam 70-432. My primary reason is to keep up my skills and learn more about SQL Server areas that I don’t necessarily use in my day to day job. Last year I set a goal for myself to take the 70-431 and passed it in July 2010 [Blog].
2. Participate in all T-SQL Tuesday Blog posts in 2011. I participated in 3 events in 2010.
3. Write at least 1 SQL Server Blog post a month.
4. Volunteer\Help at SQL Saturday #68 in Olympia (April 2011). If I am able to attend PASS in 2011 (3rd timer), I will volunteer there as well. Hope my boss approves budget.
5. Continue to learn SSIS. I worked on two projects this last year using SSIS, which I really enjoyed and hated at the same time. It’s funny looking back at the first project that I built. Oh, please nobody look at it. HAND IN FACE.
Better planning, being more organized, and continue to grow as a DBA
T-SQL Tuesday #13 – I don’t know what I want, but I want it
This month’s TSQL2sDAY is being hosted by Steve Jones (Twitter | Blog). The topic this month is around “what the business says is not what the business wants”.
Have you heard this one before? I am not sure what I want, but I want it.
I am currently involved in a project where we are upgrading/moving from an old in-house created application to a new in-house created application. This involves the business analysts working with the other department getting all of their business needs. Don’t get me wrong you need the input from the people that are going to be using the system or it will fail. However, if you don’t know what you want, how can we deliver that to you.
I think what I am trying to convey is we do need to interact with the business users to see what they really need and give them those things that will help them do their job, which helps the company, which helps us stay employed.
I am thankful for a strong business analyst group in our department. They know more about the business process then the actual users do. They are able to anticipate with the user needs and work with development to give them those features.
Beware of the Dark Side of negativity
I read an interesting article the other day about dark side of negativity and pettiness. The jest of the article was maintaining a positive attitude during stressful times. The article was in my wife’s ASRT Scanner magazine written by Peter Rath, M.P.A., R.T.(R). (Article is unavailable to link to, not online).
The author goes on to say that it is easy when things aren’t going right or in a stressful situation to say something that you will regret later.
Reminds me of the scene in Star Wars – Return of the Jedi where the Emperor is trying to turn Luke to the Dark Sid
e. You too must resist the draw from “The Dark Side”, The Dark Side of negativity. Oh, I feel the anger building up in you, let it out, let it turn to negativity.
I think we all have been in stressful situations at work. A user complains about a reporting taking a long time to run, to a server/database crash. The stress can escalate when a solution is needed right away and an answer is not known right away. How we handle this can tell a lot about you as a person. These are the situations where we can shine and be that Rock(star) or be the “Go-To Guy or Girl”.
I leave you with a quote from Jedi Master Yoda. “But beware. Anger, fear, aggression. The dark side are they. Once you start down the dark path, forever will it dominate your destiny.“
What do you do to help you in stressful situations?
T-SQL Tuesday – Why Are DBA Skills Necessary?
This month’s topic is hosted by Paul Randal (blog | twitter), and the topic is “Why are DBA skills necessary?”
Just like any trade there is a specific level of skill necessary to do the job. Just like an auto-mechanic there is training to do the job. You wouldn’t want just anybody working on your car, especially if it is a newer one with all the computer modules etc.
There are some basic things that a technical people can do in SQL Server, like changing a flat tire. You don’t need to be a rocket scientist to change the tire. You do need to have a jack, wrench, some brute strength, and the knowledge where the spare is located, most people can change the tire. If you want to change the oil the on your car you need to be a little more technical. You need to know what type of oil you need and the oil filter, and location of the filter.
I believe that there are skills necessary to manage a database. As much as software vendors like to say there is no "Set it and Forget it" feature in SQL Server. If you forget it, it will bite you and usually where the sun don’t shine.
In my previous job I worked in technical support for a software company. I would get a call once a week from customers where their Transaction Log file filled up their C Drive (insert laugh here), and they had no database backups (insert tear here). This is DBA 101 stuff, actually it is Business 101, but most of the companies I dealt with did not have a DBA on staff. It was generally the person closest to the server.
In those cases and most of the other issues that I dealt with if they had someone with the basic SQL Server knowledge it could have prevented 99% of their issues.
Bottom line: I feel that a DBA is necessary for the well being of your companies PRICELESS data.
Requesting SSIS Help
Data Import from log/text file (poorly formatted)
I have been given the task to import some data logs from our firewall system into SQL Server for reporting purposes and to help keep data longer (also for reporting) as the firewall system database needs to purge data more regularly.
The fun part is the firewall software uses PostgreSQL as its database and I have not found a way to export the data out and into SQL Server. So that leaves me to try and import the daily logs that it creates.
Sample data:
1120557876,,9087042812EBD,1006,1143243492,1,0,6,SMTP-Inbound-SlimeWarner-00,smtp/tcp,102.98.153.2,1163,72.192.41.10,25,0.0.0.0,0,0.0.0.0,0,Slime Warner,Trusted,590,0,0,,SMTP-Incoming.GUS.1,,,,header=Date: Sat\, 4 Sep 2010 11:30:01 +0430,rule_name=Date:*,,,,,tr,ProxyAllow: SMTP Header,,2010-09-04 00:00:10
I created a simple database on my SQL Server called Firewall and created a table similar to the table in the firewall software. (I know bad table)
USE [Firewall]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Firewall].[dbo].[cluster_traffic] (
[sid] NVARCHAR(50),
[cluster] NVARCHAR(50),
[sn] NVARCHAR(50),
[tag_id] NVARCHAR(50),
[raw_id] NVARCHAR(50),
[disp] NVARCHAR(50),
[direction] NVARCHAR(50),
[pri] NVARCHAR(50),
[policy] NVARCHAR(50),
[protocol] NVARCHAR(50),
[src_ip] NVARCHAR(50),
[src_port] NVARCHAR(50),
[dst_ip] NVARCHAR(50),
[dst_port] NVARCHAR(50),
[src_ip_nat] NVARCHAR(50),
[src_port_nat] NVARCHAR(50),
[dst_ip_nat] NVARCHAR(50),
[dst_port_nat] NVARCHAR(50),
[src_intf] NVARCHAR(50),
[dst_intf] NVARCHAR(50),
[rc] NVARCHAR(50),
[pckt_len] NVARCHAR(50),
[ttl] NVARCHAR(50),
[pr_info] NVARCHAR(50),
[proxy_act] NVARCHAR(50),
[alarm_name] NVARCHAR(50),
[alarm_type] NVARCHAR(50),
[alarm_id] NVARCHAR(50),
[info_1] NVARCHAR(100),
[info_2] NVARCHAR(100),
[info_3] NVARCHAR(100),
[info_4] NVARCHAR(100),
[info_5] NVARCHAR(100),
[info_6] NVARCHAR(100),
[log_type] NVARCHAR(50),
[msg] NVARCHAR(255),
[bucket] NVARCHAR(50),
[update_time] [datetime] NULL
)
The problem that I am running into is with the [info_] data field. Some of the data has commas in it and this is causing problems when trying to import into SQL Server.
I initially used the SQL Server Import and Export Wizard in SSMS and this is how the data looks when I preview. Some of the data is jumbled and moved over to the wrong column. The highlighted shows the problem.
Question:
Is there a way to import this data correctly through SSIS with some sort of pre-cleanup?
Have you heard “Don’t be this guy”?
Don’t be this guy
Take a moment and read Tim Mitchell’s [Twitter | Blog] blog post “Don’t be this guy” from last year. Basically he writes about his experience at a previous summit where he did not really take advantage of the networking opportunities at PASS and really regretted it afterward. It is really a great read and it home with me.
Unfortunately, I did not read this until after my first PASS Summit last year. Sad to say, I was that guy last year. In reading the comments of his blog post, it looks like there were other people just like Tim, that were that guy as well.
This year, I am not going to be that guy. I really want to meet a lot of the people that I follow on Twitter and who’s blogs I read on a regular basis. I am going to be somewhat of a summit stalker.
Get yourself out there.
To help with the initial conversation ice breaker I had some Moo cards made up that I plan to distribute out at PASS. Not so much of a business card, but my social contact info (Twitter, email, LinkedIn, and Blog) “Just for fun”. I have 50 of them, so my goal is to distribute all of them during PASS. I think they will make a pretty interesting and fun conversation topic.
Events I am going to do.
Monday : I am going to participate in the PASS 2010 Photowalk [Follow Twitter tag | #passphotowalk]. This is put on by Tim Ford [Twitter | Blog] and Pat Wright [Twitter | Blog]. Find out more about it here.
Monday night is the Welcome Reception and Quiz Bowl (6:30-8pm) and SQLServerCentral opening night party (8pm-10pm).
Tuesday : Birds-of-a-feather (BOF) Lunch (11:45am-12:45pm) and the non PASS sponsored event Inappropriate PASS Session party put on by Jeremiah Peschka [Twitter], Jen [Twitter], and Kendra Little [Twitter]. Should be fun.
Wednesday : WIT Luncheon and Panel Discussion (11:30am-1:30pm) and the Microsoft Appreciation Party at Gameworks (7pm-10pm)
Thursday : Chapter Lunch (12pm-1pm)
Please check out SQLPASS’s website for full details on all the events. Be sure to follow #sqlpass on Twitter for any impromptu events as well.
I am ready to get my SQLPASS on! Are you?
Passed 70-431 – late to the party
Goals
One of my goals that I set for myself at the beginning of the year was to take a Microsoft Certification exam. I eyed the 70-431/2005 exam mainly because I have been working with SQL Server 2005 for several years now and felt comfortable with this version.
In a previous post I discussed what books I used for studying for the exam, they’re all good, but for me the biggest thing was getting my hands dirty. I setup a test machine at home where I could try and work through a lot of the features that I was less familiar with (like End Points and Service Broker). This helped me get a better understanding of those features. For me this is how I learn. If I read it and don’t actually do it, I won’t understand it.
I finally got serious this last month and arranged my exam for July 31st. It was set and there was no turning back. I think that I get a little more serious when there is a deadline or goal to accomplish.
Test Day
On the test day I slept in and had a nice breakfast. I did some last minute cramming on XML and End Point (yuck), and did a last minute practice test. I shouldn’t have done that. I failed miserably, let the self-doubt begin.
I arrived an hour before scheduled test and spent the time studying in my car. I finally went into the testing site and took the test. You know where all that old computer technology goes, it goes to these testing sites.
I took my time during the test and did not go back and review any answers. Once I hit the next button my answer was stuck in stone. I don’t like going back and reviewing my answers, this causes me more self doubt. I say let it ride.
Got to the last simulation question hit the next button and then the big finish button popped up. I took a deep breath and clicked. It was like an eternity, I could hear the computer grinding and churning. The results came up on the screen, but I didn’t initially see it. I saw the possible score 1000, the passing score 700, and then my eyes finally found my score. Bada Bing! Passed! Felt like the world was lifted off my shoulders.
Walked up to the front desk trying not to show my joy and got my results. Went out to the car and sent a couple of texts to the wife and drove home. To celebrate I took the family to Red Mill Burgers in Ballard. Best burgers EVA! My plug for the community. Also, I like good food.
New Goals
Next up 70-432/2008. I am initially planning on late October – Mid November to take that test. Over the weekend I wiped my lab computer clean and started a fresh build for my SQL 2008 labs. Let the party begin. Wahoo! I am certifiable.
What you’re running SQL on a netbook?
In my previous blog post I gave my review of my new Toshiba Satellite T115D-S1120 netbook. My primary use of the netbook so far has been for checking email, syncing my Zune, a little bit of TweetDeck, working with Microsoft Word, and blogging. That was until I came across Noel McKinney’s blog post [Post1 & Post2 | Twitter] where he recently picked up a new netbook and installed SQL Server 2008 R2.
My first initial thought was You’re going to do what? Then it quickly changed to hmmm why not? You see Noel is one of the lucky few that are getting ready for SQLCRUISE trip with co-Captain Brent Ozar [Blog | Twitter] and co-captain Tim Ford [Blog | Twitter]. Noel is setting up his netbook for the onboard training and why not. Netbook’s are compact, the battery life is amazing, and they are getting more powerful. I was amazed that I could get 4GB of ram in mine and that it was 64-bit.
I was still a little hesitant at first to install SQL Server on my netbook. I wasn’t sure how the single core processor was going to handle running SQL Server, but this is what we do, we think outside the box, we overclock our CPU’s, we put cool spoilers on our cars to make them look fast. Maybe not the last one, but you get my drift.
Not everything goes faster with a racing spoiler.
Why not install SQL Server on a netbook? What the heck, I took a good image of my machine and took the leap and installed it. I installed SQL Server 2008 R2 Developers edition (x64), why not since it is a 64-bit machine. I also installed Reporting Services and Analysis Services, as well as AdventureWorks sample databases and exercises.
One of the things that I did in my SQL Configuring was I set the SQL Services to manual and I created a desktop shortcut on my desktop (just like Noel explains in his post), so I could start the services when needed. This will help keep unnecessary services running every time the computer is started.
It’s a little to early to know for sure performance or what exactly I will use it for, but so far I have been very happy. Stay tuned I will blog more on this.
Lifetime of learning
This month’s T-SQL Tuesday #008 “Gettin’ Schooled” is being hosted by Robert Davis [Twitter | Blog] and the topic is on learning and teaching, which can be found here. Great topic and I can’t wait to read other people’s blogs on the subject.
How do you learn and what am I learning?
I am a hands-on learner. I need to read it, touch it, and test it, (smell it and taste it) to get a good understanding of it. I generally start by reading an article, then step through the process, and finally test the process to get a better understanding. My favorite types of blogs are those who are very detailed and step through the process.
I am currently studying for my Microsoft SQL Server 2005 Implementation and Maintenance (70-431) exam. This process has been very challenging and rewarding for me. I am learning new aspects of the software that I have not used in my current position, like service broker and end points to name a few. For my studying I purchased Wiley’s Microsoft Office Academic Course (MOAC) book and I love how it is arranged. It does a great job explaining the topic and then goes into a great detailed lab that details the steps of the task.
How do you teach?
I did some teaching at my previous job. I taught a 5 day admin course on the ERP software that the company developed and did several conference style sessions. The classes were generally small with on average of 6-10 people and the conference sessions could range from 15-30. This was a huge challenge for me because I am generally a shy person and getting out in front of people presenting was really terrifying. I tend to be a very technical person and this was my style of teaching. I tried to give technical information in a way that was easy to understand and follow that up with hands-on labs that helped put what you learned to practice. With more practice I think that I could be a better speaker and I may look into that in the near future.
What is successful teaching?
I think that successful teaching has to have content that is delivered in a fashion that the person being taught can understand it, remember it, and be entertained in the process. I am not a big fan in which the instructor is reading the information from a book or slide deck. I don’t get anything from it and I can do that on my own.
I think that we all something that we can teach each other and learning, well it takes a lifetime.
I am always doing that which I can not do, in order that I may learn how to do it.
-Pablo Picasso
Thanks Robert for the great topic. Look forward to next months T-SQL topic.
Setting up a machine for my 70-431 studies
I just added a new netbook to my arsenal of computers, so I am going to use my big power laptop as my new training machine. It is a couple year old HP DV9543CL that has a nice 17″ display, 4GB Ram, and what I like most about this is it has two hard drives in it, which I put in (2) 7200 rpm drives 160GB & 500GB. I installed a demo version of Windows 2008 R2 ENT, SQL Server 2005 ENT x64, and Office 2010 x64 as well.
I am very happy with the performance, the computer is very snappy. I also like running Windows 2008 R2 on my laptop.
Cameron Mergel 