Sql-server – how to find the name of the distributor server and publisher server from the subscriber server/subscriber database

replicationsql serversql server 2014transactiontransactional-replication

Having in mind that I can add computed columns to the subscription tables
according to this link:

SQL Server : Transactional Replication Computed Column

That I am already doing, and also adding indexes to replicated tables (in the subscriber server)

However,

While I am doing it, I have to face too many locks and blocks to/from the replication as you can see on the picture below:

enter image description here

this is the replication command I was blocking while creating my index:

create procedure [sp_MSupd_dbotblBGiftVoucher]
        @c1 binary(24) = NULL,
        @c2 smallint = NULL,
        @c3 varchar(20) = NULL,
        @c4 int = NULL,
        @c5 varchar(20) = NULL,
        @c6 int = NULL,
        @c7 bit = NULL,
        @c8 char(1) = NULL,
        @pkc1 binary(24) = NULL,
        @bitmap binary(1)
as
begin  
if (substring(@bitmap,1,1) & 1 = 1)
begin 

update [dbo].[tblBGiftVoucher] set
        [strVoucherNumber] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [strVoucherNumber] end,
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end  
else
begin 

update [dbo].[tblBGiftVoucher] set
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end 
end 

there are too many servers, and I am not sure of which server is the publisher,
where is the distributor database, and if there is a distributor server, which one is it?

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber?

some information is here:
How to restart the distributor agent of transactional replication?

but I am still struggling to pause the replication for at least one article, but I could pause it for the whole publication.

What I want to achieve is to pause the replication for my current subscription (the database where I am on)…

Best Answer

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber?

For push subscriptions from the subscriber you can query MSsubscription_agents to get the publisher and publisher database.

For pull subscriptions from the subscriber you can query MSsubscription_properties to get the publisher and distributor. Have a look at the columns publisher, publisher_db, and distributor. The distribution database name will likely be distribution.