How do you use cloud databases? Take the survey.

SQL Monitor Data Repository on GCP Cloud SQL Server

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.


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.

Answers

  • Hi, there are no current plans to support GCP for hosting the SQL Monitor repository.

    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.
    Have you visited our Help Centre?
  • jchoksijchoksi Posts: 3 New member
    edited November 19, 2020 8:47PM
    >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 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.

      <repositorySettings readOnlyMode="false">
        <maintenance dayOfWeek="Sunday" localTimeOfDay="1:00" mode="Sample"/>
        <dataStore databaseEngine="Mssql" autoUpgrade="false">
          <mssql version="Auto" connectionStringName="DataConnectionString"/>
        </dataStore>
      </repositorySettings>

    This will be my last attempt to try to get it to work on GCP Cloud SQL and I will try to setup an Azure SQL db next.

    Is there any documentation on what I would need to do to use Azure SQL as the host for the data respository?
  • Russell DRussell D Posts: 1,324 Diamond 5
    edited November 20, 2020 2:24PM
    If connecting to an existing database, the installer doesn't need to access the model database, so I'm not sure why you would see that error if you connected the installer to an existing database, rather than chose the option to create a new one.

    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.
    Have you visited our Help Centre?
  • jchoksijchoksi Posts: 3 New member
    Just closing the loop on this thread for anyone else looking to setup a GCP Cloud SQL instance as their data repository for SQL Monitor.

    It won't work.

    SQL Monitor's installer + Base monitor service fail to query details about the schema for the RedgateMonitor database due to the SQL queries used for this, accessing the "model" database on SQL Server.

    In the end we didn't bother trying to use a managed instance of SQL Server at all and simply used a SQL Server machine image (https://cloud.google.com/compute/docs/images/os-details#sql_server) to bring up a Windows server with SQL Server pre-installed. This comes with on demand licensing which was the major factor in pursuing a managed instance for us.

    Thanks for your input Russell. All the best.
Sign In or Register to comment.