Overview

In this lab, you create database servers running on Compute Engine. You create both Linux and Windows servers and use the CLI to automate the creation of a server.

Objectives

In this lab, you learn how to perform the following tasks:

• Create a MySQL database on Linux.
• Create a SQL Server database on Windows.
• Automate server creation using the Google Cloud SDK.

Setup

In this task, you use Qwiklabs and perform initialization steps for your lab.

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

1. Sign in to Qwiklabs using an incognito window.

2. Note the lab's access time (for example, 1:15:00), and make sure you can finish within that time.
   There is no pause feature. You can restart if needed, but you have to start at the beginning.

3. When ready, click Start lab.

4. Note your lab credentials (Username and Password). You will use them to sign in to the Google Cloud Console.

5. Click Open Google Console.

6. Click Use another account and copy/paste credentials for this lab into the prompts.
   If you use other credentials, you'll receive errors or incur charges.

7. Accept the terms and skip the recovery resource page.

Task 1. Create a MySQL database on Linux

1. Open a new web browser window and navigate to the Google Cloud Console (console.cloud.google.com). Use the project selector to choose the first project with a leading name of 'qwiklabs-gcp.'

2. On the Navigation menu (), click Compute Engine > VM instances.

3. To create a new instance, click Create Instance.

4. On the Machine configuration page, specify the following values:

   Field	Value
   Name	mysql-db
   Region
   Zone

5. Click OS and storage.

   Click Change to begin configuring your boot disk and select the values for:

   • Operating system: Debian
   • Version: Debian GNU/Linux 12 (bookworm)

   Leave the size as default and Click on Select.

6. Accept the remaining defaults, and then click Create.

7. When the VM is ready, click SSH.
   

   This will log you in to the server in a new tab.

   

8. In the terminal window, to update the packages and install MySQL using the apt-get package manager, enter the following command:

   sudo apt-get update sudo apt-get install -y default-mysql-server

9. To secure the database, enter the following command:

   sudo mysql_secure_installation

10. When you are prompted for the current password for root, press Enter (Return) for none.

11. When you are prompted for Switch to unix_socket authentication [Y/n], press N.

12. When you are prompted for Change the root password? [Y/n], press Y.

    Enter something you will remember, because you will need it later.

13. When you are prompted for Remove anonymous users? [Y/n], press N.

14. When you are prompted for Disallow root login remotely? [Y/n], press N.

15. When you are prompted for Remove test database and access to it? [Y/n], press N.

16. When you are prompted for Reload privilege tables now? [Y/n], press Y.

17. Now log in to the database. Enter the following command, and then enter your password when prompted:

    sudo mysql -u root -p

18. At the MariaDB [(none)]> prompt, to see the current databases, enter the following command:

    SHOW databases;

19. To create a new database, enter the following command:

    CREATE database petsdb;

20. Switch to that database:

    USE petsdb;

21. Create a table:

    CREATE TABLE pets ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), breed VARCHAR(255) );

22. Add a record:

    INSERT INTO pets (name, breed) VALUES ('Noir', 'Schnoodle');

23. Confirm that your record was added:

    SELECT * FROM pets;

24. Type exit to log out of the database.

25. Type exit again to close the SSH session.

Review

You just created a MySQL database server using Debian Linux.

Click Check my progress to verify the objective. Create a MySQL database on Linux

Task 2. Create a SQL Server database on Windows

1. Return to the Google Cloud Console and the Compute Engine service.

2. To create a new instance, click Create Instance.

3. On the Machine configuration page, specify the following values:

   Field	Value
   Name	sql-server-db
   Region
   Zone
   Series	E2
   Machine Type	e2-standard-4

4. Click OS and storage.

   Click Change to begin configuring your boot disk and select the following values:

   • Operating system: SQL Server on Windows Server
   • Version: SQL Server 2019 Web on Windows Server 2019 Datacenter

   Leave the size as default and Click on Select.

5. Accept the remaining defaults, and then click Create.

6. When the Windows server is ready, click the dropdown arrow in the Connect column, and then select Set Windows password.

   

   Note: If you get to this point too quickly, the Windows server may not be ready, even though a green check in the console indicates that it is. Wait a couple of minutes and try again.

7. Leave the Username as it is (the generated username is a portion of your Qwiklabs username), and then click Set.

8. Copy and paste the generated password into a text file so you don't lose it, and then click Close.

   You need to have an RDP client to log on to the Windows machine.

9. Do one of the following:

   • If you are using Windows, you already have RDP:
     • Click the dropdown arrow in the Connect column, and then select Download the RDP file.
     • When the download is complete, double-click the file and then log in with your username and the password you copied.
   • If you are using a Mac:
     • Install the Microsoft Remote Desktop client from the Apple App Store (you may already have it installed).
     • After you install the client, download the RDP file, and then log in with your username and the password you copied.

