Distributing Tables across Multiple SQL Server Databases

The Multiple SQL database feature enables tables to be distributed across multiple databases, instead of only a single database. Table distribution enables different backup regimes, access security, or storage defaults, etc. to be implemented at an individual database level.
Previous versions of RED only allowed SQL Server target locations to be created on the RED Metadata connection. This resulted in the RED Metadata tables and all data warehouse tables being stored in the same database, as defined in this single connection.
WhereScape RED has enhanced the connection functionality to enable more than one SQL Connection, each including a combination of targets, a database attribute, and one or more schema, which provides the option of distributing the data warehouse objects across separate databases.
The following important points apply when using multiple databases:

• There can be one or more data warehouse database connections, all of which must be associated with a single SQL Server database server.
• Each database must have a unique ODBC DSN connection defined. The connection can have one or more targets defined, with each target having a database and a schema attribute.
• All source tables for OLAP Cubes and MSAS tabular database connections must be in the same database.
• The RED metadata tables must all reside in a single repository database.
• The associated update procedure of a table object residing in a data warehouse target (e.g. distinct schema/database in the same SQL Server instance) is stored in the same database as their target table by default. The location of stored procedures can be changed and locked via the Procedure Properties window. Refer to Procedure Migration for details.