0

I'm taking an old application and creating an entire new version. I'm going to use SQL Server for the local and a remote Database(s). In order to allow the local and remote databases to stay in sync I'm going to write a sync process...

May use Microsoft Sync Framework or something custom... that is a question for later.

The current version of the Database uses int Primary Key values with Auto Increment. Bad news for keeping changes in sync because of the possibility of PK duplicates.

In order to get around this I see two basic options:

1) Create a new PK based on the int ID value + the source (like "local" or "remote_db_name")

2) Switch the PK to GUID

I see negatives in both, but I'm leaning towards GUID ... which means a painful data conversion with lots o scripts, plus possible slow index issues...

After reading this post I'm wondering if I should do some sort of combo, like using the GUID for the sync, but the "combo key" for everything else? That might be a waste though....

Am I missing alternative approaches?

Thanks!

Community
  • 1
  • 1
pStan
  • 1,084
  • 3
  • 14
  • 36
  • Another good article on this issue: http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/27/guid-vs-int-debate.aspx – pStan Mar 26 '15 at 15:17

1 Answers1

1

For a quick & dirty solution, you could set the Auto-Increment Seed to a large number, so the new system would create its IDs in a range which wouldn't collide with the old system.

The following script will change the auto-increment seed to 1 million:

DBCC CHECKIDENT ('NewTable', RESEED, 1000000)
Jon Tirjan
  • 3,556
  • 2
  • 16
  • 24
  • That might be to "Dirty" for my taste. I have the possibility of multiple "client" databases, so I don't think that would work. – pStan Mar 24 '15 at 22:55