SqlServerDsc icon indicating copy to clipboard operation
SqlServerDsc copied to clipboard
verified publisher icon dsccommunity

SqlRS: when installing HA, error - Could not drop object 'dbo.Catalog' because it is referenced by a FOREIGN KEY constraint

Open shurick81 opened this issue 5 years ago • 4 comments

Details of the scenario you tried and the problem that is occurring

Trying to install RS with high availability on two identical nodes. When I use DSC and set up the first node, reporting services web sites work just fine. Then I start the same DSC configuration on the second node. It fails with exception and reporting services web sites stop working:

image

Verbose logs showing the problem

VERBOSE: [SWAZSERVER03]: LCM:  [ Start  Resource ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER03]: LCM:  [ Start  Test     ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Get the current reporting services configuration for the
instance 'RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following
parameters, ''namespaceName' = root\Microsoft\SQLServer\ReportServer\RS_RSInstance01\v13\Admin,'className' =
MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Reporting services SWAZDB00\SQLInstance01 are not
initialized.
VERBOSE: [SWAZSERVER03]: LCM:  [ End    Test     ]  [[SqlRS]ReportingServicesConfig]  in 1.3650 seconds.
VERBOSE: [SWAZSERVER03]: LCM:  [ Start  Set      ]  [[SqlRS]ReportingServicesConfig]
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following
parameters, ''namespaceName' = root\Microsoft\SQLServer\ReportServer\RS_RSInstance01\v13\Admin,'className' =
MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following
parameters, ''namespaceName' = root/cimv2,'className' = Win32_OperatingSystem'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Initializing Reporting Services on SWAZDB00\SQLInstance01.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Setting report server virtual directory on
SWAZDB00\SQLInstance01 to ReportServer_RSInstance01.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' = SetVirtualDirectory,'namespaceName'
 = root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Adding report server URL reservation on
SWAZDB00\SQLInstance01: http://+:88.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' = ReserveUrl,'namespaceName' =
root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Setting reports virtual directory on SWAZDB00\SQLInstance01
 to ReportServer_RSInstance01.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' = SetVirtualDirectory,'namespaceName'
 = root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Adding reports URL reservation on SWAZDB00\SQLInstance01:
http://+:88.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' = ReserveUrl,'namespaceName' =
root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' =
GenerateDatabaseCreationScript,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following
parameters, ''namespaceName' = root\cimv2,'className' = Win32_Service'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' =
GenerateDatabaseRightsScript,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Preferred module SqlServer found. (SQLCOMMON0023)
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Importing PowerShell module 'SqlServer' with version
'21.1.18229' from path 'C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18229\SqlServer.psm1'. (SQLCOMMON0025)
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'msdb'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'master'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
Could not drop object 'dbo.Catalog' because it is referenced by a FOREIGN KEY constraint.
 Msg 3726, Level 16, State 1, Procedure , Line 3.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Could not drop object 'dbo.Users' because it is referenced by a FOREIGN KEY constraint.
 Msg 3726, Level 16, State 1, Procedure , Line 3.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Could not drop object 'dbo.Subscriptions' because it is referenced by a FOREIGN KEY constraint.
 Msg 3726, Level 16, State 1, Procedure , Line 3.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

There is already an object named 'Catalog' in the database.
 Msg 2714, Level 16, State 6, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Table 'Catalog' already has a primary key defined on it.
Could not create constraint or index. See previous errors.
 Msg 1779, Level 16, State 0, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

The operation failed because an index or statistics with name 'IX_Catalog' already exists on table 'dbo.Catalog'.
 Msg 1913, Level 16, State 1, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

The operation failed because an index or statistics with name 'IX_Link' already exists on table 'dbo.Catalog'.
 Msg 1913, Level 16, State 1, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

The operation failed because an index or statistics with name 'IX_Parent' already exists on table 'dbo.Catalog'.
 Msg 1913, Level 16, State 1, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

There is already an object named 'Users' in the database.
 Msg 2714, Level 16, State 6, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Table 'Users' already has a primary key defined on it.
Could not create constraint or index. See previous errors.
 Msg 1779, Level 16, State 0, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

The operation failed because an index or statistics with name 'IX_Users' already exists on table 'dbo.Users'.
 Msg 1913, Level 16, State 1, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

There is already an object named 'ExecutionLog' in the database.
 Msg 2714, Level 16, State 6, Procedure , Line 5.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Cannot create index on view 'ExecutionLog' because the view is not schema bound.
 Msg 1939, Level 16, State 1, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

There is already an object named 'Subscriptions' in the database.
 Msg 2714, Level 16, State 6, Procedure , Line 5.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Table 'Subscriptions' already has a primary key defined on it.
Could not create constraint or index. See previous errors.
 Msg 1779, Level 16, State 0, Procedure , Line 2.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Cannot insert duplicate key row in object 'dbo.Catalog' with unique index 'IX_Catalog'. The duplicate key value is ().
The statement has been terminated.
 Msg 2601, Level 14, State 1, Procedure dbo.CreateObject, Line 46.
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'master'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'msdb'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Caution: Changing any part of an object name could break
scripts and stored procedures.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'master'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to
'ReportServer$RSInstance01TempDB'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'msdb'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'master'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Changed database context to 'ReportServer$RSInstance01'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' =
SetDatabaseConnection,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Enumerate CimInstances' with following
parameters, ''namespaceName' = root\Microsoft\SQLServer\ReportServer\RS_RSInstance01\v13\Admin,'className' =
MSReportServer_ConfigurationSetting'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Enumerate CimInstances' complete.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Perform operation 'Invoke CimMethod' with following
parameters, ''instance' = MSReportServer_ConfigurationSetting (InstanceName = "RSINSTANCE01"),'methodName' =
InitializeReportServer,'namespaceName' = root/Microsoft/SQLServer/ReportServer/RS_RSInstance01/v13/Admin'.
VERBOSE: [SWAZSERVER03]:                            [[SqlRS]ReportingServicesConfig] Operation 'Invoke CimMethod' complete.
VERBOSE: [SWAZSERVER03]: LCM:  [ End    Set      ]  [[SqlRS]ReportingServicesConfig]  in 80.4740 seconds.
PowerShell DSC resource DSC_SqlRS  failed to execute Set-TargetResource functionality with error message: Method InitializeReportServer()
failed with an error. Error: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk
condition within the database. (rsReportServerDatabaseError);Invalid column name 'ReportZone'. (HRESULT:-2146233088)
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : SWAZSERVER03.c0nt0s00.local

Suggested solution to the issue

The DSC configuration that is used to reproduce the issue (as detailed as possible)

        SqlRS ReportingServicesConfig
        {
            InstanceName                    = 'RSInstance01'
            DatabaseServerName              = $env:RS_DB_INSTANCE.Split( "\" )[0]
            DatabaseInstanceName            = $env:RS_DB_INSTANCE.Split( "\" )[1]
            ReportServerReservedUrl         = @( 'http://+:88' )
            ReportsReservedUrl              = @( 'http://+:88' )
            PsDscRunAsCredential            = $CRMInstallAccountCredential
        }

SQL Server edition and version the target node is running

Microsoft SQL Server 2017 (RTM-CU22) (KB4577467) - 14.0.3356.20 (X64) Aug 20 2020 22:33:27 Copyright (C) 2017 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows Server 2016 Datacenter 10.0 <X64> (Build 14393: ) (Hypervisor)

SQL Server PowerShell modules present on the target node

Name      Version    Path
----      -------    ----
SqlServer 21.1.18229 C:\Program Files\WindowsPowerShell\Modules\SqlServer\21.1.18229\SqlServer.psd1

The operating system the target node is running

OsName               : Microsoft Windows Server 2016 Datacenter
OsOperatingSystemSKU : DatacenterServerEdition
OsArchitecture       : 64-bit
WindowsBuildLabEx    : 14393.4104.amd64fre.rs1_release.201202-1742
OsLanguage           : en-US
OsMuiLanguages       : {en-US}
PSComputerName       : SWAZSERVER03
RunspaceId           : 98968f97-c15b-4113-a4d7-0aa8d95b0034

Version and build of PowerShell the target node is running

Name                           Value
----                           -----
PSRemotingProtocolVersion      2.3
BuildVersion                   10.0.14393.3866
PSVersion                      5.1.14393.3866
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
PSEdition                      Desktop
CLRVersion                     4.0.30319.42000
WSManStackVersion              3.0
SerializationVersion           1.1.0.1

Version of the DSC module that was used

Name         Version Path
----         ------- ----
SqlServerDsc 14.2.1  C:\Program Files\WindowsPowerShell\Modules\SqlServerDsc\14.2.1\SqlServerDsc.psd1

shurick81 avatar Dec 22 '20 10:12 shurick81

Hmm, I wonder if all steps the resource does are not meant to be done for a second node? It seems the resource tries to do a bad thing to the already configured database, like trying to reconfigure it instead of attaching to it. I'm not familiar with this. Do you know the (manually) steps to add a second node?

johlju avatar Dec 22 '20 15:12 johlju

Is this dsc configuration used on both RS servers? according to this example there should be a lot more configuration for two RS Servers. Example4

Fiander avatar Jan 09 '21 10:01 Fiander

@johlju, I have not ever done it, but in GUI you can select "Choose an existing report server database": https://www.sqlshack.com/configure-sql-server-reporting-services-databases-in-sql-server-always-on-availability-groups/

shurick81 avatar Jan 24 '21 16:01 shurick81

@Fiander the Example4 is not for installing two RS servers, as far as I understand, but one machine that runs two instances (DB and RS).

shurick81 avatar Jan 24 '21 16:01 shurick81