Replication Error: Specify all articles when subscribing to a publication using concurrent snapshot processing

Interesting one today:

In our lab environment, while setting up replication on a few nodes, we ran into this interesting error message.

Error:

 

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 15
Specify all articles when subscribing to a publication using concurrent snapshot processing.

 

At first glance, it did not make much sense — as we did not make any changes to the code. But upon further digging, it became obvious the minor change that was made. @sync_method parameters.

The section where we define the publication, looks like this:

Replication Add Publication

While defining the publication, along with all other parameters, we also mention the way in which the articles & data from Publisher is sent to each Subscriber. @sync_method defines, which approach to take.

The associated ‘add_subscription’ (which threw the error) looks like this:

Replication Add Subscription

Solution:

 

Bingo !!

When a Publication is defined with @sync_method as ‘concurrent‘, then it creates all articles/tables as one chunk of data (not as individual tables) in native mode. So we need to be sure to define @article parameter in ‘sp_addSubscription‘ with ‘all’ as value (and not individual table names) — since the articles are not available individually.

Replication Add Subscription

 

Viola !! Now the add subscription works !!

 

Side note:

 

If you want to add individual tables/articles in the ‘sp_addsubscription‘, then you want to consider using ‘native‘ for @sync_method.

Replication Add Publication

Replication Add Subscription

Hope this helps,

_Sqltimes

Read Full Post »

Sql Server Replication : Query Distribution Agent Profiles using T-SQL

Quick one today:

In our lab, quite frequently, we need to cross check the replication distribution agent profile settings between each environment to be sure they are set correctly. Replication Monitor provides a good way to check each agent and its profiles, but when you have many, many distribution agents in each environment, manual check is not fun.

This could be done by collecting all agent profile parameters using T-SQL

--
-- Run this query pointed to the Distributor
--
SELECT    D.id
		, D.name
		, D.publisher_database_id
		, D.publisher_id
		, D.publisher_db
		, D.publication
		, D.subscriber_id
		, D.subscriber_db
		, D.subscription_type
		, D.profile_id
		, D.creation_date
		, D.subscriber_security_mode
		, D.subscriptionstreams

		, CASE	WHEN P.agent_type = 1 THEN 'Snapshot Agent'
				WHEN P.agent_type = 2 THEN 'Log Reader Agent'
				WHEN P.agent_type = 3 THEN 'Distribution Agent'
				WHEN P.agent_type = 4 THEN 'Merge Agent'
				WHEN P.agent_type = 9 THEN 'Queue Reader Agent'
				ELSE 'Unknown'
		  END AS [AgentType]
		, P.profile_name
		, CASE	WHEN P.type = 0 THEN 'System Profile'
				WHEN P.type = 1 THEN 'User Defined Profile'
				ELSE 'UNKNOWN'
		  END AS [ProfileType]

		, PML.parameter_name
		, PM.value AS [ParameterValue_InUse]

		, PML.default_value
		, PML.min_value
		, PML.max_value

FROM dbo.MSdistribution_agents AS D
INNER JOIN msdb.dbo.MSagent_profiles AS P
	ON D.profile_id = P.profile_id
	AND P.agent_type = 3 -- Distribution agent

INNER JOIN msdb.dbo.MSagent_parameters AS PM
	ON PM.profile_id = D.profile_id

INNER JOIN msdb.dbo.MSagentParameterList AS PML
	ON PML.agent_type = P.agent_type -- Distributor
	AND PML.parameter_name = SUBSTRING(PM.parameter_name, 2, 50)
GO

Result looks something like this:

Hope this helps,
_Sqltimes

Read Full Post »

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 »

Sql Server : The target principal name is incorrect. Cannot generate SSPI context

Interesting Problem Today:

Ran into this issue a few times and every time its a variation of the same headache. So, here some ideas will be documented for posterity.

In general terms, the error looks like this:

Connecting to Subscriber ''
Agent message code 20084. The process could not connect to Subscriber ''
Microsoft SQL Server Native Client 11.0
SQL Server Network Interfaces: The target principal name is incorrect.
Cannot generate SSPI context

The error message seems nebulous and confusing — but for trained eyes it makes perfect sense. For me it took a while to make sense out of it.

There could be several things wrong under the hood, but essentially it says that the target SQL server that is is trying to connect to, does not have a valid SPN with Active Directory.

Meer from Microsoft has documented some information on troubleshooting this issue here. For more details, please read his article, as I’ll over simplify things and address a variation of the problem in this articles (which will be slightly different from his).

Example:

From SQLServerA, using UserA, if I’m trying to connect to SqlServerB, sometimes I get this error. Essentially, means SqlServerB does not have a valid SPN.

Resolution:

First, log into the machine that has SqlServerB. Open command prompt with Administrative privileges. Run the command below to see if there is a valid SPN.

 

SETSPN -L <SQL Server Instance Service Account>

 

