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?

  • 2
    Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something like Select * into tbl1 From Database1.server.dbo.tbl1
    – Thomas D.
    Apr 8 '19 at 19:52
  • I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
    – Chim Kan
    Apr 8 '19 at 19:53
  • @ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
    – Chim Kan
    Apr 8 '19 at 19:56
  • 1
    I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
    – Thomas D.
    Apr 8 '19 at 19:58
  • @ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
    – Chim Kan
    Apr 8 '19 at 19:59

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.