ESENT (Extensible Storage Engine) API in the Windows SDK


I’m not sure how many people know that Windows comes with an embeddable, transactional database engine which is available to developers through the Windows SDK. The ESENT database engine can be used whenever an application wants high-performance, low-overhead storage of structured or semi-structured data. This can range from something as simple as a hash table which is too large to store in memory to a complex application with many tables, columns and indexes. ESENT is used by the Active Directory, Windows Desktop Search, Windows Mail and several other Windows services and a slightly modified version of the code is used by Microsoft Exchange to store all its mailbox data. The ESENT API is available through the SDK and can be used on all versions of Windows from Windows Server 2000 on up.


The significant technical features of ESENT include:


·         ACID transactions with savepoints, lazy commits and robust crash recovery.


·         Snapshot isolation.


·         Record-level locking — multi-versioning provides non-blocking reads.


·         Highly concurrent database access.


·         Flexible meta-data (tens of thousands of columns, tables and indexes are possible).


·         Indexing support for integer, floating point, ASCII, Unicode and binary columns.


·         Sophisticated index types including conditional, tuple and multi-valued.


·         Individual columns can be up to 2GB in size. A database can be up to 16TB in size.


·         Can be configured for high performance or low resource usage.


·         No administration required (even the database cache size can adjust itself automatically).


·         No download. Your application uses the esent.dll which comes with the operating system.


Caveats: ESENT should only be used for applications which have simple, predefined queries; applications that want to do ad-hoc queries should investigate a storage solution that provides a query layer. The database file cannot be shared between multiple processes simultaneously.


To use ESENT you just need to link with esent.lib and include esent.h, which is part of the Windows SDK. Here is a sample program that creates a database with one table, inserts a record and then retrieves the data from the record:


// We need to define JET_VERSION to be at least 0x0501 to get access to the


// JET_bitDbOverwriteExisting option and JetCreateInstance. That means this


// program will run on Windows XP and up (Windows Server 2003 and up).


#undef JET_VERSION


#define JET_VERSION 0x0501


 


#include <stdio.h>


#include <string.h>


#include <esent.h>


 


// One possible error-handling strategy is to jump to an error-handling


// label whenever an ESENT call fails.


#define Call(func) { \


       err = (func); \


       if(err < JET_errSuccess) { \


       goto HandleError; \


       } \


} \


 


int main(int argc, char * argv[]) {


       JET_ERR err;


       JET_INSTANCE instance;


       JET_SESID sesid;


       JET_DBID dbid;


       JET_TABLEID tableid;


 


       JET_COLUMNDEF columndef = {0};


       JET_COLUMNID columnid;


 


       // Initialize ESENT. Setting JET_paramCircularLog to 1 means ESENT will automatically


       // delete unneeded logfiles. JetInit will inspect the logfiles to see if the last


       // shutdown was clean. If it wasn’t (e.g. the application crashed) recovery will be


       // run automatically bringing the database to a consistent state.


       Call(JetCreateInstance(&instance, “instance”));


       Call(JetSetSystemParameter(&instance, JET_sesidNil, JET_paramCircularLog, 1, NULL));


       Call(JetInit(&instance));


       Call(JetBeginSession(instance, &sesid, 0, 0));


 


       // Create the database. To open an existing database use the JetAttachDatabase and


       // JetOpenDatabase APIs.


       Call(JetCreateDatabase(sesid, “edbtest.db”, 0, &dbid, JET_bitDbOverwriteExisting));


 


       // Create the table. Meta-data operations are transacted and can be performed concurrently.


       // For example, one session can add a column to a table while another session is reading


       // or updating records in the same table.


       // This table has no indexes defined, so it will use the default sequential index. Indexes


       // can be defined with the JetCreateIndex API.


       Call(JetBeginTransaction(sesid));


       Call(JetCreateTable(sesid, dbid, “table”, 0, 100, &tableid));


       columndef.cbStruct = sizeof(columndef);


       columndef.coltyp = JET_coltypLongText;


       columndef.cp = 1252;


       Call(JetAddColumn(sesid, tableid, “column1”, &columndef, NULL, 0, &columnid));


       Call(JetCommitTransaction(sesid, JET_bitCommitLazyFlush));


 


       // Insert a record. This table only has one column but a table can have slightly over 64,000


       // columns defined. Unless a column is declared as fixed or variable it won’t take any space


       // in the record unless set. An individual record can have several hundred columns set at one


       // time, the exact number depends on the database page size and the contents of the columns.


       Call(JetBeginTransaction(sesid));


       Call(JetPrepareUpdate(sesid, tableid, JET_prepInsert));


       char * message = “Hello world”;


       Call(JetSetColumn(sesid, tableid, columnid, message, strlen(message)+1, 0, NULL));


       Call(JetUpdate(sesid, tableid, NULL, 0, NULL));


       Call(JetCommitTransaction(sesid, 0));    // Use JetRollback() to abort the transaction


      


       // Retrieve a column from the record. Here we move to the first record with JetMove. By using


       // JetMoveNext it is possible to iterate through all records in a table. Use JetMakeKey and


       // JetSeek to move to a particular record.


       Call(JetMove(sesid, tableid, JET_MoveFirst, 0));


       char buffer[1024];


       Call(JetRetrieveColumn(sesid, tableid, columnid, buffer, sizeof(buffer), NULL, 0, NULL));


       printf(“%s”, buffer);


 


       // Terminate ESENT. This performs a clean shutdown.


       JetCloseTable(sesid, tableid);


       JetEndSession(sesid, 0);


       JetTerm(instance);


       return 0;


 


HandleError:


       printf(“ESENT error %d\n”, err);


       return 1;


}


 There is a lot of information about ESENT, including complete documentation for all the APIs, on MSDN http://msdn.microsoft.com/en-us/library/ms684493(VS.85).aspx. You can get an overview of the underlying technologies by reading the “Using Extensible Storage Engine” topics http://msdn.microsoft.com/en-us/library/aa964813(VS.85).aspx.


