Taking Another Look at SQL

The man; the mystery

Many people think they can do without RDBMS's.  But before you say goodbye to the relation forever, I think it's useful to recall the problem for which it was meant to solve.  We've become so used to RDBMS's that their benefits may too close to us to be seen clearly.  I have a new-found appreciation for them after reading Ted Codd's 1981 Turing lecture.

Recall that prior to RDBMS's were DBMS's.  If you had data that you needed to persist, you had two options: file-based storage or hierarchical databases like IBM's IMS.  In either case, data storage code was highly coupled with application code.  If you wanted to add a new field to your customer information in IMS, for example, you had to update all your pointers referencing customer information.  If you wanted to query your data, you had to bake your searches in from the beginning.

One of the main things RDBMS's provided was an abstraction layer between your application code and your data model (your data structures, operators, and integrity rules).  Want to add a new persisted field?  No problem!  Programmers could be more productive by focusing on business logic rather than storage details.


Still, by using a relational data model, RDBMS's do shape application code.  They let you query data in ways you had never planned, but you need to have a system to get your data in and out of them.  Hence the need for ORM.  One of the benefits of NoSQL databases like MongoDB is that they have an incredibly simple key-value-based data model.  You can ignore ORM completely, and thus spend even less time thinking about storage.  (There is a trade-off, of course.  Querying the data becomes more difficult).

If there is a reason to go NoSQL, it's programmer productivity.  NoSQL made a lot of sense for Google in the early 2000's due to the cost of hardware. As the cost of processing power continues to decrease, the need to use commodity hardware should not be a central reason to turn to NoSQL for most people.  Programmer productivity should.

In short, I see the move from DBMS's to RDBMS's and now back to NRDBMS's (non-relational database management systems) as part of a broader trend to abstract away details and work on the interesting stuff.  Just as with garbage-collection in OO languages, we're always looking to get closer to the pure space of thought,

Polyglot Persistance

There's been a lot of breathless optimism surrounding the NoSQL movement, as well as some bashing of the SQL-based RDMBSes that have held dominance for the last 40 years. The very name 'NoSQL' might suggest that there's no need for SQL any longer.

Martin Fowler and Pramod Sadalage provide some much-needed perspective in their new book SQL Distilled. They don't see Oracle or SQL Server going anywhere any time soon. There are three main reasons for this. First, SQL has forty years of production experience. Big banks are not going to jump to Hadoop for their transactional systems. Second, it's easy to find programmers with SQL experience. Not so for NoSQL. Third, and perhaps most importantly, NoSQL and SQL are meant to solve different problems.

Fowler and Sadalage suggest two main reasons for going NoSQL: programmer productivity and data access performance.

Column-oriented and document store NoSQL databases can be more productive for programmers because they can store objects as JSON. There is no need for ORM mapping, which has been called the 'Vietnam of computer science.' Of course, many problem domains require too many or too complex of relationships between objects to allow for this.

The main reason NoSQL databases were created was scaling. Companies like Amazon and Google pioneered NoSQL because they had way too much data to store in SQL databases. Frankly, I don't think very many companies need to worry about scaling to Google size, or they can cross that bridge when they get to it.

One of the things Fowler and Sadalage emphasize is that we don't yet have enough experience to even say when we should choose NoSQL over SQL. Still, they point to a storage architecture that takes advantage of the strengths of all systems. They call this polyglot persistence.

For example, in a eCommerce system, a shopping cart could be served by a key-value store like Riak; the orders and products could be an OLTP database like Oracle; order history could be in a document store like MongoDB; and customer suggestions could be maintained by a graph database like Neo4j. Of course, we could add to this a warehouse using Pentaho or a logging system using a column-family store like Cassandra.

The important thing is to gain familiarity with these products and to do POC's demonstrating the strengths and weaknesses of different approaches. For instance, I like the idea of a polyglot approach, but this puts a lot of pressure on the DBA's. In large organizations, you often have a split between developers, who know what needs to be done, and DBAs, who have the power to do it. Maintaining a number of data stores isn't for the faint of heart.

On that note, I'll be working on putting some MongoDB into practice with Python, Django, and a bunch of other things I don't know anything about. More to follow...

HBase: A Column-Family Store

