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?
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.