Sql-server – Microsoft Sql Server – How to replicate a table from Linked Server

linked-serverreplicationsql server

I have an Origin Database – #1 with very restrictive access due to security.

I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.

I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.

How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?

Best Answer

It doesn't sound like any of the built-in SQL features are going to meet your needs here:

  • SQL replication makes a lot of changes to the source db, and probably won't work over a linked server
  • Mirroring and/or always-on availability groups likewise require lots of underlying configuration to the source server
  • Log shipping, if you had access to transaction log backups, would bring over the entire database state, not just the tables you want

You're going to have to either use a 3rd-party software (like Redgate SQL Compare), or roll your own.

Personally I'd build an SSIS package to pull down new or updated data from the required tables (hopefully you have some easy "last_update" fields to go off of) and run it via a SQL job every few minutes. There is no SQL wizard for this, you'll have to know what you're doing or hire someone who does.

Related Question