We like to send questions to company that use VistaDB and get their feedback. Some people respond with all of the information filled in and we turn them into blog posts like this one. If you would like to see us post a story about your company and how you use VistaDB please contact us.
CyberSavvy DataPlace
CyberSavvy has been a fantastic partner for VistaDB. Aside from just being really great developers, these guys have always been willing to help us fix bugs or performance in our engine by giving us concrete examples of their issues.
About Dataplace
DataPlace provides a solution encompassing automation of the design, deployment, and management of data driven applications. This SaaS solution combines a Microsoft SQL Server 2008 infrastructure with an automated database design factory, web based synchronization framework, and an optional extensible Smart Client. By providing a rich API, new applications can be designed and extended by leveraging DataPlace as a foundation feature set including column encryption, row level security, change auditing, and data and schema synchronization.
When did you first have the idea for your project?
In 2004, after years building multiple solutions for Microsoft’s internal business problems, we understood data was the core of any application, and that the biggest single impact on a project’s success was at the database level. Database design, development, deployment, and most importantly change requests significantly impacted all levels of the application and project, and more significantly, the end users. Powerful database designs are intricately fashioned with tables, types, stored procedures, triggers, functions, and data layers -- all working together to optimize and control access to the data while ensuring security, reliability, and scalability.
Although the initial implementation can be challenging, the inevitable requirement changes and application maturity cycles expose the true cost of maintaining such architecture. These costs manifest as development costs, reduced quality, extended timelines, and unhappy customers.
Most designs try to solve this problem by building layers on top of the database so it doesn’t have to change – but each of these abstractions introduce other limitations like reduced functionality, scalability constraints, increased complexity, and almost always increased cost. Really what was needed was the ultimate database developer who would work at superhuman speed without error, making on the fly changes, doing both the things requested but also the items that weren’t requested but needed to be done.
The solution/idea was simple – completely automated all the interaction with the database at the design level enabling it to change dynamically as needed without increasing cost, reducing quality, or sacrificing functionality. Allow design changes to be affected against the actual database through data only without allowing direct access to the server -- thus controlling the changes without complexity and providing transparency. Take security, auditing, online/offline design considerations for occasionally connected applications, standard CRUD concepts, and the design lessons we learned the hard way -- and automate it into the process by default. We would have 80% of the project done at the start and DataPlace was born.
How long did it take you to build it?
3 Years
What lessons did you learn from this project that might help others?
In building DataPlace, a platform where very little can be assumed about the applications built upon it, we pushed many technologies to their extremes. In this, we have discovered both the power and limitations of many technologies, and used those discoveries to bake in automated implementations of best-practice patterns into DataPlace to help anyone trying to solve a data problem.
With that said, the two core lessons we learned that shouldn’t be too surprising. Many technologies, especially new ones, offer to solve one problem just to introduce many others while increasing complexity.
Use the right tool for the right job and keep it simple – this is a good opener to VistaDB.
What problems did VistaDB help you solve?
VistaDB solved 2 huge problems for us.
The First Problem was the obvious difficulty of having a robust data engine on the client with the following attributes.
- Developer productivity – it has to integrate in with dev environments and be good to work with.
- Reliable – desktops are volatile places, it has to handle the problems
- Secure –Theft, viruses, and loss have to be mitigated by design
- Scalable – Data by nature grows, performance cannot be compromised
- Easy deployment – Users and Administrators won’t put up with difficult deployment scenarios.
Our experience with various offerings that were supposed to solve these various problems seemed to have a fundamental conflict with one or more of the other requirements. We progressed through the following technologies to solve our desktop needs in the following order with these results.
XML / Serialized ADO.NET Datasets:
- Developer Productivity: Poor
With no true query engine, developers are forced to design and maintain logic to manipulate raw data into the results needed by the views of the application.
- Reliability: Poor
No managed solution to keep a persistent store on disk while actively using the data. Unexpected process exits or other problem scenarios result in data loss.
- Security: OK
By its own design XML is extremely open, but as a flat file, it can easily be encrypted or protected by EFS
- Scalability: Poor
As data grew performance and recovery was negatively impacted
- Deployment: Very Good
As a flat file it’s easy to deploy or create on the fly.
SQL Server Express:
- Developer Productivity: Fantastic
Stored procedures, Visual Studio integrated, tons of documentation.
- Reliability: Very Good
Designed for enterprise solutions, its reliability is tried and true.
- Security: Poor
It relies largely on the server isolation for protection; it was not designed for desktop security scenarios. Unable to use EFS in a normal deployment.
- Scalability: Fantastic on the Server – not so good on the desktop.
Designed for enterprise solutions, scales extremely well on the server but on the desktop its scale compromises the local machines ability to do anything else.
- Deployment: Catastrophic
As a product designed for the server its installation can take an extremely long time, has multiple dependencies, requires several reboots, and needs administrative permissions. We learned the hard way that its installation and server resource requirements were counter to our ability to succeed on the desktop. Microsoft provided setup bootstrapper fails to account for many common desktop scenarios.
SQL CE:
- Developer Productivity: Fair
Without stored procedures, triggers, or CLR extensibility; and with mediocre TSQL compatibility; the developer’s toolset is limited. Without building a custom mechanism, queries often end up in a state that does not allow for optimization or correction while the application is running, forcing a time-consuming cycle of compile and try again.
- Reliability: Very Good
Designed for the volatility on the desktop, very recoverable.
- Security: OK
Simple security of a database password but its ability to be handled by EFS makes it a good solution.
- Scalability: Poor
Initial performance is good however as the data grows using result sets (a developer productivity enhancement) costs significant memory impact. Databases need to be preconfigured with a maximum size as opposed to scaling as needed.
- Deployment: Fair
This was designed with the desktop in mind. However due to its unmanaged dependencies, SQL CE does requires special considerations for Click Once deployment.
VistaDB:
- Developer Productivity: Fantastic
With all the major developer features of SQL Server (SP’s, Triggers, CLR extensibility,) VistaDB proved itself to be a fantastic tool for developers.
- Reliability: Very Good
Designed for the volatility on the desktop, very recoverable.
- Security: OK
Simple security of a database password but its ability to be handled by EFS makes it a good solution.
- Scalability: Very Good
Initial performance is good and as the data grows the engine scales well. It uses memory wisely on the desktop releasing when needed as a proper .NET Application should.
- Deployment: Fantastic
Designed for the desktop, doesn’t require admin permissions and using all the advantages of .NET - this sealed our success for deploying our solutions. Single file addition to an application’s Click Once deployment requires no special considerations over any other assembly reference.
The Second Problem it helped us solve was how we synchronize data over the web. Initially we had built around Microsoft Merge Replication over the Web - this worked for both the SQL Express and SQL CE implementations. However over time we identified several significant areas of concern.
- The complexity of setting up and configuring Web Synchronization for Merge Replication is very significant for a single implementation. This was compounded by our need for multiple implementations that were completely automated to create, maintain, recover and remove.
Reference the 3 walkthroughs detailed on How to: Configure Web Synchronization for Merge Replication. http://msdn.microsoft.com/en-us/library/ms151810.aspx
- Merge replication over the web is a very problematic and proved to be an unreliable process when various real world conditions occurred.
Reference Troubleshooting (Replication) for further information
http://msdn.microsoft.com/en-us/library/ms151756.aspx
- Data scale limitations of Merge Replication over the web documented in the section for Replicating Large Volumes of Data
http://msdn.microsoft.com/en-us/library/ms151255.aspx
Most Notable: “You cannot replicate any column that causes the data and metadata to exceed 25 MB.” Lacking even graceful failure, Merge has to be configured to avoid this issue when possible, at the further sacrifice of performance.
- User scale limitations of Merge as additional subscribers are added to the system. Effectively, each new subscriber to a system adds significant metadata maintenance work to the server for every change. Because of its design, this results in server blocking and increasing slower replication ability.
Note: These issues did not become apparent until SQL Server 2008 and was after we published the Data Confidence over the Web article on the Architecture Journal
http://msdn.microsoft.com/en-us/library/bb735302.aspx The majority of the article still has merit, with the exception of the now documented limitations of Merge Replication over the Web.
Many of these scale limitations are derived from its dependency on XML files to transfer changes and with a very expensive way of determining what changes needed to be communicated to the client.
To overcome these issues, we built DataPlace Distribution (DPD), which is how we now successfully synchronize data over the web. The DPD technology has allowed us to vastly improve the backend process over Merge by removing complexity, increasing security, solving all the problematic issues we had been experiencing without compromising our feature set. Our implementation has surpassed the limitations we found with other technologies and brought many new advantages to our platform that we are really excited about.
With the limitations of Merge’s success limited by its use XML as a transport, we decided early on to use a binary format for our new DPD system – ultimately using a VistaDB database as the binary payload. Combined with DPD secure streaming implementation, VistaDB’s low overhead engine and compact file format made it a perfect fit for our needs. Avoiding the memory, processor, and throughput limitations found in Merge and traditional web service implementations, VistaDB provided significant advantages by avoiding the need to re-encode the data and handling the management of committing the data to the file system. With the DDA and ADO.Net API models of VistaDB, we were able to rapidly construct a new middle tier to completely replace Merge in our architecture.
With DPD implementation server blocking is virtually eliminated in part because no pre-calculation of user data partitioning is necessary. Because DataPlace allows for a database schema to mature very rapidly, support for a constantly fluctuating schema is a key feature. Unlike Merge, which would have to resynchronize all data, DPD can handle nearly any schema or data partitioning change and continue to reconcile client systems.
With this flexibility, DataPlace can support extremely intricate data partitioning and row level security rules that were not possible under Merge and Merge Join Filters. Any DataPlace database can be partitioned by data relationships to one or more tables allowing for complex nested conditions. Data CRUD security rights can be granted to users or groups against those partitions. Furthermore these security rights can be bundled into Tasks and Roles that can be reapplied to hierarchies of data, allowing for child tables to inherit rights that only affect the child table from an upstream parent.
All of which equates to VistaDB being the right tool for several key jobs and helping us to keep our solution simple.
Tell us your name and company:
Contact Information
Subscribe