跨服务器管理身份/主密钥
我正在设计一个需要支持复制的新数据库，但我一直在决定选择什么作为我的主键。
在我们当前的主键数据库中，我们使用两个 int 列，第一列是标识，另一列用于描述该行插入到哪个服务器上。现在我想避免使用两列作为主键，而只使用一列。到目前为止，我有两种方法可以做到这一点：
使用 GUID 作为我的主键
这将确保任意数量的服务器之间始终存在唯一的密钥。我不喜欢这个的地方是 GUID 大小为 16 字节，当用于跨多个表的外键时，会浪费空间。而且，在编写查询时使用它会比较困难，并且查询速度会变慢。
使用 int 或 bigint，并手动为每个服务器上的每个表指定种子和增量值。例如，如果有两台服务器，第一台服务器上的 X 表将从编号 1 开始，第二台服务器上的 X 表将从编号 2 开始，每个服务器都会增加 2。所以会有 (1,3,5,. ..) 在第一台服务器上，(2,4,6,...) 在第二台服务器上。这种设计的好处是，在编写查询时更容易使用，速度快，并且外键使用的空间更少。不好的是，我们永远不知道有多少服务器将运行，因此很难判断增量值是多少。此外，管理服务器上的架构更改也更加困难。
管理多个服务器的最佳实践是什么？在这种情况下最好的方法是什么（如果有）？
I'm in the middle of designing a new database that will need to support replication and I'm stuck on the decision what to choose as my primary key.
In our current database for primary key we use two int columns, first column is identity and the other is used to describe on which server the row is inserted. Now I want to avoid using two columns for primary key, and just use one column instead. So far I have two way of doing this:
Use GUID for my primary key
This one will ensure that there is always a unique key across any number of servers. What I don't like with this one, is that the GUID is 16bytes in size, and when used for foreign key across many tables it will waste space. Also it is harder to use it when writing queries, and it will slower to query.
Use the int or bigint, and manually specify the seed and increment value for every table on each server. For example if there is two servers, the X table on the first server will start from number 1, and on second server it will start from number 2, each will increment by 2. So there would be (1,3,5,...) on first, and (2,4,6,...) on second server. The good thing with this design is that it is easier to use when writing queries, it's fast, and it use less space for foreign keys. Bad thing is that we never know how many servers will be running so it's harder to tell what will be the increment value. Also it's harder to manage the schema change on server.
What is the best practice for managing multiple servers, and what's the best way, if any, to do in this kind if situations?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你的问题提得很好，而且经常被问到。
从维护的角度来看，我绝对会选择 GUIDS。他们在那里是有原因的。
在此过程中，您可能会遇到移动和重新复制数据的复杂操作，然后其他选项可能会使数据变得比需要的更复杂一些。
这里有一篇关于各种选项的非常好的简短阅读：
http://msdn。 microsoft.com/en-us/library/bb726011.aspx
至于复制部分 - 如果做得正确，复制就不会有真正令人头疼的问题。
Your question is a good one, and one that is asked often.
From a maintenance perspective, I would absolutely go with GUIDS. They are there for a reason.
Somewhere along the line you might run into complex operations moving and re-replicating your data, and then the other options can make it a little more complex than it needs to be.
There is a very nice short read about the various options here:
http://msdn.microsoft.com/en-us/library/bb726011.aspx
As for the Replication part - if done properly, there are no real headaches with replication.
更新：
找到了一个更简单/手动的方法这里< /a>.涉及使用NOT FOR REPLICATION和惊人的身份正如您在评论中提到的种子值。
原文：
您最好的选择是类似于列出的第二个选项。为复制发布者和订阅者实例分配标识范围，然后打开自动范围管理。
本文讨论在复制中管理标识列的选项，并且启用身份范围管理在此处讨论。
由于您不知道复制池中有多少台服务器，因此您可能需要定期重新配置项目属性。
Update:
Found a more simple/manual method here. Involves using NOT FOR REPLICATION and staggering identity seed values as you mentioned in comments.
Original:
Your best bet is something like the second option listed. Assign identity ranges for the replication publisher and subscriber instances, then turn on automatic range management.
This article discusses options for managing identity columns in replication, and enabling identity range management is discussed here.
Since you don't know how many servers will be in the replication pool, you may need to reconfigure article properties periodically.
我敢于完全反对复制:)这肯定是痛苦多于乐趣。如果您负担得起，请查看同步框架。
至少可以说，玩弄身份并不灵活。考虑添加移动服务器。身份插入、不同模式等等。
如果您使用 newsequentialid() 作为默认值，则 GUID 对于聚集键来说是没问题的。它有点大（很多位），但它一次性解决了麻烦:)
我要采取的方法是拥有一个仅与数据库上下文相关的 int 身份聚集键。然后添加一个 GUID 列，这对同步上下文有意义。在其顶部添加 rowversion 列以查看已准备好同步的内容。
I dare to advise against replication altogether :) it's certainly more pain than fun. If you can afford, look into Sync framework.
Playing with identity is not flexible to say the least. Consider adding moving servers. Identity insert, different schemas and so on.
GUID would be alright for a clustered key if you used newsequentialid() as a default value. It is a bit larger (a number of bits), but it solves the hassle once and for good :)
The way I'd go is to have an int identity clustered key which is only relevant to the database context. Then add a GUID column, which makes sense to the synchronization context. Top it up with a rowversion column to see what's ready for sync.