Export (0) Print
Expand All
Expand Minimize

What's new: Database changes [AX 2012]

Updated: October 23, 2013

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This topic provides information about changes in Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 R2 that affect databases.

This section provides information about new features and enhancements in AX 2012 that affect system administrators.

In AX 2012 R2, you must create your Microsoft Dynamics AX business and model store database on SQL Server. You cannot create the Microsoft Dynamics AX database on an Oracle database server. All other databases, such as the databases that are used for Microsoft SharePoint 2010 products and Microsoft SQL Server Reporting Services, also require SQL Server.

In the previous version of Microsoft Dynamics AX, the application files, or AOD files, that contained Microsoft Dynamics AX metadata were stored on a file share. In this version, application objects are stored in the model store database and are managed as model files.

AX 2012 R2 has two databases. The business database contains transaction and master data. The model store database contains application objects, which are managed as model files. This database replaces the application file store (AOD) in previous versions. You can name the business database. The model store database has the same name as the business database, but _model is appended. Both databases must be stored on the same instance of SQL Server.

ImportantImportant

Before AX 2012 R2, the model store was stored in the same database as business data. Starting in AX 2012 R2, the databases were separated.

This version of Microsoft Dynamics AX has a fixed schema. In other words, tables and fields are no longer dropped and then added again when license or configuration keys are changed. This change applies to all tables and fields, except some that are used during upgrade (SysDeletedObjectsXX). These tables and fields are dropped when the relevant configuration keys are disabled in a production environment. This change was made to better support Microsoft SQL Server Analysis Services cubes on Role Centers.

In this version, Microsoft Dynamics AX provides full-text search, which lets you search business data over a large volume of text data or documents. Full-text search also supports additional features, such as language-specific word breakers, stemmers, and extensible APIs. You can create a full-text index on tables that are associated with the Main or Group table type.

NoteNote

You must configure the functionality for SQL Server full-text search before you can use this functionality in Microsoft Dynamics AX. For more information, see Full-Text Search (SQL Server).

Like regular SQL Server indexes, full-text indexes can be updated automatically when data is modified in the associated tables. By default, this behavior is used for Microsoft Dynamics AX. In the Microsoft Dynamics AX Application Object Tree (AOT), developers can set the ChangeTracking property of a full-text index to either Auto or Manual. If ChangeTracking is set to Auto, SQL Server automatically updates the index when data is modified in the associated tables. If ChangeTracking is set to Manual, you must manually update the index at a specified interval. For more information, see Getting Started with Full-Text Search. By default, the ChangeTracking property is set to Auto when a new full-text index is created. The Auto setting provides optimal response time and throughput for your program.

A new type of temporary table that is created in the TempDB database is available in AX 2012. In the AOT properties for a table, the TableType property is now an enum. The InMemory enum value is the type of temporary table that was created on Microsoft Dynamics AX Application Object Server (AOS) or the client in earlier versions of Microsoft Dynamics AX. The TempDb enum value is the new type of temporary table that is created in the SQL Server TempDB database. TempDB temporary tables can be joined on the database tier with tables from the Microsoft Dynamics AX database. The use of TempDB temporary tables can improve performance and simplify the programming model. The domain account that you use for the Microsoft Dynamics AX service must have read and write access to the TempDB database.

NoteNote

SQL Server creates TempDB at startup. Therefore, you must assign permissions to TempDB every time that SQL Server starts or restarts.

The AlwaysOn availability groups feature in SQL Server is a high-availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. AlwaysOn availability groups were introduced in SQL Server 2012 and maximize the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases that fail over together. These databases are known as availability databases. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and some backup operations. For more information, see SQL Server topology recommendations for availability and performance.

This section provides information about implementation considerations for the new features in AX 2012.

Planning the database infrastructure and storage is a critical requirement for optimal Microsoft Dynamics AX performance. For more information, see Configure SQL Server and storage settings.

For information about how to upgrade from previous releases of Microsoft Dynamics AX, see Upgrade to Microsoft Dynamics AX 2012.

Database administration activities, such as database backup, recovery, and data recovery planning, must incorporate the following enhancements in AX 2012:

  • The business database, model store database, and baseline model store databases

  • Full-text search

  • Support for SQL Server 2012 AlwaysOn availability groups to increase availability

This section provides information about new features and enhancements in AX 2012 that affect developers.

Object-oriented programming languages, such as C# and C++, support inheritance relationships between classes. A derived class inherits fields and methods from its base class. AX 2012 supports similar inheritance between tables in the AOT.

Item

Description

Required

Yes

Feature areas affected

All, especially those feature areas that query tables that are involved in inheritance relationships

Stakeholders

Technical decision makers

