Connect to Cloud SQL for SQL Server from App Engine flexible environment
Learn how to deploy a sample app on App Engine flexible environment connected to a SQL Server instance by using the Google Cloud console and a client app.
Assuming that you complete all the steps in a timely manner, the resources created in this quickstart typically cost less than one dollar (USD).
Before you begin
- Sign in to your Google Cloud account. If you're new to Google Cloud, create an account to evaluate how our products perform in real-world scenarios. New customers also get 300ドル in free credits to run, test, and deploy workloads.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Roles required to select or create a project
- Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
-
Create a project: To create a project, you need the Project Creator role
(
roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.createpermission. Learn how to grant roles.
-
Verify that billing is enabled for your Google Cloud project.
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Roles required to select or create a project
- Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
-
Create a project: To create a project, you need the Project Creator role
(
roles/resourcemanager.projectCreator), which contains theresourcemanager.projects.createpermission. Learn how to grant roles.
-
Verify that billing is enabled for your Google Cloud project.
-
Enable the Cloud APIs necessary to run a Cloud SQL sample app on App Engine flexible environment.
Console
Click the button below to enable the APIs required for this quickstart.
This enables the following APIs:
- Cloud SQL Admin API
- Compute Engine API
- Cloud Build API
- Cloud Logging API
By clicking the button, you also enable the Google App Engine Flexible Environment.
gcloud
Click the following button to open Cloud Shell, which provides command-line access to your Google Cloud resources directly from the browser. Cloud Shell can be used to run the
gcloudcommands presented throughout this quickstart.Run the following
gcloudcommand using Cloud Shell:gcloudservicesenableappengineflex.googleapis.comsqladmin.googleapis.com\ compute.googleapis.comcloudbuild.googleapis.comlogging.googleapis.com
This command enables the following APIs:
- Cloud SQL Admin API
- Compute Engine API
- Cloud Build API
- Cloud Logging API
By running the above command, you also enable the Google App Engine Flexible Environment.
Set up Cloud SQL
Create a Cloud SQL instance
Public IP
Private IP
Console
Allocate an IP address range and create a private connection to configure private services access for Cloud SQL
-
In the Google Cloud console, go to the VPC networks page.
- Select the
defaultVPC network. - Select the Private service connection tab.
- Select the Allocated IP ranges for services tab.
- Click Allocate IP range.
- For the Name of the allocated range, specify
google-managed-services-default. - Select the Automatic option for IP range and specify the prefix length as
16. - Click Allocate to create the allocated range.
- Select the Private connections to services tab for the
defaultVPC network. - Click Create connection to create a private connection between your network and a service producer.
- For the Assigned allocation, select
google-managed-services-default. - Click Connect to create the connection.
Create an instance with private IP address and SSL enabled
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Click Create instance.
- Click Choose SQL Server.
- Make sure that Enterprise Plus is selected as the Cloud SQL edition for your instance.
- In the Instance ID field, enter
quickstart-instance. - In the Password field, enter a password for the sqlserver user. Save this password for future use.
- In the Choose region and zonal availability section, select Single zone.
- Click the Show configuration options menu.
- Expand the Machine configuration node.
- From the Machine shapes region, select the 4 vCPU, 32 GB shape.
- Expand the Connections node.
- Clear the Public IP checkbox to create an instance only with a private IP address.
- Select the Private IP checkbox.
- From the Network menu, select default.
- Click Create instance and then wait for the instance to initialize and start.
- Click Connections.
- In the Security section, select Allow only SSL connections to enable SSL connections.
- In the Allow only SSL connections dialog, click Save and restart and then wait for the instance to restart.
gcloud
Allocate an IP address range and create a private connection to configure private services access for Cloud SQL
-
Run the
gcloud compute addresses createcommand to allocate an IP address range.gcloudcomputeaddressescreategoogle-managed-services-default\ --global--purpose=VPC_PEERING--prefix-length=16\ --description="peering range for Google"--network=default
-
Run the
gcloud services vpc-peerings connectcommand to create a private connection to the allocated IP address range. Replace YOUR_PROJECT_ID with your project's project ID.gcloudservicesvpc-peeringsconnect--service=servicenetworking.googleapis.com\ --ranges=google-managed-services-default--network=default\ --project=YOUR_PROJECT_ID
Create an instance with private IP address and SSL enabled
-
Before running the command as follows, replace DB_ROOT_PASSWORD with the password of your database user.
- --database-version: The database engine type and version. If left unspecified, the API default is used. See the gcloud database versions documentation to see the current available versions.
- --cpu: The number of cores in the machine.
- --memory: A whole number value indicating how much memory to include in the machine. A size unit can be provided (for example, 3072MB or 9GB). If no units are specified, GB is assumed.
- --region: The regional location of the instance
(for example asia-east1, us-east1). If left unspecified, the default
us-central1is used. See the full list of regions. Run the
gcloud sql instances patchcommand to enable only allow SSL connections for the instance.
Optionally, modify the values for the following parameters:
Run the gcloud
sql instances create command to create a Cloud SQL instance with a Private IP address.
gcloudsqlinstancescreatequickstart-instance\ --database-version=SQLSERVER_2017_STANDARD\ --cpu=1\ --memory=4GB\ --region=us-central\ --root-password=DB_ROOT_PASSWORD\ --no-assign-ip\ --network=default
gcloudsqlinstancespatchquickstart-instance--require-ssl
Create a database
Console
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select
quickstart-instance. - From the SQL navigation menu, select Databases.
- Click Create database.
- In the Database name field of the New database dialog
box, enter
quickstart-db. - Click Create.
gcloud
Run the gcloud
sql databases create command to create a database.
gcloudsqldatabasescreatequickstart-db--instance=quickstart-instanceDeploy a sample app to App Engine flexible environment
Create an App Engine app
Console
-
In the Google Cloud console, go to the App Engine page.
- Click Create application.
- From the Select a region drop-down menu, select us-central.
- Click Next.
- After the Get started page appears, scroll to the bottom of the page.
- Click the I'll do this later link.
gcloud
-
Run the following
gcloud app createcommand to create an App Engine app: - When prompted to choose the region where you want your App Engine app located, enter the numeric choice for us-central.
gcloudappcreate
Configure the App Engine service account
Console
-
In the Google Cloud console, go to the Service accounts page.
- For the service account named App Engine default service account, click the pencil icon.
- Click Add another role.
- Add the Role named Cloud SQL Client.
- Click Save.
gcloud
-
Run the following
gcloudcommand to get a list of your project's service accounts:gcloud iam service-accounts list
- Copy the EMAIL of the App Engine service account.
- Run the following command to add the Cloud SQL Client
role to App Engine service account:
gcloudprojectsadd-iam-policy-bindingYOUR_PROJECT_ID\ --member="serviceAccount:SERVICE_ACCOUNT_EMAIL"\ --role="roles/cloudsql.client"
Configure and deploy a sample app for Cloud SQL
Now that you created a Cloud SQL instance and database, and you configured the App Engine service account so that it has permissions to connect to Cloud SQL, you can configure and deploy a sample app to connect to your Cloud SQL instance.
Public IP
Go
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog box, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog box appears, then click Authorize.
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/golang-samples/cloudsql/sqlserver/database-sql/app.flexible.yamlfile. -
Replace the placeholders for the environment variables in the
app.flexible.yamlfile with the following values: - <PROJECT-ID> with your project ID.
- <INSTANCE-REGION> with
us-central1. - <INSTANCE-NAME> with
quickstart-instance. - <YOUR_DB_USER_NAME> with
sqlserver. - <YOUR_DB_PASSWORD> with the password of the
quickstart-userthat you created in Create a user. - <YOUR_DB_NAME> with
quickstart-db. - At the Cloud Shell command prompt, in the
cloudshell_open/golang-samples/cloudsql/sqlserver/database-sqldirectory, run the followinggcloud app deploycommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
gcloudappdeployapp.flexible.yaml
gcloudappbrowse
View deployed sample app.
Java
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog box, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog box appears, then click Authorize.
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/java-docs-samples/cloud-sql/sqlserver/servlet/src/main/webapp/WEB-INF/appengine-web.xmlfile. -
Replace the placeholders for the environment variables in the
appengine-web.xmlfile with the following values: - project-name:region-name:instance-name with your instance's Connection name that appears on the Cloud SQL instances page in the Google Cloud console.
- my-db-user with
quickstart-user. - my-db-password with the password of the
sqlserverthat you created in Create a user. - my_db with
quickstart-db. - At the Cloud Shell command prompt, in the
cloudshell_open/java-docs-samples/cloud-sql/sqlserver/servletdirectory, run the following Apache Mavenmvn clean packagecommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
mvncleanpackageappengine:deploy-DskipTests
gcloudappbrowse
View deployed sample app.
Node.js
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog box, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog box appears, then click Authorize.
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/nodejs-docs-samples/cloud-sql/sqlserver/mssql/app.flexible.yamlfile. -
Replace the placeholders for the environment variables in the
app.flexible.yamlfile with the following values: - <MY-PROJECT> with your project ID.
- <INSTANCE-REGION> with
us-central1. - <INSTANCE-NAME> with
quickstart-instance. - MY_DB_USER with
quickstart-user. - MY_DB_PASSWORD with the password of the
quickstart-userthat you created in Create a user. - MY_DATABASE with
quickstart-db. - At the Cloud Shell command prompt, in the
cloudshell_open/nodejs-docs-samples/cloud-sql/sqlserver/mssqldirectory, run the followinggcloud app deploycommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
gcloudappdeployapp.flexible.yaml
gcloudappbrowse
View deployed sample app.
Python
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog box, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog box appears, then click Authorize.
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/python-docs-samples/cloud-sql/sql-server/sqlalchemy/app.flexible.yamlfile. -
Replace the placeholders for the environment variables in the
app.flexible.yamlfile with the following values: - <PROJECT-ID> with your project ID.
- <INSTANCE-REGION> with
us-central1. - <INSTANCE-NAME> with
quickstart-instance. - <YOUR_DB_USER_NAME> with
sqlserver. - <YOUR_DB_PASSWORD> with the password of the
quickstart-userthat you created in Create a user. - <YOUR_DB_NAME> with
quickstart-db. - At the Cloud Shell command prompt, in the
cloudshell_open/python-docs-samples/cloud-sql/sql-server/sqlalchemydirectory, run the followinggcloud app deploycommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
gcloudappdeployapp.flexible.yaml
gcloudappbrowse
View deployed sample app.
Private IP
For private IP paths, make sure your App Engine flexible environment VM and the Cloud SQL instance are hosted on the same Virtual Private Cloud (VPC) network. When located on the same VPC network, your App Engine flexible environment app can connect directly to the Cloud SQL instance without using the Cloud SQL Auth Proxy.Go
Create and download SSL server certificate
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Click the
quickstart-instanceto see its Overview page. - Click the Connections tab.
- In the Security section, click Download certificates to download the SSL server certificate.
Build sample app with SSL server certificate
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog appears, then click Authorize.
-
Upload the SSL server certificate file to the certs folder.
- From the Explorer navigation menu of Cloud Shell Editor, navigate to the
golang-samples/cloudsql/sqlserver/database-sql/certsfolder. - Right-click the certs folder in Cloud Shell Editor and select Upload Files.
- Select the following file on your local machine:
server-ca.pem. - With the SSL server certificate file selected, click Open to complete the process of uploading the file to Cloud Shell Editor.
- From the Explorer navigation menu of Cloud Shell Editor, navigate to the
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/golang-samples/cloudsql/sqlserver/database-sql/app.flexible.yamlfile. - Copy and paste the following code into the file, replacing the existing file content:
- Make the following variable replacements:
- PROJECT_ID with your project ID.
- INSTANCE-REGION with
us-central1. - INSTANCE-NAME with your instance's ID that appears on the Cloud SQL instances page in the Google Cloud console.
- YOUR_DB_PASSWORD with the password of the
quickstart-userthat you created in Create a user. - INSTANCE_HOST with your instance's Private IP address that appears on the Cloud SQL instances page in the Google Cloud console.
- At the Cloud Shell command prompt, in the
cloudshell_open/golang-samples/cloudsql/sqlserver/database-sqldirectory, run the followinggcloud app deploycommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
runtime:custom
env:flex
env_variables:
INSTANCE_CONNECTION_NAME:PROJECT-ID:INSTANCE-REGION:INSTANCE-NAME
DB_USER:sqlserver
DB_PASS:YOUR_DB_PASSWORD
DB_NAME:quickstart-db
DB_PORT:1433
INSTANCE_HOST:INSTANCE_HOST
DB_ROOT_CERT:certs/server-ca.pem
PRIVATE_IP:TRUE
vpc_access_connector:
name:projects/PROJECT_ID/locations/us-central1/connectors/quickstart-connectorgcloudappdeployapp.flexible.yaml
gcloudappbrowse
View deployed sample app.
Node.js
Build sample app
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog appears, then click Authorize.
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/nodejs-docs-samples/cloud-sql/sqlserver/mssql/app.flexible.yamlfile. - Copy and paste the following code into the file, replacing the existing file content:
- Make the following variable replacements:
- MY_DB_PASSWORD with the password of the
quickstart-userthat you created in Create a user. - INSTANCE_HOST with your instance's Private IP address that appears on the Cloud SQL instances page in the Google Cloud console.
- PROJECT_ID with your project ID.
- At the Cloud Shell command prompt, in the
cloudshell_open/nodejs-docs-samples/cloud-sql/sqlserver/mssqldirectory, run the followinggcloud app deploycommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
runtime:nodejs
env:flex
env_variables:
DB_USER:quickstart-user
DB_PASS:MY_DB_PASSWORD
DB_NAME:quickstart-db
DB_PORT:1433
INSTANCE_HOST:INSTANCE_HOST
PRIVATE_IP:TRUE
vpc_access_connector:
name:projects/PROJECT_ID/locations/us-central1/connectors/quickstart-connectorgcloudappdeployapp.flexible.yaml
gcloudappbrowse
View deployed sample app.
Python
Create and download SSL server certificate
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Click the
quickstart-instanceto see its Overview page. - Click the Connections tab.
- In the Security section, click Download certificates to download the SSL server certificate.
Build sample app with SSL server certificate
-
In Cloud Shell Editor, open the sample app's source code.
Open Cloud Shell Editor - In the Open in Cloud Shell dialog, click Confirm to download the sample app code and open the sample app directory in Cloud Shell Editor.
- At the Cloud Shell command prompt, run the following command to activate your project:
gcloudconfigsetprojectYOUR-PROJECT-ID - If an Authorize Cloud Shell dialog appears, then click Authorize.
-
Upload SSL server certificate file to the certs folder.
- From the Explorer navigation menu of Cloud Shell Editor, navigate to the
python-docs-samples/cloud-sql/sql-server/sqlalchemy/certsfolder. - Right-click the certs folder in Cloud Shell Editor and select Upload Files
- Select following file on your local machine:
server-ca.pem. - With the SSL server certificate file selected, click Open to complete the process of uploading the file to Cloud Shell Editor.
- From the Explorer navigation menu of Cloud Shell Editor, navigate to the
- From the Explorer navigation menu of Cloud Shell Editor, click the expandable node that's the username of your Google Cloud account.
- Navigate to and open the
cloudshell_open/python-docs-samples/cloud-sql/sql-server/sqlalchemy/app.flexible.yamlfile. - Copy and paste the following code into the file, replacing the existing file content:
- Make the following variable replacements:
- YOUR_DB_PASSWORD with the password of the
quickstart-userthat you created in Create a user. - INSTANCE_HOST with your instance's Private IP address that appears on the Cloud SQL instances page in the Google Cloud console.
- PROJECT_ID with your project ID.
- At the Cloud Shell command prompt, in the
cloudshell_open/python-docs-samples/cloud-sql/sql-server/sqlalchemydirectory, run the followinggcloud app deploycommand to deploy the sample app to App Engine flexible environment. - When prompted with Do you want to continue?, enter
Y. - After the actions performed by running the
gcloud app deploycommand complete, run thegcloud app browsecommand: - Click the generated link to see the sample app running on App Engine flexible environment. This environment is connected to Cloud SQL.
runtime:custom env:flex entrypoint:gunicorn-b:$PORTapp:app env_variables: DB_NAME:quickstart-db DB_USER:sqlserver DB_PASS:YOUR_DB_PASSWORD INSTANCE_HOST:INSTANCE_HOST DB_PORT:1433 DB_ROOT_CERT:certs/server-ca.pem PRIVATE_IP:TRUE vpc_access_connector: name:projects/PROJECT_ID/locations/us-central1/connectors/quickstart-connector
gcloudappdeployapp.flexible.yaml
gcloudappbrowse
View deployed sample app.
Clean up
To avoid incurring charges to your Google Cloud account for the resources used on this page, follow these steps.
-
In the Google Cloud console, go to the Cloud SQL Instances page.
- Select the
quickstart-instanceinstance to open the Instance details page. - In the icon bar at the top of the page, click Delete.
- In the Delete instance dialog box, type
quickstart-instance, and then click Delete to delete the instance. Disabling your app stops it from running instances and serving requests. If your app is processing a request, your app completes the request before being disabled.
To disable an App Engine app and retain its data, do the following:
In the Google Cloud console, go to the Settings page:
In the Application settings tab, click Disable application.
In the App ID field, enter the ID of the app you want to disable, and then click Disable.
Optional cleanup steps
If you're not using the Cloud SQL client role that you assigned to the Compute Engine service account, you can remove it.
-
In the Google Cloud console, go to the Service accounts page.
- Click the edit icon (which looks like a pencil) for the IAM account named Compute Engine default service account.
- Delete the Cloud SQL client role.
- Click Save.
If you're not using the APIs that were enabled as part of this quickstart, you can disable them.
- APIs that were enabled within this quickstart:
- Compute Engine API
- Cloud SQL Admin API
- Cloud Run API
- Container Registry API
- Cloud Build API
In the Google Cloud console, go to the APIs page.
Select any API that you would like to disable and then click the Disable API button.
What's next
Based on your needs, you can learn more about creating Cloud SQL instances.You also can learn about creating SQL Server users and databases for your Cloud SQL instance.
For more information about pricing, see Cloud SQL for SQL Server pricing.
Learn more about:
- Configuring your Cloud SQL instance with a public IP address.
- Configuring your Cloud SQL instance with a private IP address.
Additionally, you can learn about connecting to a Cloud SQL instance from other Google Cloud products: