Replication

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

When you configure replication subscriber and if your subscriber instance is in higher version than publisher instance you might get below error

Example when you configuring subscriber on SQL Server 2014 instance for SQL Server 2012 Publisher  using SQL Server 2012 SSMS you would get below error.

Error:

The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.

Resolution:

Create subscriber using higher version SSMS (SSMS has to be subscriber version or higher) or create subscriber using TSQL.

Advertisement

How to create merge replication in SQL Server

How to create merge replication in SQL Server?

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication”

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

clip_image001

New publication wizard appears.Click Next and continue.

 

clip_image001[4]

Select the database that contains the data or objects you want to publish.Click Next.

clip_image001[6]

There are different types of publication.

Select the publication type that supports your application.Click Next.

clip_image001[8]

Specify the SQL Server version that will be used by subscribers for this publication.Click Next.

clip_image001[10]

Select the tables to publish as articles.Click Next.

clip_image001[12]

If you don’t have unique identifier in the table.Then SQL Server will automatically add unique identifier to the table.Click Next.

clip_image001[14]

Add filters to exclude unwanted rows from published tables.Click Next.

clip_image001[16]

Specify when to run the snapshot agent and click Next.

clip_image001[18]

Select security settings.

clip_image001[20]

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

clip_image001[22]

After selecting the account for snapshot agent and log reader agent,click Next.

clip_image001[24]

Check create the publication.Click Next.

clip_image001[26]

Give the publication name and finish the wizard.

clip_image001[28]

Close the window after creating publication successfully.

clip_image001[30]

 

clip_image001[32]

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

clip_image001[34]

 

New subscription wizard window appears.Click Next and continue.

clip_image001[36]

Choose the publisher and click next.

clip_image001[38]

Select the distributor agent location.Click Next.

clip_image001[40]

Select subscription database which you created.The database should be empty database.Click Next.

clip_image001[42]

Specify the process account and connection option for each distribution agent.

clip_image001[46]

Specify the domain or machines account under which the distribution agent process will run and click ok.

clip_image001[44]

 

Schedule the agent and click Next.

clip_image001[48]

Specify the type of subscription as client or server and click Next.

clip_image001[50]

Check create the subscription and click Next.

clip_image001[52]

Click Finish and complete the wizard.

clip_image001[54]

Close the window after creating subscription.

clip_image001[56]

Successfully we have created publisher,distributor and subscriber for merge replication.

clip_image001[58]

How to start replication agent in command prompt.

In this blog I will explain how to run replication agents from command prompt and enable additional parameters for agent’s while running from command prompt.

All the replication agent’s  (Snapshot agent, Logreader agent, distributor agent, Merge agent) can be run from command prompt.

All these replication agents will have corresponding exe in C:\Program files\Microsoft SQL Server\100\COM  folder

Snapshot agent –>Snapshot.exe

Logreader agent—>Logread.exe

distributor agent  –>Distrib.exe

Merge agent  –>Replmerge.exe

Before we start running the replication agents from command prompt we have to stop and disable the corresponding agent job in management studio by expanding jobs->Right click job->Stop Job

clip_image001[20]  

After stopping the job.Disable it.

clip_image001[22]

Copy the folder path in which replication agent exe’s are located ( C:\Program files\Microsoft SQL Server\100\COM  I have highlighted the agent binaries for visibility)

clip_image001[4] 

Open a command prompt and Change the directory to C:\Program files\Microsoft Sql Server\100\COM in command prompt.

clip_image001

Running Logreader agent from command prompt:

Double click Log reader Job.

  clip_image001[6]

Select steps –>Double click Run agent.

clip_image001[10]

Copy the command and paste it in command prompt.

clip_image001[8]

To run Log read agent from cmd  type logread.exe in command prompt.Then paste the command that you copied from Job step properties.

clip_image001[12]

We can monitor the progress of agent from  command prompt.

clip_image001[14]

We can also add additional parameters when we run the agents from command prompt. I will show you how to add  verbose level parameter to increase the level of logging when running agent from CMD. (–Outputverboselevel 3 increases the level of logging by replication agents).

clip_image001[40]

Like below.

clip_image001[42]

Running Replication distributor agent:

Double click the distributor job.

clip_image001[16]

Then select steps->Run agent.

clip_image001[18]

