I've got four new SSIS videos published on JumpstartTV.com:

Expression Language Basics

Secure FTP File Transfer in SSIS

SSIS Lookup Transformation Basics

SSIS Lookup Transformation - Lookup Failures

If you're not familiar with JumpstartTV.com, I would encourage you to stop by and give it a try.  There are scores of short (3-5 minutes) videos that are all free, and are each targeted toward a specific task.  You can even suggest topics for new videos.  You can leave comments for each video, and we appreciate all comments as this is the best way for us to improve content.

The event schedule for SQL Saturday Pensacola has been published.  This event will be held on Saturday, June 6 at the Pensacola Junior College.  The schedule shows that among the speakers are Brad McGehee, Brian Knight, Plamen Ratchev, and Andy Warren, just to name a few.  I am honored to be among the speakers for this event, where I'll be discussing scripting in SSIS.

I'm looking forward to this event as I'll be able to meet some people that I've been corresponding with for a while but have never met face to face, among them Steve Jones, Jack Corbett, and Brad McGehee.  If you read this blog and happen to attend SQL Saturday Pensacola, please stop by my session and say hello.

Should you ever delete data?  In a production environment, do the benefits of deleting old data outweigh the possible risks?

Data quality is important.  Whether you refer to it as data integrity, permanent retention, or simply maintaining a complete audit trail, it can be effectively argued that deleting data from a production database diminishes the big picture of your data.  After all, any data that is worthy of storing, backing up, optimizing, and mining is worth storing permanently.  Deleting data affects the ability to thoroughly research historical activity, and can impact reports and aggregations on the remaining data.  Storing only the rolled-up data, such as end-of-year financial reports are often not sufficient, because auditors or financial personnel may need to drill down to the lowest level of detail.  Other information, including certain healthcare data, is best kept forever (and in some cases, is legislated so) to ensure a proper legal record should it be necessary for judicial or civil proceedings.

The need to routinely delete data was far more critical when storage was more expensive, in terms of dollars and system time.  Purchasing disks for storage has never been cheaper, and with modern 15000 RPM drives and solid state disks, data access times continue to improve.  Removing data simply for the sake of saving bytes on a platter is not as critical as it was just a few years ago.  Data can be retained indefinitely, in the original store or in a separate archive (another table or a different database altogether).

To be clear, I’m not taking on DBAs who use the DELETE functionality to eliminate data.  A proper data retention policy would involve all levels of an organization, from the CXOs to the technical staff and end users.  And a competent retention policy doesn’t have to mandate that data remains in the RDBMS – information can be stored in the database, database backups, the filesystem, magnetic tape or optical disk, or a combination of several of these.  The specifics of permanent data storage should be dictated by how frequently or quickly the data would need to be accessed.

There are times when deleting data is expected and even commonplace.  When staging data in temp tables or table variables, one would expect deletion of data during that processing.  Any process that writes data out to an archive store would naturally need to delete data from the original location, though this could better be considered a move rather than a delete.  Sensitive data which would never be reported on or reused is expected for the protection of customers or clients – the deleting of credit card numbers after a charge is successfully posted would fall into this category.

Unfortunately, this decision does not reside with database administrators alone, or even with their employing organizations.  Some vendor applications will routinely delete older, less-often used data as part of a purge to better performance or decrease storage requirements.  I recently experienced this with a healthcare vendor during a conversion from their product to a newer system.  It was discovered during the planning phase of the conversion project that this vendor’s system was hard-coded to purge the detail data from old accounts.  Although we were able to reconstruct some of the data using other means, the ability to thoroughly report on that historical data has been permanently and irreversibly diminished.

The bottom line is that you should ask yourself whether you could ever need the data you are deleting.  You shouldn’t just ask whether it is likely that you will need the data again – approaching from this angle will eventually come back to bite you.  A more appropriate question would be whether you can imagine any scenario, however unlikely, that would require you to reference the data in the future.  Eventually your boss/the board/the CFO/the auditors will come calling, and you’ll be glad you have your safety net.

If you’re available and are (or can be) in the Jacksonville FL area the first weekend of next month, check out SQL Saturday on May 2.  This is a great opportunity for some free SQL Server training and networking, along with some swag and door prizes afterward.   If you are interested in speaking at this event, the call for speakers is open until next Monday, so submit your session today!

I got to attend and present at SQL Saturday in Jacksonville last May, but I won’t be able to make it this year.  I will be attending and (hopefully) presenting at SQL Saturday in Pensacola on June 6.  Hope to see you there.