August 17, 2007 at 7:48 am
On each of our 40 SQL Server instances (members) an internal management database has been created to collect various information about each server. We want to push all of the member server information to a single "master" database on one of these server instances for reporting purposes. My question is how to design the tables so that the primary keys do not conflict with each other when pushed to the "master" server instance. If I consider using Identity columns, unless I use a predefined range of values on each server, I am unsure whether it is possible to create a a series of starting values and increments that as additional servers are added would not eventually create conflicts or other issues. Is the only other option to define the primary keys as data type uniqueidentifer? Thanks in advance.
August 17, 2007 at 8:03 am
IF we are talking ONLY about an 'internal management database' that will compile information about the 40 instances, why not use the instance number as part of the key field? That should resolve all conflicts and allow you to trace any information back to the correct source? The instance number could be alphanumeric, rather than numeric, if that would improve readability.
What's the volume involved? If the volume is small, then you should not be terribly concerned about performance issues.
You could also inject the prefix when you are collecting into the central repository. That would allow for identical architecture on each instance, and allow you to maintain an identical server-specific image on the server where you collate all your information.
Just a quick thought. use it only if you like it.
August 17, 2007 at 8:08 am
The volume is small on each member instance. So you are suggesting a composite primary key that consists of an Identity column + another column that would create uniquness. That sounds workable. Thanks.
August 22, 2007 at 6:42 am
The same solution is talked about in this article which I found to be a good read:
http://www.microsoft.com/technet/technetmag/issues/2006/07/InsideMSFT/default.aspx
August 22, 2007 at 6:46 am
Thank you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply