x

Build and test Transactional Replication

I have followed the link http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/3/to build Publication and Replication. How do I test Transactional Replication? I have build replication for anly one table in a database. But how do I build replication for all databases in an instance. I have one instance which has about 13 databases. How do I build Transactional Replication for the tables that does not have primary key.
more ▼

asked Jul 31, 2012 at 02:05 PM in Default

Chitrarekha gravatar image

Chitrarekha
140 32 42 43

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

To replicate the other databases just follow the same steps as you did for the first.

Testing replication is as simple as making a change on your publisher and looking to see if that change also made it to your subscriber (insert/update/delete a row).

You cannot use transactional replication with tables that do not have a primary key. Consider adding a PK, using snapshot replication, using CT/CDC along with SSIS to move changed data or building some form of service broker migration along with CT.
more ▼

answered Jul 31, 2012 at 02:49 PM

SirSQL gravatar image

SirSQL
4.8k 1 3

(comments are locked)
10|1200 characters needed characters left

You cannot use transactional replication with tables that do not have a primary key. Consider adding a PK, using snapshot replication, using CT/CDC along with SSIS to move changed data or building some form of service broker migration along with CT.

Can you please find me a link or explain the above statement.
more ▼

answered Jul 31, 2012 at 04:41 PM

Chitrarekha gravatar image

Chitrarekha
140 32 42 43

I'm not sure about further explanation here.

Snapshot replication can be used for tables with no PK. The downside being that it moves the entire table and will drop/recreate on the subscribing side which can cause problems.

Change Data Capture (CDC) and Change Tracking (CT) keep track of changes on tables and can be used to build manual processes to make changes on remote servers using SSIS or Service Broker. This is not something I have done myself, so I don't have any pertinent links. A search engine should provide you some good information around doing that.
Jul 31, 2012 at 04:48 PM SirSQL
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x305

asked: Jul 31, 2012 at 02:05 PM

Seen: 688 times

Last Updated: Jul 31, 2012 at 04:48 PM