5

I need a good way to replicate a single table in SQL Server 2012 between different servers and different databases. I tried replica which works just fine, but there might be a much better / easier way which I don't see right now.

Maybe there is a way to use the task to import data as a job. The data does not need to be synchronized all the time. It is fine that the second database [B] gets updated with the data from database [A] every 5-10 minutes.

Is it possible to setup a mirroring for only one table?

Of course I checked the web which gave me the idea to script the import task.

What else is possible and how?

marc_s
  • 8,932
  • 6
  • 45
  • 51
RayofCommand
  • 1,353
  • 5
  • 15
  • 30
  • Mirroring is not possible for one table its for whole database. – Shanky Feb 13 '15 at 11:29
  • 1
    How big is the table ? You can use SSIS and do Incremental Loads every 5-10 mins. Check my answer here for more details. You can schedule SSIS package using SQLAgent job or use powershell to run it on multiple servers. – Kin Shah Feb 13 '15 at 15:38
  • the table should contain a maximum of 500k rows, thank you sir. I will check that link – RayofCommand Feb 13 '15 at 17:38
  • Transactional replication would fit here. You need PK on table to implement it. Also, you have an option to keep continuous or scheduled synch. – SQLPRODDBA May 14 '16 at 06:45
  • What if my table around 1.5 million rows and needed to be updated every night? I tried MERGE statement but due to different servers it takes too long. I need either just copy the whole table either update only records that been changed. – Serdia Feb 08 '17 at 19:37
  • Transactional replication (TR) has a drawback. Plan to get deadlocks because your table could be blocked. For pretty big amount of data it will take from hours to days. It is because many modifications of TR require its recreation with full snapshot generation and copying. This includes renaiming of columns, adding more tables, etc. TR is a working weekends solution. – it3xl Apr 25 '20 at 05:27

1 Answers1

2

You can use snapshot replication (it allows you to update the table every 5-10 minutes). If you would like instant changes, go for transactional replication.

Note that you cannot publish a replication on SQL Server Express, you need at least Standard edition.

BuahahaXD
  • 1,405
  • 2
  • 14
  • 23