Exercise: Upgrade a database
In your law firm, you need to upgrade several CRM databases to SQL Server 2019. These databases include versions from SQL Server 2008 R2 through to SQL Server 2016. To save time, you want to migrate multiple databases in parallel, and migrate database logins. You'll use the Microsoft Data Migration Assistant (DMA) to help with these upgrades.
In this exercise, you'll upgrade a database from SQL Server 2008 R2, to SQL Server 2019. After the database has been migrated, you'll export the results as a CSV report.
Migrate a database
To use the DMA to migrate the database, follow these steps:
On the taskbar, select File Explorer.
On the left, select Windows (C:), right-select in the main pane, select New, and then select Folder.
Rename the new folder temp.
On the taskbar, select Data Migration Assistant.
On the left, select +.
In the New flyout, under Project type, select Migration and then, in the Project name box, type AdventureWorks 2008-2019.
Leave the Source server type as SQL Server, in the Target server type list, select SQL Server, and then select Create.
On the Specify source & target tab, under Source server details, in the Server name box, type localhost.
In the Authentication type list, ensure Windows Authentication is selected, and that Encrypt connection is clear.
Under Target server details, in the Server name box, type localhost.
In the Authentication type list, ensure Windows Authentication is selected, and that Encrypt connection is clear, and then select Next.
On the Add databases tab, under Source server, ensure only AdventureWorks2008R2 is selected.
In the Shared location accessible by source and target servers for backup operation box, type C:\temp, and then select Next.
Note that migrating a database on the same server, to the same location, causes an error.
In the Provide target database name box, type
In the Restore options section, change the Data path for Restore as on target server to:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AWMigrate2019.mdf
Change the Log path for Restore as on target server to:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AWMigrate2019_log.LDF
Select Next, on the Select logins tab, ensure no logins are selected, and then select Start Migration.
Export the results
Next, export the results to a CSV text file:
- When the migration completes, on the View results tab, select Export report.
- In the Save migration report dialog box, change the location to the Desktop.
- In the File name box, type
AdventureWorks 2008-2019 Upgrade.
- In the Save as type list, select CSV Files (*.csv), and then select Save.
- Minimize the Data Migration Assistant window.
- On the Desktop, double-click the AdventureWorks 2008-2019 Upgrade.csv file.
- In Notepad, scroll through the results of the export. Notice that the report includes a list of databases you chose to migrate. Close Notepad.
Check the current compatibility level
Before you make the upgrade, find out the original compatibility level for the database:
- Select Start, type
SSMS, and then press Enter.
- In the Connect to Server dialog box, select Connect.
- In Object Explorer, expand Databases. You should now see the migrated AWMigrate2019 database. Right-click AWMigrate2019, and then select Properties.
- In the Database Properties - AWMigrate2019 window, under Select a page, select Options.
- The Compatibility level should be set to SQL Server 2008 (100), and then select Cancel.
Update the compatibility level
Now you can upgrade the compatibility level for the Adventure Works database:
In Object Explorer, right-click AWMigrate2019, and then select New Query.
In the query window, type the following code, and then select Execute:
ALTER DATABASE AWMigrate2019 SET COMPATIBILITY_LEVEL = 150
In Object Explorer, right-click AWMigrate2019, and then select Properties.
In the Database Properties - AWMigrate2019 window, under Select a page, select Options.
The Compatibility level should now be set to SQL Server 2019 (150). Select Cancel.
Close SSMS, without saving any changes.