Que diferencia hay entre instancia de base de datos y una base de datos?

¿Qué diferencia hay entre Instancia y Base de Datos? Esta es una pregunta muy habitual, sobre todo, para aquellos que vienen de trabajar con otros motores de base de datos (ej: ORACLE) y también para aquellos que empiezan a trabajar con SQL Server (sin experienza). ¿Cuántas instancias de SQL Server me interesa mantener? ¿Cómo puedo organizarlas? ¿Qué motivos pueden implicar la utilización de una instancia dedicada? ¿utilizar múltiples instancias o múltiples bases de datos, cuando sólo disponemos de un único servidor?

Una Instancia de SQL Server es una instalación del motor de base de datos SQL Server, que se materializa en un Servicio de Windows que ejecuta un proceso sqlservr.exe con una configuración determinada, y sus propias bases de datos (las bases de datos del sistema, y la o las bases de datos de usuario). En un mismo equipo, pueden instalarse y ejecutarse varias Instancias (distintos procesos sqlservr.exe, cada uno con su configuración y bases de datos). Cada Base de Datos mantiene sus propios ficheros de datos (dónde se almacenan las tablas, índices, vistas, procedimientos almacenados, y resto de objetos de base de datos), ficheros de LOG (dónde se almacenan las transacciones de dicha base de datos), configuración (Modo de Recuperación o Modo de Registro, intercalación, nivel de compatibilidad, etc.). Por el contrario, todas las bases de datos de una instancia particular, comparten la base de datos TEMPDB (dónde se almacenan los objetos temporales, resultados intermedios de consultas, etc.) y otros recursos de la Instancia, como la memoria, la afinidad de CPU, y la afinidad de entrada/salida (E/S). En instalaciones sobre Microsoft Cluster (MSCS), es una buena práctica instalar una Instancia por nodo, debido a que la solución Cluster de SQL Server es una solución Activo/Pasivo. Así, en un Cluster de dos nodos, podemos tener una Instancia ejecutándose en cada uno de los nodos, y aprovechar así al máximo los recursos hardware que poseemos, en vez de dejar un nodo sin carga. En caso de fallo en un nodo, se producirá un balanceo, y podremos mantener el servicio ejecutándose sobre el nodo alternativo (quizas algo sobrecargado al disponer las dos instancias temporalmente) durante el tiempo necesario. ¿Cuántas instancias de SQL Server me interesa mantener? ¿Cómo puedo organizarlas? ¿Qué motivos pueden implicar la utilización de una instancia dedicada? Bajo mi punto de vista (ojo, que me puedo equivocar, y además cada cliente es un mundo), es interesante utilizar el menor número posible de Instancias de SQL Server. Del mismo modo, es interesante mantener una Instancia para cada entorno de ciclo de vida que estemos utilizando. Es decir, si sólo utilizamos un entorno de Desarrollo y un entorno de Producción, pues dos Instancias de SQL Server. Si utilizamos un entorno de Desarrollo, otro de Pruebas Integradas, otro de Calidad y Pre-Producción, y otro de Producción, pues entonces necesitaremos cuatro Instancias de SQL Server. También es recomendable instalar cada instancia en un servidor dedicado. Es decir, si necesitamos cuatro instancias, utilizar cuatro servidors. Es evidente que en muchos casos esto se deseará reducir por costes, y/o porque no sea necesario una infraestructura tan exquisita. En tal caso, se puede utilizar una Instancia de SQL Server para Producción y otra para el resto, etc. En caso de utilizar un Microsoft Cluster (MSCS) para la instalación de SQL Server, es interesanteinstalar una Instancia de SQL Server por cada Nodo del Cluster, de tal modo, que siempre tengamos los Nodos con carga, y no perdamos dinero con nuestra infraestructura (y por supuesto, mantengamos la Alta Disponibilidad). En ocasiones surge la duda de si utilizar múltiples instancias o múltiples bases de datos, cuando sólo disponemos de un único servidor. Esta problemática tiene dos puntos de vistas: Es más interesante una única instancia que sobrecargar la máquina con múltiples instancias que puedan pelear por los recursos (Memoria, Procesador y acceso a disco), y además simplificar la administración (copias de seguridad, Service Packs, mantenimiento de los Jobs, etc.). Sin embargo, si tenemos una única instancia de SQL Server, y deseamos utilizarla para cubrir varios entornos de ciclo de vida, tenemos el problema de que los nombres de las bases de datos son únicos... es decir, si la base de datos de nuestra Intranet en el entorno de desarrollo y en el entorno de producción se llama IntraBD, para el entorno de pruebas tendremos que darle otro nombre ! Esto en ocasiones puede resultar algo problemático, en función de qué aplicación se trate. Por ejemplo, en ocasiones es posible encontrar en el código Transact-SQL referencias a objetos de base de datos cualificados con el nombre de la base de datos, por lo tanto, esos detalles habrá que tenerlos en cuenta a la hora de promocionar dichos objetos entre entornos (si los nombres de las bases de datos varía, claro).