August 18, 2017 at 5:57 pm
I have read material and combining these 2 in SQL. However, my question is a little different.
I have 2 databases. 1 db has up to date information that is updated daily or even on the hour.
Db2 is a main database that is not updated as quickly and has more dirty data, How would I create a trigger that would be added to my stored procedure that is associated with db2 and update data while providing a report? would I need to have the data in db2 updated before the stored procedure is run? can it pull concurrently? and pull data together?
any help would be appreciatded
August 18, 2017 at 6:38 pm
Triggers are written against tables, not stored procedures.
What's your ultimate goal? When DB1 is updated, do something in the other database? (add records, update?)
You could do both in side the same transaction... then if one fails, roll back the whole transaction.
August 18, 2017 at 6:49 pm
well I want when db1 certain tables are updated that it triggers and updates tables in db2 .. i will run this daily as a report.. ultimately want updated dats from db1 to transfer to db2 and provide an accurate report
August 19, 2017 at 1:53 pm
Oh, like mirroring. (That's what mirroring does - executes the transaction log against more than one database .. where the second is a copy of the first.). The second copy is read-only (only the service doing the mirroring can write to it), but you can report against the mirror.
August 19, 2017 at 4:00 pm
Yea... that would be great
August 19, 2017 at 7:02 pm
Oh fun... I think you have to use Availability Groups... Mirroring is deprecated.
Here's an introductory article to it.
August 19, 2017 at 9:21 pm
Let me do some research to find out if there's something else my people want
August 20, 2017 at 7:21 am
That doesn't sound like mirroring or availability groups, as they are high-availability solutions that can allow for reading from secondaries as a bonus.
This sounds like a problem that transactional replication solved. Replication moves data changes from selected tables to a second database that's fully read-write (though I don't recommend writing to the tables that are replicated), and you can have additional tables, different stored procedures, different users, different indexes in the replicated database, if you need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 20, 2017 at 7:51 am
That sounds intense.... I am not that advanced yet to pull that off... have any tips to help implement something like this?
August 20, 2017 at 9:52 am
oga1234 - Sunday, August 20, 2017 7:51 AMThat sounds intense.... I am not that advanced yet to pull that off... have any tips to help implement something like this?
Yup. Read the documentation, test it out on a dev/test server.
Replication's simple at the core. You can do complex things with it, but doing complex things is not required
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply