php - Web application that requires data from other databases -

i helping company redesign crm.
plan extend crm read data database used software.

all database using mssql technology.
actually, our company has 3 brands , each brand's data stored in different mssql database.

brand1: customer invoice payment  brand2: customer invoice payment  brand3: customer invoice payment 

in new database schema design. customer have several invoices , each invoice receives several payments.

it ideal have data store in newly designed db because can extract updated payments this:

select [everything want] customer inner join invoice invoice.customer_id=customer.id inner join payment payment.invoice_id=invoice.id 

but need make connection 3 database, getting data them, , combine result generate data structure this:

{   customers:[     {       customer_name: cato,       invoices:[         {           invoice_id: 1,           payments: [bla,bla,bla]         }       ]     }   ] } 

now, company thought of using trigger hard maintain. there better options can job?

yes, sql has great solution this, need sql replication, gives ability 'copy' tables remote db local, first copy table local , set replication , remote db forward insert, update etc.. commands your db, in fact, local replicated table syncronized remote 1 take @ it , follow tutorials replication