Skip to content

Commit a698c15

Browse files
jackwotherspoonjackwotherspoon
authored
feat(cloudsql/sql-server): update SQL Server to v2 samples (GoogleCloudPlatform#8025)
Co-authored-by: Doug Mahugh
1 parent 29abc0a commit a698c15

File tree

12 files changed

+596
-397
lines changed

12 files changed

+596
-397
lines changed

‎cloud-sql/sql-server/sqlalchemy/Dockerfile‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -33,8 +33,9 @@ RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
3333
COPY requirements.txt ./
3434

3535
# Install production dependencies.
36-
RUN pip install -r requirements.txt && \
37-
pip install gunicorn
36+
RUN set -ex; \
37+
pip install -r requirements.txt; \
38+
pip install gunicorn
3839

3940
# Copy local code to the container image.
4041
ENV APP_HOME /app
@@ -55,4 +56,4 @@ RUN update-ca-certificates
5556
# webserver, with one worker process and 8 threads.
5657
# For environments with multiple CPU cores, increase the number of workers
5758
# to be equal to the cores available.
58-
CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 main:app
59+
CMD exec gunicorn --bind :$PORT --workers 1 --threads 8 app:app

‎cloud-sql/sql-server/sqlalchemy/README.md‎

Lines changed: 152 additions & 43 deletions
Original file line numberDiff line numberDiff line change
@@ -5,75 +5,68 @@
55
1. If you haven't already, set up a Python Development Environment by following the [python setup guide](https://cloud.google.com/python/setup) and
66
[create a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).
77

8-
1. [Create a Google Cloud SQL "SQL Server" instance](
9-
https://console.cloud.google.com/sql/choose-instance-engine).
8+
2. Create a 2nd Gen Cloud SQL Instance by following these
9+
[instructions](https://cloud.google.com/sql/docs/sqlserver/create-instance). Note the connection string,
10+
database user, and database password that you create.
1011

11-
6. Under the instance's "USERS" tab, create a new user. Note the "User name" and "Password".
12+
3. Create a database for your application by following these
13+
[instructions](https://cloud.google.com/sql/docs/sqlserver/create-manage-databases). Note the database
14+
name.
1215

13-
7. Create a new database in your Google Cloud SQL instance.
14-
15-
1. List your database instances in [Cloud Cloud Console](
16-
https://console.cloud.google.com/sql/instances/).
17-
18-
2. Click your Instance Id to see Instance details.
19-
20-
3. Click DATABASES.
21-
22-
4. Click **Create database**.
23-
24-
2. For **Database name**, enter `votes`.
25-
26-
3. Click **CREATE**.
27-
28-
29-
30-
1. Create a service account with the 'Cloud SQL Client' permissions by following these
31-
[instructions](https://cloud.google.com/sql/docs/postgres/connect-external-app#4_if_required_by_your_authentication_method_create_a_service_account).
16+
4. Create a service account with the 'Cloud SQL Client' permissions by following these
17+
[instructions](https://cloud.google.com/sql/docs/sqlserver/connect-external-app#4_if_required_by_your_authentication_method_create_a_service_account).
3218
Download a JSON key to use to authenticate your connection.
3319

3420
## Running locally
21+
3522
To run this application locally, download and install the `cloud_sql_proxy` by
36-
following the instructions [here](https://cloud.google.com/sql/docs/mysql/sql-proxy#install).
23+
following the instructions [here](https://cloud.google.com/sql/docs/sqlserver/sql-proxy#install).
24+
25+
### Launch proxy with TCP
26+
27+
To run the sample locally with a TCP connection, set environment variables and launch the proxy as
28+
shown below.
3729

3830
### Linux / MacOS
3931
Use these terminal commands to initialize environment variables:
4032
```bash
4133
export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json
42-
export INSTANCE_CONNECTION_NAME='<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>'
43-
export DB_USER='my-db-user'
44-
export DB_PASS='my-db-pass'
45-
export DB_NAME='my_db'
46-
export DB_HOST='127.0.0.1:1433'
34+
export INSTANCE_HOST='127.0.0.1'
35+
export DB_PORT='1433'
36+
export DB_USER='<YOUD_DB_USER_NAME>'
37+
export DB_PASS='<YOUR_DB_PASSWORD>'
38+
export DB_NAME='<YOUR_DB_NAME>'
4739
```
4840
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
4941
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/overview) to
5042
help keep secrets safe.
5143

5244
Then, use the following command to start the proxy in the background using TCP:
5345
```bash
54-
./cloud_sql_proxy -instances=${INSTANCE_CONNECTION_NAME}=tcp:1433 sqlserver -u ${DB_USER} --host 127.0.0.1 &
46+
./cloud_sql_proxy -instances=<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>=tcp:1433 -credential_file=$GOOGLE_APPLICATION_CREDENTIALS &
5547
```
5648

5749
### Windows / PowerShell
5850
Use these PowerShell commands to initialize environment variables:
5951
```powershell
60-
$env:GOOGLE_APPLICATION_CREDENTIALS="<CREDENTIALS_JSON_FILE>"
61-
$env:INSTANCE_CONNECTION_NAME="<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>"
62-
$env:DB_USER="my-db-user"
63-
$env:DB_PASS="my-db-pass"
64-
$env:DB_NAME="my_db"
65-
$env:DB_HOST="127.0.0.1:1433"
52+
$env:GOOGLE_APPLICATION_CREDENTIALS="/path/to/service/account/key.json"
53+
$env:INSTANCE_HOST="127.0.0.1"
54+
$env:DB_PORT="1433"
55+
$env:DB_USER="<YOUR_DB_USER_NAME>"
56+
$env:DB_PASS="<YOUR_DB_PASSWORD>"
57+
$env:DB_NAME="<YOUR_DB_NAME"
6658
```
6759
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
6860
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/overview) to
6961
help keep secrets safe.
7062

7163
Then use this command to launch the proxy in a separate PowerShell session:
7264
```powershell
73-
Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "-instances=<MY-PROJECT>:<INSTANCE-REGION>:<INSTANCE-NAME>=tcp:1433 -credential_file=<CREDENTIALS_JSON_FILE>"
65+
Start-Process -filepath "C:\<path to proxy exe>" -ArgumentList "-instances=<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>=tcp:1433 -credential_file=/path/to/service/account/key.json"
7466
```
7567

7668
### Testing the application
69+
7770
Next, setup a virtual environment and install the application's requirements:
7871
```bash
7972
virtualenv --python python3 env
@@ -83,22 +76,138 @@ pip install -r requirements.txt
8376

8477
Finally, start the application:
8578
```bash
86-
python main.py
79+
python app.py
8780
```
8881

8982
Navigate towards `http://127.0.0.1:8080` to verify your application is running correctly.
9083

91-
## Deploy to App Engine Flexible
84+
## Deploy to App Engine Standard
85+
86+
To run on GAE-Standard, create an App Engine project by following the setup for these
87+
[instructions](https://cloud.google.com/appengine/docs/standard/python3/quickstart#before-you-begin).
9288

93-
App Engine Flexible supports connecting to your SQL Server instance through TCP
89+
First, update `app.standard.yaml` with the correct values to pass the environment
90+
variables into the runtime. Your `app.standard.yaml` file should look like this:
9491

95-
First, update `app.yaml` with the correct values to pass the environment
96-
variables and instance name into the runtime.
92+
```yaml
93+
runtime: python37
94+
entrypoint: gunicorn -b :$PORT app:app
95+
env_variables:
96+
INSTANCE_CONNECTION_NAME: <PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>
97+
DB_USER: <YOUR_DB_USER_NAME>
98+
DB_PASS: <YOUR_DB_PASSWORD>
99+
DB_NAME: <YOUR_DB_NAME>
100+
```
101+
102+
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
103+
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/overview) to
104+
help keep secrets safe.
105+
106+
Next, the following command will deploy the application to your Google Cloud project:
107+
108+
```bash
109+
gcloud app deploy app.standard.yaml
110+
```
111+
112+
## Deploy to App Engine Flexible
113+
114+
To run on GAE-Flexible, create an App Engine project by following the setup for these
115+
[instructions](https://cloud.google.com/appengine/docs/flexible/python/quickstart#before-you-begin).
116+
117+
First, update `app.flexible.yaml` with the correct values to pass the environment
118+
variables into the runtime. Your `app.flexible.yaml` file should look like this:
119+
120+
```yaml
121+
runtime: custom
122+
env: flex
123+
entrypoint: gunicorn -b :$PORT app:app
124+
env_variables:
125+
INSTANCE_HOST: '172.17.0.1'
126+
DB_PORT: '1433'
127+
DB_USER: <YOUR_DB_USER_NAME>
128+
DB_PASS: <YOUR_DB_PASSWORD>
129+
DB_NAME: <YOUR_DB_NAME>
130+
beta_settings:
131+
cloud_sql_instances: <PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>=tcp:1433
132+
```
97133
98-
Then, make sure that the service account `service-{PROJECT_NUMBER}>@gae-api-prod.google.com.iam.gserviceaccount.com` has the IAM role `Cloud SQL Client`.
134+
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
135+
secure solution such as [Secret Manager](https://cloud.google.com/secret-manager/docs/overview) to
136+
help keep secrets safe.
99137
100138
Next, the following command will deploy the application to your Google Cloud project:
139+
101140
```bash
102-
gcloud beta app deploy
141+
gcloud app deploy app.flexible.yaml
142+
```
143+
144+
## Deploy to Cloud Run
145+
146+
See the [Cloud Run documentation](https://cloud.google.com/sql/docs/sqlserver/connect-run)
147+
for more details on connecting a Cloud Run service to Cloud SQL.
148+
149+
1. Build the container image:
150+
151+
```sh
152+
gcloud builds submit --tag gcr.io/<YOUR_PROJECT_ID>/run-sqlserver
103153
```
104154

155+
2. Deploy the service to Cloud Run:
156+
157+
```sh
158+
gcloud run deploy run-sqlserver --image gcr.io/<YOUR_PROJECT_ID>/run-sqlserver \
159+
--add-cloudsql-instances '<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>' \
160+
--set-env-vars INSTANCE_CONNECTION_NAME='<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>' \
161+
--set-env-vars DB_USER='<YOUR_DB_USER_NAME>' \
162+
--set-env-vars DB_PASS='<YOUR_DB_PASSWORD>' \
163+
--set-env-vars DB_NAME='<YOUR_DB_NAME>'
164+
```
165+
166+
Take note of the URL output at the end of the deployment process.
167+
168+
Replace environment variables with the correct values for your Cloud SQL
169+
instance configuration.
170+
171+
It is recommended to use the [Secret Manager integration](https://cloud.google.com/run/docs/configuring/secrets) for Cloud Run instead
172+
of using environment variables for the SQL configuration. The service injects the SQL credentials from
173+
Secret Manager at runtime via an environment variable.
174+
175+
Create secrets via the command line:
176+
```sh
177+
echo -n $DB_PASS | \
178+
gcloud secrets create [DB_PASS_SECRET] --data-file=-
179+
```
180+
181+
Deploy the service to Cloud Run specifying the env var name and secret name:
182+
```sh
183+
gcloud beta run deploy SERVICE --image gcr.io/<YOUR_PROJECT_ID>/run-sql \
184+
--add-cloudsql-instances <PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME> \
185+
--update-secrets INSTANCE_CONNECTION_NAME=[INSTANCE_CONNECTION_NAME_SECRET]:latest, \
186+
DB_PORT-[DB_PORT_SECRET]:latest, \
187+
DB_USER=[DB_USER_SECRET]:latest, \
188+
DB_PASS=[DB_PASS_SECRET]:latest, \
189+
DB_NAME=[DB_NAME_SECRET]:latest
190+
```
191+
192+
3. Navigate your browser to the URL noted in step 2.
193+
194+
For more details about using Cloud Run see http://cloud.run.
195+
Review other [Python on Cloud Run samples](../../../run/).
196+
197+
## Deploy to Cloud Functions
198+
199+
To deploy the service to [Cloud Functions](https://cloud.google.com/functions/docs) run the following command:
200+
201+
```sh
202+
gcloud functions deploy votes --runtime python39 --trigger-http --allow-unauthenticated \
203+
--set-env-vars INSTANCE_CONNECTION_NAME=<PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME> \
204+
--set-env-vars DB_USER=$DB_USER \
205+
--set-env-vars DB_PASS=$DB_PASS \
206+
--set-env-vars DB_NAME=$DB_NAME
207+
```
208+
209+
Take note of the URL output at the end of the deployment process or run the following to view your function:
210+
211+
```sh
212+
gcloud app browse
213+
```

cloud-sql/sql-server/sqlalchemy/app.yaml renamed to cloud-sql/sql-server/sqlalchemy/app.flexible.yaml

Lines changed: 11 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -14,14 +14,18 @@
1414

1515
runtime: custom
1616
env: flex
17+
entrypoint: gunicorn -b :$PORT app:app
1718

18-
# Remember - storing secrets in plaintext is potentially unsafe. Consider using
19-
# something like https://cloud.google.com/secret-manager/docs to help keep secrets secret.
19+
# Note: Saving credentials in environment variables is convenient, but not
20+
# secure - consider a more secure solution such as
21+
# Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
22+
# keep secrets safe.
2023
env_variables:
21-
DB_USER: <your-username>
22-
DB_PASS: <your-password>
23-
DB_NAME: <your-db-name>
24-
DB_HOST: 172.17.0.1:<PORT>
24+
INSTANCE_HOST: '172.17.0.1'
25+
DB_PORT: '1433'
26+
DB_USER: <YOUR_DB_USER_NAME>
27+
DB_PASS: <YOUR_DB_PASSWORD>
28+
DB_NAME: <YOUR_DB_NAME>
2529

2630
beta_settings:
27-
cloud_sql_instances: <project-name>:<region-name>:<instance-name>=tcp:<PORT>
31+
cloud_sql_instances: <PROJECT-ID>:<INSTANCE-REGION>:<INSTANCE-NAME>=tcp:1433

0 commit comments

Comments
 (0)