If the output looks like the first image below, then the Sql Server instance does not have a valid SPN. Now its time to generate one.

Output without valid SPN:

Invalid SPN

Step 2: Download Kerberos Configuration Manager for SQL Server from here, and start generating one.

Step 3: Open Kerberos Configuration Manager for SQL Server from the same machine that has SqlServerB instance. When you run it, it shows something like this:

 

Kerberos Tool Output

Notice that for Sql Server service, there is not valid SPN or misplaced SPN. So its time to generate one.

Step 4: Hit the “Fix it” button right next to it and generate one. Make sure the user account that is logged into the machine has domain controller permissions.

Step 5: Now run the same command as in Step 1, and the output looks different.

Output with valid SPN:

Valid SPN

 

Voila !! Now you are able to connect to SqlServerB from SqlServerA using UserA

 

 

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server Replication : Exception Message: The snapshot could not be generated because the publisher is inactive

Interesting one today:

Earlier in our lab environment, ran into this interesting error:

2019-06-20 00:18:46.67 Connecting to Distributor ''
2019-06-20 00:18:46.78 The replication agent had encountered an exception.
2019-06-20 00:18:46.78 Source: Replication
2019-06-20 00:18:46.78 Exception Type: 
Microsoft.SqlServer.Replication.ReplicationAgentException
2019-06-20 00:18:46.78 Exception Message: The snapshot could not be 
generated because the publisher is inactive.
2019-06-20 00:18:46.78 Message Code: 54057
2019-06-20 00:18:46.78

Replication from publisher to subscriber was set up correctly, but when the agent runs, the SQL Agent job stops with this error.

Resolution:

Go to Distributor and run this query to check the status of the publisher instance.

EXEC sp_helpdistpublisher

As we can see, one of the publisher instance is set to Inactive. So now we need to reset it.

Run the script below to change the publisher status at distributor:

EXEC sp_changedistpublisher  @publisher = 'Publisher',  @property = 'active', @value = 'true'
GO

The result looks something like this:

Now  run the previous ‘sp_helpdistpublisher’ again and now the status is active:

 

Hope this helps,
_Sqltimes

 

Read Full Post »

Sql Server Replication : Query Tracer Tokens using T-SQL (MStracer_tokens & MStracer_history)

Interesting one today:

Last time we walked through the T-SQL steps to insert a tracer token to measure latency in replication topology. Today, we’ll look at an alternate method to query tracer token details; a.k.a. MStracer_tokens &  MStracer_history meta tables.

Along with the T-SQL procedures (sys.sp_helptracertokenhistory), Sql Server also provides a way to query the tracer tokens using metadata tables i.e. MStracer_tokens &  MStracer_history.  They keep track of details for each token. Querying them will provide us necessary information.

--
-- Query tracer token tables
--
SELECT	  publication_id
	, agent_id
	, t.publisher_commit
	, t.distributor_commit
	, h.subscriber_commit

FROM MStracer_tokens t
JOIN MStracer_history h
	ON t.tracer_id = h.parent_tracer_id

ORDER BY t.publisher_commit DESC
GO

Query Tracer Token Details

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: Drop Replication using T-SQL (procedures used to drop replication)

Interesting one today:

This is Part 2 on blogs related to Replication Setup. Full list is here.

In a recent post, we walked through the steps for setting up replication using T-SQL commands. Today, we’ll look at the commands to remove/drop replication
using T-SQL commands.

Essentially there are 3 major steps to dropping replication; And all steps are executed at Publisher instance. As and when needed, these steps will communicate with Subscriber & Distributor to remove relevant artifacts at each step.

Main Steps:

1. Remove definitions for Publication, Subscription & all relevant articles
2. Change database settings
3. Change Distributor settings

As expected, each of these major steps could have multiple sub-steps, which we’ll get into in future posts.

NOTE: All steps are carried out at Publisher instance

Replication Step	T-SQL Step
1. Remove Publication, Subscriptions, etc a. Remove subscription to each subscriber b. Remove subscription with articles c. Remove articles associated with the publication d. Finally, remove the Publication	1. Run Publisher Instance a. sp_dropsubscription b. sp_dropsubscription c. sp_droparticle d. sp_droppublication
2. Change database settings a. Disable database from Publishing b. Remove associations with all Subscribers	2. Run at Publisher a. sp_replicationdboption b. sp_dropsubscriber
3. Distributor Settings a. Remove association with Distributor	3. Run at Publisher a. sp_dropdistributor

In a future post, we’ll get into the next set of details.

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server : Distributor Setup for Replication using T-SQL

Interesting one today:

This is part of the series on Replication set up using T-SQL. The full list is here.

Today, we’ll go over setting up Distributor using T-SQL. This is the first step in our replication configuration process.

The major steps in setting up Distributor are:

1. Configure an instance as Distributor
2. Create Distributor database
3. Add the instances that will use this instance as a distributor

