DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Data Migration Assistant Custom Configuration

Data Migration Assistant Custom Configuration

If you use Microsoft's Data Migration Assistant tool, you might be interested to know that there are config files you can browse. See how you can tinker with them.

Thomas LaRock user avatar by
Thomas LaRock
·
Feb. 13, 18 · Tutorial
Like (3)
Save
Tweet
Share
5.20K Views

Join the DZone community and get the full member experience.

Join For Free

The Data Migration Assistant (DMA) is a great tool made available by Microsoft. Successor to the SQL Server Upgrade Advisor, the DMA will perform an assessment of your database against a target version. The DMA can also perform the migration of both schema and data, if desired.

The other day, I wanted to run DMA from a command line. When I went to the install directory, I noticed that there were some .config files available:

Data Migration Assistant configuration file

Yes, very interested. So, I did what anyone else would do, I opened the file to have a look. The file was set to use Visual Studio Code by default, so opening the file was easily done. Once opened, I found what I expected, an XML file with configuration settings for DMA.

I scrolled through the file looking for interesting pieces of information. I could write a whole series of posts on what that file contains, but today, I will keep it short. We will focus on one item: stretch database recommendations.

The file contains this line:

<!-- Tables are eligible to stretch only if the number of rows 
is equal or greater than the recommendedNumberOfRows treshold --> 

<stretchDBAdvisor useSimulator="false" timeBetweenIssues="0.00:00:00.10" 
timeBetweenTables="0:00:00:00.10" recommendedNumberOfRows="100000" />

So, the DMA will recommend a table as a stretch database candidate for if the number of rows is equal to or greater than 100,000. We could debate all day if that number is the correct number, but the DMA needs to have a starting point. 100,000 isn’t a bad place to start. But here’s where things get interesting.

If your shop has specific requirements, you can edit these config files to match your requirements. For example, maybe you want a minimum number of rows to be 1,000,000. You can edit the config file and run your assessment. Let’s take a look.

Check out what the assessment looks like against a copy of GalacticWorks with a target of SQL 2017:

Image title

You can see that we have 2 results returned, and I highlighted the row count and size of the table. Now, we will modify the config file. I will make the default row number to be 10,000, because I want to show you that the number of recommendations will increase. So, same source database, the only change being made here is the configuration of DMA. Here’s what the assessment looks like after the change:

Image title

Notice that the first image returns a total of 2 objects, one “High value” and one “Medium value” as candidates for stretch. After modifying the config file, we get see a total of 19 objects. I have highlighted the 2MB sized Sales.Customer table and the 19,820 rows it contains.

This is just an example to show that modifying the config file changed how the DMA worked. I am not recommending that you stretch such small tables, I just wanted to show you a quick test. Here’s a handful of other items inside the config file that may be of interest to you:

  • BCP argument defaults (both BCP in and out)

  • Database collation settings

  • Scripting options

Go download the Data Migration Assistant and have a look for yourself.

The Data Migration Assistant is a great tool to help you evaluate and migrate your database to newer versions of SQL Server, including Azure SQL Database. The DMA is also customizable to a certain degree, and can be run from a command line. With a handful of lines in PowerShell you could run assessments against a large number of databases in a short period of time.

(If you liked this post, you’ll love our session at SQL Konferenz later this month. We are going to walk you through the entire migration process, helping you to understand how to avoid the common pitfalls that affect many migration projects.)

Migration Assistant (Apple) Data migration Database Data (computing) Assistant (by Speaktoit) sql

Published at DZone with permission of Thomas LaRock, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • The Role of Data Governance in Data Strategy: Part II
  • Why It Is Important To Have an Ownership as a DevOps Engineer
  • API Design Patterns Review
  • How to Quickly Build an Audio Editor With UI

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: