Gibraltar VistaDB Embedded Database for .NET v6.4.2
Gibraltar VistaDB Embedded Database for .NET v6.4.2
Microsoft has worked hard to make .NET and SQL Server a powerful combination - and VistaDB brings that capability to a small, easy to deploy package. Instead of having to ship a large installation which has to be set up independently for your application to work you can just ship 2 MB of extra assemblies with your .NET application. It's easy to make applications that start with VistaDB and scale up with SQL Server, all from a common codebase.
Compared to Microsoft SQL Server...
Tiny Distribution: The core engine is just over 1MB in size when deployed (often 450kb when compressed) compared to over 132MB for the compressed SQL Express installation.
Cross-Platform: Works on 32 or 64 bit and can be deployed under Mono and .NET
Full Database Encryption: Instead of just encrypting individual fields or tables the entire database file is encrypted.
No Privileges Required: VistaDB can be used even by guest accounts, not requiring any special OS permissions to create or work with databases or be deployed.
Easy Database Distribution: Since a database is just a file including schema and data it's easy to make backups and create new databases just using standard file operations.
Compared to Other Embedded Databases...
100% Managed Code for Simple Deployment: Only one set of assemblies is needed, covering 32 or 64 bit, Intel and ARM processors.
Impressive Commonality with Microsoft SQL Server: Data types, User-defined Functions, and even Stored Procedures can all be shared between SQL Server and VistaDB
FIPS-compliant Full Database Encryption: Securely encrypt the entire database including schema and data using FIPS-compliant AES encryption
Visual Studio Integration: Full designer support within Visual Studio including the Dataset designer, Entity Framework Designer, schema management, and query building.
.Net Database Basics
If a relational database doesn't have advanced features like updatable SQL Views, true referential integrity, cascading updates / deletes, table value params, T-SQL stored procedures, CLR Triggers, and CLR stored procs then why would you bother? Database engines that only provide basic ADO.NET features are a waste of your time. VistaDB is the most advanced embedded SQL desktop database engine in the world. We built VistaDB from the ground up to be a managed database engine, this is not some unmananged C++ database with a .Net wrapper!
Support VistaDB and SQL Server from the same executable?
Do you wish you could build a single executable that supported both SQL Server for multi user and another database on the desktop? We know how hard it is to build applications that support both desktop and client server from a single executable. VistaDB syntax contains the most commonly used features from SQL Server. All of our syntax will scale up to SQL Server. This means you can use one codebase in your product and run it against SQL Server and VistaDB. VistaDB fully supports the ADO.NET Provider Factory model. This allows you to dynamically load the database engine at runtime based upon application preferences. Many companies use VistaDB on the desktop and SQL Server for larger deployments. It is not the easiest way to write database code, and there is much less documenation around to explain how to do some things in Visual Studio. But if you take the time using Provider Factories can be a powerful runtime feature for your application.
.Net Database Admin Tools?
Some embedded databases give you an engine and nothing to edit or maintain the actual database. You usually have to purchase a third party DBA tool, build you own, or tell users to go download the large bulky tool from the vendor.
Performance
We have done a deep dive into the way VistaDB processes queries and implemented many query optimizations. Relative to VistaDB 4.3, VistaDB 5 is generally 25-30% faster across the board with some queries now running in milliseconds rather than seconds, seconds rather than minutes.
SQL Compatibility
VistaDB 5 includes new T-SQL language features such as ANSI standard OFFSET/FETCH paging as well as new data types including DATETIMEOFFSET, DATETIME2, DATE, TIME, DOUBLE PRECISION, FLOAT, BINARY(N) and VARBINARY(N). VistaDB 5 also includes improved compatibility with T-SQL scripting and improvements to SQL functions such as CONVERT and RAND.
Security
The previous database encryption which used a managed implementation of Blowfish has been replaced with the FIPS-compliant AES implementation built into the .NET runtime. When an encrypted database is upgraded it will be converted from Blowfish to AES. For .NET 4.0 and later the FIPS-compliant unmanaged implementation built into the .NET runtime (which leverages the Microsoft Crypto API underneath) is used. For .NET 2.0 the managed implementation is used since the FIPS-complaint implementation isn't available.
The new encryption support is now database-wide instead of requiring individual columns to enable encryption. This protects the entire database including schema. This mode is also faster than the previous implementation.
Reliability
VistaDB 5 includes dozens of fixes for errors in the query parser and execution engine that have been reported by users of the past couple years. Additional bug fixes correct issues reported in VistaDB tools and Visual Studio integration.
Tooling
VistaDB 5 includes improvements to the Data Migration and Data Builder tools as well as support for Visual Studio 2013 and Entity Framework 6. In addition, VistaDB includes new diagnostic logging support that you can use with Gibraltar Loupe or other logging tools to assist with database debugging and query optimization.
We have done a deep dive into the way VistaDB processes queries and implemented many query optimizations. Relative to VistaDB 4.3, VistaDB 5 is generally 25-30% faster across the board with some queries now running in milliseconds rather than seconds, seconds rather than minutes.
SQL Compatibility
VistaDB 5 includes new T-SQL language features such as ANSI standard OFFSET/FETCH paging as well as new data types including DATETIMEOFFSET, DATETIME2, DATE, TIME, DOUBLE PRECISION, FLOAT, BINARY(N) and VARBINARY(N). VistaDB 5 also includes improved compatibility with T-SQL scripting and improvements to SQL functions such as CONVERT and RAND.
Security
The previous database encryption which used a managed implementation of Blowfish has been replaced with the FIPS-compliant AES implementation built into the .NET runtime. When an encrypted database is upgraded it will be converted from Blowfish to AES. For .NET 4.0 and later the FIPS-compliant unmanaged implementation built into the .NET runtime (which leverages the Microsoft Crypto API underneath) is used. For .NET 2.0 the managed implementation is used since the FIPS-complaint implementation isn't available.
The new encryption support is now database-wide instead of requiring individual columns to enable encryption. This protects the entire database including schema. This mode is also faster than the previous implementation.
Reliability
VistaDB 5 includes dozens of fixes for errors in the query parser and execution engine that have been reported by users of the past couple years. Additional bug fixes correct issues reported in VistaDB tools and Visual Studio integration.
Tooling
VistaDB 5 includes improvements to the Data Migration and Data Builder tools as well as support for Visual Studio 2013 and Entity Framework 6. In addition, VistaDB includes new diagnostic logging support that you can use with Gibraltar Loupe or other logging tools to assist with database debugging and query optimization.
New Features in This Release
.NET 6.0 Support
The VistaDB NuGet packages now include builds for .NET 6.0. The build target for net5.0 is still provided in this version, but may go away in future releases because Microsoft ended support for .NET 5.0 on May 10, 2022. .NET 6.0 is considered the Long Term Support version for this set of API's and applications targeting .NET 5.0 should be planning their migration to .NET 6.0 per Microsoft’s long-term support policy.
AES256 Encryption
VistaDB has used AES128 since version 5 to encrypt database pages when a password is specified to enable encryption. Starting with version 6.4, VistaDB will now use AES256 to encrypt database pages for new databases. VistaDB 6.4 will upgrade encrypted databases from AES128 to AES256 when a PackDatabase or RepairDatabase is performed.
Once an encrypted database is upgraded to AES256, it can't be opened by an older version of VistaDB. The older version of VistaDB will report that the password is incorrect even when it is correct and not open the database. The database will not be damaged by this attempt to open it.
VistaDB 6.4 can open and write to VistaDB 6.* databases encrypted with AES128 and will not upgrade their encryption unless a PackDatabase or RepairDatabase is performed.
Selecting Passwords for AES256
While shorter passwords may still be used (particularly with a test database where security is not really a concern), a longer password with more “entropy” would be required—about double what was required with AES128—in order to actually provide the greater security. For example, a Base64 encoding (or hexadecimal encoding) of 32 randomly-generated bytes (32 * 8 = 256).
Performance of Encrypted Databases
While AES256 itself is slightly slower than AES128, the way database pages are each encrypted as a block makes the overall performance impact of this change minimal—and likely negligible—compared with an AES128-encrypted database.
The format for databases that are unencrypted (the default with VistaDB) is unchanged, so prior versions of VistaDB 6 are still interoperable with unencrypted databases created or updated by this version of VistaDB.
Breaking Changes
Fixes to the VistaDBConnection.GetSchema method for “tables” (see below) can result in different tables being returned. When the restrictionValues array argument specifies a tableType value of null (no restriction on tableType) or “SYSTEM_TABLE” and should include any system tables, the [database schema] system table was not being returned in error. This defect has been fixed and the [database schema] will now be included.
If the [database schema] system table is not desired in the returned collection, the tableType value at array index 3 in the restrictionValues argument can be specified as “BASE TABLE” to return only normal (non-system) tables.
Issues Fixed in 6.4
Engine
Fixed: Using the unary operators minus (-) or bitwise-not (~) directly on a column reference can alter the internal store used for other references to the same column, causing incorrect results and potentially unpredictable filtering.
Fixed: VistaDBConnection.GetSchema("tables", ...) may not return any tables when both a tableName and tableType are specified (non-null) in the restrictionValues array argument, even when there should be a match as specified.
Fixed: VistaDBConnection.GetSchema("tables", ...) does not list the [database schema] system table when it should be included in the returned collection.
Fixed: Setters for VistaDBCommand properties CommandText and Connection can fail with a VistaDBException with error number 1015 and a message about “overlapping use” when the instance is idle but currently points to a VistaDBConnection which is immediately executing for a different VistaDBCommand instance.
Fixed: The IVistaDBDDA.OpenDatabase method and the IVistaDBDatabase.OpenTable method may fail with an exception message such as “Collection was modified; enumeration operation may not execute” or “An item with the same key has already been added” when being called from multiple threads (even with separate instances), due to a race condition. This might also affect related ADO.NET usage, which uses these methods internally.
Fixed: Row lock via IVistaDBTable.Lock(long) may not be released properly by IVistaDBTable.Unlock(long) if IVistaDBTable.Post() had not been called, until the IVistaDBTable is disposed.
Data Builder
Fixed: Data Builder will not auto-open a VistaDB database which has a custom filename extension (other than .vdb6) when passed as a command-line argument. This includes double-clicking the database file.
Known Issues
Visual Studio
Extension Doesn't Automatically Install If Visual Studio is Running: If any instance of Visual Studio 2017 or later is running when the installation runs the VistaDB extension will not automatically install into Visual Studio. To manually install, double-click VistaDB.VSDesigner.15.vsix (for Visual Studio 2017 / 2019) or VistaDB.VSDesigner.17.vsix (for Visual Studio 2022) in the C:\Program Files (x86)\Gibraltar Software\VistaDB 6\Bin directory (by default)
.NET 6.0 Support
The VistaDB NuGet packages now include builds for .NET 6.0. The build target for net5.0 is still provided in this version, but may go away in future releases because Microsoft ended support for .NET 5.0 on May 10, 2022. .NET 6.0 is considered the Long Term Support version for this set of API's and applications targeting .NET 5.0 should be planning their migration to .NET 6.0 per Microsoft’s long-term support policy.
AES256 Encryption
VistaDB has used AES128 since version 5 to encrypt database pages when a password is specified to enable encryption. Starting with version 6.4, VistaDB will now use AES256 to encrypt database pages for new databases. VistaDB 6.4 will upgrade encrypted databases from AES128 to AES256 when a PackDatabase or RepairDatabase is performed.
Once an encrypted database is upgraded to AES256, it can't be opened by an older version of VistaDB. The older version of VistaDB will report that the password is incorrect even when it is correct and not open the database. The database will not be damaged by this attempt to open it.
VistaDB 6.4 can open and write to VistaDB 6.* databases encrypted with AES128 and will not upgrade their encryption unless a PackDatabase or RepairDatabase is performed.
Selecting Passwords for AES256
While shorter passwords may still be used (particularly with a test database where security is not really a concern), a longer password with more “entropy” would be required—about double what was required with AES128—in order to actually provide the greater security. For example, a Base64 encoding (or hexadecimal encoding) of 32 randomly-generated bytes (32 * 8 = 256).
Performance of Encrypted Databases
While AES256 itself is slightly slower than AES128, the way database pages are each encrypted as a block makes the overall performance impact of this change minimal—and likely negligible—compared with an AES128-encrypted database.
The format for databases that are unencrypted (the default with VistaDB) is unchanged, so prior versions of VistaDB 6 are still interoperable with unencrypted databases created or updated by this version of VistaDB.
Breaking Changes
Fixes to the VistaDBConnection.GetSchema method for “tables” (see below) can result in different tables being returned. When the restrictionValues array argument specifies a tableType value of null (no restriction on tableType) or “SYSTEM_TABLE” and should include any system tables, the [database schema] system table was not being returned in error. This defect has been fixed and the [database schema] will now be included.
If the [database schema] system table is not desired in the returned collection, the tableType value at array index 3 in the restrictionValues argument can be specified as “BASE TABLE” to return only normal (non-system) tables.
Issues Fixed in 6.4
Engine
Fixed: Using the unary operators minus (-) or bitwise-not (~) directly on a column reference can alter the internal store used for other references to the same column, causing incorrect results and potentially unpredictable filtering.
Fixed: VistaDBConnection.GetSchema("tables", ...) may not return any tables when both a tableName and tableType are specified (non-null) in the restrictionValues array argument, even when there should be a match as specified.
Fixed: VistaDBConnection.GetSchema("tables", ...) does not list the [database schema] system table when it should be included in the returned collection.
Fixed: Setters for VistaDBCommand properties CommandText and Connection can fail with a VistaDBException with error number 1015 and a message about “overlapping use” when the instance is idle but currently points to a VistaDBConnection which is immediately executing for a different VistaDBCommand instance.
Fixed: The IVistaDBDDA.OpenDatabase method and the IVistaDBDatabase.OpenTable method may fail with an exception message such as “Collection was modified; enumeration operation may not execute” or “An item with the same key has already been added” when being called from multiple threads (even with separate instances), due to a race condition. This might also affect related ADO.NET usage, which uses these methods internally.
Fixed: Row lock via IVistaDBTable.Lock(long) may not be released properly by IVistaDBTable.Unlock(long) if IVistaDBTable.Post() had not been called, until the IVistaDBTable is disposed.
Data Builder
Fixed: Data Builder will not auto-open a VistaDB database which has a custom filename extension (other than .vdb6) when passed as a command-line argument. This includes double-clicking the database file.
Known Issues
Visual Studio
Extension Doesn't Automatically Install If Visual Studio is Running: If any instance of Visual Studio 2017 or later is running when the installation runs the VistaDB extension will not automatically install into Visual Studio. To manually install, double-click VistaDB.VSDesigner.15.vsix (for Visual Studio 2017 / 2019) or VistaDB.VSDesigner.17.vsix (for Visual Studio 2022) in the C:\Program Files (x86)\Gibraltar Software\VistaDB 6\Bin directory (by default)