20533 Module 7 Lab Answer Key

20533 Module 7 Lab Answer Key

Lab: Planning and Implementing Data Services

Scenario

Managers at A. Datum are planning to migrate some of the company�s application databases to the cloud.

To achieve this goal, you plan to use Microsoft Azure SQL Database. You have been asked to test SQL Database by creating a new database of A. Datum servers and by migrating sample data from the A. Datum customer relationship management system. Managers have asked you to investigate how SQL Database will support an existing custom application used with A. Datum, as well as disaster recovery features.

Exercise 1: Creating, Securing, and Monitoring an Azure SQL Database

u  Task 1: Create a SQL Database

1.      Ensure that the 20533B-MIA-CL1 virtual machines is both running, and then log on to 20533B-MIA-CL1 as Studentwith the password Pa$$w0rd.

2.      Start Internet Explorer, browse to http://portal.azure.com 

3.      Sign in using the Microsoft account that is associated with your Azure subscription.

4.      In the Hub menu on the left, click New, and then click SQL Database.

5.      In the SQL database blade, in the NAME box, type operations.

6.      Click SELECT SOURCE, and then in the Select source blade, click Blank Database.

7.      Click PRICING TIER, and in the Recommended pricing tiers blade, click BROWSE ALL PRICING TIERS.

8.      In the Change your pricing tier toblade, click S1 Standard, and then click Select.

9.      In the Recommended pricing tiers blade, click Select.

10.   Click SERVER, and then in the Server blade, click Create a new server.

11.   In the New server blade, enter the following settings and click OK:

�        SERVER NAME: any valid unique name

�        SERVER ADMIN LOGIN: Student

�        PASSWORD: Pa$$w0rd

�        CONFIRM PASSWORD: Pa$$w0rd

�        LOCATION: closest region to your location

12.   In the SQL database blade, click RESOURCE GROUP, and then in the Resource group blade, click Create a new resource group.

13.   In the Resource group blade, in the NAME box, type OpsRG, and click OK.

14.   In the SQL database blade, ensure that Add to Startboard is selected and click Create. Then wait for the SQL Database to be created.

15.   If a Message from webpage dialog box appears, click OK, and then wait until the operations database becomes online.

u  Task 2: Configure Server Firewall Rules

1.      In Internet Explorer, open a new tab with the classic portal (manage.windowsazure.com) .

2.      In the service pane on the left, click SQL DATABASES and verify that the operations database you created in the new portal is listed.

3.      On the sql databases page, click SERVERS, and verify that the uniquely named server you created in the previous task is listed.

4.      Click the server name, and then click CONFIGURE.

5.      Note the CURRENT CLIENT IP ADDRESS, and click the ADD TO THE ALLOWED IP ADDRESSES icon. Change the START IP ADDRESS to XXX.XXX.0.0, and the END IP ADDRESS to XXX.XXX.255.255, leaving XXX as it is (where XXX.XXX is the first two fields of the Current Client IP address), and then at the bottom of the page, click SAVE.

u  Task 3: Use SQL Server Management Studio

1.      Minimize Internet Explorer, and start SQL Server Management Studio.

2.      In the Connect to Server dialog box, specify the following settings (replacing server_name with the unique name you specified when creating your SQL Database server) and click Connect:

�        Server type: Database Engine

�        Server name: server_name.database.windows.net

�        Authentication: SQL Server Authentication

�        Login: Student

�        Password: Pa$$w0rd

3.      In SQL Server Management Studio, in Object Explorer, under the server name, expand Databases and verify that the operations database is listed.

4.      In SQL Server Management Studio, open the Operations.sql file in the D:LabfilesLab07Starter folder and view the Transact-SQL code it contains.

5.      On the toolbar, in the Available Databases list, select operations. Then click Execute.

6.      Click New Query and enter the following Transact-SQL code in the new query pane:

SELECT * FROM dbo.serverlist;

7.      On the toolbar, in the Available Databases list, ensure that operations is selected. Then click Execute.

8.      View the query results and verify that a list of three servers and their IP addresses is returned.

9.      Keep SQL Server Management Studio open for the next exercise.

u  Task 4: View Database Metrics

1.      In Internet Explorer, on the tab containing the preview Azure portal, in the Startboard, click the tile for the operations SQL Database (which was pinned to the Startboard when you created it).

2.      On the operations SQL Database blade, note the charts displayed in the Monitoring section, which show details of connections and storage space used.

3.      Click the Storage chart. Then in the Metric blade, view the chart.

4.      On the Metric blade, click ADD ALERT. Then in the Add an alert rule blade, specify the following settings and click OK:

�        RESOURCE: operations

�        NAME: operations storage alert

�        DESCRIPTION: storage alert for operations database

�        METRIC: total database size

�        CONDITION: greater than