10. When you are logged on to the Windows server, close the Server Manager Dashboard.

11. On the Start menu, type SSMS, then right click the SQL Server Management Studio shortcut, and choose Run as administrator to run the program.

12. On the Popup window choose Yes when prompted Do you want to allow this app to make changes to your device?.

13. In the Connect to Server dialog, the server name should be SQL-SERVER-DB.

14. Check the Trust server certificate and click Connect.

15. In Management Studio, in the Object Explorer, right-click SQL-SERVER-DB, and select Properties.

16. In the Server Properties dialog, select the Security page.

17. For Server Authentication, select SQL Server and Windows Authentication mode, and then click OK.

18. Read the warning message and click OK again.

19. In Object Explorer, right-click the SQL-SERVER-DB server, and select Restart.

20. Click Yes when prompted. If the Restart command is disabled, click the Start menu and reboot the server. Give it a minute or two and then restart your RDP session.

21. When you're logged in, return to SQL Server Management Studio and Run as administrator and connect to the database server.

22. Expand the Security and Logins branch of the tree view. Right-click on the Logins branch, and select New Login.

23. For Login name, enter your first name or another unique value.

24. Select SQL Server authentication.

25. Enter a password you will remember (paste it in a text file if you like).

26. Clear Enforce password policy.

    a. Click the Server roles page, and select the sysadmin role.

    b. Click the User Mapping page, and select all the databases.

    c. Click OK.

27. To test the login you just created, in Object Explorer, click Connect, and then select Database Engine.

28. For Authentication, select SQL Server Authentication.

29. Enter your login and password, then check Trust server certificate. Click Connect.

30. In the toolbar, click New Query.

31. To create a database, enter the following command, and then click Execute:

    CREATE DATABASE petsdb;

32. In the databases dropdown to the left of the Execute button, select your petsdb database.

33. To create a table, enter the following command, and then click Execute:

    USE petsdb; CREATE TABLE pets ( id INT PRIMARY KEY IDENTITY (1, 1), name VARCHAR (MAX), breed VARCHAR (MAX) );

34. To add a record:

    INSERT INTO pets (name, breed) VALUES ('Noir', 'Schnoodle');

35. To confirm that it worked:

    SELECT * FROM pets;

36. Close your RDP connection.

Review

You just created a SQL Server database server running on Windows.

Click Check my progress to verify the objective. Create a SQL Server database on Windows

Task 3. Automate server creation using the Google Cloud SDK

1. Return to the Google Cloud Console and the Compute Engine service.

2. To create a new instance, click CREATE INSTANCE.

3. On the Machine configuration page, specify the following values:

   Field	Value
   Name	db-server
   Region
   Zone

4. Click OS and storage.

   Click Change to begin configuring your boot disk and select the following values:

   • Version: Debian GNU/Linux 12 (bookworm)

   Leave the size as default and Click on Select.

5. Click Observability

   • Uncheck the Install Ops Agent for Monitoring and Logging under Ops Agent (Recommended).

6. Click Advanced then in Automation - Startup script paste the following:

   #! /bin/bash sudo apt-get update sudo apt-get install -y default-mysql-server

7. Scroll to the bottom, but do not click the Create button; instead click the Equivalent Code button on the top right and then navigate to the Command Line pane on the right of the screen.

   This pane shows you the gcloud CLI command you can use to automate this server's creation as you configured it. Next, make a small change to this command.

8. Click Copy at the bottom of the pane.

9. Cancel the Create an instance wizard.

10. In Notepad or similar text editor, paste the copied code.

11. If present, from the list of commands remove the following parameter: --provisioning-model=STANDARD

    Note: Please be careful to only delete the provisioning-model parameter.

12. Select the entire edited command and copy it to the clipboard.

13. Launch the Cloud Shell in the top right section of the Cloud Console page, paste the command, and press Return/Enter to create the VM.

    If prompted, click Authorize.

14. Press Return (Enter) to allow for an empty passphrase.

15. Please wait for 3 to 5 minutes for the startup script to run. Then in the Cloud Shell terminal, enter the following to log in to the virtual machine:

    gcloud compute ssh db-server --zone={{{project_0.default_zone | ZONE}}}

16. If asked: Do you want to continue(Y/n)?, Enter Y. Accept all defaults for setting up secure communications.

17. To see whether your database server is running, enter the following command:

    sudo systemctl status mysql

    The output on the console should contain a row similar to the following:

    Aug 17 14:32:10 db-server systemd[1]: Started MariaDB 10.5.15 database server.

18. Press Q to exit the status screen.

Click Check my progress to verify the objective. Automate server creation using the Google Cloud SDK

Congratulations!

You created database servers running on Compute Engine. You created both Linux and Windows servers and have used the CLI to automate the creation of a server.

End your lab

When you have completed your lab, click End Lab. Google Cloud Skills Boost removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.