Database Blog

Evans Data Market Research database survey from Dec 2007

Nik Hemdal — 2008-03-06T11:04:58-05:00

The survey, compiled from 1470 developers and IT Managers, compared Oracle, DB2, MySQL, Informix Dynamic Server, PostgreSQL, Microsoft SQL Server, and Sybase Advanced Server Enterprise on 13 categories.

The complete survey results may be downloaded from here.

Microsoft SQL Server Data Services

Nik Hemdal — 2008-03-05T15:54:36-05:00

SSDS Promises scalable, on-demand data storage and query processing web services that are pay as you go. No restrictions on the amount of data storage. Supports REST and SOAP interfaces, and there is no surprise that it utilizes LINQ as a query language. SSDS is still in private Beta, but public Beta is coming.

Also see Amazon SimpleDB.

Personal Disaster Recovery on a stick

Nik Hemdal — 2008-03-04T01:42:39-05:00

What started out as a simple knowledge management life-hacking exercise has now blossomed into a full-blown personal disaster recovery solution. Aside from the traditional paper filing system, a redundant electronic version of my life�s inventory was nonexistent. I lacked a personal electronic data management policy. A data management policy where the documents that represent my life are secure and stored on my person at all times. With the help of a scanner and a custom JDBC application to store and retrieve BLOBs, I�ve corralled all of my personal documents into an encrypted Apache Derby database. An Apache Derby database is stored in platform-independent files in a directory of the same name as the database. Apache Derby encryption provides complete encryption of on-disk data: indexes, tables, transaction log file, table data, metadata, etc. Using Apache Derby also allows me to provide my relatives with secure backups of my life�s inventory.

My Life On A Stick

Nik Hemdal — 2008-02-01T15:58:35-05:00

Ok, so I have come to the realization that my memory isn�t quite what it used to be. My wife came to this realization a decade ago, but that is a topic for another day and another blog for that matter. I used to have an uncanny knack for storage and retrieval of both professional and personal data using just my brain. I am basing this perceived waning of my data storage and retrieval capabilities on the fact that I find myself clicking �Forgot Password� or �Forgot Username� more often. I don�t think that I have a data capacity issue, but you never know. To combat this, I�ve enacted a �No Data Left Behind� policy. This means that, provided there is a Linux or Windows PC available, I will always have access to all the data (URLs, login credentials, account numbers, etc.) that I can no longer seem to store and retrieve efficiently using just my brain. The additional value proposition of storing images of important documents and receipts will come in handy as well. Oh yeah, the biggest win for me is that all the data is portable and searchable. It really is amazing that I will get all of this on a simple USB flash drive � essentially �my life on a stick�.

Adding comments, or remarks, to database schema objects.

Nik Hemdal — 2007-11-27T20:18:41-05:00

Last week I was asked a question about the ability to provide explanatory remarks to database schema objects. The question focused on the typical remarks that elucidate the intent or purpose of the schema objects; however, keep in mind that remarks can also specify justification of standards or best practice violations, stored procedure limitations, use of undocumented features, column data units, conversion information, and so forth. An experienced database designer, or database developer, will make every attempt to create self-documenting schema objects. In the event that there is information that cannot be expressed by the name of the schema object itself, the database designer/developer will apply an appropriate schema object remark. The remarks are stored in the system catalog. In addition to providing an essential inline database reference for the developers and/or maintenance team, remarks make you think about the design.

Preserving XML document order when using XQuery.nodes in relational table joins

Nik Hemdal — 2007-11-15T12:48:10-05:00

If you are not taking advantage of the XML support in SQL Server 2005, then shame on you. Among other things, XML is the perfect data representation format for passing simple collections (i.e. arrays and lists) to stored procedures. Whether you want to easily optimize your CRUD operations, and/or take advantage of stored procedure parameter validation using typed XML. Furthermore, XQuery is great at shredding the XML into a relational format for use within your stored procedures.

Applying Sprint Updates to a Database Codeline

Nik Hemdal — 2007-10-24T23:47:02-05:00

The previous posts discussed a generic codeline folder structure and PowerShell management scripts for database schema version control. This post discusses the management of active database development work in the Mainline codeline.

For every database in our Mainline codeline folder structure there are two child folders: Previous and Sprint. The Previous folder contains DDL files for every database schema object, including all BCP domain/default data files, necessary to restore a reference image of the last released database schema. In the folder structure below, the Previous folder contents would construct a 2007.2 equivalent database schema for MyDatabase2. The Sprint folder contains the DDL and DML necessary for active development work. In older codelines, the Sprint folder provides a quick snapshot of what happened in the database schema for that particular release.

Powershell and SMO scripts to support Version Control

Nik Hemdal — 2007-10-22T17:25:50-05:00

In the previous post we established a generic version control folder structure for our codelines. I have created two scripts: ScriptDatabase.ps1 and CreateSchema.ps1 to support this codeline structure. The ScriptDatabase.ps1 script will extract all of the database objects from a specified database into our Previous folder in the codeline structure. The CreateSchema.ps1 will create a new database schema from the codeline folders on the specified Microsoft SQL Server instance.

