Setting up Transactional Replication in SQL Server 2008 R2.
4. Select Transactional Replication from the available publication type and Click on the Next> button as shown in the screen capture below:
5. Select the Objects that you want to publish. In this example, we will select a table named Person which we need to Replicate. Select the table as shown in the screen capture below and Click on the Next> button. One important point to note is that Only those tables can be replicated in Transaction Replication which has a Primary Key column in it.
6. Since there are no filtering conditions, Click on the Next> button as shown in the screen capture below:
7. Check the first radio button as shown in the screen capture below and Click on the Next> button.
8. Click on the Security Settings tab as shown in the screen capture below.
A new window appears as shown in the screen capture below.
Select Run under the SQL Server Agent service account as the account under which the Snapshot Agent process will run and Connect to the publisher By impersonating the process account as shown in the screen capture below and then Click on the OK button.
Click on the Next> button as shown in the screen capture below.
9. Click on the Next> button as shown in the screen capture below.
10. Give a suitable name to the publisher ad Click on the Finish button as shown in the screen capture below.
Creating the Subscriber
Once the publisher is created the next step is to create the subscriber for it.
The following steps needs to be performed for creating the subscriber.
1. Right Click on the publisher created and select New Subscriptions as shown in the screen capture below.
2. Click on the Next> button as shown in the screen capture below.
3. Click on the Next> button as shown in the screen capture below.
4. Click on the Next> button as shown in the screen capture below.
5. As shown in the screen capture below, it asks for the Subscriber name as well as the subscription database. The subscriber database can be created by restoring the publisher database at the start itself or by creating a new database as shown in the screen capture below.
If you have already restored the backup of the database which is a publisher, then the database name will appear in the dropdown as shown in the screen capture below:
If we wan’t to now create the subscriber database then it can be done as follows:
Click on New Database as shown in the screen capture below.
A new window appears as shown below. Give a suitable database name as well as the path where the data and log file are going to reside.
Click on the OK button.
If the subscriber is some other server, then the following steps need to be performed.
Click on the down arrow available on the Add Subscriber button as shown in the screen capture below.
Click on Add SQL Server Subscriber as shown in the screen capture above.
A new window appears which asks for the SQL Server Name as well as the Authentication neeed to connect to the SQL Server, please refer the screen capture below.
6. Click on the Next> button as shown in the screen capture below.
7. Click on the button as shown in the screen capture below. Here we need to specify
Process account as well as the connection options for the distribution agent.
Well Done. Simple but yet descriptive.
ohh my god sahi re sahi hai keep it up
Thank you for this. Have been looking for a clean explanation of setup.
Nice stuff, Walked me through a full setup for my class model.
Thanks very much
James
complete step by step explanation. Good job..
thank you , but you didnot added this subscribtion in another server as you told us at the first of the lesson
Thanks a lot This is very useful for me. Once again say thanks for this kind of assistance.
Thanks a lot for the step-by-step setup!!
Helped me very much.
Thank you very much, your explanation helped me a lot
Great job 
Thanks for your efforts and wonderful explanation!
Thanks a lot. Good job.
Nice post!
zee
http://walisystemsinc.com
Nice, helped me very much.
Christian
Thank for your Article!!!!
helped me Very Much
Thank you good one
this is really helpfull
thanks
Siraj
Could you please elaborate why you chose to ignore the security recommendations to choose separate accounts other than the service account?
excellent job very very thanful to you for this tutorial sir .
thanks a lot
i could like to have more tutorial
owesom doccument.it is very easy to understand…thanks a lot
Nice one..really helpful
The publisher doesn’t/shouldn’t care about the exact location of the subscriber’s database files, right? For example, once the subscriber has been set up and replication has been going on for some time, if I move the database files underlying the subscriber database, will that break the transaction replication?
Very Nice Job; Thanks a lot. I wonder if this procedure can be done with SQL Express? I know it’s not but maybe there is a way to tweak the application.
Great tutorial, got me started real quick. Thanks Satnam!
Excellent tutorial. Thank you very much.
Need to Know more on “Always On High Availability ” Feature. its new to task to me in SQL Server 2012.
nice article
Thanks. it is good.
can you share about clustering in SQL Server 2008 R2
Superb explanation for each steps. Good work!!
thanks its useful
owesom document.it is very useful to me…thanks a lot
It throws me an error, when configuring the distributor.
It says, ‘ SQL server could not be configure rajan\sqlexpress’ as a Distributor.
So kindly help me, what mistake am i doing. And why it is coming for me.
Exception as
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Thanks its very Usefull
Thanks. Its a very useful for me. Whatever i missed I recover from ur article. Thanks Again.
hi , Thanks for very useful guide.
i have question about this.
is it possible my publisher server hosted out of my network and doesn’t have valid IP?
hi…
but i am use subscribtion in another server,in this case work only local System not network.
can hlep me how to subscribtion in another server done Succeffuly.
thanks
kamal
Hi,
How can we add articles to existing publishers? is it with only Sp_addarticle or any GUI way? can you please reply?
What happens if you add another publisher to the same database?
Does SQL server try to add a new identity column and then bomb out?
Must you back up the Publisher database and restore the database to the subscriber before creating a new Publisher?
Or would you simply create a new Publisher and let the snap shots update the Subscriber database.
Thank you so much for giving us very good knowledge…….
Good work. Great Thanks.
Thanks. it is good.
Thanks . its very Nice
Excellent Guidance !!! Thanks.
The way of presentation is good..Keep it up.
Thanks for ur time.
Great Explanation, thank you so much
Cheers
This is very clear concept
Hi,
Need more details with steps…
I have an two Active/Active SQL database servers 2008 with STD edition in my Lab server. Wanted to configure the Trasactional Replication and wanted to replicate the data from server A to Server B and Server B to Server A….i have tried in my lab server but facing some chanllanges… while replicatiing the data from the Server B to server A its givinf me the error through Application…below is the details.
SqlException: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint ‘PK_STATE_AUDIT’. Cannot insert duplicate key in object ‘dbo.STATE_AUDIT’. The duplicate key value is (3)
so need ur assistance to solve it. as i know it is talking about the primary key but dont know how to resolve it and wht to do…new for Database.
With regards
Narsingh reddy
Hi Narsignh,
//Need more details with steps…//
The failure cause is very clearly given in error. Please check the destination table’s(STATE_AUDIT’) column in which primary key is defined. Since Primary key column should not have any duplicate values, the reason your application is failing.
Check for which data you are getting the error and if needed delete the particular row and try again.
Regards,
Sajal Bagchi