Differene between affinity mask and max degree of parallelism

Affinity mask is instance level configuration
Degree of Parallelism is Query level configuration
----------------------------------------------------
If you have Multiple physical processors or logical Cores in your machine .It is very easy to understand the benefit of Afinitymask.For example u have 4 cores and 2 instances in your machine then ,u can share processors for each instance like 2 cores per instance.(for better resource balance).


Maxdop is for query execution purpose. By default it will be on for all processors.(All processors will work on one query execution).You can change this behavior for particular query like alter index .for ex if u give maxdop =2 while executing query ,2 processors will dedicated for this query execution where as other processors can handle server work load.

Subscriber marked as inactive/Expired

If database replication has been disconnected for too long, the subscription may become inactive/Expired/Not Running Expiring Soon. The error message may read: "The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. The step failed."

To fix the problem, you can either delete the subscription and recreate,But this will need downtime. We can simply execute the below scripts and reactivate the subscription.

To Re-active all the subscription's :
----------------------------------------
select status,* from distribution..MSsubscriptions
update distribution..MSsubscriptions set status=2

(or)
Use Distribution
update dbo.MSsubscriptions set status=2 where status=0

After running the update statement, refresh the jobs and make sure the status on the REPL-Distribution job shows Executing.

Script to get Replication Configuration Information

My company uses SQL Server replication for moving data between servers and we have several publications with many articles being replicated. Its very difficult to find what tables are being replicated, what databases are published, what servers are subscribers, etc.... We can get it through management studio but again its time consuming. Below scripts is very helpful and it will give all the information about replication. We need to execute this script on distributed DB/server.

USE Distribution
GO
-- Get the publication name based on article
SELECT DISTINCT
srv.srvname publication_server
, a.publisher_db
, p.publication publication_name
, a.article
, a.destination_object
, ss.srvname subscription_server
, s.subscriber_db
, da.name AS distribution_agent_job_name
FROM MSArticles a
JOIN MSpublications p ON a.publication_id = p.publication_id
JOIN MSsubscriptions s ON p.publication_id = s.publication_id
JOIN master..sysservers ss ON s.subscriber_id = ss.srvid
JOIN master..sysservers srv ON srv.srvid = p.publisher_id
JOIN MSdistribution_agents da ON da.publisher_id = p.publisher_id
AND da.subscriber_id = s.subscriber_id
ORDER BY 1,2,3