dsccommunity / dsccommunity/SqlServerDsc

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

Open
#1,655 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

bug help wanted
Dominant language
PowerShell
Stars
385
Forks
224
No merged PRs in 30d

Description

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 (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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the SqlRS ReportingServicesConfig DSC resource and the generated database creation script during second-node initialization. Reproduce the two-node high-availability setup, then verify that the configuration completes without foreign-key drop errors or duplicate Catalog, Users, and related database objects.

Written by the indexing model from the issue text.

Assessment

Tech stack
powershell, sql
Domain
database, devops, infrastructure
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.