Please read the complete blog post over on the Infinite Codex site about how we are unifying our blog feeds to a central blog for all products.

Read the Unifying the Blog Post

This post has been moved to the following Infinite Codex blog post.

How do you take time to do pure research on new technologies in a small company?  This is not a rhetorical question.  Small business teams rarely have the money to justify spending months on research. 

It is usually a case of build what works and run with it, so when do you get the time to do that research?  Without ongoing research into new technologies and designs you have to find a way to fund those activities. Building a product by growing things in place is a good way to accrue a lot of technical debt that eventually leads to a fragile design or a complete rewrite.

How do other companies handle this?

Ok, this is my personal answer to the question; Your Mileage May Vary (YMMV). It may or may not be correct for everyone, or every situation.  I want to share parts of how I came to this conclusion in the hopes that it may help other small companies. 

I have always believed that any product without continuous development is dead.  How can a product innovate without new work being done on it?  Only through investigation and application of technology can a company learn to innovate.  Otherwise you are just playing catch up with others in your market and telling the developers to do what you competitor just did.  Sure, maybe you improve it a bit, add some polish.  But without looking way ahead of the pack you will never leap frog anyone.

In large companies they usually handle this by having multiple teams.  Team A is on the current release, and Team B is looking at the next major.  Then they flip flop when Team B is ready for production Team A then goes into looking at the next major release.  Small businesses can’t afford to employ this strategy.

Some companies do this by having a long release cycle.  They release 1.0, then spend 2 years getting to 2.0, etc.  This leads to long cycles of no improvement in the product, but for some industries this is acceptable. 

Most small companies iterate over their design making small improvements and adding customer features.  At some point it seems like “enough” changes and they rev the major.  Some small companies ignore the problem entirely and call everything Product 2009, Product 2010.  Basically telling you that they are all the same, but with some form of improvement over time.

There is nothing wrong with any of these strategies, but they don’t work in our scenario.  We have large research work to do in some fields that just can’t be iterated into the core product at all.  They are literally all or nothing scenarios. You could spend a year of effort to build it into the core engine only to discover it doesn’t work well.

New ideas are sometimes too big

In our case we build a great dot net database engine.  The architecture is quite different from SQL Server, in that we are 100% managed code.  But do we really take advantage of everything that managed code has to offer? 

There are newer data structures and practices in Computer Science for dealing with very large data structures.  Can we employ some of them for our indexes?  Dot Net 4 now offers memory mapped files for the first time, could that help our sort and indexing?

What about some of the new parallel abilities in Dot Net 4?  How can we leverage them without spending a huge amount of time integrating them in the main database?

Our SQL parser does not implement a grammar parser like BNF.  The entire parser is built around a pcode system that probably worked great in VistaDB 2 under Pascal, but is problematic under .Net to expand easily.  Would implementing Oslo or one of the other domain specific language (DSL) parsers allow us to be faster and more flexible with SQL dialects?  Could we, for example, parse MySql syntax as well?  Under the hood we are not SQL based at all, what if we just exposed a pure LINQ syntax and dropped the SQL parser entirely?

Trying to build and implement any one of these ideas into the main database engine could take a very long time with no yield at the end.  How can we work on these theories and mitigate our risk?

Design a new product around a single idea or technology

What if you took and built a new product around specific ideas or new technologies?  Nothing real fancy, not a general purpose answer for every scenario, just a very specific niche.  Don’t try to implement it in your main product.  Just look at the technology and where could it go, how would you build it, apply it, sell it?  Do you think you could learn lessons from that specific application that would help you better serve your larger customer base later?

Could we use a domain specific language (DSL) to implement SQL dialects for a Data Migration API first, before going to the main database engine?  Could we build a way to generate large quantities of test data through an API as a way of testing load and index systems?

Could we use the Dot Net 4 parallel extensions to perform faster indexes for a specific type of index first, like full text search?  Could using other indexing strategies lead to better search performance, indexing speed, or both?

Could we use different technologies for ETL (Extend Transform Load) of database data into VistaDB?  Could that provide a way of performing bulk load, or transformation services?

These ideas would allow us to work in isolation without fear of breaking the main engine, but still move forward on research.  And many of these small niches fill product needs for people today, why not offer it to them?

But it’s not VistaDB…

Eventually much of the technology may find its way back into VistaDB, but not directly.  Each of these technologies will be stand alone tools or products.  Each needs to be applied against specific use scenarios to prove or disprove ideas. In some cases they may work fantastic for their applied niche, and never make it back into the core product because they don’t work well in the general purpose case.  But by doing the research we will know the answers, without wasting a lot of time ripping apart our core product to test theories.

These new technologies have different potential customers, and needed their own name.  As a result, we are building new websites specific to some of these ideas.  Some of them will be released as stand alone products or APIs for specialty use scenarios.

So how do you brand that other “stuff”.  It isn’t VistaDB, it is something different.  I didn’t like the idea of publishing it under the VistaDB brand name as I think it would confuse users about what VistaDB is as a product (100% managed SQL database engine, just to be clear).  All this other stuff is new technology, not constrained around our core product. 

Would it all be “Brought to you by VistaDB”?  That would seem sort of misleading because then people might think it was VistaDB under the hood of the tool, in most of these cases it will not be anything like a full database engine.  In fact most of these niche users probably won’t need a database at all, maybe they only need this niche product.  Giving it the full VistaDB name might scare these users off because they don’t want a full database.

We eventually decided to change the company name to reflect the fact that we will have more than one brand of product.  A new site is being built to reflect the idea that we are a technology entity producing various products. 

Infinite Codex, Inc is born

We are going to change the company name to Infinite Codex, Inc. signifying that we can write an unlimited number of programs that are all related by application of technology. 

I think naming the company and the main product the same thing was a mistake (several people advised me against it at the time. Sorry, will listen to you more in the future!).  The company name needs to be independent of any single product brand it produces in order to be able to grow and expand into other markets.

VistaDB will continue to be the core of the products we produce, but we are going to start producing other smaller systems as well.

Research, but with specific niche products

Either it will be brilliant, or totally insane; only time will tell.  This is how we, as a small company, are attempting to pay for the various research we need to do in order to expand.  Most of them are not directly related to VistaDB as a product, they are either internal technologies, or external tools that could apply to almost any database.

You can’t sell pure research without it having an application.  That application will drive your research much further than pure abstract ideas ever can on their own, and as long as you are filling a niche you can derive income from that niche at the same time.

All of the new technologies are being built with an eye towards making us a stronger company within the database market  in general, but at the same time expanding our market into other niches.  If we can better understand single technologies, then we can better incorporate them into our main database product over time.  But if along the way we become more widely known for implementing really good developer tools and APIs, we will have a larger market in total.

This all seems familiar to me

At my last company it was this ability to take our core product and apply it to so many different niche applications that eventually led to outside interest in purchasing the company.  Having that core technology demonstrated across several different niche applications made us more attractive, and proved just how flexible the technology was for almost any scenario.

The end goal of all of this research is to lead us to a stronger next major version of VistaDB, and to look towards the future.  I am sure many of these technologies will find their way into incremental updates as well, but I am already thinking of the next major engine structure. 

In order for us to go where I want with the database engine we have probably two years of research ahead of us, a little longer if we productize these ideas along the way.  But, I think in the end it will help the company into new markets, new partners, and fund our growth over the long term.

I am not just thinking of any one database vendor, or application.  I am thinking more in terms of where is the Dot Net platform going, the growing NOSQL movement, and what will that mean for database apps 7-10 years from now.

Expect new announcements of technologies and components in the next few weeks.  We are all pretty excited to have so much new stuff prepping for launch, I hope you are too.

This build is designated as a development build because we made a change in the optimizer that could affect a lot of applications.  We don't expect it to cause any problems (it fixes one possible error case), and you are free to Go Live with it with your products.  We just ask that you test your code and let us know if there are any problems.

Logging Build

