Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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".

share|improve this question

closed as not constructive by Michael Hampton, Zoredache, devicenull, Ward, jscott Dec 13 '12 at 4:49

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Why though? What purpose does doing this serve? –  MDMarra Dec 11 '12 at 22:11
    
You mean one "single point of failure," right? I can't think of any good reason to do this. –  Michael Hampton Dec 11 '12 at 22:22
    
Why don't you trust your DBAs? –  mfinni Dec 12 '12 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 '12 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 '12 at 16:27
show 2 more comments

3 Answers

up vote 1 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
    
:) 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 '12 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 '12 at 0:44
add comment

'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.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.