The next NoSQL database in my whirlwind tour is Apache HBase, a column-oriented database. Riak was a simple key-value database, which mapped an object it knew nothing about to a bucket/key combination. Its job was just to make sure the data was replicated consistently in a highly fault-tolerant manner. HBase schemas, on the other hand, are made up of tables, which are dictionaries of dictionaries. It gives you more granularity and schema control, but it's still dumb about the values of its key-value pairs.

For SQL developers, HBase terminology will not make much sense at first. The large objects are tables, which are made up of key-value pairs consisting of row keys and column families. The column families are, in turn, collections of key value pairs. A table is not a group of relations, nor are there any guarantees that one row will look much like another.

An HBase table

The value of this system is that you get another layer of granularity over a key-value store like Riak. You have a bunch of stuff (a table of rows), which has a couple broad characteristics (a row of column families). Listen, these NoSQL database systems are not all that different from each other. Which one is right for you will depend upon 1) your other applications, 2) your size needs, 3) your performance needs, and 4) your fault-tolerance needs.

For example, HBase cannot read until one client succeeds in writing to all replicated instances. Riak can be tweaked so that writes are successful when only one node has been written to, so it should be faster (though it's hard to find any data on these things--you'll have to do the POC yourself). HBase is best when you have a lot of data and need to run MapReduce. Riak is right if you need 99.999% uptime.

For me personally, HBase was a pain in the ass to set up. I'm not a Linux pro, and HBase isn't really meant to be run in a single instance on a crappy laptop, but this was a real turnoff. MongoDB, Riak, and PostgreSQL were all pieces of cake to set up. So why do it?

One reason is that HBase is part of an ecosystem of tried-and-tested enterprise applications, like the Hadoop Distributed File System and the query language Hive. Its users include Facebook, Twitter, and Yahoo! (Google uses its own BigTable.) There's a large community of users to go to with questions.

Still, HBase has the flavor of many Open Source systems that grew out of a matter of necessity of scaling fast on commodity hardware. I'll be interested to see what Microsoft does with Hadoop and HBase. Call me a Microsoft fan-boy, but I think their integration of technologies is often very good, and it takes time to work out the kinks in a new platform. The first iPhone was a piece of junk. It takes a few iterations for a technology to mature and solve the problems it's designed to solve.

Links:
-Check out this nice comparison of Cassandra, MongoDB, CouchDB, Redis, Riak, HBase, Membase, and Neo4j

Mapreduce and Key-Value Systems

I've been playing around with Riak (ree-ack), my first foray into NoSQL, and it's been a lot of fun. Riak is an open source implementation of Amazon's DynamoDB. It's a web-ready key-value store that scales easily. And it's a whole different world from RDBMS's. Basho has a great tutorial for getting started.

