Creating SQL Server Databases on

Compute Engine
experimentLabschedule2 horasuniversal_currency_alt5 créditosshow_chartIntermedio

student-04-5a9c6985c62d@qwiklabs.net
mHuPXGEDVtSx
qwiklabs-gcp-03-413fe9104d18
infoEs posible que este lab incorpore herramientas de IA para facilitar tu aprendizaje.
Overview
In this lab, you provision a SQL Server database server in a private network. Then, you create a
Windows machine in a public network that you can use to administer that server. You also create a
Linux client in the public network that can be used to connect to the database. Lastly, you set up a
firewall rule that allows access to the SQL Server database only from the private network.
Objectives
In this lab, you learn how to perform the following tasks:
 Create client and server VMs.
 Administer your database server.
 Connect to the database from a client.
Setup and requirements
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.
Note: Do not click End Lab unless you have finished the lab or want to restart it. This clears your
work and removes the project.
Task 1. Create client and server VMs
1. Open a new web browser window and navigate to the Google Cloud Console
(console.cloud.google.com).
2. Use the project selector to choose the first project with a leading name of 'qwiklabs-gcp.'
3. On the Navigation menu ( ), click Cloud Overview.
4. In the Project info section, find your Project ID and copy and paste it into a text file. (You
will need it later.)
 5. Click the Activate Cloud Shell ( ) icon in the upper right of the Console.
The Cloud Shell terminal will open in a pane at the bottom of the window.
6. To clone a GitHub repository that includes a completed version of the last lab, enter the
following command:
git clone https://github.com/GoogleCloudPlatform/training-data-analyst
Se copió correctamente
content_copy
7. Change to the following folder:
cd ~/training-data-analyst/courses/db-migration/terraform-sql-server-on-gce/
Se copió correctamente
content_copy
8. Type ls and you see this folder has the Terraform files completed from the last lab.
9. Click Open Editor, and when prompted, click Open in a new window.
10.Navigate to training-data-analyst/courses/db-migration/terraform-sql-server-on-
gce/ folder, open the terraform.tfvars file.
11.Change the project_id variable to match your project's ID.
12.Also, change the region to Region and zone to Zone.
13.Create a SQL Server machine in the private network. Add a file named vm-sql-server-
windows.tf, and paste the following Terraform code into it:
# Create Windows SQL Server in Private VPC
resource "google_compute_instance" "sql-server-windows" {
name = "sql-server-windows-${random_id.instance_id.hex}"
machine_type = "e2-standard-2"
zone = var.gcp_zone_1
tags = ["allow-rdp", "allow-sql"]

boot_disk {
initialize_params {
image = "windows-sql-cloud/sql-2017-express-windows-2016-dc-v20200414"
}
}

network_interface {
network = google_compute_network.private-vpc.name
subnetwork = google_compute_subnetwork.private-subnet_1.name
# access_config { } - Remove access_config for no External IP
}
}

output "sql-server-windows" {
value = google_compute_instance.sql-server-windows.name
}

output "sql-server-windows-external-ip" {
value = "NONE"
}

output "tsql-server-windows-internal-ip" {
value = google_compute_instance.sql-server-windows.network_interface.0.network_ip
}
Se copió correctamente
content_copy
 14.Create a Windows machine in the public network that can be used to administer the SQL
Server. Add a file named vm-windows-admin.tf, and paste the following Terraform code
into it:
# Create VM Windows Admin
resource "google_compute_instance" "windows-admin" {
name = "windows-admin-${random_id.instance_id.hex}"
machine_type = "e2-standard-2"
zone = var.gcp_zone_1
tags = ["allow-rdp"]

boot_disk {
initialize_params {
image = "windows-cloud/windows-server-2016-dc-v20200424"
}
}

network_interface {
network = google_compute_network.public-vpc.name
subnetwork = google_compute_subnetwork.public-subnet_1.name
access_config { }
}
}

