You can use SQL Server as the persistence database for an MDriven application; this page helps MDriven Designer and MDrivenServer users find the SQL Server guidance that applies to their task.
SQL Server in MDriven
SQL Server is a persistence mapper frequently used with MDriven. A persistence mapper stores the objects defined in your model in database tables and reads them back when the application needs them.
For example, when your model contains a class named Customer, the SQL Server persistence mapper is responsible for persisting instances of that class in the SQL database used by the application.
For an overview of SQL and the database operations it supports, see Documentation:SQL. For information about the SQL database used by MDriven, see Documentation:SQL Database.
Choose the guidance for your task
| If you need to... | Read this page |
|---|---|
| Configure or troubleshoot a SQL Server connection where the certificate is not trusted | Documentation:SQL not trusted |
| Detect changes made directly in SQL Server by another system and invalidate affected objects in MDrivenServer | Documentation:SQL Server change tracking |
| Set precision, scale, or length for a class attribute stored through Transact-SQL | Documentation:Precision |
| Import reference or other data from an external SQL-based system through MDrivenServer | Training:Import data from other SQL servers |
| Work with MDrivenServer configuration, jobs, and database-management features | Documentation:Server |
| Resolve a failure when saving an ECO model to a SQL database script | Documentation:Problem saving database script |
Connection certificate errors
Newer SQL connection behavior can require certificate configuration even when a connection could previously be made without it. If the login fails with an error such as The certificate chain was issued by an authority that is not trusted, use the connection-string guidance in Documentation:SQL not trusted.
The required connection-string settings depend on whether you use integrated security or a SQL Server login. Do not copy an integrated-security setting into a connection that uses SQL Server credentials.
Changes made outside MDriven
MDrivenServer maintains its own dirty list for objects changed through MDriven. It does not automatically discover a direct update to an underlying SQL Server table made by a separate application or service.
When another system writes to the same database, enable and configure SQL Server change tracking only for the tables that can be changed externally. Documentation:SQL Server change tracking describes the database preparation, the SysSingleton attributes, and the MDrivenServer jobs used to identify changed primary keys and invalidate the corresponding objects.
For example, if a legacy service updates rows represented by Customer, configure tracking for the corresponding table and invalidate the changed Customer objects. Avoid enabling this mechanism indiscriminately on tables that no external system updates.
Implementation snapshot
A snapshot of the SQL Server persistence-mapper implementation is available in File:SQLServerPMapper.zip. This archive is intended for inspecting the implementation.
See also
- Documentation:SQL Database
- Documentation:SQL
- Documentation:SQL not trusted
- Documentation:SQL Server change tracking
- Documentation:Server
Schema mapping and reverse engineering
Schema mapping and reverse engineering
Persistence mapping maps model classes to database tables and class attributes to table columns. MDriven provides an abstraction layer that translates object-oriented constructs into SQL statements. SQL Server is one of MDriven's built-in persistence mappers.
Current and legacy naming conventions
The naming convention used by a persistence mapping affects the names of generated key fields. The supplied material describes the current convention as using the class name followed by ID for a primary key. For example, a class named Class1 has a primary-key field named Class1ID.
For foreign keys, the current convention uses association names. MDriven also provides alternate mappings for older database conventions. The documented legacy mapping uses EcoID for the primary key; the source notes that foreign-key naming differs between the conventions.
Work with an existing database
A database schema may not fully describe the intended model. For example, SQL joins may be used even when the database has no explicit primary-key or foreign-key definition for the relationship. As a result, reverse engineering can leave classes, attributes, or associations undiscovered.
To inspect and supplement an existing database mapping:
- In MDriven Designer, right-click the package and choose Functions, then Reverse Database.
- Select Analyze db to inspect the database schema.
- Use Reverse worker to inspect tables and columns.
- Mark selected columns for use as a primary key or attribute, or as a foreign key.
- Continue to Find possible actions in the Model to review suggested additions, such as a class or association.
If reverse engineering does not identify a required element, add the missing class, primary-key definition, or association manually. The reverse-engineering documentation describes this as necessary when the schema is incomplete or does not express the intended relationships.
When extending a reversed model with new classes or tables, consult the documentation for the administrative database tables required to fully support standard MDriven models.
Needs human verification
Before adding guidance on schema generation or evolution, verify it against documentation covering the SQL Server mapper and the relevant MDriven version. In particular, the supplied sources do not establish:
- generated SQL Server datatype rules, lengths, precision, or scale;
- generated indexes, constraints, or foreign-key creation behavior;
- inheritance-table strategies;
- mapping XML structure or mapping-builder configuration;
- schema comparison, migration, destructive-change, or rollback behavior;
- MDrivenServer schema-update behavior;
- SQL Server change tracking, cache invalidation, or performance recommendations.