sql-server - studio - importar datos de excel a sql en una tabla existente

Compartir datos entre bases de datos SQL (5)

Estoy tratando de resolver un problema que, por una vez, no creé.

Trabajo en un entorno con muchas aplicaciones web respaldadas por diferentes bases de datos en diferentes servidores.

Cada base de datos es bastante única en su diseño y aplicación, pero aún hay datos comunes en cada una que me gustaría abstraer. Cada base de datos, por ejemplo, tiene una tabla de proveedores, una tabla de usuarios, etc.

Me gustaría abstraer estos datos comunes a una sola base de datos, pero aun así permitir que las otras bases de datos se unan a estas tablas, incluso tener claves para imponer restricciones, etc. Estoy en un entorno MsSql.

¿Cuáles son las opciones disponibles? A mi modo de ver, tengo las siguientes opciones:

Servidores vinculados
Solo se registran los inicios de sesión para dar acceso a las vistas.

¿Hay algo más a considerar?

Creo que deberías echar un buen vistazo a la replicación, como lo han indicado muchas respuestas, especialmente en un entorno de alto TPS o si quieres esto en muchas tablas. Sin embargo, ofreceré algunos códigos sobre cómo lograr los objetivos establecidos en algunos de mis sistemas utilizando servidores vinculados, sinónimos y restricciones de verificación.

Me gustaría abstraer estos datos comunes a una sola base de datos, pero aun así permitir que las otras bases de datos se unan a estas tablas, incluso tener claves para imponer restricciones, etc.

Puede configurar una vista o synonym en sus bases de datos a una tabla común en un servidor vinculado (u otra base de datos local). Prefiero los sinónimos si la vista hubiera sido select * from table todos modos.

Un sinónimo de tabla le permitirá ejecutar DML en el elemento remoto si tiene permisos.

En este punto, sin embargo, no puede tener una clave externa para su vista o sinónimo, pero podemos lograr algo similar con una restricción de verificación.

Veamos un código:

create synonym MyCentralTable for MyLinkedServer.MyCentralDB.dbo.MyCentralTable go create function dbo.MyLocalTableFkConstraint ( @PK int ) returns bit as begin declare @retVal bit select @retVal = case when exists ( select null from MyCentralTable where PK = @PK ) then 1 else 0 end return @retVal end go create table MyLocalTable ( FK int check (dbo.MyLocalTableFKConstraint(FK) = 1) ) go -- Will fail: -1 not in MyLinkedServer.MyRemoteDatabase.dbo.MyCentralTable insert into MyLocalTable select -1 -- Will succeed: RI on a remote table w/o triggers insert into MyLocalTable select FK from MyCentralTable

Por supuesto, es importante tener en cuenta que no obtendrá un error si elimina un registro de referencia en su tabla central.

Echa un vistazo a Microsoft Sync Framework . Tendrá que escribir una aplicación de sincronización, pero podría darle la flexibilidad que necesita.

Hay muchas maneras en que podría abordar este problema. Recomiendo encarecidamente cualquiera de las soluciones 1, 2 o 3 según las necesidades de su negocio:

