March 24, 2020 at 11:07 pm
Hi, app uses cross db connections, the dbs are in different AG's, so I need them to use 4 part naming convention - in the place of servername would be listener name, but when I run a simple query like:
select * from Listener_Name.DatabaseName.[schemaname].[objectname]
go
I get:
Could not find server 'Listener_Name' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Listener_Name is not on sys.servers table.
My current env has 2 nodes, and 3 AGs each having their own listeners, Do I need to add the 3 Listener Names as linked servers on both nodes? And does anyone have a script on how best to create the linked server using listener name, I would like it to be similar to the local server which shows up on the sys.servers table with server_id 0.
March 25, 2020 at 1:28 pm
Hey,
You can't just put the server name in the naming convention, it has to be a linked server name.
You have to create a linked server as your listener as the source, creating a linked server with a listener as the server is the basically the same thing as creating a linked server with the server as the source, just make sure your user have access to the database.
for it to appear in the sys.servers you need to create a linked server, the local server will always show as the server_id 0.
March 25, 2020 at 4:41 pm
Hi Alejandro,
Do you have a script for the linked server - I created a loop back linked server (linked servername = listener, datasource= listener), but I am getting various errors on the creation about security etc, I am still working thru those but what would help me is a linked server script which people are already using & which works, thanks.
March 25, 2020 at 6:30 pm
It really depends on your security context, if you want to go the easy way with security just create the user that's gonna be connecting to the remote server in the remote server, if you are trying to use the "Be made using the logins current security context" you'll need to enable some stuff on the AD enable delegation on your accounts.
Here we use a remote username and a remote password.
EXEC master.dbo.sp_addlinkedserver @server = N'YourListenerNameGoesHere', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'YourListnerNameGoesHere', @catalog=N'YourDatabaseNameGoesHere'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'YourListenerNameGoesHere',@useself=N'False',@locallogin=NULL,@rmtuser=N'YourRemoteUserNameGoesHere',@rmtpassword='YourRemoteUserNamePasswordGoesHere'
GO
Here's an old link provided by Sue (Thank you) from an old post regarding delegation.
Configuring Linked Servers for Delegation
March 26, 2020 at 11:50 pm
Thanks Alejandro, you are right on the delegation etc for linked servers, they still apply to below:
Well, I had went thru the links and many more, there isn't one place where I found everything written properly, which maybe is because not many people use cross AG dist txs, so this is what needs to be done:
1. Have app make code change to use 4 part naming convention using the appropriate listener_name.
2. Create linked servers for all listeners on all nodes:
script is from https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-clustered-dtc-for-an-always-on-availability-group?view=sql-server-ver15 step 12:
IF NOT EXISTS (SELECT * FROM sys.servers where name = N'Listener1')
BEGIN
EXEC master.dbo.sp_addlinkedserver @server = N'Listener1';
END
do the same for the other listeners. above should suffice - you can do a distributed query test now (its given in the same step 12 of above link) , but as I am on sql 2016 sp2+, I did this also:
ALTER AVAILABILITY GROUP AG_Name
SET (
DTC_SUPPORT = PER_DB
);
did the same for other AG's , https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-availability-group-for-distributed-transactions?view=sql-server-2017#manage-unresolved-transactions
March 27, 2020 at 1:29 pm
So you are in an AG, in 2016 they added the DTC_Support per DB so you didn't have to create it at the WSFC level.
This information can come in handy for anyone with this issue, thanks for your research!
We had that issue once regarding unsolved transaction, our main DB wouldn't start, thanks to the SQL Server log tells you the issue and the transaction that's causing the issue.
I think there's another thing i have not tested or tried yet because i have not found the way to make a transaction unresolved in the DTC, but you can change the way SQL Server treat unresolved transactions, here's a link, i really advise some testing, but like i said, i have not found a way to change the state of a transaction to unresolved at the DTC level.
in-doubt xact resolution Server Configuration Option
Regards,
April 6, 2020 at 5:16 pm
Yes, thanks Alejandro, I made that change too, made in-doubt as 1, I wrote about it here, https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b08472b8-3716-45ae-8158-b30453756602/spconfigure-indoubt-xact-resolution?forum=sqldatabaseengine
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply