Sql Server : Snapshot Replication Delivers Incremental Changes (after delivering initial snapshot)

Interesting one today:

This may be common knowledge, but upon running some tests its evident that Snapshot Replication does both the things:

1. Deliver a complete snapshot (of all articles chosen) to the Subscribers
2. Also delivers, incremental changes to those articles a.k.a INSERTS/UPDATES / DELETES

Or let’s state it differently:

1. Snapshot and Transactional replication are two different replication types
2. When we set up replication, we create:
   1. LogReader Agent : Reads TRN Log at Publisher and writes changes to Distributor
   2. Snapshot Agent: Creates a complete snapshot/backup/data of Publisher database to be created at Subscriber (but does not deliver it to Subscriber)
   3. Distributor Agent: This agent delivers data created in the above two steps to the Subscriber.
      1. Delivers the continuous changes that LogReader brings
      2. Delivers snapshot that Snapshot agent creates
3. Transactional Replication:
   1. When we create Transactional Replication, the continuous activity at Publisher is replicated to Subscriber. This is carried out by LogReader Agent & Distributor agent.
      1. Distributor has replication procedures for INSERT, DELETE & UPDATE that take each DML activity at Publisher and re-play it as Subscriber
   2. But Transaction replication expects database & tables to already exist at the Subscriber
      1. And that the object structure matches — as the replication procedures (INSERT & UPDATE) depend on the Table & Column names and column order
   3. Creating Snapshot Agent is an optional step while establishing Transactional Replication. Its not needed to create Snapshot Agent, if the database objects already exist at Subscriber;
   4. And all we care about is replicating data from this point onward (and not the old data)
4. Snapshot Replication:
   1. As part of creating Snapshot replication we create Snapshot Agent & Distributor
   2. We create all replication procedures (for each article) to replicate incremental changes on top of initial snapshot/backup/data

Summary

• Yes, when we set up Snapshot replication, not only is the initial data from Publisher is replicated to the Subscribers, it also replicates incremental changes happening at Publisher to all Subscribers.
• Snapshot Agent is different Snapshot Replication

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server : Login failed for user. Reason: Attempting to use an NT account name with SQL Server Authentication. [CLIENT: ]

Interesting one today:

In our lab, this error propped up in one of the environments that relies on replication heavily.

Problem Definition:

Sql Agent jobs for all the Logreader Agents & Distribution Agents are spitting out this error message while they are in an endless loop to “retry”.

Login failed for user. 
Reason: Attempting to use an NT account name with SQL Server Authentication.
[CLIENT: 'machine IP']

The error makes sense, but it does not seem actionable.

Context:

Replication is carried out between Publisher-to-Distributor-to-Subscriber (simple terms). This action is carried out by Sql Agent job running on Distributor.

Each agent, runs under the security context of a user account (Local OS or Domain account). Since some of these agents needs to talk to Publisher or Subscriber or both, they need proper privileges on all the machines to be able to carry out operations.

Usually when all the machines are in a domain, a single account runs the Agent jobs and also has permissions to connect to Subscriber & Publisher. But we have an extra option in the replication UI.

We could set up one account to run the agent on Distributor; Another to connect to Publisher or Distributor.

• For LogReader agent, it needs permissions on Distributor & Publisher
• For Distribution Agent, it needs permissions on Distributor & Subscribers

See the image below for how security is set up for LogReader Agent:

In the window above, the top section shows under what user context the agent runs on Distributor. Since it also need to connect to Publisher, in the bottom portion, it shows the account needed to connect to Publisher.

To make things easier, we have two options:

1. By Impersonating the process account:
   1. We could use the same account mentioned the top section or give a new account.
2. Use the following Sql Server Login:
   1. This new account needs to be a Sql Server Login (and not windows or domain account).

Resolution:

The line in red above was the root of the problem.

Someone selected “Use the following Sql Server Login” to “Connect to the Publisher” and provided a Domain account, rather than a Sql Server Login.

Hence the error message :

Attempting to use an NT account name with SQL Server Authentication.

Either provide correct Sql Login or choose “By Impersonating the process account”.

More Info: When you “impersonate process account” it uses the same account under which the LogReader agent runs on Distributor — hence called Process Account

 

Hope this helps,
_Sqltimes

 

Read Full Post »