Exercise - Restore AdventureWorks2019 database in an Azure Arc-enabled SQL Managed Instance

In this exercise, you restore and query AdventureWorks2019 sample database to familiarize yourself with your newly deployed Azure Arc-enabled SQL Managed Instance.

Exercise 1: Restore AdventureWorks2019

1. Download AdventureWorks2019 sample database backup AdventureWorks2019.

2. Use the kubectl cp command to copy the downloaded backup file to your backup drive, which is attached to your Arc-enabled SQL Managed Instance.

3. Open Azure Data Studio.

4. Expand Connections.

5. Expand Azure Arc Controllers.

6. Expand your Arc data controller.

7. Right-click on your Arc-enabled SQL Managed Instance and select Manage.

8. The external endpoint is what you connect to your Arc-enabled SQL Managed Instance. Copy it and open either SQL Server Manage Studio or Azure Data Studio. The primary external endpoint is available from Azure Data Studio as shown, or by using the az sql mi-arc list command.

9. Connect to your external endpoint and name accordingly.

10. Open a new query window to perform your database restore.

11. Run the Restore Filelistonly command to confirm you have access to the AdventureWorks2019 backup file. In this example, we restore from locally attached storage.

12. Run the Restore Database command to restore to your Arc-enabled SQL Managed Instance.

    

13. Refresh your database list in the Connections tab for your Arc-enabled SQL Managed Instance connection.

Explore AdventureWorks2019

Whether you deploy your Arc-enabled SQL Managed Instance to Azure, on-premises, or any public cloud, you can access it in the same way as any other instance of SQL Server. Using your preferred tools like SQL Server Management Studio or Azure Data Studio, you explore the AdventureWorks2019 database we restored in the previous exercise.

1. Right-click on AdventureWorks2019 in your database listing and select New Query.

2. Run the following statement to list all schemas and tables in the AdventureWorks2019 database:

   Select S.Name as 'SchemaName',
           T.Name as 'TableName'
   From sys.schemas as S
       Inner Join sys.tables as T on T.schema_id = S.schema_id
   Order By S.Name, T.Name;
   

   

3. Run the following statement to retrieve all order details in the AdventureWorks2019 database:

   Select SUM(OrderQty) SumOfOrderQty, P.Name, SOH.OrderDate
   From Sales.SalesOrderHeader as SOH
       Inner Join Sales.SalesOrderDetail As SOD ON SOH.SalesOrderID = SOD.SalesOrderID
       Inner Join Production.Product as P on SOD.ProductID = P.ProductID
   Group By P.Name, SOH.OrderDate
   Order By SOH.OrderDate Desc;