SQL Server 2008 data replication

Question

Hi,

I have a requirement to replicate the data between 2 database servers.

I have one server (S1) and 2 database (D1,D2) on a machine

       S1 ---> D1 (Database 1)

       S1 --> D2 (Database2)

(these 2 database D1, D2 are in the same server S1), 

I need to replicate the data for 25 tables(total number of tables are 300+) only from D1 --> D2 , every 4 hrs of interval.

If possible pls let me know how to achieve this using "SQL Server Management Studio 2008 R2"

Regards

Muthu

Answer 1

Hi Muthu,

If you will have average data changes and your destination database should be accessible at all times then the best is for You to create transactional replication between these two databases. In this case, the server S1 will serve as publisher, distributor and subscriber.

When you keep the interval as 4 Hours, you might want to consider the growth of the transaction logs due to the replicated contents which will effectively reduce the chance of log reuse.

How to create this is normal like creating replication between any servers, just that you specify the same server as subscriber.

I think this link should help you with the steps.

You can also go with snapshot replication, but if the data set is huge i wont prefer it.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 2

I concur with Ashwin - transactional replication is the way to go. Here is a tutorial on how to use it.

http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 3

Thanks Ashwin, Hilary for your comments, normally replication happen for all the database objects from one database to another.

In my case I need to replicate only the 20 tables from D1 --> D2.

Can you pls let me know if this can be done, If you have any other suggestion other than replication also pls do suggest.

Regards

Muthu

Answer 4

When you configure replication in the screen where you select articles dont select all the article, just select what you want and it will only replicate the ones required. In the below screen if you uncheck column 2 then column2 wont get replicated.

HTH

---

Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

Answer 5

With  replication you can select a subset of the total tables in your database to replicate.  In the image Ashwin has posted you will see a check box besides the tables (as well as the columns). You can deselect or select tables you wish to replicate.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941