output "windows-admin-name" {
value = google_compute_instance.windows-admin.name
}

output "windows-admin-external-ip" {
value = google_compute_instance.windows-admin.network_interface.0.access_config.0.nat_ip
}

output "windows-admin-internal-ip" {
value = google_compute_instance.windows-admin.network_interface.0.network_ip
}
Se copió correctamente
content_copy
Note: Because this server is in the public network, you can RDP into it. Once there, you RDP into
the SQL Server in the private network to administer it.
15.Create one more machine, a Linux client that you can use to connect to the SQL Server. Add
another file named vm-sql-client.tf, and then add the following Terraform code to it:
# Create VM SQL Client
resource "google_compute_instance" "sql-client" {
name = "sql-client-${random_id.instance_id.hex}"
machine_type = "e2-micro"
zone = var.gcp_zone_1
tags = ["allow-ssh"]

boot_disk {
initialize_params {
image = "ubuntu-os-cloud/ubuntu-1604-xenial-v20200429"
}
}

metadata_startup_script = "sudo apt-get update;"

network_interface {
network = google_compute_network.public-vpc.name
subnetwork = google_compute_subnetwork.public-subnet_1.name
access_config { }
}
}

output "sql-client-name" {
value = google_compute_instance.sql-client.name
}

output "sql-client-external-ip" {
value = google_compute_instance.sql-client.network_interface.0.access_config.0.nat_ip
}

output "sql-client-internal-ip" {
value = google_compute_instance.sql-client.network_interface.0.network_ip
}
Se copió correctamente
content_copy
Note: This is a Unbuntu Linux machine that you install the SQL Server client software on and use
to test the connection to the SQL Server database.
16.Create a firewall rule to allow communication to the SQL Server from the private network.
Open the vpc-firewall-rules-private.tf file, and add the following firewall rule to the end:
# allow SQL only from public subnet
resource "google_compute_firewall" "private-allow-sql" {
name = "${google_compute_network.private-vpc.name}-allow-sql"
network = google_compute_network.private-vpc.name
allow {
protocol = "tcp"
ports = ["1433"]
}
source_ranges = [
"${var.subnet_cidr_public}"
]
target_tags = ["allow-sql"]
}
Se copió correctamente
content_copy
17.To initialize Terraform and create the plan, return the Cloud Shell terminal and enter the
following commands:
terraform init
terraform plan
Se copió correctamente
content_copy
18.To create the resources, run the following command:
terraform apply -auto-approve
Se copió correctamente
content_copy
Click Check my progress to verify the objective.
Create client and server VMs
Revisar mi progreso
Task 2. Administer your database server
1. When the Terraform process completes, on the Navigation menu ( ), click Compute
Engine.
2. Several machines should be listed, and you need to keep track of usernames and passwords.
To do that, open a text editor on your computer and paste the following template into it:
Windows Admin RDP Login
Username:
Password:

SQL Server RDP Login

Internal IP:
Username:
Password:

SQL Server User

Username:
Password:
Se copió correctamente
content_copy
3. Find the windows-admin- machine, click the dropdown arrow on the RDP button, and
select Set Windows password.
4. Change the username to any name you like, and click Set to generate the password.
5. Copy the password to the clipboard, and then record the username and password in the text
file you created.
6. In the same way, generate a username and password for the machine with the name sql-
server-windows-. Don't forget to record the username and password in your text file. Also,
find the internal IP address of your SQL Server machine and record it in your text file.
7. Log in to RDP for the Windows admin machine.
 8. On the Windows Start menu, select the Remote Desktop shortcut. You can close the