Replicación transaccional : si la base de datos común es el registro de la cuenta y desea proporcionar versiones de solo lectura de los datos a aplicaciones separadas, puede replicar las tablas principales, posiblemente incluso las columnas centrales de las tablas, en cada servidor separado. Una ventaja de este enfoque es que puede replicar a tantas bases de datos de suscriptores como desee. Esto también significa que puede personalizar qué tablas y campos están disponibles para los suscriptores según sus necesidades. Entonces, si una aplicación necesita tablas de usuario y no tablas de proveedores, entonces solo se suscribe a las tablas de usuario. Si otro solo necesita tablas de proveedores y no tablas de usuarios, entonces puede suscribirse solo a las tablas de proveedores. Otra ventaja es que la replicación se mantiene sincronizada y siempre se puede reinicializar una suscripción si surge un problema.
He utilizado la replicación transaccional para expulsar más de 100 tablas de un almacén de datos para separar aplicaciones posteriores que necesitaban acceso a datos agregados de múltiples sistemas. Dado que nuestro almacén de datos se actualizó en una hora programada desde fuentes de datos de envío de récord y registro, las aplicaciones de producción tenían datos de numerosos sistemas dentro de una ventana deslizante de 20 a 80 minutos por hora.
La replicación transaccional de igual a igual como tipo de publicación puede ser más adecuada para el caso de uso que proporcionó. Esto puede ser realmente útil si desea desplegar los cambios de esquema o replicación nodo por nodo. La replicación transaccional estándar tiene algunas limitaciones en esta área.
Los tipos de publicación de replicación de instantáneas tienen más latencia que las publicaciones transaccionales, pero es posible que desee considerarla si un grado de latencia es aceptable.
Aunque mencionó que es una tienda de Microsoft SQL Server, tenga en cuenta que otras RDBM tienen tecnologías similares. Ya que está hablando de MS SQL Server específicamente, tenga en cuenta que la replicación transaccional también le permite replicar en bases de datos Oracle. Entonces, si tiene algunos de estos en su organización, esta solución aún puede funcionar.
Una desventaja del uso de la replicación transaccional es que si su servidor central se desactiva, puede comenzar a experimentar latencia con los datos en copias posteriores de los objetos replicados. Si los objetos replicados (artículos) son realmente grandes y necesita reinicializar una tabla, entonces eso también puede tomar mucho tiempo.
Mirrors : si desea que la base de datos sea accesible casi en tiempo real en servidores de flujo descendente, puede configurar hasta dos espejos asíncronos. He integrado datos con una aplicación de CRM de esta manera. Todas las lecturas vinieron de juntas al espejo. Todas las escrituras se enviaron a una cola de mensajes que luego aplicó los cambios al servidor de producción central. La desventaja de este enfoque es que no puede crear más de 2 espejos asíncronos. No desea utilizar espejos síncronos para este propósito a menos que también planee usar los espejos para la recuperación de desastres.
Sistemas de mensajería : si espera tener numerosas aplicaciones separadas que necesitan datos de una sola base de datos central, entonces puede considerar sistemas de mensajería empresariales como IBM Web Sphere, Microsoft BizTalk, Vitria, TIBCO, etc. Estas aplicaciones están diseñadas específicamente para abordar este problema. Tienden a ser costosos y difíciles de implementar y mantener, pero pueden ampliarse si tiene sistemas distribuidos globalmente o docenas de aplicaciones separadas que necesitan compartir datos hasta cierto punto.
Servidores vinculados : Parece que ya pensaste en este. Podrías exponer los datos a través de servidores vinculados. No creo que esta sea una buena solución. Si realmente desea ir por esta ruta, considere la posibilidad de configurar un espejo asíncrono desde la base de datos central a otro servidor y luego configure las conexiones del servidor vinculado al espejo. Esto al menos mitigará el riesgo de que una consulta desde las aplicaciones web cause problemas de bloqueo o de rendimiento con su base de datos de producción central.
En mi opinión, los servidores vinculados tienden a ser un método peligroso para compartir datos para aplicaciones. Este enfoque aún trata los datos como un ciudadano de segunda clase en su base de datos. Esto conduce a algunos hábitos de codificación bastante malos, especialmente porque sus desarrolladores pueden estar trabajando en diferentes servidores en diferentes idiomas con diferentes métodos de conexión. No sabes si alguien va a escribir una consulta realmente grata contra tus datos centrales. Si establece un estándar que requiere enviar una copia completa de los datos compartidos al servidor no central, entonces no tiene que preocuparse por si un desarrollador escribe un código incorrecto o no. Al menos desde la perspectiva de que su código deficiente no jeapordizará el rendimiento de otros sistemas bien escritos.
Hay muchos, muchos recursos por ahí que explican por qué usar servidores vinculados puede ser malo en este contexto. Una lista no exhaustiva de motivos incluye: (a) la cuenta utilizada para el servidor vinculado debe tener permisos de DBCC SHOW STATISTICS o las consultas no podrán hacer uso de las estadísticas existentes , (b) las sugerencias de consulta no pueden ser uesd a menos que enviados como una OPENQUERY, (c) los parámetros no se pueden pasar cuando se usan con OPENQUERY, (d) el servidor no tiene estadísticas suficientes sobre el servidor vinculado, por lo tanto, crea planes de consulta bastante terribles, (e) los problemas de conectividad de la red pueden provocar fallos, (f) cualquiera de estos cinco problemas de rendimiento y (g) el temido error de contexto de SSPI al intentar autenticar las credenciales de Windows Active Directory en un escenario de doble salto . Los servidores vinculados pueden ser útiles para algunos escenarios específicos, pero no se recomienda crear acceso a una base de datos central en torno a esta función, aunque técnicamente es posible.
Proceso de ETL a granel: si un alto grado de latencia es aceptable para las aplicaciones web, entonces podría escribir procesos de ETL en masa con SSIS (muchos enlaces buenos en esta pregunta de ) que ejecutan los trabajos del agente de SQL Server para mover datos entre servidores. También hay otras herramientas ETL alternativas como Informatica, Pentaho, etc., así que use lo que mejor le funcione.
Esta no es una buena solución si necesita un bajo grado de latencia. He utilizado esta solución al sincronizar con una solución de CRM alojada por terceros para campos que podrían tolerar una latencia alta. Para los campos que no podían tolerar una alta latencia (datos básicos de creación de cuentas), confiamos en crear registros duplicados en el CRM a través de llamadas de servicio web en el punto de generación de la cuenta.
Copias de seguridad y restauraciones nocturnas: si sus datos pueden tolerar altos grados de latencia (hasta un día) y períodos de indisponibilidad, puede hacer una copia de seguridad y restaurar la base de datos en todos los entornos. Esta no es una buena solución para aplicaciones web que necesitan un 100% de tiempo. La idea es que realice una copia de seguridad de línea de base, la restaure a un nombre de restauración por separado, luego cambie el nombre de la base de datos original y la nueva tan pronto como la nueva esté lista para usar. He visto esto hecho para algunas aplicaciones internas de sitios web, pero generalmente no recomiendo este enfoque. Es más adecuado para un entorno de desarrollo más bajo, no un entorno de producción.
Secundarios de envío de registros : puede configurar el envío de registros entre los secundarios principales y cualquier número de secundarios. Esto es similar al proceso nocturno de copia de seguridad y restauración, excepto que puede actualizar la base de datos con más frecuencia. En una instancia, esta solución se utilizó para exponer datos de uno de nuestros principales sistemas centrales para usuarios intermedios al cambiar entre dos destinatarios de envío de registros. Había otro servidor que señalaba las dos bases de datos y cambiaba entre ellas cada vez que la nueva estaba disponible. Realmente odio esta solución, pero la única vez que vi esta implementación cumplió con las necesidades del negocio.

Puede haber otras opciones, pero cree que es el camino correcto para la mejor opción con una combinación de servidores vinculados y vistas. Esto podría ser tan simple como crear una nueva base de datos, agregar dos servidores vinculados, establecer sus permisos y luego crear la vista necesaria.

Si sus objetivos son abstract out this common data to a single database but still let the other databases join on these tables, even have keys to enforce constraints , esta solución debería funcionar bien.

En el lado negativo, puede tener problemas de rendimiento con los servidores vinculados, por lo que si prevé que la base de datos obtendrá mucho tráfico, es posible que desee ver cómo realmente se mueven los datos utilizando los métodos sugeridos por Doug o mwebber.

Si va a la ruta del servidor vinculado, le recomendaría leer en OPENQUERY . Hay un buen artículo en OPENQUERY vs 4 identificadores de partes here .

También puede considerar el uso de la replicación integrada de SQL Server entre el almacén de datos común y las bases de datos de aplicaciones. Desde mi experiencia, es muy adecuado para la transferencia de datos de dos vías, y hay una instancia de las tablas en cada db que permite el uso de claves externas (no creo que los FK sean posibles a través de un servidor vinculado).