This build is a full logging build.  Logging does not happen automatically against every run of the engine.  You have to turn it on from the Diagnostics namespace, and the Customer Experience tray app has to be running with logging enabled.  By default the only thing we are logging in the CEIP application are Data Builder runs, and the Visual Studio tools (logging exceptions, errors, etc).

To turn on diagnostic logging within your application

At app or unit test startup you would call the startup like this:

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

VistaDB.Diagnostic.Support.BeginLogging("Starting Test to demo some error"); 

Then during your application shutdown call the EndLogging method:

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

VistaDB.Diagnostic.Support.EndLogging("Test shutdown complete");

You could also do the begin and end just around a problem area as well.

There are two boolean options on the logging right now to turn on additional information.  By default we only log exceptions and errors in the engine process.  If you are having a problem with an output being incorrect, or some other SQL specific problem you can turn these two options on to log more detailed information.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

VistaDB.Diagnostic.Support.IncludeCommandSQL = true;
VistaDB.Diagnostic.Support.IncludeDataReaderRows = true;

The IncludeCommandSQL includes each SQL command that is executed and the parameters passed as a part of the query.
The IncludeDataRows will log each row returned to the command (NOT recommended unless REALLY necessary to see a problem since these rows can get quite large).

Issues and Fixes

Changed the optimizer code to be a little more deliberate in its choice of indexes.  There was an error case where a compound index containing two columns also had a duplicate index covering the first column of the compound index.

Index1 = COLA, COLB
Index2 = COLA

The optimizer could pick either of them to satisfy a where clause against COLA.  In fact it would pick the first one it found and use it in most cases.  But this lead to an error when COLB was not used at all in the query (without constraints).  You could end up with entries in the index that don't strictly match your constraint.

The fix was to actually choose the exact match when available.  If we find an index that has an exact match on your query constraints we will use it over all others.  The edge cases come when a query has one and not the other column, or when you have multiple covering indexes AND a compound index.  We have tested as well as we can the various combinations, but there could always be an edge case we missed.  There are actually 4096 combinations of index, order and column type.  Makes it pretty hard to test them all, but the code is generic and doesn't actually care about the types or order.

Fixed an issue with order by that prevented the system from recognizing columns in aliased tables.

Example using DBDEMOS.vdb4 sample database:

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

SELECT o.[OrderNo] 
FROM orders o 
ORDER BY o.[OrderNo] ASC 

This would fail due to the alias column and has been fixed.

Fixed issue created by change to the |DataDirectory| handler, also added unit tests to prevent regression.

Happy New Year!

This will probably be the last build of the 4.0 line as we are almost ready to start the beta testing for 4.1.

Errors and Exception Handling

Changed how the Error dialog displays exceptions.  Many exceptions include a Data dictionary collection of Key/Value pairs with additional information about the exception.  This is present even on a DbException object.  We do fill these out with helpful information in many cases, but the Data Builder dialog was not showing the Data dictionary.  You can trap this in your code and walk the dictionary also using the code below.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

   1:  private string GetAdditionalData(Exception ex)

   2:  {

   3:      StringBuilder sb = new StringBuilder(); 

   4:   

   5:      foreach (DictionaryEntry de in ex.Data)

   6:      {

   7:          sb.Append(de.Key);

   8:          sb.Append(" : ");

   9:          sb.Append(de.Value);

  10:          sb.Append(Environment.NewLine);

  11:      } 

  12:   

  13:      return sb.ToString();

  14:  } 

The custom error box we provide has been updated to show this information to the user.

TOP used with variables

Fixed issue where TOP could not be used with a variable dynamically allocated within a SQL Script. 

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

   1:  declare @howmany as integer; 

   2:  set @howmany = 30; 

   3:  select top(@howmany) from table; 

This now works as expected.

Mono 2.6 now supported

Mono support issue with an invalid IL Code being inserted through our obfuscator now appear to be working.  We have only tested this under Mono 2.6 as the previous production build of Mono had a bug in their implementation of the Microsoft License class.  This has been fixed by the Mono team in the current branch of their code repository, but I am not aware any plans to release it to the prior 2.4 branch. 

As always we recommend you test your application on your target runtime.  Mono is not something we do special coding scenarios around, if there are bugs in the implementation they have to be fixed by the Mono team.  We develop and test on Windows, any compatibility issues should be brought to the attention of the Mono team.

Other Issues for this Build

Fixed issue with declare that could result in null object reference in stack trace. The error was still correct (Invalid DECLARE statement), but the error message was wrong.  Changed error message to be more explicit.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

   1:  DECLARE dummyvar; 

Error 509: Invalid or incomplete statement:  Missing @variable. Example: DECLARE @variable Integer;
Error 509: Invalid or incomplete statement:  declare

The stack trace is now much more readable and give syntax hint as well.

Data Builder

Fixed issue in DataBuilder where canceling a close operation could leave tree view in invalid state.  Not all operations can be canceled due to the SQL Script being modified.  An example is pack and repair.  They cannot be canceled just because the script window was changed.  The operations have been changed to attempt a clear prior to pack being called so the user has a chance to save any changed in the SQL query window.

Entity Framework

Fixed an issue in the EF provider dealing with compound foreign keys.  Indexes with compound FK's would not map correctly in the Entity Model.

Fixed an issue when renaming a column that has constraint statements on it that are cased differently than the original column name.  We were not taking case sensitivity into account and missing constraint renames that didn't match exact case.

General Issues

Fixed an issue in the SQL parser where using []'s would not escape column names that conflict with SQL function names.  This would prevent some reserved word tablenames from being used [like].[wordcol] would not work for example.

Fixed an issue with Order By on subqueries with aliased or reused column names. Orderby will now sort the appropriate column.  The problem could occur when you have a subquery that projects a column to a new name in the parent query, and then sorts on the inner subquery name.  Another scenario this could occur is when a subquery columns are renamed to virtual output columns in the parent query multiple times (grandparent->parent->child all subqueries with renames at each level).  These types of queries with order by and renaming now work correctly.

Fixed a memory leak issue with connection pooling where the pooled objects would not dispose child objects correctly.

Changed behavior in the ConnectionStringBuilder class that if the Database property is set with a fully pathed filename it will instead set the DataSource for the user.  This is apparently a common error as reported by our CEIP program (Thanks Gibraltar)!  So we are introducing this as a helper to prevent errors for the user. 

Changed ConnectionStringBuilder attributes to more closely match attributes of SqlConnectionStringBuilder. This will allow designer code patterns to use both builders in a similar fashion.

Changed DataDirectory macro to expand and use the correct path separator under Mono.  If you specify a directory after the DataDirectory macro it must conform to the platform path specifications.  |DataDirectory|\subdir\database.vdb4 will not work under Mono since \ is not the path character.  You should either leave the database under the base DataDirectory path, or specific a complete path with proper indicators under Mono.

The first release of the VistaDB 4 source is now available for download.  This source is based upon what is going to be released as Build 11.  Since this will be the final planned release of 4.0 we wanted to ensure we had a solid source release for customers.  We have improved a number of internal tools we use to generate the source build, so we will probably be able to provide 4.1 builds more quickly (really appreciate the patience with this initial 4.x build!).

Read the included PDF / Docx file for more detailed information about the source, how to build it, etc.  The entire process is different than the 3.x source system was built.  The entire directory structure of the source was changed for 4, so the build processes are also quite different.

The only real requirements are Visual Studio 2008 SP1 and .Net 3.5 SP1 on the machine.  We provide one master solution file for VS 2008 to build all the parts.

VistaDB Source Edition Information

The full source of the VistaDB engine is made available to companies through a source license agreement.  The source license also requires an active and current subscription to VistaDB Corporate edition.

The purpose of the Source Code is edition is NOT to provide daily builds. It is intended similar to how a source code escrow works.

At each major milestone companies sync up the source code in order to ensure stability in the event one company ceases to exist. We provide these drops at infrequent intervals because the majority of the time the individual builds are not important for a company.

If you want to run your application off the source builds that is fine, but always realize there will be a delay between fixes and source releases. Most companies do not ship nearly as frequently as we do and an occasional source drop gives them time to incorporate at their next update cycle rather than trying to track our current builds.

