SELECT * FROM [Musings]

Database matters.

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.

Posted at 11:04 AM  Permalink |

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.

Posted at 03:54 PM  Permalink |

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.

Continue reading "Personal Disaster Recovery on a stick"

Posted at 01:42 AM  Permalink |

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”.

Continue reading "My Life On A Stick"

Posted at 03:58 PM  Permalink |

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.

Continue reading "Adding comments, or remarks, to database schema objects."

Posted at 08:18 PM  Permalink |

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.

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

Posted at 12:48 PM  Permalink |

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.

Continue reading "Applying Sprint Updates to a Database Codeline"

Posted at 11:47 PM  Permalink |

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.

Continue reading "Powershell and SMO scripts to support Version Control"

Posted at 05:25 PM  Permalink |

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.

Continue reading "Database Version Control for Agile teams using Scrum"

Posted at 12:34 AM  Permalink |

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.

Posted at 01:25 AM  Permalink |