1

I have an application that needs to know if it is connected to the original database that it was installed with or if the connection is to a copy of that database. Is there any known method to know if the database has been cloned and the application is no longer connected to the original? I am specifically interested in MS SQL Server and Oracle.

I was kicking ideas around for a stored procedure but that most likely doesn't have access to the hardware to confirm unique hardware information that would somewhat guarantee that the database is the one that it was originally connected to during installation.

I'm trying to prevent/detect cloning of a database so that there is only 1 "true location of truth".

7
  • Why though? What purpose does doing this serve?
    – MDMarra
    Dec 11, 2012 at 22:11
  • You mean one "single point of failure," right? I can't think of any good reason to do this. Dec 11, 2012 at 22:22
  • Why don't you trust your DBAs?
    – mfinni
    Dec 12, 2012 at 3:55
  • I can see the rationale behind it. It's not a matter of trusting DBA's. Say you sell a product that makes use of a database. It is important to you (for, say, warranty purposes) to know that the database you installed the product with, is the very same database that is being used once the client calls your customer support line. Might sound a bit strange, but still plausible.
    – Isaac
    Dec 12, 2012 at 5:03
  • @Isaac, but if it's a clone, then it shouldn't matter. And if it's not a clone, support should be able to see that immediately.
    – MDMarra
    Dec 12, 2012 at 16:27

3 Answers 3

1

My take on it is this: if it is made of bits, it can be cloned in a way that you will never be able to tell the difference between the original and the clone. Your only chance would be if there was some sort of a calculated value that each database instance returns, so -

  1. Each database instance returns a different value.
  2. The value returned by each database instance is consistent across invocations.

Condition number (2) is your villain here. If the value returned by each instance is consistent across invocations, then the value must rely on some sort of a stored value, somewhere. That stored value can be cloned, too...

Q.E.D

3

I have cloned Oracle databases for two purposes. To the best of my knowledge only one of these will meet your requirement.

  • I have cloned database to provide an additional instance (sometimes read-only) for another environment or use. In this case I change the database name. These clones can be detected by checking the database name.
  • I have cloned the database for recovery or roll-back purposes. Usually, these replace the original database. I know of no way of identifying this as a cloned database.

There is data that may indicate the database was cloned, or moved. Things like filenames for tablespaces are likely to change when a database is cloned, but they can change even if the database is not cloned. I believe you can retrieve the hostname or IP address on which the database is running. Again, this may be indicative that the database is cloned, but can't be used to verify that it is a clone.

While it is possible to end up connected to the wrong clone, it is generally somewhat difficult to get in that situation. Spend some time understanding how your connections to the database are routed.

2
  • :) I need to do the detection from the db client end and not on the db server itself. Also someone running the DB in a VM could easily clone it including the server it's on. I suspect I may be in one of those infinite impossibility problems :(
    – millebi
    Dec 12, 2012 at 21:36
  • @millebi The information I referred to can all be check from the client. With the connection methods I use, most problems would involve DNS changes and/or ARP poisoning.
    – BillThor
    Dec 13, 2012 at 0:44
0

'show slave status;' you know which server you are connected to...the show slave status will tell you which server is the master...if they are on different servers. You may be able to use the global variable server_id to figure out the difference on the same server...I have not played with it.

Not the answer you're looking for? Browse other questions tagged .