question

Chitrarekha avatar image
Chitrarekha asked

Replicating the database

I have followed the link below http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/ to create replication database but it is replicating only one table in a database. But how do I replicate all the databases in the same instance.
sql
10 |1200 characters needed characters left characters exceeded

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

KenJ avatar image
KenJ answered
On page 2 of the article is the new publication wizard where you select "articles" (tables) to be published. Here is the image - [ http://c3154802.r2.cf0.rackcdn.com/article_images/transactional_replication_sql_server_2008r2_files/image015.jpg][1] You can see in that image that only one table is selected for publication. When you get to this screen during your setup, select all of the tables you want to replicate [1]: http://c3154802.r2.cf0.rackcdn.com/article_images/transactional_replication_sql_server_2008r2_files/image015.jpg
10 |1200 characters needed characters left characters exceeded

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
Based upon the article you followed you would check all of the table that you want to replicate (note that for transactional replication you will only be able to check tables with a primary key). You might want to create multiple publications to ease the load of snapshots or if you need to make changes. This will all depending on the number of tables you want to replicate and the size of each table.
10 |1200 characters needed characters left characters exceeded

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
Each database has 100 tables so what should be the design mode of setting up the replication. Should I setup transactional or merge replication.
1 comment
10 |1200 characters needed characters left characters exceeded

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

Transactional and merge replication serve two different functions. You would use transactional to offload queries to a remote server and help reduce the load on your key OLTP system, or to consolidate data from multiple sources to a single data repository (while not exhaustive this is the primary usage). Merge replication is designed to provide you with a distributed architecture and provide the ability to write in multiple location s and sync that data up. It can have significant levels of overhead depending upon the method of conflict resolution used and you need to be sure that you are fully aware of the potential pitfalls as regards your application and data usage patterns.
1 Like 1 ·

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.