What’s included?

Complete database engine source code, Entity Framework provider, and NUnit tests. Everything you need to build the VistaDB 4 engine.

What’s not included?

There are a few things excluded from the Source Edition. Things like our private keys we use to sign the assemblies, the tools we use to generate the API documentation, the methods we use to generate trial versions of the assemblies, Visual Studio plugins, and a few internal tools.

What can you do with the source?

The biggest reasons for owning the source is probably peace of mind. For some companies and business sectors having the code is a must for any serious development effort, for others it is just an insurance policy in case something happens to VistaDB Software.

You can build a complete database engine and customize it for your own products and services.

You can modify the engine to perform specific needs for your organization.

You do not have to share any custom modifications you make to the source back to VistaDB.

What can you NOT do with the source?

You cannot create a product to compete directly with VistaDB or to replace VistaDB. You cannot create any API based product intended to give third parties access to VistaDB file formats without them owning a valid license to VistaDB. You cannot create an open source product, or release any of the code to the public or any third party. You may not license the engine, components or DLL to any third party as a standalone product.

We are finally ready to unveil some of the things we have been hard at work on since the release of VistaDB 4.0.  One of the largest announcements is that we are going to support two way sync through the Microsoft Sync Framework ( Version 2 ).  Sync has been a huge source of time wasted for us in the past through Sync 1, but it finally appears Microsoft has stabilized the API in Version 2 and is shipping it with Visual Studio 2010.

Two Tier Sync using Sync Framework V2

Two tier is the name Sync Services uses to describe when the machine performing the sync has direct connectivity to the server endpoint of the sync operation.   This server has to either be exposed on the Internet, or you have to provide a way for users to VPN and perform their sync.  Of course this always works great within an office where you have direct connection to a SQL Server also.

There are several other models for Sync (including an N Tier model through proxies), but we are not committing to those other models for the initial release.  By far the largest number of users said they needed two way sync with SQL Server, so that is our primary user story for this release.  We would like to be able to sync to other services (like SQL Azure), but that will all happen after this initial release cycle.

Two way sync with SQL Server

Two way sync with SQL Server is our primary goal for Sync 4.1.  SQL Server schema is close to ours, and queries will be more portable between the two engines.  We have not nailed down if SQL Server 2005 will be supported, it will come down to the Sync provisioning system and if SQL Server 2005 will work correctly or not.

Two way sync with VistaDB 4 databases

And of course we also plan to support sync between VistaDB 4 databases.  This does not appear as useful to most people at first, but there are many customers who do not have a local SQL Server.  They are simply using VistaDB on a shared drive and want some way to take that data offline.  We will support the ability to put a database locally on the desktop and have it sync to that shared drive database.

Traveling Salesman Scenario

Let’s use a common scenario discussed with Sync; the traveling salesman.  Your company has a salesman named Bob who is in the office occasionally, but his typical work week involves flying to potential customer locations and trying to input orders.  Now Bob usually has internet access from his hotel, but not while on a plane or at the client site.  He still needs to be able to input his data while offline, and then have it sync the next time he is connected.  Many companies build solutions around Microsoft Outlook just because this model is the default behavior for that type of application syncing to Exchange and Sharepoint.

In this scenario your application is always talking to the local VDB4 database for Bob to do his work.  Maybe Bob has a way to VPN to the office (for accessing the Intranet), or he has to wait until he physically is present on the office network.  While he is connected to the office network he can then have his app Sync with the home database server.  Bob’s changes are pushed up to the server, and any changes from the server are brought to his laptop as well. 

We will post follow up blog entries about Sync and the usage scenarios as we get closer to rolling out the beta.

VistaDB 4.1 Beta

As we always do here at VistaDB, current subscribers will be given early access to the next release as a beta.  You don’t have to participate, but it is a great benefit of having a subscription.  We often get great feedback from customers during these beta periods of scenarios they need, or little extra features we hadn’t thought of to support the main items on the release.

In the blog post on how we release I covered our release philosophy in depth, VistaDB 4.1 is a major release for us.  This means there are lots of new APIs, changes in some existing ones, and major new features added.  It is expected that all existing apps will continue to function fine, with possibly some minor API updates. 

Our current plan is to start the beta... [snip] (see UPDATE at bottom of this post).  There are no Personal or Lite builds during beta periods, and many of the features listed below will not be available in those SKUs.  We may offer them as add-ons to the base SKUs after release.  As we continue to add major new functionality these new feature systems will be outside the scope of the base Personal product.  Many developers have asked us to keep the Personal as the basic level of support because that is all they need, and they are very price sensitive.  We are trying to keep those SKUs as low as possible in our pricing matrix.

New Datatypes

There are new datatypes added in 4.1 to more closely match SQL Server 2008. 

• DateTime2 – SQL Server DateTimes cannot store the same range as a .Net DateTime datatype.  The DateTime2 adds this additional range.  In addition we are modifying our DateTime to match the SQL Server limitations on the date range (1753/01/01 - 9999/12/31) to avoid data issues between VistaDB and SQL Server exchanging data.
• Date – For storing just the date part of a datetime.  Stores the complete dot net date range (1/1/1 – 9999/12/31).
• Time – Time of day without timezone awareness.
• DateTimeOffset  - DateTime combined with a timezone awareness with complete dot net ranges supported.

will all be supported (we do not plan to add the GIS types).

We are also changing how our varbinary and binary work to match the SQL Server concept of MAX on a column as well.  This lets you define a column as varbinary(max) and it will be stored in row data if the data fits, or to extended data if the data is too large. 

Engine Updates

The database engine itself also continues to improve with new syntax support, and further improvements to SQL Server compatibility.  We have a number of items on our backlog list.  Specifics will be announced with the initial beta release.

Data Builder Updates

The VistaDB Data Builder includes to get improvements in UI, new plugins ability for handling some advanced text processing (plugins will not be in the initial 4.1 release though), and many other new features within the application.  Some of these will only be available in the Data Builder for this release, but many we are also going to migrate to the Visual Studio plug-in as well.  In fact we are working to unify the two codebases to use the same user controls for a lot of actions. 

Help integration changes inside Data Builder are also new.  We have a new local database that includes all the help topics in a searchable manner within Data Builder (using FTS actually).  We are working on a way to sync that independent of the build release cycle so users can always have local help that is up to date.   We hope to also be able to incorporate user feedback within the help and have it pushed back through sync to our servers for others to see.  Remote help sync is a great way for us to use the sync system within VistaDB to improve the user experience in Data Builder.

Pricing and License Changes

Of course with all of these new features and add-ons pricing is going to change as well.  Professional subscribers continue to get access to all of our tools and features.  Many of these new tools and APIs will be available with source as well redistribution rights.

The renewal price for a current developer stays the same at $150 per year.  Corporate renewal prices also stay the same as their current rate.

Personal Editions

The Personal Edition is the minimum set of tools and features to make a stand alone developer productive with the VistaDB Engine in Visual Studio.  Optional tools like Sync support, Data Migration API access, Data Generator, SQL Script Generation will all be add-ons for the Personal edition.    Each of these products and features will be a new line item with a separate purchase price.

Personal Editions are meant for single developers who have short term projects or do not wish to receive support or updates.  The Personal edition is intended to be used by stand alone developers, or very small teams (1-3 people) who do not need advanced capabilities or redistribution of tools like the Data Migration Wizard.  I like to think of this as the Visual Studio Standard users compared to the Visual Studio Pro teams who need the more advanced features.

Some options are not available with Personal Editions (things like Build Licenses, Embedded Assembly Licenses, redistribution of certain tools and APIs, advanced samples, etc).

Professional Subscriptions

Purchasing a Professional subscription will always be the best value and the easiest way to gain access to all of our tools and APIs.

Pricing for VistaDB 4.1 new developer subscription price subscription will be $750.  Subscriptions will now include 2 years of maintenance, support tickets, and updates by default. Some of the new tools will also include source and redistribution rights at the Professional level.

