SQL Monitor Data Repository on GCP Cloud SQL Server
jchoksi
Posts: 3 New member
Hi
We know that on the supported platform page ((https://documentation.red-gate.com/sm/installation-and-setup/planning-the-sql-monitor-infrastructure-and-installation/supported-platforms#Supportedplatforms-InstallingSQLMonitor)), it does not list GCP Cloud SQL Server as supported but we were wondering whether it was feasible to use such a managed instance of SQL Server for hosting the SQL Monitor data repository. Maybe via a config change?
We did a quick POC test and it seems that Base Monitor service is unable to create its schema in an empty database due to being unable to access the model database.
The permissions available to the GCP Cloud SQL user are limited due to it being a managed instance - https://cloud.google.com/sql/docs/sqlserver/users
Could you please clarify whether there is a workaround or whether there are any plans to support GCP Cloud SQL Server as a possible host for the Redgate SQL Monitor database?
Thanks.
We know that on the supported platform page ((https://documentation.red-gate.com/sm/installation-and-setup/planning-the-sql-monitor-infrastructure-and-installation/supported-platforms#Supportedplatforms-InstallingSQLMonitor)), it does not list GCP Cloud SQL Server as supported but we were wondering whether it was feasible to use such a managed instance of SQL Server for hosting the SQL Monitor data repository. Maybe via a config change?
We did a quick POC test and it seems that Base Monitor service is unable to create its schema in an empty database due to being unable to access the model database.
2020-11-18 20:21:48,833 [ 8] ERROR RedGate.SqlMonitor.Common.Utilities.Sql.Abstraction.RepositorySqlConnectionProvider - SQL Exception 'The server principal "sqlmonitor" is not able to access the database "model" under the current security context.' raised for the following T-SQL:
SELECT COUNT(*)
FROM [sys].[objects]
WHERE [is_ms_shipped] = 0
AND [object_id] NOT IN (SELECT [object_id] FROM [model].[sys].[objects])
The permissions available to the GCP Cloud SQL user are limited due to it being a managed instance - https://cloud.google.com/sql/docs/sqlserver/users
Could you please clarify whether there is a workaround or whether there are any plans to support GCP Cloud SQL Server as a possible host for the Redgate SQL Monitor database?
Thanks.
Tagged:
Answers
However, this just sounds like the exact same problem that using Azure SQL DBs has (ie can't access the model database).
So you might be able to workaround this in the same way we recommend for Azure, by creating a blank empty database in GCP, and when you install Monitor, use an existing database. The installer can then connect to it, and perform a schema upgrade and away you go.
I haven't tested GCP and I do want to stress it isn't Supported, but there's no reason I can think of why it wouldn't just work the same.
>So you might be able to workaround this in the same way we recommend for Azure, by creating a blank empty database in GCP, and when you install Monitor, use an existing database. The installer can then connect to it, and perform a schema upgrade and away you go.
I tried following that advice but the base service monitor installer complains that it can't access the model database.
What I've also tried so far is:
- I installed base monitor service on a throwaway VM which had SQL Server Express on it.
- I wen through the base monitor service installation and used it to create a RedGateMonitor database in the SQL Server Express instance.
- I used SSMS to "Generate Scripts" for the RedGateMonitor database and save the sql into a single script.
- I stripped the script for any database creation sql and all that was left was the sql to create the objects under the RedGateMonitor monitor database. I had to also remove any mention of ", OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF" which seems to be a SQL Server 2019 feature and GCP Cloud SQL only supports SQL Server 2017.
- This script was successfully applied to an empty database in the GCP Cloud SQL instance.
- I tried launching base monitor service with its config amended to point to this database and I still experienced the can't access model database issue.
Am I missing some change that was done to make this work for Azure SQL databases?
I looked at the base monitor service's config file and I was wondering whether I need to amend the databaseEngine value to something other than Mssql?
I tried amending autoUpgrade to "false" but that did not resolve the issue but did reduce the number of exceptions reported.
Is there any documentation on what I would need to do to use Azure SQL as the host for the data respository?
The only requirement is listed here: https://documentation.red-gate.com/sm/installation-and-setup/planning-the-sql-monitor-infrastructure-and-installation/sql-monitor-database-requirements, under Using an Azure SQL Database.
The installer needs to connect to an existing blank or existing SQL Monitor database is all, and the login has to either be the server admin or have a user on the master database as well, but only for the installer; for normal use, you only need the user on the specific azure sql db.
It's possible that GCP does something different though, where is your basemonitor? We do only support GCP machines being monitored if the basemonitor is within the same network environment on GCP, due to issues with WMI queries running remotely in GCP.