In this section, you will use the Data Migration Assistant to see what you should expect for your databases when you migrate them to SQL Server 2017 or Azure SQL Database. This is a standalone program and you can run it by executing the Dma.exe file in the default install directory C:Program FilesMicrosoft Data Migration Assistant. When you start the tool, you should see an intro screen, as shown in the following screenshot:
To start a project, you need to click on the + symbol and the new project form appears. On the new project screen, choose Assessment as Project type, type AdvWorksStretchDB in the Project name field, and choose SQL Server as Target server type (Source server type is preselected to SQL Server), as displayed here:
After you are done, click on the Create button and you will see the next screen, similar to the one shown in the following screenshot:
On this screen, you can choose the target SQL Server version. In the dropdown, all versions from 2012 are available; you should, of course, choose SQL Server 2017 on Windows. In addition to this, you can select the report type. This time you will choose New features' recommendation since you want to see which new features Data Migration Assistant recommends to you and not potential compatibility issues.
You then need to click on the Next button, connect to a SQL Server 2014 instance, and select the desired databases. In this example, I have selected the Microsoft former standard sample databases AdventureWorks and AdventureWorksDW that I have restored. If you don't have these two databases, you can choose any other database, but you will most probably end up with different results.
After you have established the connection with the SQL Server 2014 instance, you need to choose the databases that should be analyzed by Data Migration Assistant. As mentioned, choose the AdventureWorks2014 and AdventureWorksDW2014databases. You should see a screen like the following one:
When you click the Add button, the selected databases are added to the sources collection. You'll get another screen, where you can start the assessment by clicking on the Start Assessment button:
The analysis takes less than a minute, and the next figure shows its results for the AdventureWorks2014 database:
On the left radio-buttons, you need to choose the Feature recommendations option. Stretch DB-related recommendations are located under the Storage tab. In the case of the AdventureWorks2014 database, two tables are listed as tables that would benefit from using the Stretch DB feature: Sales.SalesOrderDetail and Production.TransactionHistory.
However, both of them have properties that prevent the use of the Stretch DB, so you can conclude that this feature is irrelevant for the AdventureWorks2014 database. The result of the analysis for the AdventureWorksDW2014 looks a bit better, as shown in the following screenshot:
Data Migration Assistant has found one table (dbo.FactProductInventory) that is ready to use the Stretch DB feature. It does not mention the other tables in the report—just three tables from two selected databases. At this point, I need to mention again that although this sample databases have a few very simple tables, with just a few rows, even for them you cannot use the Stretch DB feature. In data warehouse databases, tables seem to be more stretch-friendly, but according the tool, it is very hard to find a table that qualifies for stretching.
Stretch Database will give you benefits with your data warehouse databases, especially with historical data that is taking up space and is rarely used. On the other hand, this feature might not be eligible for your OLTP system due to the table limitations that your OLTP system has. Now, it is finally time to see in detail what these limitations are.