SQL Server Performance

Replication Object Recreate

Discussion in 'SQL Server 2008 General DBA Questions' started by danny123, Mar 21, 2011.

  1. danny123 New Member

    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!
  2. satya Moderator

    How frequently the schema changes will happen on Database A and Database B?
    Are you looking at DML triggers in this ccase?
  3. danny123 New Member

    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
  4. satya Moderator

    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
  5. danny123 New Member

    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!
  6. satya Moderator

Share This Page