DMV to Find the Ports that SQL Server is Listening On

Query

SELECT      e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint
FROM        sys.endpoints e
            LEFT OUTER JOIN sys.dm_exec_connections ec
                ON ec.endpoint_id = e.endpoint_id
GROUP BY    e.name,
            e.endpoint_id,
            e.principal_id,
            e.protocol,
            e.protocol_desc,
            ec.local_net_address,
            ec.local_tcp_port,
            e.[type],
            e.type_desc,
            e.[state],
            e.state_desc,
            e.is_admin_endpoint 

Root Blocker Query in SQL Server

Root Blocker Query in SQL Server

SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM SYS.SYSPROCESSES R CROSS APPLY SYS.DM_EXEC_SQL_TEXT(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 2) + CASE WHEN (LEN (LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END + CAST (SPID AS NVARCHAR (10)) + ' ' + BATCH AS BLOCKING_TREE FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T

GO

Adding Artical in transaction replication with schema Name change at subscriber (Not required re-initializing of snapshot)

How to add Articles in transaction replication with out reinitializing snapshot

Step-1

Please run the below command and make sure values must be '0' for the database which is participating in Replication

select immediate_sync , allow_anonymous from syspublications

If You find the values are '1' please change the values from the below commands

EXEC sp_changepublication @publication = 'Publisher name', @property =
N'allow_anonymous', @value='true'
Go
EXEC sp_changepublication @publication = 'Publisher name', @property =
N'immediate_sync', @value='true'
Go

Step-2

Adding articles to Publisher , run the below script.

exec sp_addarticle @publication = N'Publisher name',
@article = N'Publisher Articles',
@source_owner = N'dbo',
@source_object = N'Source Publisher Table',
@type = N'logbased',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'delete',
@schema_option = 0x000000000803509F,
@identityrangemanagementoption = N'none',
@destination_table = N'Destination Table',
@destination_owner = N'Schema Name',
@status = 24,
@vertical_partition = N'false',
@ins_cmd = N'CALL [dbo].[sp_MSins_12Name_insert_sp]',
@del_cmd = N'CALL [dbo].[sp_MSdel_12Name_delete_sp]',
@upd_cmd = N'SCALL [dbo].[sp_MSupd_12Name_Update_sp]',
@force_invalidate_snapshot=1

Note;

@destination_owner = N'Schema Name', --->change the schema name here for subscriber table.

There were three sp will use in replication for moving data from PUB to SUB so please adde some name to the below red coloured space for recognising sp's

   @ins_cmd = N'CALL [dbo].[sp_MSins_12Name_insert_sp]',
   @del_cmd = N'CALL [dbo].[sp_MSdel_12Name_delete_sp]',
   @upd_cmd = N'SCALL [dbo].[sp_MSupd_12Name_Update_sp]'

Step-3

Add the subscription to publisher which we have added before.

EXEC sp_addsubscription @publication = 'Publication Name',
           @subscription_type = N'push',
           @article = 'Articles name',
           @subscriber ='Subscriber server name', @destination_db = 'Subscriber Database Name',
           @reserved='Internal'


Step-4

select * from msdb..sysjobs where name like '%Publisher name%'


Run the job of snapshot which contain step-1 '‘Snapshot Agent startup message’

Make sure that you have see only added articles only snapshot will run.see the below screen shot.

From the above we have added 4 articles at a time.

when you launch the replication monitor,

First .sre and .sch ,.bcp scripts on subscriber end  then bulk inserts will inserts data from PUB to SUB find the screen shot.