Laurion Burchall
Software Design Engineer, ESENT Team

Comments (34)

  1. Although the ESENT API has been available in the Windows SDK for several years, I don’t think enough

  2. ESENT is powerful embedded database engine that is part of Windows. It used by the Active Directory,

  3. My latest in a series of the weekly, or more often, summary of interesting links I come across related to Visual Studio. Scott Guthrie posted the latest installment of links for ASP.NET, Visual Studio, WPF and Silverlight . Vance Morrison posted links

  4. CoqBlog says:

    Vous avez probablement déjà vu, au cours de vos recherches dans Process Explorer par exemple, que certains

  5. flatfish says:

    Any help here?

          I want to create an instance of Database by ESE API, then attach an offline backuped Active Directory Database file "ntds.dit". It always report error "database page size mismatches engine size".

          The offline Active Directory Database size is 8192. However, The ESE API on windows server 2003 always creates an instance with default database page size 4096.

          I try to call JetSetSystemParameter function to set database page size to 8192, it always fails.

    question:

        How can I create an instance of Database with default page size 8192 on Windows Server 2003 by ESE API?

    Any feedback will help. Thanks

  6. laurionb says:

    If setting the database page size is failing then esent is probably already initialized. Set the system parameter before calling JetInit.

  7. flatfish says:

    I ever tried the method of setting system parameter before calling JetInit, but it still failed.

    Thanks

    Changhua

  8. flatfish says:

    I ever tried like thus.

    1.) set system parameter

    2.) create instance

    3.) call JetInit

    Actually, it will fail in calling JetInit.

  9. flatfish says:

    I ever tried like thus.

    1.) call setsystemparameter to set page size to 8192

    2.) create instance

    3.) call JetInit

    Actually, it will fail in calling JetInit

  10. laurionb says:

    You are probably referencing the wrong logfiles then. Look for logfiles in the current directory and make sure you are setting the log path, basename and extension system parameters properly.

  11. flatfish says:

    Thank you very much! I will try.

  12. flatfish says:

    // Mount01.cpp : Defines the entry point for the console application.

    //

    #include "stdafx.h"

    //#undef  JET_VERSION

    //#define JET_VERSION 0x0501

    // #define _WIN32_WINNT 0X0501

    #undef  WINVER

    #define WINVER 0x0502

    #include <stdio.h>

    #include <string.h>

    #include <esent.h>

    #include <string>

    using namespace std;

    typedef struct {

       int errcode;

    string msg ;

    } errmap_t;

    void PrintErrorMsg(JET_ERR retcode, char *name);

    #define IMPLEMENT_MAP(code, msg) {code, #msg},

    #define END_MAP() {0, ""}

    errmap_t map[] = {

    IMPLEMENT_MAP(JET_wrnRemainingVersions, The version store is still active. This error is returned by the directory manager.)

    IMPLEMENT_MAP(JET_wrnNyi, The function is not yet implemented)

    IMPLEMENT_MAP(JET_errRfsNotArmed,  The Resource Failure Simulator has not been initialized.)

    IMPLEMENT_MAP(JET_errInvalidParameter, An invalid API parameter is being used.)

    IMPLEMENT_MAP(JET_errOutOfMemory, The system is out of memory.)

    IMPLEMENT_MAP(JET_errInitInProgress, The database engine is being initialized.)

    IMPLEMENT_MAP(JET_errLogFilePathInUse, The log file path is already being used by another database instance.)

    IMPLEMENT_MAP(JET_errTempPathInUse, The path to the temporary database is already being used by another database instance.)

    IMPLEMENT_MAP(JET_errDatabaseUnavailable, This database cannot be used because it encountered a fatal error.)

    IMPLEMENT_MAP(JET_errNotInitialized, The database engine has not been initialized.)

    IMPLEMENT_MAP(JET_errAlreadyInitialized, The database engine is already initialized.)

    IMPLEMENT_MAP(JET_errDatabaseCorrupted, There is a non-database file or corrupt database.)

    IMPLEMENT_MAP(JET_errInvalidDatabaseVersion, The database engine is incompatible with the database.)

    IMPLEMENT_MAP(JET_errPageSizeMismatch, The database page size does not match the engine.)

    IMPLEMENT_MAP(JET_errAttachedDatabaseMismatch, An outstanding database attachment has been detected at the start or end of the recovery but the database is missing or does not match attachment info.)

    IMPLEMENT_MAP(JET_errCatalogCorrupted, Corruption was detected in the catalog.)

    IMPLEMENT_MAP(JET_errPartiallyAttachedDB, The database is only partially attached and the attach operation cannot be completed.)

    IMPLEMENT_MAP(JET_errInvalidSettings, The system parameters were set improperly.)

    END_MAP()

    };

    int _tmain(int argc, _TCHAR* argv[])

    {

       JET_ERR errcode = 0;

       JET_INSTANCE instance;

    JET_SESID    sesid;

    JET_API_PTR  value;

    char         szbuff[1024] = {0};

    JET_DBID     dbid;

       // JET_PFNSTATUS status;

    // Database Parameters

       errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramDatabasePageSize, 8192, NULL);

    PrintErrorMsg(errcode, "JetSetSystemParameter");

       // create database instance;

       errcode = JetCreateInstance(&instance, "JetDatabaseInstance");

    PrintErrorMsg(errcode, "JetCreateInstance");

       // Transaction Log Parameters

       errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramLogFilePath, NULL, "D:\ntdsbackup\");

    PrintErrorMsg(errcode, "JetSetSystemParameter");

       // errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramBaseName, NULL, "edb");

       errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramSystemPath, NULL, "D:\ntdsbackup\");

    PrintErrorMsg(errcode, "JetSetSystemParameter");

    // I/O Parameters

       errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramAccessDeniedRetryPeriod, 1000, NULL);

    PrintErrorMsg(errcode, "JetSetSystemParameter");

       errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramCreatePathIfNotExist, 1, NULL);

    PrintErrorMsg(errcode, "JetSetSystemParameter");

    // Database Parameters

       // errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramDbExtensionSize, 256, NULL);

    // Database Cache Parameters

       // errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramCacheSize, 10, NULL);

    // Temporary Database Parameters

       errcode = JetSetSystemParameter(&instance, JET_sesidNil, JET_paramTempPath, NULL, "C:\WINDOWS\Temp");

    PrintErrorMsg(errcode, "JetSetSystemParameter");

       errcode = JetGetSystemParameter(instance, JET_sesidNil, JET_paramDatabasePageSize, &value, szbuff, 1024);

    PrintErrorMsg(errcode, "JetGetSystemParameter");

       errcode = JetInit(&instance);    

    PrintErrorMsg(errcode, "JetInit");

       errcode = JetBeginSession(instance, &sesid, 0, 0);

    PrintErrorMsg(errcode, "JetBeginSession");

       errcode = JetGetSystemParameter(instance, sesid, JET_paramDatabasePageSize, &value, szbuff, 1024);

    PrintErrorMsg(errcode, "JetGetSystemParameter");

    errcode = JetAttachDatabase(sesid, "D:\ntdsbackup\ntds.dit", 0);

    PrintErrorMsg(errcode, "JetAttachDatabase");

    errcode = JetOpenDatabase(sesid, "D:\ntdsbackup\ntds.dit", 0, &dbid, 0);

    PrintErrorMsg(errcode, "JetOpenDatabase");

    errcode = JetCloseDatabase(sesid, dbid, 0);

    PrintErrorMsg(errcode, "JetCloseDatabase");

    errcode = JetDetachDatabase(sesid, "D:\ntdsbackup\ntds.dit");

    PrintErrorMsg(errcode, "JetDetachDatabase");

    errcode = JetTerm(instance);

    PrintErrorMsg(errcode, "JetTerm");

    return 0;

    }

    void PrintErrorMsg(JET_ERR retcode, char *name)

    {   if(retcode ==0) return;

    for(int n = 0; n < sizeof(map) / sizeof(errmap_t); n++) {

    if(map[n].errcode == 0) break;

    if(retcode == map[n].errcode) {printf("%s : %d —– %sn", name, retcode, map[n].msg.c_str()); break;}

    }

    return;

    }

    I run above code, it still reports -1216 error.

    "An outstanding database attachment has been detected at the start or end of the recovery, but the database is missing or does not match attachment info."

  13. flatfish says:

    The error is returned in calling JetInit.

  14. flatfish says:

    There are a few files in directory D:\ntdsbackup\

    They are ntds.dit edb.chk edb.log edb00002.log.

  15. flatfish says:

    Hi laurionb:

           I had backuped all Active Directory files, including ntds.dit edb.chk edb.log edbxxxxx.log, temp.edb, res1.log res2.log to the directory "d:ntdsbackup".  Then I run above program. It still returns error -1216.

         Would you please give me extra help?

    Thank you very much…

  16. flatfish says:

    It seems that it fails because of failing of soft recovery in calling JetInit.

  17. flatfish says:

    I Attached an Active Directory Database. But I didn’t know that how many tables there are in it

    and the table’s name, id of each.

    Question:

           How did I get to the table’s name, id for above case?

    Thanks

  18. flatfish says:

    It seems that there isn’t revelant ESE API to traverse the table’s id and name etc.

  19. flatfish says:

    Are there ESE API to traverse all tables’name of Active Directory Database?

  20. LonTonG says:

    Mungkin ada benarnya juga kata pengantar di blog Windows SDK yang membahas tentang ESE , bahwa sedikit

  21. MSDNArchive says:

    LonTonG, I’m not very fluent in Bhasa.  Are you saying that a correction is needed in the preface of the ESE article?  

    –Karin

  22. flatfish says:

    It seemed that I found a workaround.

    The concrete way is that we can get all tables’name by microsoft tool esentutl.exe, then I can access each table with the table’s name just gotten.

  23. flatfish says:

    Why is there only few resource relevant to program with ESE API?

  24. flatfish says:

    I wanted to present Active Directory Objects logically from an offline Active Director Database.  Currently, I can mount and open the offline Active Directory Database. But I didn’t know any relation between their datas in the tables.

    Question:

    Can I present the Active Directory Objects with close logical relationship like the Active Directory Users and Computers console in Windows 2003 by ESE API?

    Any feedback will help. Thank you in advance.

  25. MSDNArchive says:

    Flatfish, you will have better luck getting your questions answered on the MSDN Developer Forum for General Windows Development Issues http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/threads/.  The SDK team is only a ship vehicle for this technology and can’t answer your questions.  Thousands of developers, including Microsoft MVP, technical support staff and Microsoft developers review and answer questions posted on the  Developer Forums.

  26. flatfish says:

    Thanks, KarinM.  I will try my luck in the Developer Forum.

    In Windows Server 2003, there isn’t ESE API used to get column’s name of a table. How do I get the column name of a table in Windows Server 2003 by ESE API, or any workaround?

  27. flatfish says:

    Which is the schema table in Active Directory Database? What is the schema table’s name?

    Any help?

  28. MSDNArchive says:

    Flatfish, please post your question on the MSDN Developer Forum for General Windows Development Issues http://social.msdn.microsoft.com/Forums/en-US/windowsgeneraldevelopmentissues/threads/. This is not the forum to ask development-related questions. Thanks!

  29. Hi… most US citizens see in the term Jet Blue the airline. Some others (like me) remember there was something

  30. jlu2000 says:

    Here is the scenario I like to do with ESENT:

    On a table, there is index on two colums:  

        PararnetId byte[8]   nuallable

        recId                byte[8] unique

    index on these two columns: idxParentToId

    To query a list of records in a database which belongs to same parented, do you know how to achieve this :

         JetSetCurrentIndex: idxParentToId

         JetMakeKey  -> key – {parentId} to search ?

     JetSeek

     JetSetIndexRange

    Then using JetMove to read each record.

    However, I am not able to do any seek on JetSeek, it always failed JET_wrnSeekNotEqual. Could anyone give me a sample how should I do this?

  31. jlu2000 says:

    JetOpenTable return with error = JET_errCallbackNotResolved, A callback function could not be found.

    How could I register callback if I am not able to open table, since register would require table id?

  32. Mithya says:

    How do I achieve sorting results by columns? CreateIndex seems just for faster retrieval.