Unique Keys with Multiple Database Replication
One system I worked on years ago had a central database containing the contents of all regional databases aggregated into a single database through replication. The architecture was perfect for the company.
Regional databases contained data required for regional operations without overhead of accessing the entire corporate data. Because their data storage was local, performance was excellent.
Overnight, regional database transactions were replicated to the corporate database using transactional replication. This resulted in a centralized comprehensive database with the ability to aggregate data on the corporate level.
One of the difficulties we had to solve was the generation of unique keys so that as data was replicated to the central store from multiple regions, no two databases contained the same primary key. Tomorrow I’ll share some of the solutions we tried and where we finally ended up.
For today, I’ll just present the problem and provide you all with an opportunity to share your experience solving this or similar solutions. Send your comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Top 10 Tips for Optimizing SQL Server Performance
read more)
Featured Script
dba3_fx_LuhnCheckDigitValid
returns 1 or 0 depending on checkdigit validity… (read more)