Ideas for Combining Thousands of Databases in One Database

Question

We have a SQL server with a database for each client, and we have hundreds of clients. So imagine the following: database001, database002, database003, ..., database999. We want to combine all these databases into one database.

We must add the column siteId, 001, 002, 003, ..., 999.

We are exploring options to make this transition as smooth as possible. And we would like to hear any of your ideas. This proves that this is a VERY complex problem.

I heard about a method that would create a view that would match, and then filter.

Any ideas guys?

Answer 1

. , . " ", ( ) , . , .

SSIS, , . .

, , , ( , , ).

, " ", , . , .

, , , .

Answer 2

?
, SO # 19 ( 40- 50 ) .

Answer 3

"site-id" - , .

, ( ), - . "" , . - - . (, Oracle, DB2) , .

, - - siteId .

"". - FK, , . siteId, .

Answer 4

, , . , , , , . , , .

, , . , ? , . , , .

, , . , , . , , siteid. .

, procs, views, udfs , , siteid . SQL. B. . ( , ). , client_id. , , , , , , , . ( , .) , , , , , . , .

Answer 5

, , - . :

• ( ).
• , SiteID.
• .
• , , SiteID .
• , "" . , . , , .
• ( , , SiteID) Off .

Answer 6

, . , , , - ; , , .

Answer 7

.

, " ". , / VIEWS , .

IIRC "Trusted Oracle" ( , Oracle 7 8 ). , " sourceKey = @userSecurityKey" ( ). , , .

Answer 8

Gregory, ssis, , foreach.

The parent package queries the configuration table and places it in the object variable. The foreach loop then uses this set of records to pass variables to the package, such as the name of your database and any other details that may be required for the package.

In the table, you can specify all your client databases and set a flag when you are ready to move them. This way you are not sitting around the ssis package on 32,767 databases. I am attached to the foreach loop in ssis.