Developers who have a valid subscription will be automatically upgraded to the current version when available, and new tools are included.  Now is a great time for developers to get the current subscription and be upgraded to 4.1 for free.

We have had some internal discussions about a base and premium version of the Professional subscription.  The base subscription would be for those developers who just want the core engine with updates and support, versus those who also want all the tools.  All existing subscribers would be upgraded to the Premium level automatically if we were to implement such a two tier system.  I am not convinced that the price point would make enough of a difference in sales to justify complicating the SKUs.

More Subscriber Hardware Installs

The current license allows for a single developer to install on two machines at the same time, as long as the credentials are the same and they are the user of the software.  This is to avoid companies who purchase 5 licenses for 10 developers thinking that each developer license can be on two machines.  That is not the intent of the two machine license.  It was meant to cover developers with a desktop and laptop where they are the primary user.  In almost all reported cases where the credentials do not match it is because the developers are different.  Since this system seems to working so well, we may relax the number of concurrent hardware installs a developer can use and activate at the same time.  The exact number is still being discussed, but more than two machines – as long as all the credentials match.

Certain scenarios are going to be restricted in the next release to avoid abuse scenarios.  Right now we see a number of installs to Citrix and Terminal Server machines, or local user installs as Administrator so all the developers can use the same license.  That will be blocked as it is outside the acceptable usage of the current license.  The price of building and managing a Citrix or Terminal Server is literally hundreds of times the price of VistaDB Corporate.  Only Corporate users will be able to install on these environments. 

Personal Editions will have one hardware license to match the lower price point.  Each developer will only be allowed one hardware install by default, additional licenses for the same credentials will be available for an extra fee.

Royalty Free Runtime

We charge nothing for our runtime, it has always been royalty free.  We have had many companies tell us this has saved them tens of thousands of dollars per year in license fees they were paying to another database vendor.  The additional technology samples (like the DBA Sample Tool) also add tremendous value over having to start from scratch on their own DBA tools they would need to build with typical “free” products.  Building those tools adds to the total cost of ownership for any product.  Our legendary ease of deployment also means a lower support cost for your product in the field.

Forward Thinking Statements

Many of these ideas are forward thinking statements, they may or may not make it into the final 4.1 release.  It depends upon the feedback we receive from users, and any technical problems that may arise.  The general outline of features and new offerings are definitely what we intend to ship, but the real world gets in the way sometimes. 

UPDATE

I am updating this post because apparently some people are confused about the timing of 4.1.  The beta will be announced here on the blog.  If you don't see the blog announcement yet then it is not public for subscribers.

We delayed 4.1 due to a problem in 4.0 that had to be addressed quickly for a customer problem.  But making that change was risky engine wise, so we had to do it as a development build, get user feedback, and then we will do 4.0 Build 13 as a final build of the 4.0 releases.  That was not our original plan, we didn't plan to release Build 12.  That whole cycle has cost us about 4 weeks of time.

 

Mindscape has announced a new version of their dot net ORM tool named Mindscape LightSpeed 3.  Version 3 includes support for VistaDB 4 and a lot of other new features.  Priced at only $199 per developer (source is available for an extra fee) LightSpeed is a very cost effective tool to add to any developers tool belt.  There is also a free version available for databases with less than eight tables if you just want to take the product for a test drive.

We recently spoke with John-Daniel Trask of Mindscape about their new release, and adding support for VistaDB 4.

What is the minimum version that supports VistaDB?  What version of VistaDB?

All current versions of LightSpeed support VistaDB 3.x. Support for VistaDB 4 was added for LightSpeed 3.0. It’s important to note that unlike other object relational mappers, the support for databases goes beyond the core mapping framework – we have full visual designer support integrated into Visual Studio. LightSpeed has supported full schema round tripping for some time which means developers can migrate changes they have made to their model down to the database AND from the database up to the model, depending on how they prefer to work.

What is the experience for a new developer using your product?

When a developer is targeting VistaDB but they still want all the benefits of an object relational mapper, then LightSpeed is the answer. We have aimed to provide more than just the core mapping framework and try to give developers a fantastic end to end experience. With the Visual Studio designer a developer can literally be up and running with their data model in under 10 minutes. They can be even more product with LightSpeed 3.0 which can create and manage database schema migrations automatically – saving valuable time from writing SQL migration scripts manually.

We have a great getting started video which demonstrates the first user experience. In the video we target SQL Server however the experience is identical across most underlying database engines.

What was your reason for adding VistaDB support?

We received requests from new users who wanted to work with VistaDB and who also wanted to use LightSpeed. We added support and now have many more happy VistaDB users using LightSpeed for their data access and domain modeling needs.

What was your experience adding support for VistaDB compared to other database engines?

Originally support was added easily – the way our provider architecture is built meant there was very little work required to get the core framework speaking to VistaDB comfortably. There were some initial challenges in adding support for the Visual Studio designer, however this was primarily around learning the queries required to get meta data from VistaDB about what tables exist, fields, etc.  One other challenge we have encountered is the lack of a command line tool that would allow us to rebuild our test database in a continuous integration environment. Unfortunately we cannot run the GUI builder as part of our CI process. 

(NOTE: VistaDB does provide a sample called VistaDBcmd that can run scripts from a command line similar to sqlcmd for SQL Server)

Describe a typical user story for your product, how do users choose the database engine being used by the product?

Developers come to the LightSpeed/VistaDB combination from two directions.  First, we have customers for whom LightSpeed is their ORM of choice, but have a project that needs a lightweight local database with managed code ease of deployment and more comprehensive T-SQL support than competitors like SQLite and SQL CE.  For those customers, VistaDB is an obvious choice.  Second, we have customers who have already adopted or are planning to adopt VistaDB, but want to improve their productivity over what's possible with data readers and handwritten SQL.  For these customers, LightSpeed provides a huge boost by being easy to understand and to set up, allowing them to get productive within a matter of minutes.

I have gotten a lot of questions from Silverlight users wanting to know if VistaDB will be able to load in Silverlight 4.  This would be a great thing if you could actually have a client side database in a Silverlight app.  I still am not 100% convinced this is a good idea in most circumstances, but for offline Silverlight apps this would definitely make sense.  It would allow you to store data locally in isolated storage on the users machine, and then send it to the server when they next are online.  The typical airplane offline app scenario. 

Nope – Not really using .Net

The short answer – NO.  Silverlight 4 can load “some” assemblies, but nothing built for .Net 4 or .Net 4 Client Profile. 

I built a .Net assembly as a .Net 4 client profile, and then wanted to load it in a Silverlight app.  This is the dialog you will see when you attempt it.

Hmm, can’t I just target Silverlight within my current Visual Studio solution?  No.  You have to build a new Silverlight assembly.  I tried this and was surprised to see the namespaces loaded in a Silverlight control assembly by default.

You can’t load any desktop assemblies into the Silverlight assembly, you have to work with the Silverlight subset of the framework.  You can’t do things like ADO.NET, you can’t even implement the interfaces because they don’t exist.  Why not let them exist, but not allow them to load due to platform restrictions?

Custom built Silverlight assembly you can load from your .Net app?

Take a look at the using statements at the top of a Silverlight control library.  Hmm, notice anything strange?  Maybe that everything is in the System.Windows namespace?

Why aren’t these API’s called Microsoft.Silverlight.xxx?  Or even System.Silverlight.xxx?  Most of these have nothing to do with Windows (the Operating System or User Interfaces).  I know many of them come from the WPF included in .Net 3, but they don’t use the same runtime anyway. 

According to this blog post about assembly import in Silverlight 4, there are only 5 dlls you can include within your assembly.  Note that none of this implement things like ADO.NET or even most of what an API would need to work with local data.  I personally do not consider object serialization to be a viable data store, not even close.

For SL 4 and .NET 4, we have made the following assemblies portable:

• Mscorlib
• System
• System.Core
• System.ComponentModel.Composition
• Microsoft.VisualBasic

Hmm, looks like a very small subset of .Net indeed.  Nothing like the “run .Net 4 assemblies” I was expecting after reading the posts previous to this one about assembly loading.

