I have sql server 2014 running my main database at location A. I want to know if it would be possible to export all insert,update and delete queries to txt files for me to then run on a off site database ? I need to set up something like sql servers transactional replication but with the queries being sent to txt files.

Is it possible for sql 2014 to log and export queries to txt files ? is there any softwares i could use ?

Due to cost reasons setting up a vpn to the offsite database for transactional replication is unfeasable.

  • 1
    Why text? And why not use built-in transactional replication? And why is setting up a VPN cost prohibitive? Aug 24 '18 at 10:16
  • it doesn't necessarily have to be text but I need to be able to have an application read the query and write to the offsite database.
    – M-Corp
    Aug 24 '18 at 13:51
  • due to where the offisite database is located, the service provider is overcharging us, to set up a vpn
    – M-Corp
    Aug 24 '18 at 13:52
  • You don't need a VPN. Any encrypted and firewalled connection would do. Aug 24 '18 at 14:41
  • Hi Colin, Can you please provide me with resources on how to set this up without VPN. When i go through the official microsoft documents it says you require a vpn when setting up offsite databases as you cannot connect to ip addresses etc.... thanks
    – M-Corp
    Aug 27 '18 at 15:44

This probably isn't the best idea. The only way that comes to mind to do this is to capture the queries in a profiler or extended events trace and transforming the output to do what you want. That's going to add overhead and ultimately be quite hacky and unreliable.

You'd probably be much better off using something like log shipping, so you can take log backups from your main database, copy them to your off site server, then restore them against the database(s) on that server. Then you get all the changes in a manner you can rely on with minimal overhead.

In answer to follow-up question: There would be no downtime. Taking transaction log backups (like any other backup) creates some overhead, but it is generally good practice to be doing this regardless of needs to replicate the data.

Log shipping is generally easy to set up: https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/configure-log-shipping-sql-server?view=sql-server-2017

But you may need to craft your own equivalent if you don't have easy connectivity between the two servers.

Before you start you need to make sure the source database is in FULL recovery model (you can't take transaction log backups otherwise) and restore a copy of it to the destination server using a full backup with the STANDBY option. STANDBY will put the database in the state where it is readable (but not writeable) and transaction log backups can be applied.

Then your log shipping consists of three components:

1) Have a process on the source box (a SQL Agent job usually) that takes log backups on your source database. The more often you take these, the closer to real-time your destination database can be.

2) A process that copies the transaction log backups to your destination server.

3) Another process, this time on the destination box (again usually SQL Agent) your restores the copied logs to your destination server. Again, using the STANDBY option.

Apart from that you just need some housekeeping, e.g. to remove the log backups once they have been restored or after a certain length of time.

When you use the "built-in" version of log shipping you get some extras, such as monitoring and logging of what is going ob, but overall it is a simple process, so not too hard to replicate on your own.

  • Hi, thanks for the input. If i go with log shipping, would it cause any downtime on the live database ? I may need to do a restore multiple times a day in order to keep the offisite database current. do you have any simple reference links i can read up on ? thanks
    – M-Corp
    Aug 24 '18 at 13:57
  • No probs. I've edited my response above to hopefully answer your question. Cheers Aug 28 '18 at 9:08

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