Greetings.
Situation here. Having a local SQL Database for client and cloud Database. Every month will be needed to manually copy the backup of local database to migrate to cloud database. Issue raise when both of the id for the table inside the database not tally. Is there any effective way to perform the restoration or migrate the database which are not having the conflict of id issue?
Hi,
How about setup the replication between the 2 databases?
Rgds,
Dennis
1 Like
Hi,
Thx Dennis for your kind reply. After your reply i do have some searching on the replicated server and the function of replicating server is still confusing me. I'm wonder that the synchronization for the replicate server is it applicable to correct all the linked id in the local database when it sync to the cloud database?
Do you Backup and Restore the database, or are you sending data to / merging data into the Cloud Database - e.g. using INSERT statements etc?
If the Cloud Database is also being updated, and you have local databases also being updated, separately, and you cannot immediately send the data to the Cloud (e.g. replication) then you could consider using GUID instead of ID, but you should consider the negative points about using GUID instead of ID in case any of them would be a problem for you. They mostly relate to extra disk space, fewer index entries per page, and page splitting / fragmenting of indexes which are more only likely to cause performance problems on databases with lots of Inserts
1 Like
Hi Kristen
From what I have doing from the past is manually insert the records to the cloud by lookup for their reference id.
Since there a lots of records, the lookup will be taking daily of times.
Yes, the local db is on the client side as they use it for their daily business transaction and we collect the backup of their database and perform the insert for those records to our cloud server so that we can further process their data.
Sounds like you need an ID that is unique to the Cloud database (in case you create a new row in a table there) and then a different ID for any row created on the Local database.
A quick solution would be to change the SEED for the Identity. For example, you could use EVEN numbers on the Local database and ODD numbers on the Cloud. Or you could leave the Local database at its current number but change the SEED on the Cloud to, say, 100,000,000 (Maximum number of INT is 2,147,483,647).
Yet another solution (but your APP and/or Users!! may not like it) is to make one of the IDENTITY increment NEGATIVELY and give a SEED of -1 - so the Cloud could have ID numbers of -1, -2, -3, ...
The easiest is to reseed the Cloud (or Local) table to allocate numbers from, say, 100,000,000 :
DBCC CHECKIDENT ('dbo.YourTableName', RESEED, 100000000)