Unable to install the current Version of SQL Monitor

We are trying to upgrade to SQL Monitor 9. We already had an older version installed previsoly. But at the end of the installation it stucks while it's trying to start the "SQL Monitor Base Monitor" and we get the message "Service SQL Monitor Base Monitor" could not be installed. Verify that you have sufficinet privileges to install system services." (Even we run it as admin)

We uninstalled everything and tried again, but with the same result. 

We also tried to just remove the service and install the SQLMonitorBaseMonitorInstaller.exe, but this didn't help either.
«1

Answers

  • I also am experiencing this.
  • Hi @anka00 and @DanAppleyard,

    We have seen occasions when this occurs for a while, even when the users are using Domain Admins and have not been able to reproduce the behavior.

    What we have seen, is that if you install just the web portion of SQL Monitor, but do not install the base monitor at this point, it should succeed.

    Then, if you navigate to "<PathToWebInstall>\Download" (e.g. C:\Program Files\Red Gate\SQL Monitor\WebSite\Download) and run the SQLMonitorBaseMonitorInstaller.exe found therein it should then install successfully.  The only difference here is that you may need to specify the location of the base monitor as localhost (if installing on the same machine) when you first start the web UI.

    Please let me know if this helps!

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
  • This resolved my issue.
  • This didn't resolve the issue for me. 
    I've followed these steps but still get failure based on permissions.
  • crimdoncrimdon Posts: 53 Bronze 2
    I'm having this trouble at the moment as well. What I've noticed is that it doesn't appear to pass the correct password to the Base Monitor service when installing the new version.
  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    I too, am having issues upgrading.  It failed on 5 of 7 of my Base Monitor installations.  I was able to do a little digging and found that the real issue is not permissions, but instead a failure in the database repository upgrade process.

    The install log shows the following error:

    RedGate.SqlMonitor.Default.Config.SchemaVersioning.Sql.SqlSchemaUpgradeException: Violation of PRIMARY KEY constraint 'PK_datawarehouse_AllRPOs'. Cannot insert duplicate key in object 'datawarehouse.AllRPOs'. The duplicate key value is (~HostnameSnip~, , ~DatabaseNameSnip~, 636879933070000000). ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_datawarehouse_AllRPOs'. Cannot insert duplicate key in object 'datawarehouse.AllRPOs'. The duplicate key value is (~HostnameSnip~, , ~DatabaseNameSnip~, 636879933070000000).

    Further digging shows that a new procedure is created called [datawarehouse].[PopulateAllRPOs] that contains the following:

    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE PROCEDURE [datawarehouse].[PopulateAllRPOs]
    AS
    BEGIN TRANSACTION;

    DELETE FROM [datawarehouse].[AllRPOs];

    WITH [AllDbs]
    AS (SELECT [data].[Cluster_SqlServer_Database_Keys].[Id] AS [DatabaseId],
               [data].[Cluster_Keys].[_Name] AS [ClusterName],
               [data].[Cluster_SqlServer_Keys].[_Name] AS [InstanceName],
               [data].[Cluster_SqlServer_Database_Keys].[_Name] AS [DatabaseName]
        FROM [data].[Cluster_SqlServer_Database_Keys]
            INNER JOIN [data].[Cluster_SqlServer_Keys]
                ON [data].[Cluster_SqlServer_Keys].[Id] = [data].[Cluster_SqlServer_Database_Keys].[ParentId]
            INNER JOIN [data].[Cluster_Keys]
                ON [data].[Cluster_Keys].[Id] = [data].[Cluster_SqlServer_Keys].[ParentId]),
         [AllBackupDates]
    AS (SELECT DISTINCT
               [BTK].[ParentId] AS [DatabaseId],
               [BI].[_FinishDate] AS [FinishDate]
        FROM [data].[Cluster_SqlServer_Database_BackupType_Backup_Instances] [BI]
            INNER JOIN [data].[Cluster_SqlServer_Database_BackupType_Backup_Keys] [BK]
                ON [BK].[Id] = [BI].[Id]
            INNER JOIN [data].[Cluster_SqlServer_Database_BackupType_Keys] [BTK]
                ON [BTK].[Id] = [BK].[ParentId]),
         [RPOs]
    AS (SELECT [DatabaseId],
               [RpoFrom],
               [RpoTo],
               CASE
                   WHEN [RpoFrom] IS NULL THEN
                       NULL
                   ELSE
                       [RpoTo] - [RpoFrom]
               END [Duration]
        FROM
        (
            SELECT [DatabaseId],
                   [FinishDate] AS [RpoTo],
                   LAG([FinishDate], 1) OVER (PARTITION BY [DatabaseId] ORDER BY [FinishDate]) AS [RpoFrom]
            FROM [AllBackupDates]
        ) [ConsecutiveBackups] )
    INSERT INTO [datawarehouse].[AllRPOs]
    (
        [ClusterName],
        [InstanceName],
        [DatabaseName],
        [BackupFinishDate],
        [DurationTicks]
    )
    SELECT [adb].[ClusterName],
           [adb].[InstanceName],
           [adb].[DatabaseName],
           [RPOs].[RpoTo] AS [To],
           [RPOs].[Duration]
    FROM [RPOs]
        INNER JOIN [AllDbs] [adb]
            ON [RPOs].[DatabaseId] = [adb].[DatabaseId];


    COMMIT TRANSACTION;
    GO

    This query is producing duplicates and violating the primary key constraint in [datawarehouse].[AllRPOs]. 

    I'm not sure if this has anything to do with the issue, but the two repositories where it succeeded, were on SQL Server 2016, but the 5 that failed were hosted on SQL Server 2012.

    At any rate, I haven't been able to get the repositories on 2012 to upgrade.   

  • SeanPerkinsSeanPerkins Posts: 8 New member
    edited March 12, 2019 9:25PM
    We're experiencing the same issue as DonFerguson.

    No matter what we do, the 9.0.4 update fails during Base Monitor installation indicating an "insufficient privileges" error, but is actually due to a primary key violation in a new stored procedure called [datawarehouse].[PopulateAllRPOs].

    Our [RedGateMonitor] database is also stored on SQL Server 2012.
  • same here.. any fix for this yet?
  • I was having similar issue while installing 9.0.4 however 9.0.3 installed successfully. I am staying there until we have a fix for this issue.
    Please also note i was coming v8 to v9.

    Hope this helps.
  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    As a test, I went ahead and restored a copy of the RedGateMonitor database onto a test SQL Server 2016 instance, installed the basemonitor service and pointed it to the 2016 instance, and it worked.  So I believe that it is due to unexpected behavior of SQL Server 2012.  I suspect that it may be caused by the use of the LAG function, but this would take far more dissecting of the query causing the issue to confirm.  Hopefully Red Gate can find a solution.  Upgrading the database engine, albeit a major undertaking, may be a potential work around. 
  • Russell DRussell D Posts: 1,146 Diamond 4
    edited March 13, 2019 12:42PM
    We're looking into this. Thanks for the additional information, @DonFerguson were you upgrading from 8.x or 9.x? @SeanPerkins which version are you trying to upgrade from as well please?
    Have you visited our Help Centre?
  • I can't replicate this going from v8 to v9.0.4. Can anyone share a pre-upgrade database via support? I can get a file link generated.
    Have you visited our Help Centre?
  • SeanPerkinsSeanPerkins Posts: 8 New member
    @Russell D Our first attempt was an upgrade from 9.0.2 to 9.0.4. It failed and we reverted to 9.0.2. On our second attempt, we successfully upgraded from 9.0.2 to 9.0.3, but failed again from 9.0.3 to 9.0.4. We are sitting tight at version 9.0.3 for now.
  • markonpeakmarkonpeak Posts: 8 New member
    Also experiencing this issue. Have installed it without issue using the same info for the past few years.

    It throws a generic service won't start permissions error, but upon digging further I also noticed the primary key issue that DonFerguson mentioned above. It also seems to apply to multiple DBs, not just one. Always with the issue:

    SqlSchemaUpgradeException: Violation of PRIMARY KEY constraint 'PK_datawarehouse_AllRPOs'. Cannot insert duplicate key in object 'datawarehouse.AllRPOs'.

    Some details:
    Windows Server 2012 R2 
    Upgrading from Redgate 9.0.3.20042 to Redgate 9.0.4.20475
  • We're aware of an issue that can cause the upgrade to V9.0.4 or V9.0.5 to fail when the repository is hosted on a SQL Server 2012 instance.  We have a fix for this that will be released in V9.0.6, which is scheduled for release next week.  In the mean time, for customers experiencing this issue, we would recommend using V9.0.3, which can be downloaded from
    ftp://support.red-gate.com/patches/SqlMonitorWeb/12Feb2019/SQLMonitorWeb_9.0.3.20042.zip
    It shouldn't be necessary to restore from a backup in order to do this.

    Apologies for any inconvenience caused.
  • markonpeakmarkonpeak Posts: 8 New member
    I tried to install the 9.0.5 fix and got the same issue. Also, downgrading to 9.0.3 times out on service startup still.
  • Russell DRussell D Posts: 1,146 Diamond 4
    edited March 14, 2019 8:03AM
    Yes @markonpeak if you're on SQL 2012 we'd expect you to hit the same problem. The fix is not in 9.0.5, it will go out in 9.0.6.
    Have you visited our Help Centre?
  • markonpeakmarkonpeak Posts: 8 New member
    Is it possible to get a preview version of 9.0.6?
  • Russell DRussell D Posts: 1,146 Diamond 4
    edited March 14, 2019 2:42PM
    Not presently I'm afraid. Your best bet is to reinstall 9.0.3: https://download.red-gate.com//checkforupdates/SQLMonitorWeb/SQLMonitorWeb_9.0.3.20042.exe

    Have you visited our Help Centre?
  • markonpeakmarkonpeak Posts: 8 New member
    9.0.3 still fails on the service startup doing a full uninstall and reinstall
  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    For me, I needed to first uninstall the newer base monitor and then reinstall 9.0.3.
  • Yes I'd expect that as normal, because the 9.0.5 basemonitor is installed it would need removing before you can install 9.0.3.

    @markonpeak if a complete uninstall is failing for you, what are you seeing trying to install 9.0.3?
    Have you visited our Help Centre?
  • markonpeakmarkonpeak Posts: 8 New member
    I should have been more clear. I had installed 9.0.5. When that failed, I completely uninstalled the base monitor and web. Then I restarted the server. I verified that there was nothing related to SQL Monitor in the Programs and Features.

    Then I ran the 9.0.3 executable as admin. The service still fails to start properly. The issue still appears to be related to the Pkeys and thus not being able to start the service
  • markonpeakmarkonpeak Posts: 8 New member
    Also of note, I am using SQL Server 2014. Not 2012
  • Ok thats worrying - can you send us your repo?
    Have you visited our Help Centre?
  • markonpeakmarkonpeak Posts: 8 New member
    Sure. Whats the best way to send it to you?
  • Email into support asking for a secure link, or if you've got it uploaded already just send them the link there.

    Sorry, didn't get a notification this had been updated on Friday for some reason.
    Have you visited our Help Centre?
  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    I just tried version 9.0.6.  Still no joy.  I am getting the same error.

    [Red Gate Monitor (9.0.6.20734), Running as:  (Service), Working Dir: C:\Program Files\Red Gate\SQL Monitor\BaseMonitor]
    [Running on: , OS Version: Microsoft Windows NT 6.1.7601 Service Pack 1, .NET Runtime Version: 4.0.30319.42000 (Microsoft)]
    [Start Date: 2019-03-20 17:51:48Z, Current Date: 2019-03-20 17:51:54Z, Elapsed Time: 00:00:05.8281250]
    2019-03-20 17:51:56,014 [              7] WARN  RedGate.SqlMonitor.Default.Config.SchemaVersioning.Sql.SqlSchemaVersionManager - Requested schema version upgrade from SchemaVersion(Seq224) to SchemaVersion(Seq227)

    Upgrade path is:

    SchemaVersion(Seq224)
    SchemaVersion(Seq225)
    SchemaVersion(Seq226)
    SchemaVersion(Seq227)
    2019-03-20 17:52:51,201 [              7] WARN  RedGate.SqlMonitor.Engine.Alerting.Base.Service.SchemaInitialization - Schema upgrade failed with schema upgrade exception
    RedGate.SqlMonitor.Default.Config.SchemaVersioning.Sql.SqlSchemaUpgradeException: Violation of PRIMARY KEY constraint 'PK_datawarehouse_AllRPOs'. Cannot insert duplicate key in object 'datawarehouse.AllRPOs'. The duplicate key value is (~snip~, , ~snip~, 636886845050000000). ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_datawarehouse_AllRPOs'. Cannot insert duplicate key in object 'datawarehouse.AllRPOs'. The duplicate key value is (~snip~, , ~snip~, 636886845050000000).

  • sudpsudp Posts: 2 New member
    Same issue I logged a question via https://forum.red-gate.com/discussion/84983/error-upgrading-to-sql-monitor-9-0-5.

    Uninstalled and tried 9.0.4, same issue.

    Uninstalled 9.0.4 and installed 9.0.3 worked OK.

    Upgraded from 9.0.3 and installed 9.0.6, Encountered same error regarding Service SQL Monitor Base Monitor.

    In all failed instances on SQL Web Monitor 9 installs OK and was uninstalled via Control Panel

    SQL Base Monitor service is in a starting state.
    sc delete MonitorBaseDeploymentService - to delete the service

    Rebooted Server and installed 9.0.3 OK
  • @markonpeak / thread we are aiming to release the fix for this today.
    Have you visited our Help Centre?
Sign In or Register to comment.