Nope, that is not what we are looking for. But your article was very helpful. Thanks again.

Never done it, but check this MSDN blog. He seems confident in its reliability for using it for multiple listener scenarios. https://blogs.msdn.microsoft.com/sqlalwayson/2012/02/03/how-to-create-multiple-listeners-for-same-availability-group-goden-yao/

Thanks for the answer mate!

But is it reliable to use the WSFC name and IP in place of SQL Server Listener?

Yep, that’s essentially what a listener is. I prefer adding it on the SQL side.

When you create a new AG it will automatically create a role in WSFC. So by using the WSFC virtual name and IP address client will be redirected to the primary replica.

CREATE PROCEDURE [dbo].[LoyalAGFailover]
— Add the parameters for the stored procedure here
@DBMailProfile VARCHAR(MAX),
@AvailabilityGroupKingName VARCHAR(MAX)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
— interfering with SELECT statements.
SET NOCOUNT ON;

— Insert statements for procedure here
— Set the Database Mail Profile to use on this server
DECLARE @profile VARCHAR(MAX) = @DBMailProfile;

— Set the King Availability Group
DECLARE @ag_king_name VARCHAR(MAX) = @AvailabilityGroupKingName;
DECLARE @ag_king UNIQUEIDENTIFIER;

SELECT
@ag_king = ag_id
FROM sys.dm_hadr_name_id_map
WHERE ag_name = @ag_king_name;

— Create information table of all Loyal Availability Groups that are Secondary
PRINT ‘Filling information table with all Loyal Availability Groups that are Secondary on this server’
DECLARE @AGs as table
(
RowNum INT,
AG_Name VARCHAR(MAX),
AG_ID UNIQUEIDENTIFIER,
AG_Role VARCHAR(MAX)
);
INSERT INTO @AGs
SELECT
Row = ROW_NUMBER() OVER(ORDER BY ag_name ASC),
AG_Name = m.ag_name,
AG_ID = m.ag_id,
AG_Role = s.role_desc
FROM sys.dm_hadr_availability_replica_states s
JOIN sys.dm_hadr_name_id_map m
on m.ag_id = s.group_id
WHERE
m.ag_id != @ag_king
and is_local = 1
and s.role_desc = ‘Secondary’
— Specify specific availability groups to fail over automatically with the King availability group
and ag_name in (‘AG1′,’AG2′,’AG3’); — ********** THIS NEEDS TO BE SET MANUALLY FOR EACH SERVER IF USED **********
PRINT ‘Informational table has been filled’

DECLARE @ag_loyal_id UNIQUEIDENTIFIER;
DECLARE @ag_loyal_name VARCHAR(MAX) = ”;
DECLARE @count INT;

SELECT @count = COUNT(1) FROM @Ags; — Count of Loyal Availability groups that need to failover
PRINT ‘There are ‘ + cast(@count as nvarchar(10)) + ‘ Loyal Availability groups that need to failover’
PRINT ‘Checking if ‘ + cast(@ag_king_name as nvarchar(max)) + ‘ Availability Group is the Primary on this server’
IF — Check if King Availability Group is Primary on this server
(
SELECT role_desc
FROM sys.dm_hadr_availability_replica_states
WHERE
group_id = @ag_king
and is_local = 1
) = ‘PRIMARY’ and @count > 0
BEGIN — King Availability Group is Primary, Failover any Loyal Availability Groups that are currently Secondary
PRINT cast(@ag_king_name as nvarchar(max)) + ‘ Availability Group is the Primary on this server, failing over any Loyal Availability Groups that are currently Secondary’
WHILE @count > 0
BEGIN
SELECT
@ag_loyal_id = ag_id,
@ag_loyal_name = ag_name
FROM @AGs
WHERE
@count = RowNum;
SELECT @count = @count – 1;
DECLARE @message VARCHAR(MAX);
DECLARE @recipients VARCHAR(MAX) = ‘[email protected]’;
DECLARE @subject VARCHAR(MAX);

BEGIN TRY
DECLARE @sqlcmd VARCHAR(max);
SET @sqlcmd = ‘ALTER AVAILABILITY GROUP [‘ + @ag_loyal_name + ‘] FAILOVER;’;
EXEC (@sqlcmd);
PRINT ‘Failover of ‘ + cast(@ag_loyal_name as nvarchar(max)) + ‘ SUCCEEDED, sending email’
SET @subject = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ SUCCEEDED for [‘ + @ag_loyal_name + ‘]’;
SET @Message = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ SUCCEEDED for [‘ + @ag_loyal_name + ‘]’;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile,
@recipients = @recipients,
@subject = @subject,
@body = @Message;
END TRY

BEGIN CATCH
PRINT ‘Failover of ‘ + cast(@ag_loyal_name as nvarchar(max)) + ‘ FAILED, sending email’ + ERROR_MESSAGE()
SET @subject = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ Failed for [‘ + @ag_loyal_name + ‘]’;
SET @Message = ‘Availability Group Failover to ‘ + @@SERVERNAME + ‘ Failed for [‘ + @ag_loyal_name + ‘]’+’
‘+ERROR_MESSAGE();

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘alasql0002’,
@recipients = @recipients,
@subject = @subject,
@body = @Message;
END CATCH;
END;
END;
ELSE
PRINT ‘Either ‘ + cast(@ag_king_name as nvarchar(max)) + ‘ Availability Group is not the Primary on this server, or there are no Loyal Availability Groups that need to fail over – FINISHED’
END

I’ve watched your courses about database security and about using Entity Framework. If I’m allowed to, please let me ask some questions about ORMs and security to you as somebody who is familiar with both topics*.
At my company, we are currently discussing about using ORM (EF6) and Stored Procedures (sprocs) with reference to security:
• use sprocs only, all free** CRUD operations are forbidden on the database to gain security, using an ORM does not provide significant advantages anymore
• use it in a mixed fashion, get the best of both worlds but having trade offs in security
o forbidding free CUD-Operations and perform them via sproc, free Selects are allowed, using the ORMs ability to easily make DB queries
o allow free CRUDs and using the ORMs ability to easily make DB queries and to save modified object graphs
o forbidding all Delete operations …??
Is it a good a way to gain security by forbidding free CRUD? Or is it better to focus on other security mechanisms as you described them in your course with resources saved by using an ORM?

Thanks in advance
Otto

*There are a lot of discussion you can find in the web, but it seems to me, people who are familiar with ORMs are debating with people who are familiar with sprocs with people who are familiar with security – not knowing if anybody is getting the whole picture (https://kevinlawry.wordpress.com/2012/08/07/why-i-avoid-stored-procedures-and-you-should-too/)
** free in the sense of CRUD operations that are not encapsulated in a sproc