Welcome, and thanks for joining.

In this video, I’ll show you how Data Architect will allow you to easily forward engineer data model changes to a database by automatically creating database ALTER scripts. This is vital to ensure that your database can be easily kept up-to-date with your ever-evolving data model. 

The data model on-screen is the physical model of my SQL Server AdventureWorks Database. In this model, I want to edit the product table so that it begins to capture ‘Product Color’. So I’ll add a new column ‘Color’ with data type ‘nvarchar’, ‘width 15’. Now let’s add this column directly under the ‘Size’ column. 

Let’s now push this change to the database by automatically creating an ALTER script with the Compare and Merge Utility. For a physical model, the Compare and Merge Utility will allow me to compare to a live database, a SQL File, or to another model. In this case, we’ll choose to compare to a database. We’ll log into the SQL Server that contains the AdventureWorks database. We want to make sure that we’re comparing to the right database and to the right owner. We want to also make sure we’re comparing tables and columns, and then we’ll want to choose what we want to compare. I can just choose to compare the product tables; however, the Compare and Merge Utility only shows differences in its results, so I’ll leave everything selected. 

Going to ‘Next’, the Compare and Merge Utility will kick off the comparison process, and we can see that there’s a difference in the product table, specifically the Color column does not exist in the database. These other columns are appearing only because their sequence order is now off since I placed the Color column in the middle of the table. Let’s go ahead and merge the Color column into the database. By clicking ‘next’, we’ll begin to create our ALTER script.

The ALTER script is created and loaded into Data Architect SQL Facility. You can see that this script is quite complex. The reason being, the Color column was added into the middle of the table. Therefore, we need to account for any dependencies that need to be updated with the Color column. 

This would take some time to write by hand, but Data Architect can do this in seconds. All we need to do now is connect to the database and execute the script. And with that, we’ve added the change to the product table within the AdventureWorks database.

How to Forward Engineer Data Model Changes in ER/Studio Data Architect

IDERA ER/Studio Data Architect is a powerful data modeling tool that enables companies to discover, document, and re-use data assets. With round-trip database support, data architects have the power to easily reverse-engineer, analyze, and optimize existing databases from diverse platforms. Comprehensive model management and collaboration capabilities simplify building and maintaining complex enterprise data models in the repository.

ER/Studio Data Architect provides unique capabilities including universal mappings, business data objects, and agile change management that help data professionals to map, describe, and audit their data models. With an extensive feature set, ER/Studio Data Architect offers superior data modeling for single- and multi-platform environments. Learn More →

Start a FREE Trial of ER/Studio Data Architect
Share This
Contact IDERA:

Join Our Global Community

Join our email list and receive the latest case studies, event updates, product news, and much more.