1

I am working on online and store ordering Pizza project. In this project I have two different databases one is the server where online ordered records will insert and another one is another server database which runs only in the stores.

My requirement is whenever any order is placed in the server database, then that same order must be inserted into the another server database automatically without using triggers.

Is there any solution please let me know ?

Bhavana Ky

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Bhavana Kaparthy
  • 15
  • 2
  • 10
  • This seems very not ideal. If you have any control over that decision, I would heavily advise against it. Beyond that, can we know the version of SQL Server? Also, I'm not sure you'd be able to do this without triggers. The best option would probably be to change code and just do it manually that way. But if you can't even do that, then that's an extra challenge. – Matthew Haugen Nov 28 '14 at 08:14

3 Answers3

2

As an alternative to using Linked Servers as suggested elsewhere, there are various other options depending on your replication requirements:

  • Log shipping Allows you to periodically send transaction logs from the primary server to one or more secondary servers. More info
  • Database mirroring Typically used to increase availability of SQL Server databases, automatic failover, etc. More info
  • Replication, Change Tracking, Change Data Capture are features within AlwaysOn availability groups (only SQL Server 2012 or above), to easily automate replication. More info

These alternatives help you replicate data at the database level rather than at the application level (you will need custom code, triggers, etc. when replicating data using Linked Servers).

Dan
  • 10,480
  • 23
  • 49
0

I have done this in the past with good results:

Use the SQL Linked Server functionality to link the servers. (How to create the linked server for SQL Server 2008 where we have the database from 2000 and 2005)

Then make some sort of a job that copies the new rows from the source do the destination tables.

You will need some sort of incremental field (an bigint is usually enough) to know what your last synced row was when you run the job again.

Community
  • 1
  • 1
Adrian Nasui
  • 1,054
  • 9
  • 10
0

None of the other answers seem to have mentioned this enough but a key part of what you need is a trigger.

A trigger allows you to update/insert multiple tables when a condition is met on your original table, in this case the condition would be inserted

Couple this will a linked server and it should be fairly stright forward to achieve what you need.

Triggers

Linked Servers

Alec.
  • 5,371
  • 5
  • 34
  • 69