Re: Comparing tables over multiple servers

[RiotTown]

I can see a couple of different solutions, the best one would be have to be determined by you.

Firstly, I'm assuming that at least 1 of these tables would have to be the master table that all data would be referenced against.

Secondly, my solutions would depend on the size of the table.

Thirdly, being a system table (IMHO) it would probably make more sense to have these scripts notify a DBA, but that is a separate conversation entirely. (You may also want to seriously consider removing all non-select access to system tables except for 1 to 2 DBA types to eliminate this from happening in the future)

*also worth noting: With each of these solutions, if your master table gets horked, it cascades down...

1. If the tables are small: Write a script that opens up connections to the 7 tables.

Then do a 'select stuff from master table'. While looping through the results, make select calls out to the remaining 6 tables and compare the results (most likely based on primary key). If something is out of wack send notice or fix it.

This could also be done on each of the slave dbs making a connection to the master as explained below, just using perl instead of db actions.

2. If the tables are large/very large: Write a script that runs (daily/hourly/etc) that connects from each of the 'slave' databases. This script would connect to the master, then copy the contents of the master system table into a local table on the slave db. You _should_ (this is based on knowledge of oracle and 2 seconds of googling sybase) then be able to create a synonym of the new table to be referenced as the system table (you may also have to drop the system table first). You'd also have to make sure to enable all of the same grants that are in place on the master table before you create the synonym.

Hope this helps.

[mpeppler]

You may also want to seriously consider removing all non-select access to system tables except for 1 to 2 DBA types to eliminate this from happening in the future

You need to have sa_role to be able to write to these tables, and you usually update the tables via system stored procedures. So your (good) suggestion is actually already implemented. In addition, updating the tables directly requires setting a special "allow update to system tables" flag.

Michael