Multi-site per database vs. one site per database

This chapter explains the differences between an environment with multiple sites all in one SQL database, versus an environment with each site in a different SQL database.

You can also set up an environment where you have multiple sites in one database, and another site in a separate database.

This table shows the advantages and disadvantages of each type of environment:


Topic	Multiple sites per database	One site per database
Maintenance	Pro: Because you have only one database, backups, administration of indexes, etc. are easier. Con: Backups and restores take longer to complete for all sites participating, and will take progressively longer over time, exponential to the number of sites. Increased time to run index rebuilds, analysis, statistics, etc. Con: Only one recovery model can be selected.	Pro: Backups and restores can be done on the databases (sites) during the various sites’ maintenance windows, based on each site’s operations low utilization or down times. Con: Administration of multiple databases for indexes, statistics and database maintenance is more complex and potentially more time-consuming. Con: Data recovery, high availability, and disaster recovery could require coordination between multiple database (site) failover points for all of the application databases.
Upgrades and issue fixes	Pro: Because you have only one database, upgrades are easier. Pro: You have one copy of the application code that is used by all of the sites in the database. So when you upgrade or install bug fixes, you only have to update one database. Con: When you apply single fixes that involve core database logic (method calls), the fix impacts all sites.	Pro: Single sites can be upgraded to new versions of SyteLine, rather than having to simultaneously train and convert all sites at once. (However, this gets more complicated if the site is replicating data to other sites.) Con: You have copies of the application code in each site database. So when you upgrade or install issue fixes, you must update all site databases.
Processing speed	Pro: If all your sites are in one database, tasks that require performance of application code at another site may display results more quickly at the originating site, because they can take advantage of shared memory. Con: Writes to the log file of a single instance database with multiple sites could create a bottleneck at the log file. Log file maintenance is more important in this scenario.	Pro: Each site can be separately administered and tuned to best perform for the usage of that site (memory allocation, backup schemes, number of active sessions, etc.) Con: Collecting and querying data from multiple databases is less efficient. You need multiple execution plans and connection sets.
Data integrity	Pro: Keeping data in sync between sites is easier, especially after a restore	Con: You could lose some multi-site data integrity when you back up and restore individual databases in various states that are participating in a multi-site environment.
System down	Con: If the database goes offline, all sites are down. If you have to do a system restore or backup, it affects all sites in the database.	Pro: When you take a site database offline, there is little or no impact on other site databases.
Scalability	Con: The database server that is processing data for the single database with multiple sites will need to scale up as the database grows in size and usage. The ability to distribute site load across multiple database server infrastructure/instances would not be effective.	Pro: If sites reside in their own databases, you can distribute of those databases across multiple database server infrastructure/instances.
Objects database	Con: Only one objects database can be used with all sites in the application database.	Pro: Sites can use different objects databases.
Licensing	Con: A shared license is required.	Pro: You can license each site separately, or use a shared license.
Portability	Con: Use the Copy Sites utility to copy the site to a different database, but no option to remove one site from an existing multi-site database.	Pro: Sites are physically separate in their own database, so portability is less of an issue. A site and its contents can be moved or removed if necessary.
Other considerations	Pro: UETs and events are shared between sites. (This can also be a “con.”) Con: You cannot isolate site-specific procedure changes, either programmatically or from a SQL security perspective. A change made to application code is visible to all sites in the same application database. Coding to exclude sites could be implemented in custom application code. Con: If you turn on performance tuning or troubleshooting (for example, Profiler, execution plan estimation, etc.), it impacts all sites. For tables that contain large binary fields, rebuilding an index locks the table for all sites.	Pro: Sites are logically separate, each with a complete set of data and programs. Pro: Performance tuning and troubleshooting have less impact on the other sites running on the infrastructure.