If you're a database guy like me, you're probably wondering what you can do with a key-value store. Using key-value tables is a SQL anti-pattern. Even if you get performance and scalability (which I'll discuss in an upcoming post), it's hard to see how you could do any interesting queries with a key-value system.

One thing to note is that a file system is basically a key-value store. The key is the file name and the value is the contents of the file. You can do searches on file systems, but you can't really do much querying. If your data needs are more like a file system than complex analytics, a key-value store might be right for you.

I also finally learned what the MapReduce algorithmic framework does. You may have heard of MapReduce (amongst many other new technologies) in connection with Hadoop. MapReduce will probably go down in history as one of Google's greatest contributions to computer science (along with the PageRank algorithm).

MapReduce is inspired by a functional language, LISP.  The idea is simple: take the algorithm to the data. In a highly distributed and scalable system, it's not feasible to move data to a processor in order to aggregate and slice it. You have to do any processing in-place. The way to do this is to map your keys to some kind of broader category and then run a reduce algorithm over the mapped data to pick out only the information you need.

For example, let's say your key-value store contains documents, and you want to count the number of documents beginning with the letter 'X' that contain the word 'tibialoconcupiscent'.  In this case, you would map all documents with a key like 'x*', such as 'xerophagy.pdf'.  Your reduction algorithm would simply return 1 for every case of the word and 0 otherwise. This reduction could be run on each server, a group of servers, and then all servers, returning a single (probably small) number.

This framework may seem limited, and it is. Key-value systems just aren't great for complex querying. One reason SQL has been so popular is that it allows for unlimited combinations of queries. You're limited in the ways you store data, and you have to wait for the system to write the data to disk in order to ensure consistency, but you can read the data in lots of interesting ways. That's why it's called a Structured Query Language.

(Riak does let you do some more interesting things because you can create links between different keys that define any kind of relationship between them. These links are just metadata. You can MapReduce across links, but the basic idea is the same.)

Now, where would I use Riak? There are a good number of production users already. I would think it would be best used in systems that don't require a lot of complex querying or complex data types. For instance, it could store webpages, messages, or other content. Unless you have a huge amount of data or require very fast writes, it's probably not necessary. But if you're looking to grow fast, it might be the right choice.

Cathedrals, Bazaars, and Data

According to Eric Raymond, with enough eyeballs, all bugs are shallow. This is the secret to the success of open source software. Linus Torvalds' primary innovation wasn't so much creating Linux as the development model surrounding it: get something out there, get people using it, and have them be your testers / contributors. That is, make users co-owners, not just consumers. Raymond compares open source software development to a bazaar, as opposed to a building a cathedral.

I've been using Linux and open source software for more than a decade, but I've always been a bit wary of open source databases. Do you really want a fail early/often approach when dealing with people's data? Oracle has made a killing off of Data Fear. After all, can you promise 99.999% uptime with an unproven system? Open source might be fine for startups, but is it fit for enterprise?

PostgreSQL seems to merit the hype. Even SQL Server DBAs like it. I don't have experience with it on the enterprise, but I'm impressed so far. I believe that it's a tested solution, and I really like its extensibility. Anyone can create any kind of extension, such as an Amazon Web Services interface or K Nearest Neighbor mapping.

As a .Net/SQL Server developer, however, I have some concerns.

First, is free really free? SQL Server Enterprise is $6,874 per core. Oracle Enterprise is $47,500 (though Standard Edition One is only $5,800). That's nothing to sneeze at. I don't know Oracle that well, but with SQL Server, you get a whole suite of technologies--ETL (SSIS), data warehousing (SSAS), and reporting (SSRS) in particular. You get a number of disaster recovery options, like 2012's AlwaysOn.

By default, PostgreSQL works from the command line. You can download PGAdmin, but Navicat costs money. Graphical ETL tools? I don't think so. Reporting? You'll probably have to spring for Crystal reports. Data Warehousing? I hear Pentaho is a pain. It would be hard to keep PostgreSQL free--at least in an enterprise environment.  If you're just doing CRUD for a web UI, you're probably ok--for a while.

The second thing that makes me nervous is the ridiculous number of open source technologies out there. The principle of open source is to 'let a thousand flowers bloom,' but in practice this means playing around with a whole lot of different products and figuring out how they can fit together. It seems like chaos. Microsoft is known for its Three Letter Acronyms (TLAs), but at least everything fits together pretty well.

Finally, because of the complexity of open source solutions, I can't imagine they'd be easy to maintain. Most companies now outsource common DBA and production support tasks. If you're running a PostgreSQL OLTP system with a MongoDB document store and Hadoop web logging, you'll have trouble getting Tata to maintain that for you. Even if you're not interested in outsourcing, it is useful to have standards and best practices across companies.

Of course, it's the innovative start-ups that are using new open source technologies to do new things.  This is one of the main factors that helps them be innovative. They don't want to be standardized. They want to be part of the bazaar of new tech IPOs, rather than the cathedrals of established companies--at least until they have enough users for the cathedrals to buy them.

Anyway, I'm having a lot of fun.  Check out my new favorite book.

Object Relations

I recently talked about the architectural problem of mapping objects to databases. A related problem is the behavior of such mappings. Martin Fowler explains, "That behavioral problem is how to get the various objects to load and save themselves to the database." Though the architectural part is not simple (Jeff Atwood calls it the Vietnam of computer science), behavior can be a much more intractable problem.

It's magic

Think about it. If you load a bunch of stuff from the database into memory and then do some work--especially unpredictable work generated by user commands--you have to figure out what to write back to the database. Some things will have changed, and others will not. Some things might have been updated since you last wrote to the database.

The main thing you need is a unit of work, which keeps track of all changes, writes them back to the database, and checks to make sure there are no conflicts when updating. You could, of course, have a 'dirty' flag for each object, and check each one at the end of your unit of work in order what to determine what to write back. But it's often more elegant to create a unit of work object and take care of objects that are new, dirty, or removed. The problem remains of determining the order of write backs.

If you have a lot of overlap between different units of work in a single session, you may want an identity map, which ensures that objects are only loaded once. This way, you can't accidentally modify two different instances of the same information in memory. An identity map could be located in a session object or as a static object in units of work.

Finally, since you may not want to load all objects into memory at the same time, you might consider implementing lazy loading. The idea is that you don't load data until you actually need it. This may defeat the purpose of O/R mapping, if it ends up leading to a lot of separate loads, but it is a natural extension of the identity map.

Now, much hinges on the actual map itself. How do your objects relate to each other and to the database? If your objects simply mirror your database schema, you could use an identity field which stores the primary key for each object / row. If you do this, you'll probably need a foreign key association between objects / tables.

Things become more complicated if you're making use of inheritance. You could have one table to manage an inheritance hierarchy. This is called single table inheritance. This may lead to lots of NULL values in the database table and difficulty in naming things in a single namespace. Another option is to use class table inheritance, in which you have one table for each class. This simplifies the relationship between objects and tables, but it leads to complex joins. You'll also have to be careful about foreign key relationships to other tables, as a primary key cannot be referred to by other tables. A variation on this pattern is what Fowler calls concrete table inheritance, in which you have one table for each level in an inheritance hierarchy. This should help with the foreign key relationships.

An example of class table inheritance

All this would be a lot to implement by hand, which is why Microsoft created the Entity Framework, first introduced in .NET 3.5. Java coders may try Querydsl. I haven't had a ton of experience dealing with OR behavioral issues, but I can understand the appeal of the EF. You can simply build your objects, build the relations, and then export a database from Visual Studio. I worry about the performance of these tools, but they seem flexible enough to allow for customization. Whether or not this customization is worth the development effort required is another question.

Data Staging

The best image I could find?

In Extract, Transform, & Load ETL processes, you might save the data you are extracting to a temporary location and in an unadulterated state. This is called staging the data. Staging is an interesting word. It's a synonym for scaffolding, or a temporary support for something else, such as a career in cooking, a cancer treatment, or a rocket's trajectory. In this case, data staging is a preparation for data that has the format, structure, and purity you want. A data mart is a stage for a data warehouse.

Staging could seem like a bad idea for a number of reasons. First, you're duplicating data. Duplication is almost always a bad word when it comes to data, but staged data should not be used for anything besides staging and perhaps reporting. Still, it requires space, which can be a precious thing even when space is so cheap.

Second, staged data must be written to disk and then read from before doing the final transformations. You're effectively doubling your disk I/O. If the amount of data is significant, this could be a lot of reads and writes.

Written in 2010, a product of experience

These are real trade-offs, but there are a number of reasons to stage, some of which did not occur to me before reading Microsoft SQL Server 2008 Integration Services: Problem-Design-Solution, an excellent book on SSIS best practices by MVP's Erik Veerman, Jessica Moss, Brian Knight, and Jay Hackney.

• Data Lineage - When you run into data purity issues in production, the first question your production support team will ask is, "Where did this data come from?" A staging area provides means of tracking the import of data and may allow you to run subsequent transformations in a temporary transaction so that you can identify duplicates, null values, or other data purity issues. You will need to keep data around for enough time for such problems to be identified and addressed.


• Restartability - Jobs fail. The last thing you want to have happen at 3 AM during a conversion process is a job failure (yes, this has happened to me). A staging area should help you restart the job halfway through, after the necessary corrections have been made. This assumes you run your transformations in a transaction, which you are doing, right?


• Source Alternative - A staging area can act as a reporting environment when your source system is highly transactional or under significant strain. Just make sure you're not doing any writes to stage.


• Archive - Stage can also be an archive system, which may be useful if your destination system becomes corrupt and needs to be rebuilt. For instance, a data warehouse updated by incremental loads could be rebuilt by running through all transactions. I haven't run across this in practice, though.


• Performance - Finally, an ETL process might perform best with staged data. Though SSIS can buffer data flows, databases can often sort and aggregate data faster and with less resource load.

As always, there are no universal solutions to data architecture. Don't stage data just for the sake of staging data. You'll have to weigh the pro's and con's according to your business, technology, QA, and production support needs.

Where's the Logic Go?

Typically, the business logic resides in the middle, or domain, layer

Every application has at least two components: the design of technology platforms, called the application logic, and the processes that need to happen, called the business logic. In theory, the business logic is dependent of the application logic, since a business has rules, workflows, and transactions that have nothing to do with any programming languages or database systems. In practice, however, application logic can put constraints on business 'illogic.'

One of the key design choices in developing any application is deciding where the business logic should go. Database developers think it should go in the database, since keeping the code at the database level is often most performant. The problem with this is that SQL doesn't have many of the basic niceties of any Object-Oriented language. Furthermore, since stored procedures use proprietary SQL, they can prevent the migration of database code to another vendor.

OO developers think business logic should reside in the domain layer, since objects are best at representing the real world. Libraries and IDE's like Visual Studio make it very easy to get an OO application off the ground, and they help with maintainability. For many applications, however, the amount of code necessary to create an MVC model, for example, is not necessary and may even be prohibitively burdensome.

In reality, no application design should be used for all problems. Martin Fowler provides four models that couple domain and database access logic.

Transaction Script / Row Data Gateway - Domain code simply passes requests from the UI to the database. Database access is modeled at the record level.

Table Module / Table Data Gateway - Domain code is organized in objects corresponding to tables in the database. Database access is modeled at the table level.

Domain Model / Active Record - Domain code is organized according to business rules. Database access is modeled by CRUD objects.

Domain Model / Data Mapper - Domain code is organized according to business rules. Database access is modeled by a mapping object layer.

Fowler suggests that your choice of pairings should depend upon the complexity of your business logic. An application used for reporting can simply send requests to a database, but a complex sales order process should probably be mirrored by a domain model and a data mapper. A domain model will have a higher up-front cost, but it may pay off as the complexity of an application increases.

I think this general trade-off makes a lot of sense, and it helps me understand and categorize a number of applications I've seen. But, unless I am mistaken, pretty much any enterprise application is going to require a layer for business logic objects, a layer for data mapping, a layer for data access, a layer for the data itself, and, of course, the presentation layer. If it's possible to reduce the complexity of these layers, do so!

Bigger Faster Stronger

Scalability is one of those words that everyone uses but few understand. It's a measure of how adding resources (typically hardware) affects performance. You can scale vertically by increasing the power of a server. You can scale horizontally by adding servers. The scalability of a system depends on how performance is defined. Martin Fowler suggests a few categories:

Expect more posts on this one

• Response time, or the amount of time it takes to process a request
• Responsiveness, or the amount of time it takes to acknowledge a request
• Latency, or the amount of time it takes to get a response (this is especially important when there is no data to return)
• Throughput, such as transactions / second
• Load, or the amount of stress a system is under
• Load Sensitivity, or response time / load
• Efficiency, or performance / resources
• Capacity, as in maximum throughput or load

Systems must be designed to scale, but what scaling means will depend upon the purposes for which a system is built. It might be tempting for database professionals to think about scalability in terms of transactions / second or the number of active accounts. But what really matters is whether or not the system is usable given an increase in transactions or accounts, and this depends upon the use for which the system was created. If we're talking about an e-Commerce system, throughput is probably more important than response time, as long as responsiveness is high. If we're dealing with a manufacturing system, we'll probably be most interested in throughput.

It's important to design systems to be scalable. The Internet has increased adoption rates to unprecedented rates. Consider Instagram, which has 30 million users after 2 years. Draw Something had 36 million users in three weeks. Scalability is a prerequisite for virality.

In the case of N-tier applications which have a Service-Oriented Architecture, it's usually easy to add hardware to the web and application servers. Load balancers and web farms can take care of extra load by distributing it evenly across a number of servers. The real problem is, as always, the database layer.

You can't just add servers to the database layer, because databases must be architected across multiple database servers. Concurrency adds to the difficulty, as database transactions must be ACID (atomic, consistent, isolated, and durable). In other words, you have to manage updates to multiple servers, making sure that an update to Server 2 does not depend on Server 1.

Lighting bolts make it faster

I thought the Cloud might be the solution to database scalability, but Microsoft Azure currently supports databases of only 150 GB in size. In talking with Microsoft consultants, they recommend 'sharding' databases. This means having a master database that directs transactions to the appropriate database server. For instance, all transactions dealing with North American accounts should go to Server 1, South America to Server 2. Sharding adds a layer of abstraction and a layer of complexity, and it requires duplication of database schema, but it's an increasingly popular approach.

Another option is Oracle's RAC system or Microsoft's MatrixDB, which has basically been ported to Azure. I'm skeptical that MatrixDB will make it in to the next edition of SQL Server (2012 has AlwaysOn, which is close, but the mirrors are read-only). In RAC or MatrixDB, databases are replicated across multiple servers and a load balancer directs reads and writes to the server with the least load. Changes are replicated asynchronously between database servers. Still, there are limitations to the size of databases for which this would be feasible.

Relational databases are great up to a certain size (though this is growing, thanks to SSD's and improved caching). It's hard to say exactly what this size is. In the end, scalable databases adhere to principles of normalization and partitioning. After a certain amount of data, RDMS's will be of no use, and NoSQL solutions are the answer to a different problem. Are you ready to scale?

The Art of War

While heeding the profit of my counsel, avail yourself also of any helpful circumstances over and beyond the ordinary rules.
-- Sun Tzu

How do you win a war? There is no easy answer. If ever there was an art that could not be reduced to a set of axioms, it's war--and perhaps database programming. Admittedly, I have neither fought in nor commanded in any skirmishes, battles, police actions, or wars, but I'm pretty sure that if there was a surefire way to win, it would have been discovered by now. (Well, there is one rule: never start a land war in Asia.)

The same is true with database programming (never
select *). All forms of programming, and of problem-solving more generally, involve trade-offs. But the trade-offs are the greatest with database programming. As the database is the greatest barrier to performance in any application, its design is the most fraught with philosophical and practical dilemmas. I find it very difficult to interview people for database-related jobs, because there are rarely any right answers. It's also difficult to glean advice from books and blogs, because situations vary so wildly.

For instance, there is a trade-off between reads and writes in a database. If you have a lot of reads, you can create indexes to your heart's content, assuming you have unlimited space, which is never the case. Similarly, there is a trade-off between batch processing and real-time transactions. If your database handles both, and you cannot simply schedule batch jobs for off-hours, you will have to prioritize one or the other. Another trade-off is between reporting and online transactions. Even if you report off of a mirrored snapshot, you must accept the timing delay thus incurred.  This list could be extended indefinitely.

An early database under siege

It can be a frustrating realization that there are no general principals by which lowly database programmers abide. But this can be liberating as well. What would be the point of programming if you too could be replaced by a machine following a set of principals? Programmers are the last stop against the ever-increasing automation of the world. Embrace the art!

More practically, there are some things you can do. Once you've achieved a certain level of proficiency, the best you can do--really--is to catalog trade-offs.  I recently ran into one between database complexity and service traffic. It is usually a good idea to reduce the amount of chatter over a web service, but it's also a good idea to write clear, maintainable SQL code. Another trade-off: you can make all your code dynamic in order to deal with the deployment processes and future change, but then you have to deal with a more complex database design.

Stéphane Faroult outlines five factors that contribute most to performance:

• The number of rows in the tables involved
• The existing indexes on these tables
• Storage peculiarities (like partitioning)
• The quality of the criteria provided

Query diagram à la Tow

• The size of the result set

Compare to Dan Tow's query diagrams, which show:

• The join order of tables
• The selectivity of the filters on those tables
• The selectivity of the joins between tables

Of course, performance is only one of many database goals and involves trade-offs at a micro and macro level. For example, decisions have to be made about indexes on particular tables, and about whether performance or maintainability is more important. There aren't any universal truths, just experience and deliberate thought.

What trade-offs have you found?

Database Version Control Goals

I'm currently version controlling a database and developing version control processes which will be used by a new team at my company. Everyone knows you should version control your database, but what exactly this means and how to go about doing it are not obvious. As I've mentioned before, version controlling databases is much different from version controlling application code, because you can never start from scratch with a database.

To meet the minimum requirements of a version control system (VCS), you must be able to:

1. Maintain a directory structure of all database objects
2. Maintain change/upgrade scripts
3. Isolate development from production code

The most basic version of Subversion will allow you accomplish these tasks, with two caveats. First, you have to either build your directory structure from the ground up, or you have to form some kind of process that will script out your database. I've tried the latter with Powershell, SSMS, Visual Studio database projects, and custom .Net code using SMO. In my opinion, the least bad option is .Net, since the code is not too difficult to write and you can customize it to your needs. You can add in functionality to script out only objects changed after a certain date. However SMO is incredibly slow. It took me 2 hours to create a baseline for my database.

The challenge shared by all of these home-brewed approaches for scripting out database objects is that you have to be consistent. For instance, if sometimes you use SSMS and sometimes Powershell, the object scripts may be slightly different and create false positives in your VCS.

If you stick with these minimum goals, you are also hampered by the annoying and uncertain process of having to maintain change scripts on top of your object scripts. I'm sure you always test your change scripts thoroughly on test environments that exactly mirror production, but you can never be 100% sure that your codebase actually matches your production database. Finally, if you want to create sandbox environments, you'll have to create another process for turning your object scripts into database generation scripts. This is more important if you work at a software company and need to be able to reproduce defects in old, supported code than if you work in an in-house database system. In either case, it's still a good idea to be able to create sandboxes for developers so that people don't break each other's code from working in the same database.

For these reasons, you also want to be able to:

4. Generate change scripts automatically
5. Create database environments with test data at any version
6. Know what version a database is

Now we're talking about incurring some monetary costs, but most likely ones that will pay for themselves. The two main options for achieving these advanced goals are Red Gate tools and Visual Studio Ultimate / Database Edition.

Visual Studio database projects are a cool idea, since you can populate object scripts and compare them against your database, as long as you have something better than VS Premium. You can get Anhk SVN or Visual SVN to version control directly in VS. VS has an 'offline' development model, meaning that you do your code in VS and then later create change scripts by 'compiling' your database. I've had issues with circular dependencies among databases, making this an unfeasible process. You get what you pay for with Red Gate tools, and you'll really want both Source Control and SQL Compare. Source Control just helps you deal with the basic goals in an elegant way, but, when paired with SQL Compare, it will allow you to create scripts to turn any database at any version and turn it into one of any other version. You can also get tools like Data Compare to sync your test and production environments, and you can use the Data Generator to create non-sensitive test data. These features improve the development process, but are not necessarily within the scope of version control.

There are a few options for knowing what version a database is on. SQL Compare will allow you to compare the database with a version in a repository. You could also create a table that stores the database version, which is useful but will never provide 100% certainty about all the objects. Finally, you can create extended events for database objects but Phil Factor doesn't recommend it in the eBook, since there are not many options for documentation. Database version control is slowly making it's way out of the Dark Ages and into the modern, Agile world that other developers have long taken for granted. Once you are able to handle all of the advanced goals, you can do even more things, such as automate the build process and run automated test scripts, a process called Continuous Integration. I must admit that the developments like those Red Gate has made take some of the mystery and the fun away from good ole database development. But I'll probably get over it.

Links:
-Grant Fritchey's article on Simple Talk, which is chapter 3 in the Red Gate eBook
-Michael Baylon's own list of goals and solutions
-Another good article

Database Refactoring

I'm taking ownership of a database, so I've been reading about database development best practices. Database development is unique from other kinds of development for a number of reasons. Most crucially, you can never start from scratch with a database, because it's a living, breathing thing--you can't simply recompile and redeploy. Second, you have to design your database so that it can be set up with representative data on developers' workstations, in a development environment, and a testing environment. Third, you have to make sure that any scripts can be deployed on any environment (it usually helps to have some way of knowing what 'version' a database is on). And you may have to do all this while being unsure of all the applications that access a database, or the manner in which they do it.

These challenges led me to read Scott Ambler and Pramod Sadalage's book on database refactoring (2006). "Refactoring" is a term popularized by Martin Fowler's eponymous book (1999). He defines it as a "disciplined technique for restructuring an existing body of code, altering its internal structure without changing its external behavior." It is important to have a concept like refactoring, because development is usually focused on adding functionality only. When things change quickly, who has time to refactor?

There is some anecdotal evidence that spending time refactoring will decrease the amount of time coding later, but there's no rule for how much time you should spend refactoring. However, if you can't do any of the things mentioned above (limit access to your database, know what version it is, deploy to multiple environments), you must refactor. The hoops you'll have to jump through just to do normal development are far costlier than the effort needed to refactor. Ambler and Pradalage also suggest that it might be time to refactor if you notice any of the following "database smells": multi-purpose columns, multi-purpose tables, redundant data, tables with many columns, tables with many rows, "smart" columns, and fear of change.

Defining refactoring in the way Fowler does is also important, because it simplifies testing by separating the development of added functionality from development that should preserve existing functionality. Ambler and Pradalage embrace a Test-Driven Development approach, where you test, deploy, and test again. If you make testing central to your development process, you can be sure that you're not breaking anything during refactoring.



Though refactoring seems necessary only in (hopefully) rare circumstances, the steps that constitute it should bleed into normal development. For example, it's always a good idea to develop in small, easily testable pieces, rather than making many large changes all at once. An iterative process also allows for feedback from users. In short, refactoring should help you to embrace AGILE principles in database development, and it should make further development faster and easier. Even if you can't make your database development completely AGILE, these principles should help you evaluate bottlenecks in your process and suggest means of addressing them.

Links:
-An article that contains the main sections of the book

Silverlight, Security, and Sandboxing

I'm taking ownership of an application that uses Silverlight for a front end. I had never programmed in Silverlight before and only really knew of it from Netflix's Instant Streaming. I've come to really appreciate Silverlight, since it provides a rich user interface while leveraging the resources of powerful platform like .Net.

It's easiest to think of Silverlight as an alternative to Adobe Flash, which does not easily integrate with other code. The recent Internet-based UI revolution has taken two paths: Flash-like applications (called Rich Internet Applications (RIA)) and AJAX-powered pages like GMail. It's hard to remember the days when you had to refresh an entire page to gain new content, since AJAX and Flash provide such a seamless experience.

Developing in Silverlight is just like developing Web Forms, Sharepoint controls, or User Controls. It just uses XAML instead of ASPX. See the snippet below:

<stackpanel name="LayoutRoot" background="White">
<textblock text="Hello, World!" horizontalalignment="Center">
<ellipse name="FirstEllipse" height="100" width="200" fill="SlateBlue">
<button name="FirstButton" width="100" content="Click" click="FirstButton_Click">

private void FirstButton_Click(object sender, RoutedEventArgs e)
{
FirstButton.Content = "Click Again!";
}

(taken from this example)

As a long-time ASP.NET Web Forms developer, I was surprised that I couldn't just make a DataGrid object and connect it to a DataSource object. To connect to any data, you have to create a Windows Communication Foundation (WCF) web service. This entails learning yet another technology (more on that in a later post), and all the difficulties entailed in creating, deploying, and debugging web services.

I had a hard time finding anything explaining why all this was necessary, until I found this article by Josh Twist. Since a user downloads a Silverlight application that runs in their browser, it cannot be trusted by a server. You need a boundary of abstraction and authentication in order to ensure that the integrity of your data (and your servers) is maintained.


When you have a standard Web Forms application, all of the code is executed on the server. Even though Silverlight code looks like server-side code, it's really executing on the client's machine. For this reason, it faces all of the same limitations as client-side code you might be more familiar with, such as JavaScript and Java applets. A Silverlight application can't be trusted, since its code is non-deterministic and might become corrupted at any time. For this reason, it is sandboxed by your browser. It can't take up all your memory. It can't access the file system. And it certainly can't access your database.

This secure sandbox model is going to become more and more prevalent. The 2000's saw a resurgence of the client-server model in which thin clients (like web pages) did little processing but produced a lot of network traffic with servers. But with the rise of mobile phones, tablets, and other cheap devices, there is no reason to continue to put such a strain on servers and networks. RIA's also provide a great user experience--so great that Windows 8 is going to make it a major part of its architecture. The Windows Metro platform is meant to emulate the touch-driven interface of mobile apps while maintaining the tools many developers have continued to use.

If you want to port an application to desktop, tablet, and mobile devices, you'll have to make this architecture your friend.

Links:
-MSDN article on Silverlight sandboxing
-Windows Metro preview at Engadget