Unable to install the current Version of SQL Monitor

anka00 · February 21, 2019 3:16PM

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 · February 21, 2019 9:23PM

I also am experiencing this.

Alex B · February 22, 2019 2:45PM

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 · February 22, 2019 5:28PM

This resolved my issue.

sheldonhull · March 12, 2019 1:00AM

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

crimdon · March 12, 2019 12:05PM

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 · March 12, 2019 6:10PM

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

DevendraSingh · March 12, 2019 10:58PM

same here.. any fix for this yet?

DevendraSingh · March 12, 2019 11:13PM

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 · March 12, 2019 11:35PM

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 · March 13, 2019 12:31PM

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 · March 13, 2019 12:49PM

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 · March 13, 2019 3:00PM

@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 · March 13, 2019 4:35PM

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 · March 13, 2019 6:01PM

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 · March 13, 2019 7:00PM

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 · March 14, 2019 8:01AM

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 · March 14, 2019 2:28PM

Is it possible to get a preview version of 9.0.6?

Russell D · March 14, 2019 2:41PM

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 · March 14, 2019 2:56PM

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

DonFerguson · March 14, 2019 3:29PM

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

Russell D · March 14, 2019 3:36PM

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 · March 14, 2019 3:45PM

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 · March 14, 2019 3:46PM

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

Russell D · March 15, 2019 8:26AM

Ok thats worrying - can you send us your repo?

markonpeak · March 15, 2019 2:53PM

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

Russell D · March 18, 2019 2:11PM

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 · March 20, 2019 6:08PM

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 · March 21, 2019 2:21AM

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 · March 21, 2019 11:12AM

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

Unable to install the current Version of SQL Monitor

Answers

Product Learning

Community Forums

Events & Friends

Simple Talk