Tools eligible for data movement

The database architectures described in the previous sections could be established from the only database containing several schemas, but more databases on more instances of the SQL Server could also be used. This difference determines how to develop data movement and transformations from one schema or database to another. We have a few options for this:

• If we have just one database divided into landing, staging, and target areas by database schemas, then the situation is quite simple because we mainly use T-SQL queries and transactions
• If we have more databases on just one instance of an SQL Server, then this leads to a very similar approach, because there are no complications involved in writing T-SQL queries between more databases hosted by a single instance
• However, if we have more instances of the SQL Server hosting certain databases, then we have more options than just T-SQL queries

The following few paragraphs will provide an overview of the certain data movement possibilities.