Database Version Control for Agile teams using Scrum

Nik Hemdal — 2007-10-13T00:34:26-05:00

The key challenges of maintaining database schemas for agile teams are:

1. providing easy restoration of previous versions
2. repeatable process that supports autonomous work
3. allow concurrent updates to the database schema objects
4. synchronization with the application code to ensure stable builds
5. storage of production regression test data
6. storage of default domain data
7. quick deployment of a specific version of a database schema through reference objects
8. a quick view of what happened to the database schema for a particular release.

SQL Server 2005 Forensics

Nik Hemdal — 2007-10-02T01:25:15-05:00

Check out this recent addition to the SANS InfoSec Forensics Reading Room. It contains an interesting report that includes some methods and techniques to uncover digital evidence related to a SQL Server 2005 security incident. The report follows proper cyber forensics investigative procedures, and the data acquisition section will definitely increase your personal knowledge portfolio.

SMO and PowerShell 1.0

Nik Hemdal — 2007-09-28T19:42:18-05:00

This is an introductory post, in a series of posts, where I intend to use PowerShell and SMO to describe a database schema versioning process that supports the needs of your Agile development team. I'm not going to bore anyone with a Powershell or SMO Primer, so if you have any questions please contact me at niklas@hemdal.com for assistance.

For this post, I just wanted to expose a little PowerShell script to demonstrate how amazingly powerful this technology is. The following code enumerates the schema objects for a user-supplied Microsoft SQL Server database, and generates the corresponding T-SQL create script files for objects whose names match a user-supplied regular expression. I chose to enumerate the objects based upon the object name, but I have left script comments in to specify the enumeration using the schema object type (i.e. Stored Procedure, Table, Foreign Key, etc.). To create T-SQL create script files for all database schema objects simply supply ".*" as the regular expression to match.

An introduction to SQLite.

Nik Hemdal — 2007-08-21T11:24:25-05:00

SQLite is used by some of the biggest names in IT; Apple, Adobe, and Google to name a few (Apple in the iPhone, Adobe in AIR for building and deploying web applications on the desktop, and Google in the Gears browser extension). It may already be the most widely distributed database in the world. It truly is a zero-cofiguration, almost SQL-92 compliant, public domain database that runs on pretty much every operating system. Little Endian, Big Endian, makes no difference - files can be freely shared. It certainly is not an enterprise database replacement, but there are quite a few situations where SQLite works very well. Dr. Richard Hipp, the primary author of SQLite, provided a Google TechTalk briefing in early 2006; legible slides are found here.

While the SQLite Library core is C code, the number of language bindings available is staggering. As my SQLite introduction, I developed a simple .NET 2.0 assembly that uses SQLite to aggregrate Internet Explorer favorites from all of my PCs. The supplied command-line administration tool provides the capability to quickly generate a consumable html file of aggregated links. What is nice is that I can use the power of SQL to filter the aggregated links by title, date, grouping, etc. No surprise that Mozilla's Firefox 3 is moving to SQLite for storage of their bookmarks among other things.

One of the distinctive SQLite features that did lead to a data defect was column datatype affinity. While traditional databases use static datatyping on columns, SQLite uses the column datatype as a recommendation. In other words, you can store any value of any datatype into any column (except a column that specifies INTEGER PRIMARY KEY). I had improperly formatted the creationTime attribute for insertion which created a problem when attempting to use it in an ORDER BY later on. Besides column type affinity, it's also important to keep in mind that SQLite does not enforce RI and complicates table evolution with limited ALTER TABLE options.

It was extremely simple, and deserves a closer look for use in other endeavors. You can read the source listing in html here.

Assessing Production Data Quality

Nik Hemdal — 2007-08-08T01:25:06-05:00

In a recent Agile Newsletter, Scott Ambler highlighted the need to validate data quality via testing (Questioning Traditional Data Management). At a minimum, he proposed regression testing things like column domain value rules, column default value rules, value existence rules, row value rules, and size rules to help ensure data quality. Scott also points out that constraints setup to prohibit data quality errors are easily dropped or reworked.

Katmai and the End of DMO Support

jdorsey — 2007-08-07T14:13:09-05:00

SQL Server 2008 (Katmai) is set for launch on February 27, 2008, and Microsoft had previously announced that this would be the last version to support SQL Database Management Objects (SQL-DMO). However, Allen White notes in his SQLJunkies blog that the July CTP release of Katmai contains a warning that the Express version of SQL Server 2008 will not support DMO. Developers should instead use the SQL Server Management Objects (SMO) library introduced in SQL Server 2005.

New Blog by Niklas Hemdal

Nik Hemdal — 2007-08-02T13:58:51-05:00

Nik designs secure and efficient Enterprise and COTS data solutions in Washington, DC. His career includes database development/administration and data warehousing using vendor products from Sybase, Oracle, IBM, and Microsoft on a variety of platforms. You can reach him at niklas@hemdal.com.