Copy the command.

clip_image001[24]

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give distrib.exe –>copy the command from Job step properties.

clip_image001[28]

Now we could start distributor agent in command prompt itself.

clip_image001[30]

Running Snapshot agent:

Double click snapshot agent.

clip_image001[32]

Select Steps->Run agent.

clip_image001[34]

Change the directory to C:\Program files\Microsoft SQL Server\100\COM.

Then give snapshot.exe –>copy the command from Job step properties.

clip_image001[36]

Then we could start the snapshot agent in command prompt.

clip_image001[38]

How to create transactional replication in SQL Server

How to create transactional replication in SQL Server?

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber.

In  transactional replication  snapshot of the publication database objects and data is saved.If there is any data changes and schema modifications made at the Publisher,it will be delivered to the Subscriber. The data changes are applied to the Subscriber in the same order and within the same transaction boundaries as they occurred at the Publisher.Therefore, within a publication, transactional consistency is guaranteed. 

Transaction replication has three agents namely,Snapshot agent,Log reader agent and distribution agent .

The Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder.

The  Log Reader Agent monitors the transaction log of each database that is configured for transactional replication and copies the transactions from the transaction log into the distribution database.

The  Distribution Agent is an executable that moves the snapshot and the transactions held in the distribution database tables to the destination tables at the Subscribers.

For all types of replication it is mandatory to create distribution for the publisher instance before configuring replication. For configuring distribution follow the steps in “How to configure distribution for replication”

Once the distribution is configured follow the below steps to create publisher

Let us create New publisher:

Right click Local publication->New Publication

 clip_image001[16]

New publication wizard appears.Click Next and continue.

clip_image001[18]

Select the database that contains the data or objects you want to publish.Click Next.

clip_image001[20]

There are different types of publication.

Select the publication type that supports your application.Click Next.

clip_image001[22]

Select the tables to publish  as articles.Click Next.

clip_image001[24]

Add filters to exclude unwanted rows from published tables.Click Next.

clip_image001[26]

Specify when to run the snapshot agent and click Next.

clip_image001[28]

Select  security settings

clip_image001[30]

Specify the domain or machine account under which the snapshot agent process will run.

Select Run under the SQL Server agent service account.Click ok.

clip_image001[32]

After selecting the account for snapshot agent and log reader agent,click Next.

clip_image001[34]

Check create the publication.Click Next.

clip_image001[36]

Give the publication name and finish the wizard.

clip_image001[38]

Close the window after creating publication successfully.

clip_image001[40]

Let us create the subscribers:

Before creating subscribers create one empty database where we could subscribe the publisher database.

Right click Local subscriptions->New subscriptions’

clip_image001[42]

New subscription wizard window appears.Click Next and continue.

clip_image001[44]

Choose the publisher and click next.

clip_image001[46]

Select the distributor agent location.Click Next.

clip_image001[48]

Select subscription database which you created.The database should be empty database.Click Next.

clip_image001[50]

Specify the process account and connection option for each distribution agent.

clip_image001[52]

Specify the domain or machines account under which the distribution agent process will run and click ok.

clip_image001[54]

Schedule the agent and click Next.

clip_image001[56]

Initialize the subscription as immediately and click next.

clip_image001[58]

Check  create the subscription and click Next.

clip_image001[60]

Click Finish and complete the wizard.

clip_image001[62]

Close the window after creating subscription.

clip_image001[64]

Launch replication monitor to view the connection.

To open Replication monitor Right click Replication->Launch replication monitor. 

clip_image001[66]

Successfully we have created publisher,distributor and subscriber.

clip_image001[68]

How to configure distribution for replication

Replication allows as to automatically distribute copies of data from one server to one or more distributor server.In replication we will have Distributor,Publisher and Subscriber. 

First we have to configure a Distributor to set up replication.Distributor is a server that contains the distribution database.It  stores all the transactions for transactional replication and meta data for all types of replication .  Each Publisher can have only one Distributor instance.But multiple publishers can share one Distributor.We can also Configure a centralized Distributor for multiple Publishers.

There are two types of distributor

1.Local distributor.

2.Remote distributor. 

Let us see how to configure distribution:

Right click Replication->Configure distribution

clip_image0014_thumb1

Configuration Distribution wizard window will appear.Click Next and continue.

clip_image0016_thumb2