Sharing a Silverlight assembly with full .Net on desktop?

You can build a Silverlight 4 DLL and then load it within your .Net code.  Well, sometimes.  Some of those above namespaces above are present in .Net 3 or higher, but if you want to use .Net 2 only then you can’t do it.

Why would you want to do this?  Well, lets say you really want to encapsulate your business logic into a DLL and use it for both your ASP.Net site and your Silverlight app.  You can do that, but the dll has to be a Silverlight DLL.  Not a .Net assembly.  This is bizarre to me.

Even if you don’t reference ANYTHING in your dll (just write pure C# logic) you can’t load that assembly into Silverlight.  You have to build a Silverlight assembly (which doesn’t work in VS 2010 beta 2 for me even after following pages of instructions on blogs).  That special Silverlight assembly can then be referenced in your .Net desktop app (WPF seems to be the only ones I can get to work) and used as an external assembly. 

Two implementations of .Net runtime

What this still comes down to in the end is that there are TWO implementations of the .Net runtime.  One is the what we all use today, the other is the Silverlight version of these assemblies.  Those five assemblies above are not running the desktop versions.  They are a completely new implementation of those same assemblies.  True, they may have the same methods and signatures (that is how you can load them on the desktop), but they are NOT the same. 

Many developers I talk to seem to think that Silverlight 4 will run the .Net 4 engine.  That is not true.  They are completely separate implementations of the .Net framework (with the Silverlight one being a huge subset).

This to me still represents a fragmentation of the .Net runtime.  Microsoft please, please stop fragmenting .Net.  Either make it a profile (which I still don’t entirely buy as a viable distribution vehicle), or use the tools that we developers have to use.  Implement stub libraries that throw things like MethodNotImplemented rather than making an entirely new subset of .Net.  Give us developers a way to build once and gracefully fail depending upon the deployment.

Gracefully handle a subset?

Why can’t there be a subset of .Net that your app can detect at runtime and fail gracefully down to that level of compatibility?  We have to do this in our applications.  If someone is running in Medium Trust there are things we can’t do, handle it.  Right now everything Microsoft is doing with .Net results in a new build for you and I.  If your app tries to run on a Server Core box and wasn’t built for it you crash and burn at startup with an exception you cannot handle.  Why can’t they handle the bindings and then throw exceptions to tell you what is present on the system? 

This sort of fail gracefully to a subset would make perfect sense in a headless scenario (you can’t open Winforms because the machine is headless), in sandboxes (you can’t open that file because we are in a browser), etc.  These scenarios are all handled TODAY by the full .Net framework.  There are security permission demands, domain load demands, things that can be handled in the framework to scale gracefully.  I am not saying it is easy, but this is a totally different direction than where I see them going today.  Right now I see a more and more fragmented .Net runtime, and that is bad for all of us who make our living building and selling .Net tools and technologies.

VistaDB 4 on Silverlight 4?

My conclusion:  Not a chance.  There is simply still way too much missing functionality in Silverlight 4.  There are no provider models present (other than some for WCF services and data binding), and the binary handling of files is even worse that Compact Framework support.

In order for us to support something like Silverlight it would not be able to use the same file format, it would have to be an entirely new SKU.  Something that uses a new file format, new API (can’t be based on ADO.NET), and the worst part – an entirely new engine implementation from us that is not compatible with the desktop engine.

Does that mean we won’t do it?  I don’t know.  We may be implement something like a VistaDB Silverlight control that serializes to local disk when it can’t reach your ADO.NET Data Service (but I think Astoria Offline is already supposed to be working on that front).  So it appears the Silverlight portion would be a weak storage mechanism, not a full data engine.  Perhaps you could do something like a DBase fixed width format file that would perform well enough, but is that really a product?

UPDATE

I have spoken with a Microsoft PM about this at great length.  Without announcing anything he was trying to reassure me that Microsoft is well aware of these issues and are working to solve them for the final release.  The build scenario and ability to actually target Silverlight as a build option for an existing project were both mentioned as tracking issues for them. 

 

As I mentioned in my previous Dot Net 4 strategy blog entry, we have been thinking about how we want to handle Dot Net 4 from an API and runtime standpoint.

Just as a little refresher, remember that when I am talking about a .Net 2 assembly it can actually be any of the .Net 2, 3.0, 3.5 or 3.5 SP1 runtimes.  They are a layered cake design in that they all use the same base and build up with additional tiers of functionality.  You cannot have a .Net 3.5 app without .Net 2 present, doesn’t exist.

 

In VistaDB 3 and 4 our core engine is .Net 2.0 SP1, and in VistaDB 4 the Entity Framework assembly requires .Net 3.5 SP1.  So everything mentioned in this post equally applied to VDB3 and VDB4 applications.

.Net 4 app loads .Net 2 API

Any application (executable) built under .Net 4 will have it’s dependent assemblies automatically promoted to .Net 4.  You will not be able to have controls run in a side by side version of the framework because there is no way to communicate across the .Net runtimes.  For example there is no way for a .Net 2 Winform control to communicate to a .Net 4 hosting application if it is not auto promoted to .Net 4.  In order to do this type of thing you would need to use COM or some other cross process communication mechanism.

But what about applications that are unmanaged (think Office)?  Which runtime will they bind against to load plugins?  It turns out the answer is “it depends”.  The runtime loaded is based upon what runtime the plug-in was built against.  Your .Net 2 application will not auto promote to .Net 4 just because some other .Net 4 plug-in is loaded by Outlook (as was the case with .Net 1.1 apps with 2.0 installed).

Enter Side by Side

It turns out there is an interesting new feature in Dot Net 4 to allow side by side running of managed runtimes within the same application.  There is a great CLR Inside Out article online that explains probably way more than I will here, but I wanted to point out a few specific parts of the article and how they impact API writers like VistaDB.

Library Developers and Consumers: In-Proc SxS does not solve the compatibility problems faced by library developers. Any libraries directly loaded by an application—either via a direct reference or an Assembly.Load*—will continue to load directly into the runtime and AppDomain of the application loading it. This means that if an application is recompiled to run against the .NET Framework 4 runtime and still has dependent assemblies built against .NET 2.0, those dependents will load on the .NET 4 runtime as well. Therefore, we still recommend testing your libraries against all version of the framework you wish to support. This is one of the reasons we have continued to maintain our high level of backward compatibility.

-from “In process Side by Side” by Jesse Kaplan

.Net 2 CLR gets promoted, but what about the other way?

This is interesting to me, as we were sort of hoping that we could load .Net 2 CLR Procs in their own side by side so they have the runtime they expected when they were built.  Now the behavior is non-deterministic.  The runtime the CLR Proc gets will be dependent upon the executing application.  So if you load a 2.0 CLR Proc in a Data Builder built for .Net 4 your CLR Proc will run against .Net 4.  Maybe that is ok, but maybe it is not what your CLR Proc was expecting.  But the other way is impossible…

Let’s say you build your CLR Proc against .Net 4 and install it into the database.  Now you use that database in an IIS hosted site that only supports .Net 3.5 SP1.  The CLR proc will fail to load!   Older runtimes cannot load newer runtime binaries.  This leads to some very interesting edge cases in the VDB4 database.

 

If we were unmanaged…

But if we were unmanaged code (like SQL Server) each CLR proc would be loaded against the runtime it was compiled against and run as expected.  This is because of the way the side by side works, only an unmanaged host process can gain this ability.  We fully managed coders get whatever we get, you can’t choose or control it.

AAARRGGHHHH

That about sums it up.  Sometimes I feel like we just can’t win.  I understand fully the reasons why these differences exist, and even the fact that they are required due to the way .Net works.  But it just really makes me upset that (once again) there are things we are going to have to document, work around, answer a million tech support requests over, because of an implementation decision by Microsoft that we have no control over, or way to change how it works.

And people wonder why I am going gray so rapidly.

.Net 2 to 4 migration timeframe for developers?

Hmm, I wonder how many years until .Net 4 will be the defacto standard for development?  Lots of studies show developers want to jump on 4.0 right now, and many claim they will adopt it as soon as it is shipped.  But the reality is that products have development cycles, and you have to wait for tool vendors, hosting companies, IT departments, etc to adjust to the new runtime.

API developers have some time

I think this process will be 3-5 years.  I don’t see Microsoft pulling .Net 2 targeting from Visual Studio until 2015.  There is just way too much invested in the .Net 2 infrastructure, training, tools, etc.   Sure, there will be a wave of early adopters who are quite loud and vocal, but then the rest of the world will take years to phase out their .Net 2 tools.

We still get requests from companies who are using .Net 1.1 and refuse to move to 2.0 because of one issue or another.  What they don’t usually understand is that they are not running on .Net 1.1 anyway, they have already been promoted just by .Net 2 being on the box.  .Net 4 is different in that is does not ever promote automatically, a .Net 2 executable will prompt for that framework if only .Net 4 is present.  This will further allow developers to truly stick to .Net 2 if they so desire for a long time to come.

Start thinking now

If you are an API writer, you need to start thinking now.  Early adopters are going to start hitting your API’s with .Net 4 soon (if they are not already).  Make sure you understand the implications of such a move and think through the edge cases.

Should VDB4 be .Net 2 only?

I have no answers, only more questions.

Should VDB4 as a platform only accept .Net 2 assemblies in the database to ensure they can always run?  You can target .Net 2 within VS 2010 for a DLL.  Maybe this is the best answer to ensure compatibility.

We could keep the entire VDB4 engine always as a .Net 2 assembly and build a different execution platform around the same API and features for .Net 4 (and call it something like VistaDB 4 Cinco or something).  This would require a more complex sales process, documentation, and understanding by users. But it would keep the core functions the same while allowing the platform to move forward with .Net 4 features.

More resources

Take a look at this post by the CLR team for all sorts of scenarios where CLR side by side IS enabled (not just unmanaged code can do it).  But the one scenario we need to support is the one not supported.

I get questions all the time from users who are confused about our release cycle and product versioning.  I think I may finally have come up with a good analogy thanks to some conversations with users.

Single Version?

The Single Version moniker on the VistaDB Personal and Lite editions can be confusing.  These are both single releases of the platform.  You get all the builds within that major.minor, but when a new release of the platform comes out they are not included.  The “dot release” is not simply a rollup of the previous builds with a new name.  They are a new feature set and release point for functionality.

Maybe they should be called a single release edition since a release to us is a major.minor.  But I think that term would be even more confusing to most users.

A Release is a promise of functions and features

Our goal is to ensure all major.minor builds are compatible with each other in both API, and functionality.  We strive very, very hard to never break an API until the next bump in the version.  Otherwise you could end up with two builds of the same major.minor that behave differently, or are not runtime compatible.

Each time we need to change an API we bump the product minor to ensure that everyone knows they are going to need to compile their app, not just replace the DLL.  Normally for builds within a single release you can just drop in the new DLL and continue to run (with whatever fixes, updates were included of course).

Apple OS X as a platform

Apple OS X (10) is really a platform.  You know when something says OS 10 compatible that you have a certain set of minimum features that are different from OS 9.  But OS 10.1, 10.2, 10.3 are all really new releases.  Not just rollups of service packs or bug fixes. 

Each “minor” release is a paid upgrade from Apple specifically because they are really new feature sets and additions.  And not all software made for 10.3 will run on 10.0 if they take advantage of those new features.

VistaDB 3 platform history

VistaDB 3 is a platform for our 100% managed code database engine.  It is very, very different under the hood from VistaDB 2.   They really could have been called two different product names.  Even though they are built upon the same base foundation of philosophies and design principles, they serve very different needs.

3.0 was the initial release of the platform and introduced the new base level features for the platform.  But we always intended to add new features to the platform over time (and we did add a lot).

3.1 was skipped for technical reasons, and 3.2 was the next release.  It included a lot of roll ups from previous to be sure, but it also introduced several new features and API changes.  The number of API changes was the primary reason for bumping the release numbers.  Once again, just to ensure everyone knew their were API changes that their code would have to be tested against.

3.3 introduced major new features like T-SQL Procs so it was another new release point.

VistaDB 4 platform

VistaDB 4 is our latest platform that includes Entity Framework support, and continues to build on our SQL Server compatibilities while adding new features to the engine and tools.

We had considered naming it VistaDB Cuatro (spanish for 4) to give it more of a product platform image.  But we figured no one would be able to spell it correctly, and their used to be the Quatro database product (could be a little misleading).  Then we would have had a 1.0 release, 2.0, etc.  Each of them would stay within the VDB4 product platform, while adding new features.

Instead we decided to stick with the 4.0, 4.1, etc naming convention.  I am still not 100% convinced one way or the other is better, just different.

VistaDB 4.1 is under way

We are hard at work on VistaDB 4.1 already.  Not ready to unveil the feature list quite yet, but we are continuing to add both major and minor features and API changes to the engine and product tools.  This is a “major” release in that the entire engine is basically revved for the new release.

What are Service Packs?

Users often tell us that Microsoft names things like .Net 3.5 to mean the platform and then SPs are the post release changes.  We call those builds.  So VistaDB 4.0 is the initial release of the 4.0 platform, but there are builds 2-10 right now.  Any app written against Build 2 should also work with Build 10.  There are no API changes.

Microsoft tends to have a much longer rollup and hotfix cycle than a small company like us.  That is one of the benefits of using VistaDB, patches and updates are available every month.  The last fix we got from Microsoft to the .Net framework took 9 months from the time they told us it would release just due to the extreme long cycles between their releases.

Same thing, only different?

It is not that one way or the other is better, they are different.  This release early and release often mentality is something I have always embraced in all my products.  It is a key reason for users to have subscriptions.  You get all the updates, builds, releases during the subscription period.  That ensures you have a healthy platform, and helps ensure we have the revenue stream to provide that platform.

Many companies go through a release cycle, and then the product stays dormant as the development team moves on to other products for a cycle through it (Microsoft does this with some dev teams as well).  This means you have a stagnant platform during that cycle period until the developers “come back” to the project.  I don’t want to do that. 

We could split people and spend more time on other products, then cycle back around to the core again at a later time. It would probably be more profitable for us to do so, but I think we have tons of ideas for things to do within the engine and tools.  To split us off would be counter productive in my opinion.  I would rather add new product lines once we can afford to hire full time resources for them, rather than time slice what we have today.

This “full time” aspect of the team on the engine does add to it’s engineering costs significantly, but I feel with a good subscriber base who appreciates the release cycles and their goals it should be sustainable.  That is where subscribers come in for us, they help regulate that cash flow so we don’t have to do a release cycle and charge for upgrades to pay for it, then rinse and repeat.  This is a cycle I see too many small companies fall into. 

Version 14?

Heck, look at Winzip.  Is there really a need for it to be version 14?  No, they just need to sell upgrades every year to continue to pay for the staff and resources of maintaining the product.  I never understood why they didn’t just offer a low annual subscription that keeps everyone up to date.  Instead people buy 1 version, then wait until it can’t run anymore before they finally upgrade to the current release.

Seeing as how Dot Net 4 is out already in preview form, and slated for release in March I think it is about time for us to start thinking about our strategy for Dot Net 4. What are your companies doing?

Any plans?

Does your company have any plans around .Net 4?  Thinking about it?  Don’t care?

Some options

• Continue with your existing 2.0 app since 4.0 apps can load your assembly.
• Upgrade the 2.0 code to 4 since 2 will not always be present on the runtime, but then how much refactoring do you do to take advantage of new things in Dot Net 4?  Optional params could help API writers a lot by removing all those overloaded versions of the calls.
• Wait and see what your favorite tool vendors are going to do and then plan afterwards
• Investigate the new release and see what new features might help you improve your product and schedule out only those.
• Should you build 2 assemblies?  A Dot Net 2 for legacy apps, and Dot Net 4 for current apps?  Is there really going to be an early adopter market for .Net 4 only tools?

Reading and thinking

Right now I am reading and thinking about what I read and watch online.  Other companies that I talk to say they want to be there first just to cut off their competitor.  Well, Microsoft will be there before us (built into the framework), so that doesn’t work.

A few said they would recompile their existing codebase to run on 4, and ship a 4 version with only that recompile (if it will work).  I think this is sad, but am also sure it will happen a lot.  Just to get something on Dot Net 4 there will be many who cross compile and work out the issues.

Visual Studio 2010

One thing I know for sure.  Devs are going to flock to VS 2010 when it ships.  That always happens.  All tool vendors have to get ready starting now.  We may end up with a new Visual Studio designer in VS 2010, and some new templates. 

Siliverlight 4

I’ll admin that I am interested in researching Silverlight 4 if it can run normal desktop code.

Microsoft Sync V 2

Sync 2 is already under research here at VistaDB.  We are working on some forms of sync, what works, what sucks, etc.  Pro users will get beta access of course once we get that far. 

ADO.NET Data Services Offline

This has the most potential I think to let people build client / server line of business apps with offline and online access using only Entity Framework technology.  This could be a huge market for developers in the next few years.

What about you?

Are you giving any thought to all of this? 

Our blog uses the excellent Dot Net Blog Engine as our blogging platform.  You can use VistaDB as the backend store quite easily.  Al Nyveldt’s blog has some great posts about setting up and configuring VistaDB with Blog Engine, but they are all for VistaDB 3.x. 

Upgrading to VistaDB 4

The Dot Net Blog Engine knows how to load provider factories, so it is quite easy to update the configuration to talk to VistaDB 4.  I did these steps on our blog a while ago and everything has continued to run smoothly.

The following are the steps you have to perform in order to switch to using VistaDB 4 engine with the Blog Engine 1.5 release.

• Upgrade the database
• Alter the web.config with new values
• Put a licenses.licx in the root directory
• Compile the runtime license in Visual Studio
• Put the runtime license (App_Licenses.dll) and the VistaDB.4 dll in your bin folder

Upgrade the database

If you already have a blog up and running grab the BlogEngine.vdb3 and use the Data Builder to upgrade the database.  Change the name to BlogEngine.vdb4 as a part of the upgrade.

Web.config Changes

The following lines need to be added / updated in the web.config.  They should already exist if you followed Al’s setup for 3.x previously.

ConnectionStrings

<add name="BlogEngine" connectionString="Data Source=|DataDirectory|\BlogEngine.vdb4; 
               Open Mode=NonexclusiveReadWrite" providerName="System.Data.VistaDB"/>

Change the connection string to load the vdb4 version, and update the provider name.

DbProviderFactories

<remove invariant="System.Data.VistaDB"/>
<add name="VistaDB 4.0" description="VistaDB 4 ADO.NET Provider for .Net 2.0-3.5" 
      invariant="System.Data.VistaDB" type="VistaDB.Provider.VistaDBProviderFactory, 
             VistaDB.4, Version=4.0.0.0, Culture=neutral, PublicKeyToken=dfc935afe2125461" />

You only have to do the remove portion if you already have the provider factory in your machine.config (to prevent conflicts). But I usually always put them in just to be safe.

Assemblies

<add assembly="VistaDB.4, Version=4.0.0.0, Culture=neutral, PublicKeyToken=dfc935afe2125461"/>

Add Licenses.licx

Add a new text file to the root of your solution named licenses.licx with the following entry.  If you are using any other third party licensed components they may already have entries present as well.  Just add this line at the end.

VistaDB.VistaDBLicense,VistaDB.4, Version=4.0.0.0, Culture=neutral, PublicKeyToken=dfc935afe2125461

Compiling a licenses.licx in Visual Studio

Right click the file in the Visual Studio solution and choose the Build Runtime licenses entry.

This will build a new dll in your bin folder with all your runtime licenses embedded from your local machine.  This is how you get a licensed component into a website.

Runtime Binaries

The resulting App_Licenses.dll in the bin needs to be uploaded with the rest of the site to your webhost.

The VistaDB.4.dll is the runtime engine that also needs to be present in your bin folder.

Test local and then deploy

A local test should be able to run just fine from within Visual Studio, then you can deploy it to your webhost just like the previous version.

 

NOTE:  Don't forget to change the blogProvider to be the database provider (it will use the XML one by default).

<blogProvider defaultProvider="DbBlogProvider">

 

From the last blog post I talked about how to use the SQL Server Import Wizard to get data into SQL Server from VistaDB.  You can use that Wizard to build DTS-like packages (They are called dtsx files in SQL Server 2008, but are actually run by SQL Server Integration Services).  But that process is a bit cumbersome and only works one table at a time.

You also lose null rows, foreign keys and check constraints.  There is a better way, but it involves a LOT more work – build an SSIS package.

SSIS packages are Integration Services Projects

You can build a package through Visual Studio if you have the full SQL Server or SQL Server Developer installed with the SSIS templates.  These templates show up as a part of the project types you can choose.

If you do not have Visual Studio on the SQL Server, but have the full SQL Server you get a special version of Visual Studio called SQL Server Business Intelligence Development Studio (I think only Standard and Enterprise come with this).

Launching the shortcut looks very much like Visual Studio Team System.  Select Create a new project from the Business Intelligence Projects area of the new project dialog.

Integration Services Project

Choose the Integration Services Project as the template you wish to build. And you will see a new solution like the one pictured. 

The SSIS Packages are the output from the DTSWizard in the previous post.  You can open one of them to see what steps it took as a result of your choices.

I don’t like the default DTSWizard packages for the above reasons, but also because they don’t use a very good pattern in the SSIS Package.

The first step they call is a Drop Tables task, which as shown yesterday will result in an error if it does not exist because the error clause is not trapped in the package. 

The second step is a prepare SQL task that builds the target table (incorrectly in most cases).

And the third step is to do a Data Flow task to migrate the data.

A better approach

A better approach is to build the schema yourself, so the foreign keys, identity fields, etc are all built in advance.  I usually run a first step that calls drop on each of the tables I want to migrate, or at least just their data.

These initial tables can be built manually, or from an existing database scripted in SQL Management Studio.  A future build of Data Builder will also include SQL scripts that are compatible here as well (it is in the works and this very process how I have been testing them).

Then I manually build Data Flow tasks for each table I want to migrate because you have much finer grain control through Visual Studio.

Chaining the Data Flows Together

The flow diagram you see here is each of my tables being migrated one at a time to the OLE DB datasource for SQL Server.  Why OLE DB?  For the very specific reason that it works with constraint checking off as an option (required if you have FK’s and Identities in your data).

Data Flow Details

Each of the Data Flows shows (like Tickets) has a detail that breaks down into 3 steps.  The first is the loading of a source table entirely (no SQL statement required).

 

The VDB Database Tickets above may be right clicked and select Edit to see this edit dialog.

Being able to select a table or view avoids nasty surprises with the SQL statement (no need to do a select * on the table).  You can drill down to the columns and choose which ones you want to import, what data types they should expose, etc.  The interface is really quite powerful, but has one problem.

Unicode – Gotcha

Search around about SSIS packages and you will run into tons of posts where people are having errors in their Data Flow because their types are being confused as unicode.  This can come from Excel, flat files, or VistaDB.  In any case where a .Net string is returned the packager things it should convert to a DT_WSTR (Wide String) to the OLE DB SQL Server provider.

This leads to problems where the column is taking up “twice” the space you thought it was because a varchar(100) suddenly becomes a 200 byte WSTR entry.  Some column types fail altogether at insertion time.  Microsoft still states this is by design, they didn’t want to auto promote types for users and would prefer you have to be explicit in your conversion requirements.  DTS never required this (SQL 2000) and everything else in SQL Server tends to auto and implictly convert when you don’t want it to!  But NOW they decide it is a bad idea.

Data Flow Conversion

The solution is to add a Data Flow Transformation of type Data Conversion to your Data Flow task.  You then drag the green output from the VDB4 database and put it into the Data Conversion.  In that object (right click edit) you can control the output of each column, including conversion.

Notice the Login column has been changes to an output of Copy of Login with an output of DT_STR rather than the DT_WSTR.  This will give the OLE DB provider an ASCII string rather than unicode.

    OLE DB Destination

Now the final step is to  put the data in the database using the OLE DB Destination object.  Drag and drop the green output from the Data Conversion to the OLE DB control, right click edit.

Now we see a dialog that lets us determine where we will put the data in the target server.

You have a number of access modes for the table, and you can select the targeted object directly.  Turn off Check Constraints to allow Identity inserts to happen correctly, and no FK validation.  Turn on Keep Identity to avoid your old identity values being changes, and Keep Nulls ( the default for the column will be applied when null is found otherwise).

Now those Copy of column names can be visually mapped up to the destination columns to avoid the Unicode problem.  You can also avoid all of these steps if your columns are all Nvarchar and Ntext since they don’t have to be migrated.

Build and Run

Once you get all of these in place (one per table, and yes it is a lot of work).  You can run and rerun the migration whenever you would like.  But if any schema changes, you will have to manually go in and determine what happens to new or old columns. 

The messages were not very helpful to finding out why things failed, but they are better then nothing.

Tada!  Data in SQL Server!

That’s it!  You now have your data in SQL Server.  And a way to reproduce the migration whenever you need it.

On my server the migration of the TickeSystem to SQL Server takes about 55 seconds.  The same table migrated back to a VistaDB database from SQL Server takes about 7.  (I like that)

Quick Video showing editing the data types

Here is a quick video showing how to edit the SSIS package column mappings.

We get this question from customers every so often, how can you use the SQL Server Import and Export Wizard to migrate data from VistaDB 4 to SQL Server 2008?

There is only one real tricky part that is specific to VistaDB 4, the license setup.  SQL Server obviously was not built by you (or us), so it has not built in license for VistaDB.  But we already thought of that and give you a way to add a Design Time license flag to third party applications through their app.config.

DTSWizard does have an app.config

Take a look at the property for the shortcut from your start menu.  If should point somewhere like "C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe".  Go to that directory and you will find a DTSWizard.exe.config file that you can edit to include the following lines.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

  <appSettings>
    <add key="VistaDBUseDesignTimeLicense" value="true"/>
  </appSettings>

Save the config file and relaunch the DTSWizard, now it has a design time license of VistaDB.  That means it can run on any machine where you have a valid developer license installed.  The VistaDB provider will show up in the list of Providers you can choose in the source database step (only on machines where it is installed of course).

Select the … in the Data Source row to go and find your database.  Everything else is pretty normal at this point.

Looks easy!

At first it seems quite easy, there is a Data Import Export Wizard for SQL Server, and it claims to support ADO.NET Providers.  But actually using it turns out to be a pain in the rear as for non Microsoft providers it only supports a mode that copies one table at a time and you have to write the queries by hand first!

There is a nice pretty “Copy data from one of more tables or views” but it is grayed out for third parties.  Nice.

But it is not so easy for third parties

Sure wish third parties could use that first option, would save you a ton of time.

So you go ahead with the only option you have available and write a quick select * from sometable.  First thing you will notice is that the destination table has nothing to do with the table you chose, it is [dbo].[Query].  Go ahead and change that to what you wanted it to be (maybe the table name, crazy thought).

You can change the destination by just selecting it and typing in what you want (sort of non obvious though).  Don’t hit next just yet though, select the Edit Mappings button near the bottom of the dialog.

Make sure you select the enable identity insert if you already have identity values you don’t want to lose!  Create destination table is checked by default if the target database doesn’t have a table already present. 

Also keep an eye out for Type changes.  For some reason the wizard wants to convert NText columns to XML!  Review your types to ensure they are correct.

Identity columns not mapped

You may also want to edit the SQL to tweak anything you might like (maybe your identity clause since the wizard isn’t going to create it).

In my example the TicketID is the identity, but the wizard is not going to recreate the table with that identity present.  In my case I needed to add the IDENTITY(2829,1) in the SQL.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

CREATE TABLE [dbo].[Tickets] (
[TicketID] int IDENTITY(2829,1)  NOT NULL,
[Title] nvarchar(128) NOT NULL,
[EmailAddress] nvarchar(128),

That identity property is set on the column at the provider level, but the wizard never checks for it.

Unicode .Net Mapping

Clicking next will present you will a bunch of text basically telling you that there is no specific mapping file present for VistaDB, so it is going to use the SQL to Unicode .Net mappings by default.  This is in fact just fine.

Save or Run Immediately

At this point if you have a full SQL Server and the SQL Server Integration Services installed you can save the SSIS package and modify it later.  If you are running SQL Server Express you do not have this option, you can only run it.

Run SSIS results

The run SSIS results look a little strange at first though, if you chose to drop the table you may have an error like the one below.

The error actually just means that there was no table present to drop.  Why that counts as an error I am not sure (ever hear of try / catch?).  But you can ignore it as long as you see the rows transferred on the copying entry.

One table at a time

Yep, you can basically use this technique to recreate your database, one table at a time.  And you will have no Foreign Keys present because the system never asks for them.

If you have the full SQL Server installed you can save the dtsx file to your machine and then go edit them in the Business Intelligence Studio later.  If enough people are interested I will cover that in another blog post.

Manually Build FK’s

The worst side effect of this migration of data is that all FK’s are lost.  Using the full Business Intelligence Studio you can work around this issue though.  It does take quite a bit of time to setup an SSIS package to migrate your database, but it works in the same basic steps as above.

If you are interested in seeing a full migration in the studio let me know.

What about the other tools?

There are other tools that the SSIS uses for full package running and editing.  All of those tools are present in the same directory as the DTSWizard and all have app.config files present as well.  Edit each of them to include the same lines and all of them will be able to use the design time license on the machine.  If you ever see an error stating that you must have a license to use VistaDB you probably missed an app.config for a tool.

Build 10 is mostly a rollup of fixes from user tickets over the past few weeks.  This will also become the new trial version since it works with Visual Studio 2010 Beta 2.

ASP.NET Membership

Corrected issues in the VistaDB ASP.NET Membership providers and wizards pertaining to the Asp.Net Configuration Wizard of Visual Studio. The web.config should persist newer provider entries, and the license manager can correctly fall back to the design time license for use in the Asp.Net Configuration wizard of Visual Studio.

Corrected issue in the VistaDB ASP.NET Membership providers where the GetRolesForUser and GetUsersInRoles methods were not returning any results when roles were mapped in the database.

DBNull vs Null

Corrected VistaDBDataReader to return DBNull instead of null on column values.  Attempts to read from a row that contained a null value would return null rather than DBNull.  The SQL Server behavior is to return DBNull, so we have changed to match that behavior.  This was actually performed in Build 7, but we forgot to include it in the release notes.

DBA Sample Tool Update

A big thanks to Chris Bray (long time Team VistaDB member, customer, and contributor) for updating the DBA Sample tool.  Chris made a number of changes to make it faster to modify the tool to be branded for your own company, and updated some features as well. 

There were also some artifacts still left of VDB3 in the code that he removed.  This sample can be used commercially within your applications, but may not be sold as a stand alone product or tool.  You have to be including it with a product for your users to use as an admin tool, something like that.  It should not be used as a data entry replacement - it is not end user friendly in that regard.  Tech support personnel, local admins, etc are all good candidates for the tool. 

A new file (Common.cs) has been included to allow a central point for the customization of the tools naming and config options.

The DBA Sample Tool is a tremendous time saver for building a tool your tech support people can send to customers.  We have had a number of companies tell us this tool alone was worth the purchase price because it saved them more than the VistaDB license cost in programmer time to build something like this for their applications.

Fixes

Fixed a memory leak in Data Builder when browsing the Data tab memory could become pinned due to certain column types not being cleaned out of the grid.

Fixed a column result returning inaccurate results with sub queries that are filtered by columns in their parent query.  This behavior is used in some cases through Entity Framework when using aggregates like Count on sub queries.

Fixed an issue where performing a database update in an Entity Framework model with views present would generate an error.