Independent software vendors (ISVs)/developers

Partners

Table inheritance provides better support than traditional foreign key relationships for customized extensions.

Table inheritance provides a rich type of metadata that describes the relationships between tables. Tools can use this metadata to provide better functionality.

The following tables are some of the base tables that are involved in inheritance relationships:

  • AgreementHeader

  • AgreementLine

  • BankLC

  • BankLCLine

  • CaseDetailBase

  • CatProductReference

  • DirPartyTable

  • EcoResProduct

  • HRPDefaultLimit

  • IntercompanyActionPolicy

  • VendRequest

Because of inheritance relationships between tables, some fields in Microsoft Dynamics AX 2009 have been moved to different tables in AX 2012. Some legacy custom queries might have to be updated. This update requires the same amount of pre-upgrade work as any change to table schemas. In some cases, the size of an X++ SQL Select statement can decrease, and the statement can become simpler.

The application data framework has changed since AX 2009. AX 2012 includes changes to the following aspects:

  • AOT design time aspects

  • Query development aspects

What can you do?

AX 2009

AX 2012

Why is this important?

Capture rich metadata about the relationships between tables.

Foreign key relationships were supported. Inheritance relationships between tables could be managed only by custom code.

Inheritance relationships between tables can be captured and described by setting properties on the tables.

Developers can more fully express the relationships between certain entities.

What can you do?

AX 2009

AX 2012

Why is this important?

Access fields from various tables by using simpler code.

A query could take advantage of a foreign key relationship to obtain fields from a parent table by joining.

A query can take advantage of an inheritance relationship to obtain fields from a parent table without joining.

X++ SQL code and AOT queries take less developer time to create and maintain.

In AX 2009, table relations could be defined through extended data types (EDTs). In AX 2012, table relations are defined under the Relations node for each table in the AOT. Each relation has properties that can be set in the Properties window.

Item

Description

Required

Yes

Feature areas affected

All

Stakeholders

Technical decision makers

ISVs/developers

Partners

The new format enables rich metadata to be stored about each table relation. The metadata can be used throughout the application to increase functionality.

Examples

  • In AX 2012, the system stores the cardinality of each relationship. Tools that generate diagrams of database entity relationships in Microsoft Visio 2010 can read the cardinality data to draw the diagram correctly.

  • The new UnitOfWork class simplifies transaction management for table relations that are defined under the Relations node for a table. However, the UnitOfWork class cannot take advantage of relations that are defined through EDTs.

  • In AX 2012, table relations can no longer be created through EDTs.

  • Legacy custom table relations that were defined through EDTs continue to work in AX 2012.

Table relations are defined in a new way in AX 2012.

What can you do?

AX 2009

AX 2012

Why is this important?

Define a table relation by using the AOT.

Table relations were defined only through EDTs. This limitation was a problem if multiple relations were required between tables, or if relations involved composite keys.

EDT table relations are recognized, but relations are now defined under the Relations node for an individual table.

Relationship metadata can be stored. This feature supports other system features and future growth.

Automatically migrate table relations to the AOT.

Not available

A tool is provided that converts legacy table relations from EDT nodes to table nodes in the AOT.

This automation can save the developer time and reduce the chance of errors.

For more information about table relations, see the EDT Relations Migration Tool topic on MSDN.

Sometimes, deactivating a configuration key affects a table in Microsoft Dynamics AX. In AX 2009 and earlier versions, the associated table was removed from the underlying SQL Server database. When queries were run on that table later, messages were displayed.

Item

Description

Required

Yes

Feature areas affected

All, especially operations that issue SQL queries from outside Microsoft Dynamics AX

Stakeholders

Technical decision makers

ISVs/developers

Partners

Tables that are deactivated by a configuration key now remain available to external SQL queries. This functionality can provide a practical benefit, depending on the ongoing data changes that must be made to the tables.

System administrators who deactivate a configuration key must inform users who issue external SQL queries that the data in the deactivated tables will no longer be updated, and that the data in those tables will become outdated. After a configuration key has been deactivated, messages are no longer displayed for external SQL queries.

The effects that deactivated configuration keys have on tables have changed since AX 2009. AX 2012 includes changes to the following aspects:

  • SQL queries on inactive tables

What can you do?

AX 2009

AX 2012

Why is this important?

Run queries on inactive tables in Microsoft Dynamics AX.

Queries failed, and messages were displayed.

Queries on inactive tables continue to function, but data in the inactive tables is ignored.

Queries on the tables still function.

Run external queries on tables from Microsoft Dynamics AX.

Queries failed, and messages were displayed.

Queries on inactive tables continue to function, but data from the inactive tables is outdated.

Queries on the SQL Server database still function.


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft