HELP! Synchronize db and maintain foriegn key relations?

I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql.

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls--gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Sounds like a nice problem :p
Never had this one but it's fun to think about it, so this is what I came up with:

Use different ranges... Set the IDENTITY (or AUTO_INCREMENT) on MySQL on a very high number, one you won't expect to reach in this product life cycle. Let say 10000000.

When synchronizing set the IDENTITY_INSERT ON, insert the records from the other server. Set IDENTITY_INSERT OFF, reseed to the lower value and continue.

Example:
CREATE TABLE tst (c1 INT IDENTITY, c2 INT)

INSERT tst(c2) VALUES (1)
INSERT tst(c2) VALUES (2)
INSERT tst(c2) VALUES (3)
INSERT tst(c2) VALUES (4)

SET IDENTITY_INSERT tst ON

INSERT tst(c1, c2) VALUES (10000000, 5)
INSERT tst(c1, c2) VALUES (10000001, 6)
INSERT tst(c1, c2) VALUES (10000002, 7)

SET IDENTITY_INSERT tst OFF

DECLARE @.i INT
SELECT @.i = MAX(c1) FROM tst WHERE c1 < 10000000
DBCC CHECKIDENT ('tst', RESEED, @.i)

INSERT tst(c2) VALUES (8)
INSERT tst(c2) VALUES (9)
INSERT tst(c2) VALUES (10)

SELECT * FROM tst

DROP TABLE tst
Ofcourse this could be useless in your case, 'cause what happens when someone is inserting while you're synchronizing!? But maybe it's a start.|||or else u can use another set of tables for remote data and use views to combine both local & remote data. u might be needing an additional flag field in the view to identify the source.

HELP! Synchronize db and maintain foreign key relations?

I have to synchronize 2 databases hourly but am having difficulty maintaining foreign key relations. These tables use auto-increment columns as primary keys, with child records in other tables related with foreign keys. I can't change the way the local software uses primary or foreign keys as it is hardcoded in the local app. (microsoft retail management system)..(however the web-remote app is easily customized). I am using CDB synchronizer to sync the two databases because the remote one is mysql...local is ms sql..

Example tables layout:
Items table has auto-increment primary key 'id'
TransactionEntry table has its own auto-increment primary key 'id' and a foreign key 'item_id'

Example of how remote and local database foreign key relations are incorrect after sync using CDB synchronizer:
8:00am -first installation of database-'item' tables auto-increment 'id' columns match with id last record value of '6'

locally the following products are added:

11001 short sleeve t--gets added with primary key in 'item' table 'id' of '7'

11002 long sleeve t--gets added with primary key in 'item' table 'id' '8'

remotely the following products are added:

21001 hipster jeans- --gets added with primary key in 'item' table 'id' of '7'

31001 overalls--gets added with primary key in 'item' table 'id' '8'

remotely someone orders 21001..so TransactionEntry table records sale of "item_id" of '7', but after synch with our local server,

product with "item_id" of '7' is "short sleeve t".

9:00 -synch takes place...item_id foreign key isn't accurate because of independent auto-increment values..

whenever a product is ordered, the TransactionEntry table will record the product's ID column thats available in it's own local copy... after synch, the 'item_id' field will not match the 'Item' table id field and the data about the transaction's product is lost.

I have read of solutions involving staging/temporary tables to cascade update foreign keys before synching into main database, but hopefully there is a more elegant solution for this. If this is only way, will it be reliable? foreign key mix-match seems like could cause havoc.Hi,

The way i'm using is to create my own table with two columns...

The columns are Old_ID and New_ID

Before syncronization u inserts all elements from the synchronized table there!

After appending ur data into the main table u need to identify using other field /s/ which id corresponds to ur ancient id /with Update action query

The last step is appending the info from the table with foreign keys linked with ur temporary table. Instaed using the old foreign key u need to put the new entry and the information will be at its place!

Hope this helps as conception!

:)

Help! Removing SQLServer builtin/Administrators

Hi,
I'm having trouble maintaining security on SQLServer as
everyone who is a member of the Local Administrators (on
the system) has full control by default as SQLServer has
builtin/Administrators added by default to its System
Administrators List.
Last time I removed this group, so many things went
wrong. I dont' want the entire local administrators to be
the SQL Admins. So please suggest a way where I can
safely remove the default Built-in\Adminstrators from the
SQLServer security. Any article will be helpful.
ThanksIf you subscribe to SQL Server Professional, I wrote a piece on this:
http://www.pinpub.com/html/main.isx?sub=64&story=783
Briefly, you can add a domain account to the sysadmin role first and then
remove the BUILTIN\Administrators role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:1475301c3c352$a5d28e00$a601280a@.phx
.gbl...
Hi,
I'm having trouble maintaining security on SQLServer as
everyone who is a member of the Local Administrators (on
the system) has full control by default as SQLServer has
builtin/Administrators added by default to its System
Administrators List.
Last time I removed this group, so many things went
wrong. I dont' want the entire local administrators to be
the SQL Admins. So please suggest a way where I can
safely remove the default Built-in\Adminstrators from the
SQLServer security. Any article will be helpful.
Thanks|||Adding to Tom's suggestion, you could also add a domain local group (with
only members you wish to have sysadmin equivalence) and grant that group
sysadmin permission -- then remove the builtin\administrators group. It's
also not a bad idea to reset the 'sa' account password at the same time in
case you need that account to log back in (in mixed mode).
Steve
"tony" <anonymous@.discussions.microsoft.com> wrote in message
news:1475301c3c352$a5d28e00$a601280a@.phx
.gbl...

quote:

---

> Hi,
> I'm having trouble maintaining security on SQLServer as
> everyone who is a member of the Local Administrators (on
> the system) has full control by default as SQLServer has
> builtin/Administrators added by default to its System
> Administrators List.
> Last time I removed this group, so many things went
> wrong. I dont' want the entire local administrators to be
> the SQL Admins. So please suggest a way where I can
> safely remove the default Built-in\Adminstrators from the
> SQLServer security. Any article will be helpful.

---