For this example, we’ll move forward with remote distributor a.k.a. Publisher, Subscriber & Distributor are on dedicated instances.

To accomplish the above steps, we use the following T-SQL procedures:

1. sp_adddistributor
2. sp_adddistributiondb
3. sp_adddistpublisher

1. sp_adddistributor

For setting up replication, the first step we need to configure is setting up Distributor. Go to the Distributor instance, and enable the instance as a Distributor.

--
-- Enable the instance as Distributor
--
use master
exec sp_adddistributor    @distributor = N'InstanceName'
	 					, @password = N'distributor_admin password'
GO

2. sp_adddistributiondb

Next step is to create Distribution database in the Distributor instance to hold all the replication traffic.

--
-- Create Distribution database
--
use master
exec sp_adddistributiondb @database = N'SalesDistribution'
			, @data_folder = N'E:\MSSQL\Data'
			, @data_file = N'SalesDistribution.mdf'
			, @data_file_size = 4096
			, @log_folder = N'E:\MSSQL\Data'
			, @log_file = N'SalesDistribution.LDF'
			, @log_file_size = 2048

			, @min_distretention = 0
			, @max_distretention = 120
			, @history_retention = 120

			, @security_mode = 1
GO

Most of the parameters are self-explanatory; So, we’ll look at brief descriptions.

Following set of parameters indicate the name of the Distribution database with location for its data & log files with initial sizes.

• •  @database
  • @data_folder
  • @data_file
  • @data_file_size
  • @log_folder
  • @log_file
  • @log_file_size

The next set of parameters, indicate the duration for retention of replication traffic (transactions & commands) and retention for history log entries.

• • @min_distretention
  • @max_distretention
  • @history_retention

The last parameter, shows the authentication mechanism for communicating with the Distributor.

• @security_mode : 1 indicates Windows Authentication; 0 indicates Sql Authentication (default)

3. sp_adddistpublisher

Now that Distribution database is configured, lets inform the distributor about the Publishers that it will rely on it (this Distributor).

--
-- Associate Distributor with Publishers
--
exec sp_adddistpublisher  @publisher = N'PublisherName'
			, @distribution_db = N'SalesDistribution'
			, @publisher_type = N'MSSQLSERVER'
			, @working_directory = N'E:\MSSQL\ReplData'

			, @security_mode = 1
			, @thirdparty_flag = 0
GO

The first four parameters indicate the name of the Publisher instance; And if it is Sql Server Publisher (or Oracle, etc); Working directory indicates where the replication data is stored while being communicated between Publisher and Distributor; And the name of the Distribution database.

• •  @publisher
  • @distribution_db
  • @publisher_type
  • @working_directory

The next two parameters indicate the authentication mechanism used by Replication Agents to communicate with Publisher (for Queued Updating Subscriptions); And

• • @security_mode
  • @thirdparty_flag : Indicates if Publisher is Sql Server or non-Sql Server instances (i.e. Oracle, etc)

Hope this helps,
_Sqltimes

Read Full Post »

Sql Server: Set up Replication using T-SQL (procedures used to set up replication)

Interesting one today:

This is Part 1 on blogs related to Replication Setup. Full list is here.

Quite often, we setup replication on our lab machines to replicate production environment settings. We have one “Gold” version of scripts that are used every time. Today, w’ll cover the fundamental procedures & their related stored procedures:

Create Replication

Essentially, there are 4 fundamental steps to creating replication. Some steps are carried out pointed to Distribution instance, some pointed to Publisher instance; But there are no steps pointed to Subscriber instance.

1. Configure an instance as Distributor
2. Configure an instance as Publisher
3. Configure a database as Publisher & Create Publication
4. Configure Subscription

As you can imagine, there are several sub-steps in each of these. We’ll get into details about the sub-steps in a new post.

Replication Step	T-SQL Stored Procedure
1.Distributor a. Configure an instance as Distributor b. Create Distributor database c. Add the instances that will use this instance as a distributor	1. Run at Distributor instance a. sp_adddistributor b. sp_adddistributiondb c. sp_adddistpublisher
2. Configure Publisher a.  Configure the instance to be Distributor b.  Configure the Subscribers 3. Configure Publisher Database & Publication a. Enable the database as a Publisher b. Create LogReader Agent c. Configure publication d. Assign permissions on this Publication e. Add articles (tables, SP, etc) 4. Configure Subscription a. Add subscribers for this publication b. Create the distribution agent	2. Run at Publisher instance a. sp_adddistributor b. sp_addsubscriber 3. Run pointing to Publisher database a. sp_replicationdboption b. sp_addlogreader_agent c. sp_addpublication d. sp_grant_publication_access e. sp_addarticle 4. Run pointing to Publisher database a. sp_addsubscription b. sp_addpushsubscription_agent

In a future post, we’ll get into the next set of details.

Hope this helps,
_Sqltimes

Read Full Post »