Hi there, We have replication in place for certain objects in 2 database which are backend of different application. And I am writing a script to monitor if there is any change in the schema in Database A so that we can make changes desired changes in Database B. For this I am writing a query on Sys.objects tables and keep track of objects with Object_Id. So my question is, Does everytime there is any schema change happens in Database A, will replication drop and recreate the replication objects in Database B. And if so , there will be a new objectid assigned for the newly created objects. Please suggest. Thanks!
How frequently the schema changes will happen on Database A and Database B? Are you looking at DML triggers in this ccase?
Hi Satya, Thanks for the response (as always). Because two database are on different servers and owned by two different teams. So whenever the Team A makes changes to Database A, we may/may not get information. In cases we dont get information, we are not able to make changes in our Database B and the application fails. So i am writing a stored proc where i will save the objectid and other information for the replicated objects. And will run a nightly job to match those fields. If the object id does not exist anymore, than something changed. This is the logic Thanks again
If the schema changes are frequent then your process may slow down due to the fact that same schema changes must be recreated on databaseB. In this case for reporting purpose I would suggest to look at SSMS Performance dashboard reports --> Schema changes. HTH
Thanks for the information. I will certainly look into this. Just for my knowledge, does replication recreates the objects everytime the schema changes in the Database A Thanks!