July 28, 2016 at 6:34 am
At my company, we have a database for our in-house applications we built, that houses the base tables. We use this data in another database, but we create a separate table in that isolated database for business reasons. We use TFS and RedGate Source Control to check-in changes. If a table or stored procedure is changed in the application base tables, and that table is referred in the isolated DB, I'd like to be notified about it somehow (automated email or SSRS), so I can review and determine if I need to update the corresponding table in the isolated database.
I'm looking for suggestions on best approach. We do have a meta database that houses tables and their database/schema/etc.. but I don't know if that's feasible to track changes. Is there way to report of source control check-ins? and somehow identify if that table exists in the isolated database? The table schema and name might differ though in the isolated DB... but the stored proc that loads the data will reference the source table...
July 28, 2016 at 6:55 am
You can setup an SQL Audit trace on DDL events.
Also you can have a job scheduled to periodically monitor query for modified objects and fire off an email alert in response.
select name as ObjectName, type_desc, modify_date
from sys.objects
where is_ms_shipped = 0;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
July 28, 2016 at 4:32 pm
Thanks for info Eric. I think the SQL Audit trace scripting will be the route to go.
July 28, 2016 at 4:46 pm
TFS has check-in alerts (for specific folders) so you could look into that too. I'm not sure whether Redgate's version control does it as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply