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?

 

 

 

PASS Summit 2010

Where will you be November 8th through 12th?

Me, I’ll be at the PASS Summit 2010 is the largest, most renowned Microsoft SQL Server conference in the world. PASS Summit returns to the Washington State Convention & Trade Center in Seattle Nov. 8-11, 2010.

Join thousands of SQL Server and BI professionals and learn how to cut IT costs in a difficult economy, stretch your technology investments, and squeeze the most value from your training dollars.

PASS Summit 2010 will deliver:

• 168 in-depth technical sessions
• 5 content tracks covering Database Administration, Database and Application Development, BI Architecture, BI Client Reporting, and Professional Development
• 14 full-day Pre/Post-Conference Seminars on Nov. 8 and Nov. 12
• Direct access to Microsoft SQL Server engineers, MVPs, and experts in the “Ask the Experts” Lounge
• Face-to-face interaction with the Microsoft BI team
• Personalized support at the Microsoft CSS First Aid Station
• SQL CAT best-practices sessions

Check it out:  www.sqlpass.org/na2010