MSSQL server in Cloud Run (without external db)

[Kristijan]

I'm trying to run an MSSQL server in Cloud Run because I want my college project to run maybe once a year with minimum cost. I do not want to use an external database and must use MSSQL.

I started with this official repo example. I used sql 2019-latest as base image and installed aspnet core on top of it. I added an extra line to entrypoint.sh that runs my aspnet app that connects to the database, and it all works well when testing locally, in a single container. 

In cloud run, the aspnet app serves on the defined port. But the database, which shows booting logs, appears not running. Here's the error from logs, which originates from configure-db.sh:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Also, any request to my aspnet app that requires connecting to db fails.

I tried with and without a volumes (in memory), using first and second generation execution environments, limiting number of instances, always allocating cpu, these server locations: 172.17.0.1, 127.0.0.1, host.docker.internal,
localhost, all without success. Is this really not possible?

EDIT - IT'S POSSIBLE. SOLUTION:

Create a Cloud Storage Bucket as a volume and mount it to /var/opt/mssql/data.

Without volumes or with an In-Memory File-System volume, I think this error: "error 87(the parameter is incorrect.) occurred while opening file '/var/opt/mssql/data/master.mdf' to obtain configuration information at startup."  stopped the database from fully starting. I tried different mssql versions, and startup flags, but could not find a solution to this error, that is specific to Cloud Run. 

While this could all work as a single container, I decided to put the SQL server in a sidecar (separate container) with a readiness probe to let my frontend (ingress container) know it's ready (thanks @djs_75). Otherwise, the frontend would just output an error until the database was ready. On my Windows PC, the database is ready in ~10 seconds (setup and seed scripts included). On Cloud Run (6 CPU, 8GiB, max. 1 instance) it takes ~40 seconds. My startup probe:

tcp 1433 every 3s    

Initial delay    10s

Timeout    3s

Failure threshold    20

When it's ready, the frontend starts quickly and database operations are surprisingly fast, even with pictures. I probably wouldn't notice a performance difference between this setup and Cloud SQL, ignoring the crazy startup duration. However, I'll be able to access it in 10 years with ~0$ total cost.

[djs_75]

are you running it as a sidecar ? do you have a readiness probe to let your frontend know it is ready? IMHO, MSSQL in docker takes a while to startup and would use a different SQL like mysql as a sidecar in Cloud Run, GKE would be ok

Here are some references Configure container health checks (services)  |  Cloud Run Documentation  |  Google Cloud, Cloud Run sidecars enable advanced multi-container patterns | Google Cloud Blog 

[jaydubu]

Hi @Kristijan,

Your current use case is possible only if it meets all of the Google Cloud Run criteria:

• Serves requests, streams, or events delivered via HTTP, HTTP/2, WebSockets, or gRPC, or executes to completion.
• Does not require a local persistent file system, but either a local ephemeral file system or a network file system.
• Is built to handle multiple instances of the app running simultaneously.
• Does not require more than 8 CPU and 32 GiB of memory per instance.
  Is containerized or can be built into a container.

     Note: If applicable, you can use the following to connect to a Cloud SQL instance from a Cloud Run service:

• MySQL
• PostgreSQL
• SQL Server

If not, (as mentioned by @djs_75 ) you could consider Google Kubernetes Engine (GKE) as a hosting option.

Issue: The error “Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has..” indicates a problem reaching the MSSQL instance. This is a generic error message when sqlcmd can't connect because the server is not online.

Troubleshooting: If you’d like to deep dive on the root cause, please refer to below items:

1. Network dependencies: A default instance typically runs on port 1433.
2. Error isolation: Examine the logs and errors for detailed info on each failures.

Recommendations:

• Run command in separate / correct order to avoid skipping steps (e.g starting the server).
• Perform health checks
  • Liveness probes determine whether to restart a container.
  • Startup probes determine whether the container has started and is ready to accept traffic.

Hope this clarifies your concerns.

[Kristijan · Accepted solution]

Create a Cloud Storage Bucket as a volume and mount it to /var/opt/mssql/data. See the edited question for more details.