Unable to install the current Version of SQL Monitor

anka00

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.

DanAppleyard

I also am experiencing this.

Alex B

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

DanAppleyard

This resolved my issue.

sheldonhull

This didn't resolve the issue for me. 
I've followed these steps but still get failure based on permissions.

crimdon

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.

DonFerguson

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.   

SeanPerkins

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.

DevendraSingh

same here.. any fix for this yet?

DevendraSingh

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.

DonFerguson

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 D

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?

Russell D

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.

SeanPerkins

@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.

markonpeak

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

GeoffSimons

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.

markonpeak

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 D

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.

markonpeak

Is it possible to get a preview version of 9.0.6?

Russell D

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

markonpeak

9.0.3 still fails on the service startup doing a full uninstall and reinstall

DonFerguson

For me, I needed to first uninstall the newer base monitor and then reinstall 9.0.3.

Russell D

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?

markonpeak

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

markonpeak

Also of note, I am using SQL Server 2014. Not 2012

Russell D

Ok thats worrying - can you send us your repo?

markonpeak

Sure. Whats the best way to send it to you?

Russell D

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.

DonFerguson

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).

sudp

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

Russell D

@markonpeak / thread we are aiming to release the fix for this today.