Google Cloud in their Cloud SQL managed database offering has recently introduced Cloud SQL for SQL Server. This adds to their existing MySQL and PostgreSQL support.
In this blog post, I will add a SQL Server for Cloud SQL instance to the setup described in .NET Core on Google Cloud.
1. Prerequisites
All the steps in .NET Core on Google Cloud must be completed before starting.
In that blog post, eShopOnWeb was deployed with environment variable ASPNETCORE_ENVIRONMENT=Development, which means mock data is used instead of a real database.
Also, make sure that you can run eShopOnWeb on your local machine, as that will be necessary for the “Migrate the database” step.
We will now proceed to set up a managed database instance in Google Cloud of type Cloud SQL for SQL Server and connect the existing setup to it.
2. Creating a SQL Server instance
1. Open Cloud SQL.
2. Click Create Instance.
Cloud SQL introduction screen
3. Click SQL Server.
Database engine selection screen
Wait screen for Compute Engine API needed to create a SQL Server instance
4. On the “Create a SQL Server instance” page:
Instance ID: eshop-on-web
Password: Click Generate, then, important: copy and store the password somewhere for later use.
Region: australia-southeast1 (Sydney)
Create a SQL Server instance, configuration screen
For this tutorial, we want to keep costs as low as possible, so we’ll configure the minimum size.
Machine type and storage:
Database version and edition: SQL Server 2017 Express
Machine type: 1vCPU, 3.75 GB
Storage capacity: Custom, 10 GB
Enable storage capacity increases: Disabled
Create a SQL Server instance, configuration screen - Machine type and storage
Create a SQL Server instance, configuration screen - Overview
Connectivity:
Private IP: Enabled, then click Allocate and connect. Since Cloud Run (fully managed) does not support connecting to Cloud SQL for SQL Server over public IP, we need to create a private IP: https://cloud.google.com/sql/docs/sqlserver/connect-run
Public IP: Enabled (optional, but makes it possible to connect from our local machine)
A popup may appear asking to "Enable Service Networking API", if so then click "Enable API"
Create a SQL Server instance, configuration screen - Connectivity section
Create a SQL Server instance, popup asking to enable Service Networking API
Backups / Automate backups: Disabled
Create a SQL Server instance, configuration screen - Backups, recovery, and high availability section
5. Click Create. Creating the SQL Server instance will take about 10 minutes to complete.
Wait for SQL Server instance to be created screen
SQL Server instance screen
3. Migrate the database
Next, we need to migrate the database using Entity Framework.
First, open the Cloud SQL, Overview page for the SQL Server instance that we just created. Make a note of the connection name.
Second, we need to connect to the database from our local machine. For this, the Cloud SQL Proxy for SQL Server is needed. Download it and follow the instructions to install it from here: https://cloud.google.com/sql/docs/sqlserver/sql-proxy
Then, start the proxy using the command:
Windows:
cloud_sql_proxy_x64.exe -instances=CONNECTION_NAME_FROM_CLOUD_SQL_OVERVIEW_PAGE=tcp:1433
macOS or Linux:
./cloud_sql_proxy -instances=CONNECTION_NAME_FROM_CLOUD_SQL_OVERVIEW_PAGE=tcp:1433
Where the instances parameter is the connection name, with =tcp:1433 added at the end.
NOTE: Make sure that you don’t have a SQL Server instance running on the default port (1433) on your local machine. If so, stop it before attempting to run the above command.
Next, on your local machine, run the database migration according to the documentation (Section “Configuring the sample to use SQL Server”), making sure to edit src/Web/appsettings.json to use the following connection strings:
"ConnectionStrings": {
"CatalogConnection": "Server=127.0.0.1;User Id=sqlserver;Password=PASSWORD_FROM_CREATE_SQL_SERVER_INSTANCE_STEP;Initial Catalog=Microsoft.eShopOnWeb.CatalogDb;",
"IdentityConnection": "Server=127.0.0.1;User Id=sqlserver;Password=PASSWORD_FROM_CREATE_SQL_SERVER_INSTANCE_STEP;Initial Catalog=Microsoft.eShopOnWeb.Identity;"
},
Use the generated password from the “Create a SQL Server instance” step, then proceed to run the migration according to the eShopOnWeb documentation (the steps involving dotnet ef database update ...)
NOTE: Skip step “1. Ensure your connection strings in appsettings.json point to a local SQL Server instance” from the eShopOnWeb documentation, as we’ll be using the connection strings above instead
4. Browse the SQL Server instance (optional)
Use Azure Data Studio Manager, or SQL Server Management Studio (Windows only), while making sure that Cloud SQL Proxy for SQL Server is still running.
In the main connection settings, use:
Server: 127.0.0.1
User name: sqlserver
Password: The generated password from the “Create a SQL Server instance” step
You should now be able to connect to the instance, and browse the recently created Microsoft.eShopOnWeb.CatalogDb and Microsoft.eShopOnWeb.Identity databases.
5. Connecting the Cloud Run service to the Cloud SQL instance
1. Open Cloud Run.
2. Click the Cloud Run service that we created earlier (“eshop-on-web”).
3. Click Edit & Deploy New Revision.
Container: Default values (make sure Port is 8080, this should be the default).
Variables / Environment Variables:
First, remove ASPNETCORE_ENVIRONMENT
Deploy revision screen - Advanced settings, variables, before deleting item
Then, configure:
| ASPNETCORE_URLS (already present) | http://*:8080 |
| ConnectionStrings__CatalogConnection | Server=PRIVATE_IP_ADDRESS_OF_SQL_SERVER_INSTANCE;User Id=sqlserver;Password=PASSWORD_FROM_CREATE_SQL_SERVER_INSTANCE_STEP;Initial Catalog=Microsoft.eShopOnWeb.CatalogDb; |
| ConnectionStrings__IdentityConnection | Server=PRIVATE_IP_ADDRESS_OF_SQL_SERVER_INSTANCE;User Id=sqlserver;Password=PASSWORD_FROM_CREATE_SQL_SERVER_INSTANCE_STEP;Initial Catalog=Microsoft.eShopOnWeb.Identity; |
The last two are connection strings in the format required by SQL Server.
In both connection strings, replace the Server parameter with the Private IP address that was assigned to the SQL Server instance you just created. This is visible in the dashboard for Cloud SQL and will be a private IP address, e.g. in the format 10.x.x.x
Also, replace the Password parameter with the generated password from the “Create a SQL Server instance” step.
Make sure, after removing ASPNETCORE_ENVIRONMENT, there should now be exactly three environment variables defined:
- ASPNETCORE_URLS
- ConnectionStrings__CatalogConnection
- ConnectionStrings__IdentityConnection
Deploy revision screen - Advanced settings, variables, after adding new envisionment variables
4. Continue to the Connections tab, and under “VPC Connector”, click “Generate a serverless connector”.
Deploy revision screen - Advanced settings, connections
5. Click Enable to enable to Serverless VPC Access API.
6. Click Create connector.
Enable Serverless VPC Access API screen
Serverless VPC access screen
Create connector:
Name: eshop-on-web
Region: australia-southeast1
Network: default
IP range: e.g. 10.8.0.0 (any unused /28 CIDR range in your VPC network)
Create connector screen
7. Click Create. Creating the connector will take a few minutes to complete.
8. Go back to the deployment page for Cloud Run which was open previously, and refresh the page, then click the “Connections” tab again.
9. Select the eshop-on-web connector in the VPC Connector dropdown (it should be available now).
Deploy revision screen - Advanced settings, connections, new eshop-on-web VPC connector selected
10. Click Deploy. Deploying the new container image will take a few minutes to complete.
Note that there was no need for a new build (from Cloud Build), as the container image already supports the necessary configuration values.
5. Test the deployed website
Navigate to the website again using Firefox with the “Modify Header Value (HTTP Headers)” add-on, as outlined in the previous blog post, making sure to renew the token using gcloud auth print-identity-token, as the token expires after 1 hour.
The website should function much like it did previously, but now using a real database instance for persistence.
Conclusion
Building on the setup from the previous post, we now have a managed database instance connected to our ASP.NET web application on Google Cloud, that can easily be scaled to suit any operational requirements.
The managed database instance is also easily reconfigurable to a High Availability setup with automated backups, which may be important in a production environment setting.