Select the distributor which will act as server and responsible for storing replication information used during synchronizations. If you want to choose  remote  distributor select the second option.

image_thumb3

Configure SQL Server agent to start  automatically.To start the sever agent automatically, when computer is started.Click Next and continue.

image_thumb6

Specify the distribution database name.Also specify the valid path for database file and log file.Click Next

clip_image0018_thumb1

Enable the servers which will use the distributor when they become publishers.

clip_image00110_thumb1

In complete wizard window,we will get the options which we selected during the process.Click Finish.

image_thumb5

Close the wizard and complete the distribution database creation. 

clip_image00114_thumb1

by

The merge process was unable to create a new generation at the ‘Subscriber’

The merge process was unable to create a new generation at the ‘Subscriber’

Merge replication fails with below error

Error:
The merge process was unable to create a new generation at the ‘Publisher’

Unable to decide if MakeGeneration is needed

The merge process failed to execute a query because the query timed out. If this failure continues, increase the query timeout for the process. When troubleshooting,restart the synchronization with verbose history logging and specify an output file to which to write.

 Verbose Error log:
The merge process was unable to create a new generation at the ‘Subscriber’. Troubleshoot by restarting the synchronization with verbose history logging and specify an output file to which to write.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147200994)Get help: http://help/MSSQL_REPL-2147200994
Resolution:
Change the generation_leveling_threshold to 0 in sysmergepublications table in publisher and in subscriber. Do not update the system tables directly.
Follow the steps mentioned in workaround section of http://support.microsoft.com/kb/953568

by

Update,Insert or Delete on tables at subscriber fails in transactional replication

When you update/insert/delete rows in table in subscriber which is part of transactional replication with updateable subscriber we will end with below error
{
Msg 14126, Level 16, State 3, Procedure sp_check_sync_trigger, Line 28
You do not have the required permissions to complete the operation.

Msg 20512, Level 16, State 1, Procedure sp_MSreplraiserror, Line 8
Updateable Subscriptions: Rolling back transaction.
Msg 3609, Level 16, State 1, Procedure trg_MSsync_upd_State, Line 133

The transaction ended in the trigger. The batch has been aborted.
}
Above error is raised in sp_check_sync_trigger and according to the below logic in “sp_check_sync_trigger”  
If you have a update/delete/insert trigger in a table which is not part of replication (MSreplication_objects) then we raise “You do not have the required permissions to complete the operation.”
{

if not exists (select so.object_id from (dbo.MSreplication_objects as ro join sys.objects as so 
        on ro.object_name = so.name) 
        where so.object_id = @trigger_procid and ro.object_type = ‘T’  
        and (@owner is null or schema_name(so.schema_id) = @owner)) 
    begin 
        raiserror(14126, 16, 3) 
        return –1 

   end
}
Resolution
Identify all the triggers of the table for which you are not able to update/delete/insert rows and identify the trigger which is not part of MSreplication_objects and disable it.
You can use the below query to identify triggers in table which is not part of replication

select * from sys.objects where type=‘TR’ and name not in (select object_name from  MSreplication_objects)
and parent_object_id in (select OBJECT_ID(‘Replace your Table here’))

       

by

The article ‘(null)’ does not exist

Trans Replication is failing with error while adding the subscription

Error:  “The article ‘(null)’ does not exist.”
This seems to be know issue when there is difference  is number of article between sysarticles and msarticles.
– select count(*) from sysarticles on publisher
– select count(*) from MSarticles on distributor

Once you get the articles which exists in one table and not in other use exec sp_droparticle to remove that article and add it back using exec sp_addarticle

Note:We might get this error if we have two tables with same name in databases with case sensitive collation and replicate them (Ex: table and TABLE )

by

Invalid column name ‘originator_id’. (Microsoft SQL Server, Error: 207)

Create replication fails on database which is migrated from SQL Server 2005 with error

Error:

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Invalid column name ‘originator_id’. (Microsoft SQL Server, Error: 207)

Resolution:
1.  Add originator_id  column to syspublications table in publication db. Run below query in your   publication DB.

    ALTER TABLE dbo.syspublications ADD [originator_id] [int] NULL;

2. Add a column originator_id  into the view dbo.syspublications inside distribution database

3. Add a originator_id  column into IHpublications table in distribution database