SQL Server – How to Copy Non-Clustered Indexes in Transactional Replication

I didn't try this but just giving an approach I would follow.

The log reader scans all the log records and keeps it in distributor DB. The subscriber reads the records from the distributor DB, then inserts it in subscribers and marks the last record it read.

Considering the above topology, I don't see any issues with moving the distributor DB. Assuming you are moving the publisher(push) or subscriber(pull) involved and the server name remains the same.

In a Push model, the distributor DB resides in publisher and in pull model, the distributor resides in the subscriber DB.

Replication stores all the server information for all publishers, distributors, and subscribers in the database. So if you go with this approach I would suggest the below steps:

1. Stop all jobs (log reader, distributor)
2. Move all databases(attach, detach) to new server with same server name

The only issue I see here is the instance name being the same as the old one, which can be tricky. You also can try testing this yourself with a single table replication and with the same setup.

No, It is not possible.

when the table is involved in replication it does not allow you to drop the primary key.

below is an example as how I have changed the primary key of a replicated table:

table is dbo.CategoryImportMap, database is Product_Staging.

---------------------------------------------------------------------------------------------------
-- remove the table from the replication
---------------------------------------------------------------------------------------------------

USE [Product_Staging]
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

exec sp_dropsubscription @publication = N'Product_Staging', @article = N'CategoryImportMap', @subscriber = N'all', @destination_db = N'all'
GO
exec sp_droparticle @publication = N'Product_Staging',      @article = N'CategoryImportMap', @force_invalidate_snapshot = 0
GO

---------------------------------------------------------------------------------------------------
-- do the schema changes
---------------------------------------------------------------------------------------------------

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET XACT_ABORT ON;


SELECT @@TRANCOUNT


ALTER TABLE [dbo].[CategoryImportMap]
 DROP CONSTRAINT [PK_CategoryImportMap] 

DROP index [UC_SegmentCategory]
on  [dbo].[CategoryImportMap]

ALTER TABLE [dbo].[CategoryImportMap]
ADD CONSTRAINT   [PK_CategoryImportMap]  PRIMARY KEY CLUSTERED   (  [MPlanSegmentCode] ASC  , [MPlanCategoryCode] ASC  )



SELECT @@TRANCOUNT

COMMIT TRANSACTION


---------------------------------------------------------------------------------------------------
-- add the table back to the replication
---------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL read committed;

EXEC sp_addarticle @publication = N'Product_Staging', 
                   @article = N'CategoryImportMap',
                   @source_object=N'CategoryImportMap',
                   @destination_table =N'CategoryImportMap'
GO

--========================================================================
-- REFRESH THE SUBSCRIPTIONS
--========================================================================
EXEC sp_refreshsubscriptions @publication = N'Product_Staging'
GO

--========================================================================
-- Start the Snapshot Agent job.
--========================================================================
EXEC sp_startpublication_snapshot @publication = N'Product_Staging'
go


-- test number of rows (source and destination)
sp_count 'CategoryImportMap'