How we use SQL Server as a Document Store

Paul Stovell discusses the transition from RavenDB to SQL Server for Octopus 3.x, highlighting the decision to utilize SQL Server as a document store while maintaining some advantages of a document database model. The approach involved structuring tables to store complete aggregate roots in JSON format, alongside specific fields for frequent queries, and using customized ID generation inspired by RavenDB. Stovell outlines methods for modeling relationships, handling migrations with DbUp, and deliberately managing database operations from .NET code without relying on traditional ORM tools like Entity Framework. The strategy has yielded improvements in performance and user comfort, despite the unconventional use of SQL Server as a document store, and anticipates further enhancement with SQL Server 2016's JSON support. Stovell acknowledges that while this setup suits their needs, it may not be ideal for applications requiring frequent write operations or horizontal database scaling.