Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

An alternative to GUIDs Expand / Collapse
Author
Message
Posted Thursday, September 15, 2011 4:06 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 12:21 PM
Points: 18, Visits: 558
round trip of scope_identity()???? it's a return value of your method call. Please don't feed the bears.
Post #1176097
Posted Thursday, September 15, 2011 4:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
gahayden (9/15/2011)
I have to agree with SSC Eights. Why not use a composite key?

For many years, when storage was expensive, single fields were often used to depict mutiple entities and there continue to be numerous examples of this in accounting and manufacturing. Similar stratagy was applied when DOS limited file naming to 8 characters.

Why go back there? What's wrong with 2 fields?


Everything's wrong with two columns: You see, it's not an issue of storage, but the performance impact of using suboptimal datatypes. Using more than one column compounds the problem as both columns, if used as a primary key, have to be present in foreign key columns referencing the primary key. Since there tend to be many more FK records than PK records in your average database structure, the bloat compounds further, impacting performance as it does so.
Post #1176108
Posted Friday, September 16, 2011 2:47 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 2013 8:21 AM
Points: 283, Visits: 268
Since there tend to be many more FK records than PK records in your average database structure, the bloat compounds further, impacting performance as it does so.


Wow, thank God someone finally brought that up! I've dealt with this situation before as well with many different "remote" databases synchronizing into 1 central database with... composite keys. The only thing that could be done was to roll our own synchronization app. The central store had to "re-key" EVERYTHING to it's own server ID and then send those keys back out to the remote servers. Fun!



Post #1176254
Posted Sunday, September 18, 2011 9:25 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
What about this scenario:

Site A is functioning well and persists orders 1 to 10
Site B is passive but kept up-to-date so effectively it has copies of orders 1 to 10.
Lightning strikes after order 10 is placed on Site A but before it's copied to Site B.
Site B becomes active while repairs take place on Site A
Because Order 10 is not available from Site B User has to resubmit it.
It happens not straight away, so the which was Order 10 on Site A gets number 12 on Site B.
At the same time another, totally irrelevannt order takes Number 10 on Site B.

Now, what will happen when Site A comes back to life?
GUID will prevent orders "No.10" from overlapping, but same order with No.10 on Site A and No.12 on Site B will still be duplicated.
There will be 2 identical deliveries instead of one, 2 following invoices instead of one, missing payment, penalties, disputes, etc.

Whole idea of using GUID or any other kind of auto-generated "across different instances" identifiers is faulty.

Unique entries of orders or any other business objects must be identified by natural keys defined by business logic, nothing else.
IDENTITY is perfectly good as an internal identifier within a database.
Just don't include it into replication process. Let target database generate its own IDs'.
Post #1177062
Posted Monday, September 19, 2011 2:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
Sergiy - It isn't the job of programmers/developers to reinvent the wheel of infrastructure: There are a number of infrastructure topologies that already exist to deal with datacentre destruction scenarios, including database mirroring, log shipping and clustering.

In the assessment of any disaster recovery (DR) strategy, there are trade-offs that have to be made. These include cost, service levels of how long it should take to failover, how much data it is tolerable to lose and how much of a performance hit one is willing to take if one opts for a synchronous data replication topology (be that at SAN level, synchronous mirroring or otherwise) if there is zero tolerance for data loss. Remember also that a DR strategy is one that should be implemented only when there is a total destruction of the primary site environment, so a well run operational department will probably only every implement the plan as part of a DR training exercise.

Again, as smart as programmers think they are, the people that design these products are much more familiar with the issues surrounding DR (including the need for standardisation of DR plans across systems) than programmers are and they see DR in the context of enterprise operational procedures, not as custom processes for individual applications.

In short - if you went into any well-run DBA team and told them you had a different process to implement for DR than the other hundred or so systems they had to support, they'd tell you where to go.
Post #1177118
Posted Monday, September 19, 2011 5:04 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:36 AM
Points: 4,576, Visits: 8,348
ispooner,

Does anything from your post make the approach less wrong?
If not, then what was that about?

I'm aware of the common practices.
Cursors, loops, denormalized tables, etc. - used as common practices by 90% of "normal" developers.

Does it justify use of those approaches?
Post #1177167
Posted Monday, September 19, 2011 5:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
Sergiy - What I'm trying to say is that the original idea of coming up with a more efficient globally unique identifier is good. However its usage in order to come up with a non-standard operational procedure for DR is deeply flawed.
Post #1177177
Posted Monday, September 19, 2011 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 5:38 PM
Points: 8, Visits: 96
Thought provoking.
Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.
Post #1177265
Posted Monday, September 19, 2011 8:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 20, 2013 2:19 AM
Points: 21, Visits: 72
Colin Barry (9/19/2011)
Thought provoking.
Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.


Absolutely agree - although SOA may be a buzzword on many people's lips, just because you can do something doesn't mean that you should: Not only does the cross-network and cross-process intercommunication introduce additional latency, but the additional components involved in implementing an additional service reduce the MTBF of the entire system.
Post #1177272
Posted Monday, September 19, 2011 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, August 30, 2014 5:24 PM
Points: 7,139, Visits: 15,191
iposner (9/19/2011)
Colin Barry (9/19/2011)
Thought provoking.
Why have a service issue the GUIDs? A small bit of clr code could do what you need and would be fast. You could then let your insert stored procedures transparently create the "identity" of the record and return it to your middle tier layer.


Absolutely agree - although SOA may be a buzzword on many people's lips, just because you can do something doesn't mean that you should: Not only does the cross-network and cross-process intercommunication introduce additional latency, but the additional components involved in implementing an additional service reduce the MTBF of the entire system.


Don't confuse SOA with web services. In this case - "service" is just as valid a name for the CLR code as if would be for the web service call previous advocated. SOA also has nothing to do with whether the functionality is "local" or "remote".

In short - both would be SOA if the purpose is reusability of the identifier (GUID) functionality.



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #1177310
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse