Databases Reference
In-Depth Information
Figure 9-14.
Logic used to fetch data in a multitenant, schema-based architecture
When the database request is issued, the cost fetches the Customer ID from the local cache (1) and uses the
Customer ID to retrieve the customer connection string from the login database (2). The system then saves the connection
string for the customer in its internal cache (3) for future requests. The database connection string contains the service
account to use along with the database assigned to the customer. The system then connects to the database (4). Because
the service account has a default schema pointing to the customer schema the system issues the T-SQL command against
the correct schema automatically (5). The system can then return the customer data and close the connection (6).
You should know that a multitenant system distributed over multiple databases with a service account for each
customer can create a Connection Pooling Fragmentation problem for large systems. That's because a connection
pool will be created for each customer. In addition if you use multiple load-balanced middle-tier servers, each server
will hold its own set of connection pools. As a result you should carefully test your system across multiple customers
by monitoring your database connections to avoid possible throttling conditions when too many connections are
established against a SQL Database instance. If you use SQL Database Federations instead of the schema container
approach, you will not experience the Connection Pooling Fragmentation issue because all database connections are
established against a single database (the root database) using the same service account, regardless of the Customer ID.
Performing maintenance operations on a customer schema can be a challenge. Your application will need to
prevent customers from connecting until the schema maintenance task is completed. For example, if you want to
move a customer to another database you can follow these steps:
1.
Place the customer in maintenance mode to avoid data changes (through your application).
2.
Create a backup copy of the customer schema (you can use the free Enzo Backup tool
create a complete copy of the database using the
CREATE DATABASE ... AS COPY OF...
;
however, this method requires removing all the other customers' schema containers that
are not supposed to move to the new database.
