Replication features in various editions of SQL Server 2005/2008

Reference Link:

http://blogs.msdn.com/b/repltalk/archive/2011/01/03/replication-features-in-various-editions-of-sql-server-2005-2008.aspx

Crisp information on SQL Server Replication Features availibility and limitations when using SQL Server 2005 and SQL Server 2008. I am sure this information is still useful in environments using older versions like SQL 2005 and 2008.

Snapshot from the above link shows the following… happy reading!

Feature	Express	Express Advanced	Workgroup	Standard	Enterprise	Web	Evaluation	Developer
Merge Replication	Subscriber only	Subscriber only	<= 25 subscribers	Y	Y	Subscriber only	Y	Y
Transactional / Snapshot  Replication	Subscriber only	Subscriber only	<= 5 subscribers	Y	Y	Subscriber only	Y	Y
P-P Transactional Replication	N	N	N	N	Y	N	Y	Y
Oracle Publishing	N	N	N	N	Y	N	Y	Y

Replication Architecture – Part 1

Replication was something which I always wanted to explore… luckily had the opportunity of doing just that. To start off with anything that is new, we need to understand the “Architecture” and also working.

I have tried to put my understanding in words on the “Replication Architecture”.

First of all, What is Replication?
In simple terms: Replication is the process of sharing data between databases in different locations. Using replication, we can create copies of a database and share the copy with different users.  This allows to make changes to local copy of the database and later synchronize the changes to the source database.

Next Question would be… Why do we opt for Replication?
Again, there can be many reasons for this; I have tried to capture few very valid reasons.
The primary concern for any organization is often the protection and availability of its data. Without reliable access to secure and relevant data, the smooth operation of a company comes to painful, unprofitable halt. Data should not only be secure, but also be accessible after a system or catastrophic failure. Replication – offers this accessibility.
In addition, Database replication can also supplement disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, applications can switch to the replicated copy of the data and continue operations.

Now, we understand What is Replication and Why do we opt for Replication… The next would be to understand “How does Replication work”, “What are the common entities used in deciding the type of replication?”, “What are the types of Replication available or rather provided by MS” and much more…

So, let’s move on understanding “How does Replication work”:

Microsoft SQL server uses publishing industry model to represent the components and processes in replication architecture. The working is also similar to how publishing industry works(we can think of “Daily Newspaper as an example).
Publishing industry publishes Magazines/Books; there are Distributors and Agents who carry these publications to the Subscribers. Subscribers of the magazine obtain copies of the publication and read the articles of interest to them; this is exactly how the SQL Server Replication model works.

We can identify the following Entities for the SQL Server replication model.

Publisher
Distributor
Agent
Subscriber
Articles
Publications
Subscriptions

We will start getting into each of the above listed entities in my next blog… till then enjoy reading and also get some kind of know-how on types of replication provided by MS SQL Server!

Agent message code 21036: Another distribution agent for the subscription (s) is running or the server is working on a previous request by the same agent.

Error: Agent message code 21036. Another distribution
agent for the subscription(s) is running or the server is working on a
previous request by the same agent.

Source of the error: We may notice Replication failure due to faulty distribution agent jobs. So, when we try to start the Distribution Agent job; the distribution agent job history details the error as shown above.

This means the distribution agent is not running fine. So, where do we start to isolate the issue.

WorkAround: One easy way of tackling this issue is by finding the processes running related to the distribution agent job. How? By using a simple query as shown below:

But before that make sure to stop the job if running. (Most of the cases it would have been stopped)

select * from master..sysprocesses where program_name = ‘Name_of_the_agent_job’;

Note: Make sure to run this on the Distribution server.

This will list all the active processes related to the agent job if any. Make sure to kill that process and recheck again if any agent processes are active or not. Once confirmed that there are no processes running related to the agent job we can restart the agent job.

This will be successful and no errors will be received.

Note: The workaround presented above is only with respect to local environment and can be used as a workaround. But cannot guarantee the same. Make sure to check with your local team before trying this out.

Instance 2: Another scenario where in I came across the above error.
There can be instances where we might have configured distribution agents as part of SQL Agent jobs with different names running at different time intervals and have totally forgot the same. This only happens when we have our own configured and do not use the default jobs created. So always make sure to check the distribution agent jobs created specific to our need.