question

Chitrarekha avatar image
Chitrarekha asked

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.
replication
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image
SirSQL answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Chitrarekha avatar image
Chitrarekha answered
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.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

SirSQL avatar image SirSQL commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.