Server Manager dashboard.
9. Enter the internal IP address of the SQL Server, and click Connect. Then, log in with the
username and password you recorded.
Click Yes and then you should be logged in to the SQL Server machine. You can close the Server
Manager dashboard on this machine.
10.To create a SQL Server user login, click the Start menu and type ssms, and then select the
shortcut to Microsoft SQL Server Management Studio.
When Management Studio starts, the local server name should already be filled in.
11.Click Connect to log in to it.
12.In Object Explorer, right-click the Server at the top and select Properties.
13.Click the Security tab, and then select SQL Server and Windows authentication mode.
14.Click OK and read the message, and then click OK to dismiss it.
15.To restart the SQL Server, right-click it again in Object Explorer and select Restart. Agree
when prompted, and then give the server a few seconds to restart. You might have to click
the Refresh button to see that it is restarted.
16.In Object Explorer, double-click Security and then Logins to expand them.
17.Right-click Logins, and select New Login.
 Specify a username in the Login-name text box.
 Select SQL Server authentication.
 Set the password and clear Enforce password policy.
 Click OK to create the login.
18.Record the username and password for the SQL Server user you just created in your text file.
19.Close the remote desktop session to the SQL Server machine and the RDP connection to the
Windows admin machine.
Task 3. Connect to the database from a client
1. Return to the Google Cloud Console and the Compute Engine service.
2. Click SSH next to the sql-client- machine.
3. Install the SQL Server client software on this machine and test the login you just created.
When your SSH connection is established, enter 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
sudo apt-get update
sudo apt-get install mssql-tools unixodbc-dev
Se copió correctamente
content_copy
Note: Select Yes each time you are prompted.
4. Enter the following commands so the SQL client is in your path:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
Se copió correctamente
content_copy
Click Check my progress to verify the objective.
Connect to the database from a client
Revisar mi progreso
5. Using your machine's IP address and the SQL Server username you created, enter the
following command and the password when prompted:
sqlcmd -S 10.2.2.3,1433 -U username
Se copió correctamente
content_copy
If you don't get an error, your client login worked. At this point, there's no database so there's
nothing else to do, but you have a complete solution in place. Your database server is in a secure
network with no public access. You have a Windows machine in the public network that can be
used to administer the database, and you have a client machine that can access the database via
the SQL Server port.
6. Type quit to exit the sqlcmd, and then close your SSH session.
7. Return to the Cloud Shell terminal, and then enter the following command to delete
everything you create earlier in the lab:
terraform destroy -auto-approve
Se copió correctamente
content_copy
Congratulations! You have provisioned a SQL Server machine in a private network. Then, you
created a Windows machine in a public network that you used to administer the database server.
You also created a Linux client in the public network that was used to connect to the database.
Lastly, you set up a firewall rule that allowed access to the SQL Server database only from the
private network.
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.
navigate_beforeAnterior
Siguientenavigate_next

Before you begin

1. Labs create a Google Cloud project and resources for a fixed time
2. Labs have a time limit and no pause feature. If you end the lab, you'll have to restart from
the beginning.
3. On the top left of your screen, click Start lab to begin
Skip
Finish
task_alt
Detalles de puntuación
Aceptar
Este contenido no está disponible en este momento
Te enviaremos una notificación por correo electrónico cuando esté disponible
No, gracias
Sí, notificarme

¡Genial!
Nos comunicaremos contigo por correo electrónico si está disponible
Volver a la pantalla
Got it

One lab at a time

Confirm to end all existing labs and start this one
Cancel
Confirm
Continue anyway
Cancel
¿Cuál es su grado de satisfacción con este lab?*
Cancelar
Enviar
error_outline
¿Confirmas la acción? Es posible que no puedas reiniciar el lab y, si lo haces, deberás comenzar
desde el principio.
Cancelar
Finalizar lab
error
Hay una nueva versión disponible de este curso. Se transferirá el progreso si decides actualizar. Sin
embargo, tu porcentaje de finalización puede cambiar si la nueva versión agrega o quita
actividades de aprendizaje. Haz clic en el botón de vista previa para ver los cambios del curso
antes de actualizar.
Cancelar
Vista previa