What's new: Database changes

Important

This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.

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.

Database changes that affect system administrators

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

Microsoft SQL Server–based databases

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.

Application files are now stored in the model store database

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.

Important

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.

Fixed schema

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.

Full-text search

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.

Note

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.

TempDB temporary tables

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.

Note

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

Support for SQL Server 2012 AlwaysOn availability groups

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.

Implementation considerations for new features

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

Planning and architecture considerations

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.

Upgrade considerations

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

Administration changes

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

Database enhancements that affect developers

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

Table inheritance

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

New functionality

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.

Special considerations

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.

Comparison with AX 2009

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

• AOT design time aspects

• Query development aspects

AOT design time 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.

Query development aspects

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.

Table relations

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

New functionality

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.

Special considerations

• 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.

Comparison with AX 2009

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

AOT table relations

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.

More information

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

Inactive tables remain

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

New functionality

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.

Special considerations

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.

Comparison with AX 2009

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

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.