Unable to install the current Version of SQL Monitor

• 
  I also am experiencing this.

  DanAppleyard February 21, 2019 21:23

  - 0 +
• 
  Hi @... 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

  Alex B February 22, 2019 14:45

  - 0 +
• 
  This resolved my issue.

  DanAppleyard February 22, 2019 17:28

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

  sheldonhull March 12, 2019 01:00

  - 0 +
• 
  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.

  crimdon March 12, 2019 12:05

  - 0 +
• 
  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.   
  
  

  DonFerguson March 12, 2019 18:10

  - 0 +
• 
  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.
  

  SeanPerkins March 12, 2019 21:25

  - 0 +
• 
  same here.. any fix for this yet?
  

  DevendraSingh March 12, 2019 22:58

  - 0 +
• 
  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.
  

  DevendraSingh March 12, 2019 23:13

  - 0 +
• 
  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.

  DonFerguson March 12, 2019 23:35

  - 0 +
• 
  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:31

  - 0 +
• 
  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.

  Russell D March 13, 2019 12:49

  - 0 +
• 
  @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.
  

  SeanPerkins March 13, 2019 15:00

  - 0 +
• 
  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
  

  markonpeak March 13, 2019 16:35

  - 0 +
• 
  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.

  GeoffSimons March 13, 2019 18:01

  - 0 +
• 
  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.

  markonpeak March 13, 2019 19:00

  - 0 +
• 
  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.

  Russell D March 14, 2019 08:01

  - 0 +
• 
  Is it possible to get a preview version of 9.0.6?

  markonpeak March 14, 2019 14:28

  - 0 +
• 
  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
  
  

  Russell D March 14, 2019 14:41

  - 0 +
• 
  9.0.3 still fails on the service startup doing a full uninstall and reinstall

  markonpeak March 14, 2019 14:56

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

  DonFerguson March 14, 2019 15:29

  - 0 +
• 
  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?
  

  Russell D March 14, 2019 15:36

  - 0 +
• 
  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 15:45

  - 0 +
• 
  Also of note, I am using SQL Server 2014. Not 2012

  markonpeak March 14, 2019 15:46

  - 0 +
• 
  Ok thats worrying - can you send us your repo?

  Russell D March 15, 2019 08:26

  - 0 +
• 
  Sure. Whats the best way to send it to you?

  markonpeak March 15, 2019 14:53

  - 0 +
• 
  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.
  

  Russell D March 18, 2019 14:11

  - 0 +
• 
  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).

  
  

  DonFerguson March 20, 2019 18:08

  - 0 +
• 
  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

  sudp March 21, 2019 02:21

  - 0 +
• 
  @markonpeak / thread we are aiming to release the fix for this today.
  

  Russell D March 21, 2019 11:12

  - 0 +

Add comment

Please sign in to leave a comment.