Managing Identity Values Between Multiple Databases
-
I am trying to find out the best way to manage identity values between our multiple database (live, dev, alpha, beta, etc). The biggest problem we have is that the identity values in our development database are mis-matched from our live database at times. Our developers are forced to hard-code a specific identity value into their code, and if that value changes between the dev and live environments, they will have to go back and change their code before it goes to the live environment.
Any advice on how to keep the identity values consistant between multiple databases? Is there software out there to help with this?
Thanks,
Aaron
-
Hi Aaron,
Any idea why the values are out of sync? An identity column only increments when a row is added to a table, so is it that you have different row counts in different databases?
Do you refresh your dev, alpha, and beta tables from production? If you use DTS or T-SQL, be sure you allow identity insert so new values aren't generated on insert.
Greg
Greg
-
The structure of the databases are identical, and the biggest problems is with the dev database. It is never fully refreshed from production. When a developer needs the dev database refreshed, it is only a few tables, and when we do refresh it, I have to make sure that no other developers are working on projects that will be affected if I refresh these tables.
We are unable to refresh the entire dev database since this would cause developers to lose any work that has not gone to production yet.
Generally, the developers will script out any changes made to dev that will ultimately need to go into production. Each week, those scripts will be released into the test environments, tested for quality assurance, and then put into production.
-
By the way...
I know this all sounds like a pretty poor system, and I would have to agree.
This company has had tremendous growth lately, and have never had a real DBA. They decided to hire a full-time DBA to help them get through the growing pains. I have started researching better systems to put into place, and this is one of the big issues that we have run into.
-
Red Gate's SQL Data Compare can reduce that pain:
http://www.red-gate.com/products/SQL_Data_Compare/index.htm
(If you buy, tell them an MCT sent you
)As for any aspiring developers reading this thread: re-read the whole thread a few times and then repeat to yourselves "I can build entire production systems without a single identity column".
-Eddie
Eddie Wuerch
MCM: SQL -
Thanks for the info. I will look into it.
As for using/not using identities...do you recommend staying away from identities? If so, why?
-Aaron
-
Identity values are meaningless. They serve solely to produce an artificial primary key. They should not have any relation to real-world values, unless you don't mind having, say, purchase order numbers where you cannot guarantee that there won't be any gaps. They also have no relation to the other data in the row, which is bad form from a rules perspective.
Essentially, they're a very simple, but sub-optimal, solution to a number of problems. Some would call them a crutch.
When you are creating your tables, you should look for natural primary keys as much as possible. Even if that means multi-column keys. First of all, that will improve performance, as your table will be "ordered" (as much as a clustered index is an order) by the column(s) that you're most likely to be searching on. Second, it makes it easier (from a human-readable standpoint) to keep track of foreign key relations, because you're matching up meaningful data, not made-up numbers.
If you need incremental values, create code to do it manually. It is much more easily controlled, across the board. A simple SELECT MAX(myKey) + 1 AS newKey FROM myTable usually is sufficient.
There are times when identity columns are a good solution. This can especially be the case where controlling the actual values of the incremented column isn't important, so long as each number is distinct. Or when creating a table that would have a complicated natural key, and has several other tables with foreign key relationships to it.
But, it should be a secondary tool, not something you include by default in every table.
-
Ah, the great primary key debate. If you're interested, have a read of some articles/discussions (I believe there are one or two
) - and make up your own mind...http://www.google.co.uk/search?hl=en&q=natural+vs+surrogate+keys
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply