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 availability 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

SQL Server Replication - Configuring Distributor

When setting up replication in SQL Server, the first thing to set up is Distributor.
The Distributor is a SQL server instance that contains the distribution database, which stores metadata and history data for all types of replication and transactions for transactional replication.

Properties of Distributor

• Each Publisher can be assigned to a single Distributor instance only
• Multiple publishers can share a single Distributor

To Configure Distributor:

1. Connect to the SQL Server instance using Management Studio which you want to make as Distributor

Replication Architecture - Part 2

Let me get straight into the topic "Replication Architecture - Part 1"discussed last time; about the working of replication and then the entities involved in any replication model for that matter. We also did a understanding on publishing industry in comparison with replication which helped us in identifying the entities.

Following are the same:

• Publisher
• Distributor
• Agent
• Subscriber
• Articles
• Publications
• Subscriptions

So, let us get into each of this with respect to Replication.
A SQL server instance can be configured as Publisher or Distributor or subscriber.


Publisher: In simple terms, Publisher is the server on which the "Source" database resides. It is the main source for data and also identifies what data should be distributed across.

Distributor: gathers all the published data and holds the same until it sends it across to all the registered subscribers. It is also referred to as "Bridge" between publisher-subscriber and can support multiple publisher and subscriber concept.

Subscriber: Is the final destination on which data has to be transmitted.

Articles: This is the basic unit of replication. An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects.

Publication: A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

Subscription: A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions, push and pull.

Agent: An agent is a median which identifies the changes at publisher and transfers those changes to subscriber.

There are different agents in replication,

• Distribution Agent
  
• Log Reader Agent
  
• Merge Agent
  
• Queue Reader Agent
  
• Snapshot Agent

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!

T-SQL Query to find List of Tables that do not have Primary Key

We know that Primary Key is a must for setting up articles in Transactional Replication.
This query list those tables which do not have Primary Key in that database.

USE <DatabaseName>
GO
SELECT SCHEMA_NAME(schema_id) AS [Schema Name], name AS [Table Name]
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
Order by name
GO

Replication Error - SQL Server replication requires the actual server name to make connection to the server

Sometimes when we try to configuration relpication we might receive the below error which says that "SQL Server replication requires the actual server name to make connection to the server"

This can happen during the situations

1. When you are connected to the server in object explorer using the IP address
2. When the actual server host name hosting the SQL server database engine is changed

In the first case we can solve this issue by connecting to the server with the host name.

In the second case, to fix the issue we need to update the system catalogs by dropping and adding the server and restarting the SQL Server services. The updation of system catalogs can be done using the below queries.

USE master 
GO
EXEC sp_dropserver 'OldServerName'

USE master 
GO
EXEC sp_addserver 'NewServerName',local

sp_refreshsubscriptions - An useful Transactional Replication Stored Procedure

Consider you are having a Transactional replication setup in a Live environment with a huge published database (For example, more than 50 GB publisher database size) , and you want to add an article to this existing publication without generating the snapshot for the all the articles. Also consider that the size of the new article that you are going to add is only 2 MB.:)
Now think, Do you really prefer to generate and apply the snapshot for the complete 50 GB database just for adding a 2 MB article?
I would really not prefer this as the environment is Live and its requires more time to complete the process of generating and applying snapshot. Then how will you achieve this?

You can do this by using the unpopular Transactional Replication Stored Procedure sp_refreshsubscriptions.
This stored proc expects a parameter "Publication Name" and has to be run on the publisher database.
When you run this on the publisher, it will mark only the newly added articles for generating snapshot.

Syntax:

exec sp_refreshsubscriptions 'PublicationName'

GO

Example:

-- Adding the transactional articles

use [AdventureWorks]

exec sp_addarticle @publication = N'TestAdv', @article = N'Contact', @source_owner = N'Person', @source_object = N'Contact', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Contact', @destination_owner = N'Person', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [dbo].[sp_MSins_PersonContact]', @del_cmd = N'CALL [dbo].[sp_MSdel_PersonContact]', @upd_cmd = N'SCALL [dbo].[sp_MSupd_PersonContact]'

GO

--Refresh Subscriptions

exec sp_refreshsubscriptions 'TestAdv'

GO

After running the above commands, run the snapshot agent.

Before running the sp_refreshsubscriptions SP, make sure that the publisher properties "allow_anonymous" and "immediate_sync" are set to "False", if these 2 options are set to "True" then this SP will mark all the articles for generating snapshot instead of marking only the newly added articles.

To Check the publication properties, use this query.

exec sp_helppublication 'PublicationName'
GO

If the values of the output columns "allow_anonymous" and "immediate_sync" are 0 then they are set to "False" if their values are 1 then they are set to "True"

To Change the publication properties for "allow_anonymous" and "immediate_sync", use this query

EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'allow_anonymous',

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'PublicationName',

@property = N'immediate_sync',

@value = 'false'

GO