S
S
Sergey Andreev2015-02-10 11:09:05
SQL Server
Sergey Andreev, 2015-02-10 11:09:05

What is the best way to merge multiple databases into one?

There are several databases located in different regional branches, with the same structure. The largest database that uses the maximum functionality is the Moscow database, in the rest the data volumes are smaller, and not all possibilities (in conjunction with the client program) are used.
The task was to combine all regional databases based on the Moscow one and transfer them to a dedicated server (MS SQL Server 2012 Standard Edition), with subsequent work with the system only through it.
The problem of migration is that integer counters are used as primary keys and, accordingly, table keys are unique only within one database. The database is large enough - many related tables and stored procedures, plus a client application.
Interested in what are the possible solutions for this kind of problems.
UPDATE
Thanks everyone for the advice.
I liked the idea of ​​changing foreign keys to "on update cascade". I found and finalized a script that generates SQL code first for deletion, then for creating new constructs (with saving parameters and replacing only on update) with their subsequent check. But in the process of testing, it turned out that in case of multiple foreign keys between two tables (for example, UserCreatorID, UserEditorID), all but the first fk should be no action. And unfortunately, there are a lot of such cases.
The transition to GUIDs is considered inexpedient, since client applications will also have to be finalized.
Now we decide whether to work with different ranges of keys, or regenerate them using auxiliary fields. But since another urgent task has arisen, the issue of migration to a single server has been postponed for some time.

Answer the question

In order to leave comments, you need to log in

2 answer(s)
L
ldv, 2015-02-10
@ldvldv

1. Set all links to "on update cascade"
2. For each database, select a non-overlapping range of IDs
Main DB: 0-1000000000
Regional DB1: 1000000000-2000000000
Regional DB2: 2000000000-3000000000
Regional DB3: 3000000000
to increase ID values ​​to 400000 all tables
regional db1:
"update table1 set id=id + 1000000000"
"update table2 set id=id + 1000000000"
"update table3 set id=id + 1000000000"
regional db2:
"update table1 set id=id + 2000000000"
"update table2 set id=id + 2000000000"
"update table3 set id=id + 2000000000"
regional DB3:
"update table1 set id=id + 3000000000"
"update table2 set id=id + 3000000000"
"update table3 set id=id + 3000000000"
4. Import data from regional databases
5. Set values ​​for sequences greater than the latest table IDs

A
Alexander Nikitin, 2015-02-10
@padla2k

In my opinion, your way is only through the migration of all database tables to intermediate ones with the transition to id type guid and further change of the primary key. It's a big job, but I don't see any other options.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question