Overview

In this lab, you create a SQL Server database using Cloud SQL. You enable high availability and deploy it securely with only a private IP address. You then load a sample database into it and connect to the database using a client machine. You also perform backup and restore operations and test high availability by triggering a failover.

Objectives

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

• Create a secure, highly available SQL Server database using Cloud SQL.
• Perform backup and restore operations.
• Connect to the database using its private IP address.
• Trigger a failover to test high availability.

Setup and requirements

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 secure, highly available SQL Server database using Cloud SQL

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 SQL.

3. Click Create Instance, and then select SQL Server.

4. For Instance ID, type sql-server, and then for Password, click Generate.

5. Copy and paste your generated password into a text file so you don't lose it. (If you prefer, you can enter a password that you can remember.)

6. For Database version, select the default SQL Server 2019 Standard edition of SQL Server.

7. For Choose region and zonal availability set the region to and select Multiple zones.

8. Click Show Configuration Options and expand the Machine configuration section. Change the machine type to 4vCPUs, 16GB.

9. Expand the Storage section and select 20 GB for Storage capacity. Also, make sure that Enable automatic storage increases option is selected.

10. Expand the Connections section.

11. Select Private IP and choose default network from the dropdown menu.

Note: An associated networking dropdown will appear below the private IP checkbox. This is the network that will be peered to the network Google manages your Cloud SQL database in. This is just like what you did earlier in the course when you created public and private networks and set up a peering between them.

12. Click on Set Up Connection and then in the dialog, click on Enable API.

13. For Allocate an IP range, select Use an automatically allocated IP range and click Continue.

14. Click Create connection (this will take a minute), and then clear Public IP.

15. Expand the Data Protection section and note that Automate daily backups is selected.

16. Click Create Instance to create your database server.
    (This will take a few minutes, but you can continue with the next few steps while you are waiting.)

    Click Check my progress to verify the objective. Create a secure, highly available SQL Server database using Cloud SQL

Task 2. Perform backup and restore operations

1. Click Activate Cloud Shell () in the upper-right corner of the Console. If prompted click Continue.
   The Cloud Shell terminal will open in a pane at the bottom of the window.

2. To download a sample database to use in this exercise, enter the following command:

curl -O https://storage.googleapis.com/cloud-training/dbmigration/AdventureWorksLT2017.bak

3. Type ls.
   A SQL backup file named AdventureWorksLT2017.bak is listed that allows you to restore the Microsoft Adventure Works sample database.

4. In Cloud Shell,to see the environment variable that is set to your current project's ID, run the following command:

echo $DEVSHELL_PROJECT_ID

5. Create a Cloud Storage bucket whose name is the same as your project ID. Click Authorize if prompted:

gcloud storage buckets create gs://$DEVSHELL_PROJECT_ID

6. Copy the Adventure Works backup file into that bucket:

gcloud storage cp ./AdventureWorksLT2017.bak gs://$DEVSHELL_PROJECT_ID

7. Look at your Cloud SQL server in the Console. If it is ready, continue; otherwise, take a short break.

8. When the server is ready, click its name to see its details.

9. On the Overview page, click Import.

10. Browse to your bucket and select the AdventureWorksLT2017.bak file.

11. Under Destination, name the database AdventureWorks, and then click Import.
    (It will take a short time for the import to complete.)

12. When the import job completes, click the Backups link on the left.

13. Backups are set to run automatically, but click Create backup now.

14. For Description, type Initial AdventureWorks backup and click Create. (It will take a short time for the backup to complete.)

15. Click the Operations link on the left, it tracks what has happened on the database so far.

Click Check my progress to verify the objective. Perform backup and restore operations

Task 3. Connect to the database using its private IP address

1. Click the Users link on the left.

A user named sqlserver is automatically created for you. The password for this user is the one you generated when you created the database. (You should have pasted the password in a text file. You can create additional users now if you want to.)

2. Click the Overview link on the left, and then copy the Private IP address to the clipboard and paste it in your text file.
   You will need this later to connect to the database.

3. To create a Ubuntu Linux virtual machine in Cloud Shell, enter the following gcloud command:

gcloud compute instances create cloud-sql-client --machine-type=e2-standard-2 --zone={{{ project_0.default_zone | "Zone" }}} --image=ubuntu-1604-xenial-v20200610 --image-project=ubuntu-os-cloud

4. When the command completes, navigate to the Compute Engine > VM Instance service to see your virtual machine, and then click SSH in the row for that machine.

5. To download the Microsoft SQL Server client software, run the following commands:

curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list

6. To install the software, run the following commands:

sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev

7. When prompted, select Yes.

8. To add the client software to your path, run the following commands:

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc source ~/.bashrc

9. To connect to your Cloud SQL database, enter the following command, but replace the Private IP address to match [your server's IP]:

sqlcmd -S [Your Private IP],1433 -U sqlserver

10. Enter your password when prompted.

11. To see the databases, enter the following two commands separately and in order:

Note: Press RETURN after each command. EXEC sp_databases; go

12. Query AdventureWorks:

SELECT TOP (10) CompanyName FROM [AdventureWorks].[SalesLT].[Customer]; go Note: Press RETURN after each command.

13. Change all of the company names to Google:

UPDATE [AdventureWorks].[SalesLT].[Customer] SET CompanyName = 'Google'; go

14. To confirm that your command worked, run the SELECT query again:

SELECT TOP (10) CompanyName FROM [AdventureWorks].[SalesLT].[Customer]; go

The results are not quite accurate.

15. To proceed to restore the backup you created earlier, return to Cloud SQL in the Console. Select your server to view its details, and then click the Backups link.

16. Click Restore next to your backup, then select Overwrite the source instance (sql-server) as restore destination and then enter the instance name as instructed and click Restore. (This will take a few minutes.)

17. When the restore operation completes, return to your client machine and run the SELECT query again to confirm that your backup was restored correctly:

SELECT TOP (10) CompanyName FROM [AdventureWorks].[SalesLT].[Customer]; go

Click Check my progress to verify the objective. Connect to the database using its private IP address

Task 4. Trigger a failover to test high availability

1. Return to your Cloud SQL database Overview page, and at the top, click Failover.

2. Enter the database name as instructed, and click Trigger Failover.
   It will take about a minute for the failover operation to finish.

3. When the operation completes, return to your client and run your query again.

4. To change to one of the records, run the following command:

UPDATE [AdventureWorks].[SalesLT].[Customer] SET CompanyName = 'Google' WHERE CustomerID = 1; go Note: If you are being logged out from the SQL database, reconnect to the Cloud SQL database using your server's IP, and run both of the commands again.

Click Check my progress to verify the objective. Trigger a failover to test high availability

5. Return to the Overview page, click Failover to return to the main database, and then confirm that the change made on the Failover is reflected on the main server.

6. To delete the database, from the Overview page, click Delete and follow the instructions. You can also go to the Compute Engine service and delete the client machine.

Congratulations!

You have created a SQL Server database using Cloud SQL. You enabled high availability and deployed it securely with only a private IP address. You then loaded a sample database into it and connected to the database using a client machine. You also performed backup and restore operations and tested high availability by triggering a failover.

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.