Exercise - Install the Database Experimentation Assistant

  • 3 minutes

It's easy to install the Database Experimentation Assistant (DEA), and it can then be rerun at any time.

You're upgrading several CRM databases to SQL Server 2019. These databases include versions from SQL Server 2008 R2 through to SQL Server 2016. The databases are critical to your organization's business, so you must identify any potential performance issues, before the upgraded databases go live into production. You've chosen to use the DEA to help with this task. First, you need to install DEA.

In this exercise, you'll install the DEA on the virtual machine you created earlier.

Note

Before beginning this exercise, you must complete the Prepare for the exercises steps in the unit Decide if DEA is right for your task.

Download the RDP connection file from Azure

  1. Sign in to the Azure portal using the same account with which you activated the sandbox.
  2. In the portal menu, select Virtual Machines.
  3. In the list, select SQL2019Server.
  4. On the Overview page, at the top, select Connect.
  5. In the drop-down, select RDP.
  6. Select Download RDP File then, when it's downloaded, open the file.

Connect to the SQL Server

  1. In the Remote Desktop Connection dialog box, select Connect.
  2. In the Windows Security dialog box, select More choices, and then select Use a different account.
  3. Sign in with the username azureadmin, and the admin password you used when you created the original virtual machine.

    Note

    You can use the echo $PASSWORD command in the Cloud Shell to show you the password.

  4. In the Remote Desktop Connection dialog box, select Yes to continue.
  5. You should be logged on to the VM.
  6. In the Server Manager window on the left, select Local Server.
  7. Select On next to IE Enhanced Security Configuration.
  8. In the Internet Explorer Enhanced Security Configuration window, select Off for both Administrators and Users.
  9. Select OK.

Download and restore your company's databases and workloads

  1. On the taskbar, select Internet Explorer.

  2. Go to https://github.com/microsoft/sql-server-samples/releases/download/adventureworks2008r2/adventure-works-2008r2-oltp.bak.

  3. On the pop-up, select Save As, select the Windows (C:) drive on the left, and then select Save.

  4. Go to https://github.com/MicrosoftDocs/mslearn-test-optimize-sql-server-databases-using-dea/blob/master/create-workload-on-adventureworks.sql. On the menu, right-click the Raw button, and select Save target as...

  5. In the Save As window, select Save.

  6. On the Start menu, type cmd, and select Command Prompt.

  7. Execute this command to restore the downloaded backup:

    SqlCmd -E -S SQL2019Server –Q "RESTORE DATABASE [AdventureWorks2008R2] FROM  DISK = N'C:\adventure-works-2008r2-oltp.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2008R2_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008r2.mdf',  MOVE N'AdventureWorks2008R2_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2008R2_log.LDF'"

Download the latest sqlpackage.exe component required for the DEA

  1. On the taskbar, select Internet Explorer.
  2. Go to https://github.com/MicrosoftDocs/mslearn-test-optimize-sql-server-databases-using-dea/blob/master/DacFramework.msi.
  3. Select Download.
  4. In the pop-up, select Run.
  5. On the Microsoft SQL Server Data-Tier Application Framework (x64) window, select Next.
  6. Read the end-user license agreement, if you agree, select I accept the terms in the License Agreement. Then select Next.
  7. Select Install, then select Finish.

Install the Database Experimentation Assistant

  1. Go to https://github.com/MicrosoftDocs/mslearn-test-optimize-sql-server-databases-using-dea/blob/master/DatabaseExperimentationAssistantV2.6.exe.
  2. Select Download.
  3. In the pop-up, select Run.
  4. On the screen, you'll see Microsoft Software License Terms. Read the terms and, if you agree, select I agree to the license terms and conditions. Then select Install.
  5. When the Setup Options screen appears, select OK.
  6. When you see the message Installation Successfully Completed, select Close.
  7. Select Start, type Database Experimentation Assistant, and then select it to open the application.
  8. Using File Explorer, create a new folder on the C: drive, C:\capture.
  9. Using File Explorer, create a new folder on the C: drive, C:\replay.