I'm pretty sure that you'll need a certificate which has multiple CNs not one with DNS entries.
The certificate was generated using Web Server template.
I don't think that's the correct template. I don't have access to a CA at the moment, but I don't think that template uses the correct server authentication certificate. The note in my book says:
The certificate must be a server authentication certificate that requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1). The certificate must also be created using the KeySpec option of AT_KEYEXCHANGE; optionally the key usage property will include key encipherment.
The query processor can produce an invalid execution plan for the (correct) query generated by DBCC to check that the view index produces the same rows as the underlying view query.
The plan produced by the query processor incorrectly handles NULLs
for the ImageObjectID
column. It incorrectly reasons that the view query rejects NULLs
for this column, when it does not. Thinking that NULLs
are excluded, it is able to match the filtered nonclustered index on the Users
table that filters on ImageObjectID IS NOT NULL
.
By producing a plan that uses this filtered index, it ensures that rows with NULL
in ImageObjectID
are not encountered. These rows are returned (correctly) from the view index, so it appears there is a corruption when there is not.
The view definition is:
SELECT
dbo.Universities.ID AS Universities_ID,
dbo.Users.ImageObjectID AS Users_ImageObjectID
FROM dbo.Universities
JOIN dbo.Users
ON dbo.Universities.AdminUserID = dbo.Users.ID
The ON
clause equality comparison between AdminUserID
and ID
rejects NULLs
in those columns, but not from the ImageObjectID
column.
Part of the DBCC generated query is:
SELECT [Universities_ID], [Users_ImageObjectID], 0 as 'SOURCE'
FROM [dbo].[mv_Universities_Users_ID] tOuter WITH (NOEXPAND)
WHERE NOT EXISTS
(
SELECT 1
FROM [dbo].[mv_Universities_Users_ID] tInner
WHERE
(
(
(
[tInner].[Universities_ID] = [tOuter].[Universities_ID]
)
OR
(
[tInner].[Universities_ID] IS NULL
AND [tOuter].[Universities_ID] IS NULL
)
)
AND
(
(
[tInner].[Users_ImageObjectID] = [tOuter].[Users_ImageObjectID]
)
OR
(
[tInner].[Users_ImageObjectID] IS NULL
AND [tOuter].[Users_ImageObjectID] IS NULL
)
)
)
)
OPTION (EXPAND VIEWS);
This is generic code that compares values in a NULL
-aware fashion. It is certainly verbose, but the logic is fine.
The bug in the query processor's reasoning means that a query plan that incorrectly uses the filtered index may be produced, as in the example plan fragment below:
The DBCC query takes a different code path through the query processor from user queries. This code path contains the bug. When a plan using the filtered index is generated, it cannot be used with the USE PLAN
hint to force that plan shape with the same query text submitted from a user database connection.
The main optimizer code path (for user queries) does not contain this bug, so it is specific to internal queries like those generated by DBCC.
Best Answer
I was able to resolve this issue in my lab.
I setup an Availability Group between two virtual machines in my lab. I was not able to add the listener as part of the initial setup and also couldn't add it afterwards.
Each server had two network adapters on different sub-nets. When I disabled the 2nd network adapter on each server, I was able to add the listener successfully. I then re-enabled the 2nd network adapters successfully with no adverse effect on the listener.