June 28, 2018 at 8:30 am
We have a Windows Server Cluster Pair on which we have 10 SQL Instances of SQL Server 2016 Standard.
On each Instance, we have an Integrations Services Catalogue. So far so good.
We're now trying to get the IS Catalogue working with High Availability.
We've created a new HA Group and added the SSISDB into it. Then we right click of the IS Catalogue and Enable Always On Support. On the first instance we did this on, it all worked.
On the next and all subsequent instances, we added the SSISDB to a HA Group, but when we try to enable Always On Support for the IS Catalogue, we get this error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (.Net SqlClient Data Provider)"
I've run a SQL Profiler to see what generates this and found this SQL::
SELECT dharc.[replica_server_name]
FROM [sys].[dm_hadr_availability_replica_cluster_states] dharc
INNER JOIN [sys].[dm_hadr_availability_replica_states] dhars ON dharc.[replica_id] = dhars.[replica_id]
WHERE dharc.[group_id] = (SELECT group_id FROM [sys].[availability_databases_cluster] WHERE database_name = 'SSISDB') AND dhars.[role] = 2
It appears that this SQL generated expects a single row returned from the Sub query.
The subquery returns a row for each of the Instances we've installed the SSIS Catalogue on and obviously they've all got the same name because you can't change it from SSISDB. We also tried creating the catalogue DB programatically with Powershell as detailed on this page https://docs.microsoft.com/en-gb/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017 and changing the name so each instance had a uniquely named SSISDB. Powershell throws an error.
So it appears that the SSISDB must be called SSISDB but if you have multiple SSISDBs per Cluster, you cannot enable High Availability on them.
Is this a bug or is there a workaround?
Should we create a new SQL Instance solely to host a SSIS Catalogue for all the packages for all the other instances? I'm not comfortable mixing our customers (one customer per instance) packages in a single catalogue.
Or, is there a way to enable Always On High Availability but without using the right click context menu? Is there a way to do it with TSQL where we can tweak the queries to avoid this error?
July 13, 2018 at 2:00 pm
Matt
Can you describe your always on setup in your 10 SQL instances?
July 26, 2018 at 7:49 am
We have the same error and I think that is a bug because database name "SSISDB" cannot be changed and subquery ask to [sys].[availability_databases_cluster] table for existence of SSISDB database. Obviously, if you have more than one instance in your cluster with SSISDB and want to add it to an AlwaysOn Availability group the subquery will return more than one value and the main query will fail.
I've been searching for SSIS Catalogue Option "Enable Always On Support..." T-SQL code in order to fix it manually but nothing. Microsoft must fix it!!!
July 26, 2018 at 7:54 am
posguiller - Thursday, July 26, 2018 7:49 AMWe have the same error and I think that is a bug because database name "SSISDB" cannot be changed and subquery ask to [sys].[availability_databases_cluster] table for existence of SSISDB database. Obviously, if you have more than one instance in your cluster with SSISDB and want to add it to an AlwaysOn Availability group the subquery will return more than one value and the main query will fail.
I've been searching for SSIS Catalogue Option "Enable Always On Support..." T-SQL code in order to fix it manually but nothing. Microsoft must fix it!!!
We gave up, decided not to add the Catalogue to HA, and just deploy packages and jobs manually to both nodes.
We then have a job then enables/disables jobs depending if they're running on the active node.
It's crap, but ran of of time looking for a solution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply