Overview

In this lab, you will create and connect to a Cloud SQL for SQL Server instance and perform basic SQL operations using the Google Cloud Console and a client.

Objectives

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

• Create a Cloud SQL instance and configure it.
• Connect to the Cloud SQL instance from a Windows server.
• Create a database on the Cloud SQL instance.

Setup and requirements

Sign in to the Cloud Console

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 Cloud SQL instance

1. In the Cloud Console, on the Navigation menu (), click SQL.

2. Click Create instance.

3. For Choose your database engine, select Choose SQL Server.

4. Enter myinstance for Instance ID.

5. Enter Password01 for the Password of the default sqlserver user.

6. Use the default values for the other fields.

7. Click Create Instance.

Note: Wait for the instance to finish deploying. It will take about 10 minutes to fully provision. Note: In your Qwiklabs project, the Compute API and the Cloud SQL Admin API are enabled for you by default. Working in another project, you may need to ensure those APIs are enabled before creating a Cloud SQL instance. Note: In this example, the instance is created using default settings, including a public IP address.

Wait for your instance to finish provisioning before moving on to the next step. This will take about 5 minutes.

Click Check my progress to verify the objective. Create an instance

Task 2. Connect to a Windows VM instance with RDP

For this lab, you are provided with a Windows Server Virtual Machine that has SQL Server Management Studio preinstalled. You will use this VM to walkthrough connecting to your instance.

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

2. In the VM instances list, click RDP in the row for your VM instance ssms_vm.

Note: If you are not on Windows but are using Chrome, you can RDP directly from the browser using the Chrome RDP for Google Cloud extension. Click on RDP to connect.

3. Log in with these credentials:

• Username: student
• Password: Password01

4. You can safely click Continue if presented with any messages or warnings.

Task 3. Connect to your Cloud SQL instance with Cloud SQL Auth Proxy

Once logged into your remote desktop window, SQL Server Management Studio will begin loading on startup. While it loads, you can connect your Windows Virtual Machine to your Cloud SQL instance with Cloud SQL Auth Proxy.

1. Click the Windows button. Type powershell.

2. Double-click PowerShell 7 (x64) from the results to open a PowerShell window.

3. In your PowerShell window, paste this command in to download the Cloud SQL Auth Proxy executable:

Invoke-WebRequest -Uri https://dl.google.com/cloudsql/cloud_sql_proxy_x64.exe -OutFile .\cloud_sql_proxy_x64.exe

4. Run the executable replacing INSTANCE_CONNECTION_NAME with your Cloud SQL Instance's unique connection name:

.\cloud_sql_proxy_x64.exe -instances=INSTANCE_CONNECTION_NAME=tcp:1433

A message similar to the following should appear:

Listening on 127.0.0.1:1433 for myproject:myregion:myinstance. Ready for new connections Note: You can find your instance's connection name in the Cloud SQL Instances page where you created your instance at the beginning of the lab.

Task 4. Connect using the SSMS object explorer

1. Switch to the SQL Server Management Studio window in your Windows Virtual Machine.

If it hasn't already, wait for the SSMS window to fully load.

2. In SSMS, you should be presented with a Connect to server pop up. If not, select Connect Object Explorer from the File menu.
3. Enter the following values in the Connection dialog:

• For Server Type, enter Database Engine.
• For Server Name, enter 127.0.0.1.
• For Authentication, enter SQL Server Authentication.
• For Login, enter sqlserver.
• For Password, enter the password used when the instance was created, Password01.

Note: You can find your cloud sql instance's public IP in the overview tab on the Cloud SQL instances page.

4. Click the Connect button.

Click Check my progress to verify the objective. Connect using the SSMS Object Explorer

Task 5. Create a database and upload data

1. In the SSMS Object Explorer window, right-click the Databases node under your instance, and select New Database.
2. Enter testdb for the Database name and click the OK button.

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

3. Under the newly created testdb database, right-click the Tables node, and select New > Table.
4. Enter the following values in the Create table dialog:

• In the Properties window, for Identity > Name, enter guestbook.
• For the first Column Name, enter entryID, set its Data Type to int, and clear the Allow Nulls checkbox.
• In the Column Properties window, expand the Identity Specification item and set (Is Identity) to Yes.
• For the second Column Name, enter guestname and set its Data Type to varchar(MAX).
• For the third Column Name, enter content and set its Data Type to varchar(MAX).

Note: If you do not see a properties window like the picture below, click the wrench icon in SSMS.

5. Click the File menu and select Save guestbook.
6. Right-click the testdb table under Databases and select New Query.
7. Enter the following two INSERT statements into the SQL query text window and click the Execute button:

INSERT INTO guestbook (guestName, content) values ('first guest', 'I got here!'); INSERT INTO guestbook (guestName, content) values ('second guest', 'Me too!');

As an example:

8. Expand the Tables item under the Databases > testdb item in the Object Explorer window. Right-click the dbo.guestbook table and choose Select Top 1000 Rows.

The two records you inserted are displayed as Results, along with the SQL SELECT statement used to query the records.

Congratulations!

You created a SQL Server instance on Google Cloud and connected to it using SQL Server Management Studio!

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

• Create a Cloud SQL instance and configure it.
• Connect to the Cloud SQL instance from a Windows server.
• Create a database on the Cloud SQL instance.

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.