Estructura de la database y control de origen: mejores prácticas

Fondo

Llegué de varios años trabajando en una empresa donde todos los objects de la database se almacenaban en el control de código fuente, un file por object. Teníamos una list de todos los objects que se mantuvieron cuando se agregaron nuevos elementos (para permitirnos ejecutar scripts en order y manejar dependencies) y un script VB que se ejecutó para crear un script grande para ejecutarse en la database.

Todas las tablas eran 'crear si no existe' y todos los SP, etc., se soltaron y recrearon.

Hasta el presente y ahora estoy trabajando en un lugar donde la database es el maestro y no hay control de fuente para los objects DB, pero sí usamos las herramientas de networkinggate para actualizar nuestra database de producción (SQL compare), que es muy útil, y requiere poco trabajo.

Pregunta

¿Cómo manejas tus objects DB? Me gusta tenerlos bajo control de código fuente (y, como estamos usando GIT, me gustaría poder manejar conflictos de combinación en las secuencias de commands, en lugar de DB), pero voy a estar presionada para pasar la facilidad de usar SQL se compara para actualizar la database.

Realmente no quiero que actualicemos guiones en GIT y luego usar SQL compare para actualizar la database de producción de nuestro DEV DB, ya que prefiero tener 'una versión de la verdad', pero realmente no quiero consiga volver a escribir un poco de software personalizado para agrupar todo el set de secuencias de commands.

Creo que la edición de la database visual studio puede hacer algo similar a esto, pero no estoy seguro si tendremos el presupuesto para ello.

Estoy seguro de que esto se ha pedido hasta la muerte, pero no puedo encontrar nada que parezca tener la respuesta que estoy buscando. Similar a esto, pero no exactamente lo mismo:

Cuáles son las mejores prácticas para las secuencias de commands de bases de datos bajo control de código


Comencé una recompensa, ya que estoy interesado en solicitar más opiniones. Las respuestas aquí son buenas, pero creo que realmente debería haber una manera más fácil.

Gracias por todas las excelentes respuestas, todas tienen sus méritos, así que voy a tomar el voto más alto, pero aplaude por todas las aportaciones.

Tenemos todos nuestros objects de database bajo control de fuente usando Visual Studio Database Edition (DBPro). Es una maravillosa herramienta que la versión controla nuestro esquema, hace comstackciones, validaciones, permite el análisis de códigos, comparaciones de esquemas, implementaciones, comparaciones de datos, refactorización, etc. Fue diseñado desde cero para ser un sistema de administración de bases de datos y control de versiones. Muy recomendable.

Este es el sitio de blog del arquitecto principal de DBPro: http://blogs.msdn.com/gertd/archive/2008/11/25/visual-studio-team-system-2008-database-edition-gdr-rtm.aspx

Eche un vistazo a esta serie de cinco partes sobre los principios y prácticas del control de versiones de bases de datos (por K. Scott Allen):

  1. Tres reglas para el trabajo de la database
  2. La línea de base
  3. Cambiar guiones
  4. Vistas, procedimientos almacenados y similares
  5. Ramificación y fusión

Las cinco partes son importantes, pero básicamente la idea es tener una línea base y luego cambiar las secuencias de commands (con una tabla de versiones). Actualizar la database significa aplicar scripts de modificación "arriba" de la versión actual. Y esta estrategia es muy amigable para VCS (sin conflictos).

Si ya está utilizando las herramientas de Red Gate, podría considerar usar SQL Source Control, que funciona junto con SQL Compare y SQL Data Compare para permitir que exista una versión de la verdad en el control de la fuente. Está en acceso anticipado en este momento, pero la mayoría de la funcionalidad está allí para probarse. Puede download esto de http://www.networking-gate.com/Products/SQL_Source_Control/index.htm . Sin embargo, solo admite SVN y TFS por el momento. ¿Has estandarizado en GIT?

David (Product Manager en Red Gate)

Suponiendo que usa .NET Framework, eche un vistazo al Fluent Migrator y también al Headsing Code Podcast que habla sobre el proyecto.
El objective principal, como lo veo, es codificar fácilmente las migraciones a medida que realiza su encoding normal utilizando una interfaz fluida utilizando un enfoque independiente de la database.

Está construido sobre el framework .net. y funciona con varios formattings de bases de datos, incluidos SQL Server, SqlLite y MySQL.

La ventaja de este enfoque es que vive con el rest de tu código y, por lo tanto, puede ser administrado por SCM.

Ejemplo:

  [Migration(1)] public class CreateProjectsTable : Migration { public void Up() { Create.Table("Projects") .WithIdColumn() .WithColumn("Name").AsString().NotNullable() .WithColumn("Position").AsInt32().NotNullable() .WithColumn("Done").AsBoolean().NotNullable(); } public void Down() { Database.RemoveTable("Projects"); } } 

Tenemos un sistema en el que la database es nominalmente maestra: dentro de nuestro sistema de control de origen, mantenemos una secuencia de scripts de "cambio de esquema" (files .sql), cada uno de los cuales es responsable de deshacer el cambio y aplicarlo idempotentemente. Cada script está solo numerado, por lo que tenemos 000.sql (que crea la database y establece objects estándar), 001.sql, etc.

Durante el desarrollo, un desarrollador escribe un script de cambio de esquema y lo ejecuta contra la database de desarrollo. Se requiere cada cambio para agregar una fila en una tabla dba.change_info , que contiene el número de cambio y una breve descripción. Para revertir un cambio, uno solo puede ejecutar la primera sección del mismo. Para SQL Server, la idempotencia de la sección de reversión se maneja mediante el examen de sysobjects, etc., antes de emitir commands DROP, similar a las construcciones "drop … if exists". Es posible que los cambios de esquema necesiten realizar la migration de datos si se está modificando un model en lugar de simplemente agregarlos, y también se usan para mantener los datos de reference.

Durante el process de lanzamiento, un DBA (somos una empresa pequeña, por lo que este es un rol asumido por uno de los desarrolladores) aplica los cambios de esquema para el lanzamiento a la database de producción entre detener la versión anterior de las aplicaciones y comenzar los actualizados.

Todo esto es un process completamente manual, pero cumple con requisitos como la migration de datos de un model a otro: por ejemplo, expandir un indicador boolean a un set de opciones o convertir una asociación de muchos a uno a muchos a muchos. Por lo general, esto no es algo que pueda generarse con simples herramientas de comparación de esquemas. También permite la separación de roles, aunque en la práctica todos tenemos acceso completo a la producción, hay suficiente desacoplamiento para que el "DBA" pueda leer y revisar los files .sql que se aplicarán en la producción.

En teoría, al less, una database completa (que contiene solo datos de reference) podría buildse simplemente ejecutando todos los cambios de esquema para 000.sql en adelante. En la práctica, no hacemos esto regularmente, sino que copymos nuestra database de producción a dev y luego aplicamos los scripts de cambio antes de ejecutar las testings de regresión antes de una publicación. Esto sirve para probar los scripts de cambio en sí, pero solo es práctico con una database de producción de tamaño mediano.

No estoy muy familiarizado con RedGate toolkit, pero si es similar a dbGhost , debe haber una utilidad que le permita crear los objects de la database a los files uno por object. En este caso, sugeriría lo siguiente:

  • agregue un trabajo diario (o parte de una construcción) para realizar ingeniería inversa de la database DEV en la estructura de directorys
  • luego compárelo con lo que tiene en el repository (por medio de diff simple), y BÁSICAMENTE FAIL el trabajo de compilation e informe el diff si hay alguno. Esto indicará que la estructura de la database DEV ha cambiado y no se refleja en el control de fuente,
  • lo que indicará al desarrollador que agregue los cambios al control de origen (incluso use el file .diff reportado para esto)

Si tiene muchas bases de datos DEV (una por usuario o twig de desarrollo) y es demasiado engorrosa, probablemente una mejor combinación sería realizar dicha tarea en la versión STAGE (TEST justo antes de la versión) de la database, momento en el que almacenaría el esquema PROD en el repository y lo actualizaría desde la ETAPA solo durante la fase de testing previa a la liberación, donde se asegurará de que sus cambios de esquema también estén en el repository.

De esta forma, los desarrolladores pueden seguir trabajando de la manera habitual: primero cambie el esquema en la database DEV y, con suerte, obtenga el equilibrio entre la flexibility y one truth que le gustaría.

En mi equipo agregamos cambios a VCS tan pronto como cambiamos la database DEV, pero todavía tenemos esa tarea para comparar el esquema entre diferentes bases de datos (DEV, STAGE y PROD). Básicamente, seguimos lo que una vez respondí en ¿Cómo debería build su database desde el control de código fuente? .

Actualmente mantengo un layout de database en una herramienta de modelado (DeZine for Databases) y lo almacena bajo control de fuente. Dentro de mi layout de tabla agrego una tabla con dos filas que tienen el número de versión del esquema y de los datos de reference, esto se actualiza cada vez que se cambia / suelta la database (los usuarios no tienen acceso a esta tabla).

Los datos de reference se mantienen en una spreadsheet de Excel (también bajo control de fuente) que puede generar una secuencia de commands SQL de instrucciones INSERT para rellenar nuevas bases de datos.

Cuando se requiere una nueva versión, se envían la secuencia de commands de esquema, la secuencia de commands de datos de reference y un package de installation. El package de instalador cambia el nombre de la database anterior, crea una nueva database a partir del script e importa los nuevos datos de reference (que también pueden haber cambiado). Los datos del usuario se copyn desde la database anterior (renombrada) a la nueva.

Esto tiene la ventaja de que cuando las cosas van mal puede volver a la database original ya que no se ha modificado.

Hay una herramienta especial para esto exacto. Se llama Wizardby :

… continuous integration de bases de datos y marco de migration de esquemas

Wizardby Workflow http://octalforty-wizardby.googlecode.com/svn/trunk/docs/img/database_versioning_with_wizardby.png

En el trabajo hacemos un uso intensivo de una poderosa herramienta que viene como parte de ActiveRecord (que es el ORM pnetworkingeterminado que viene con el marco web de Rails llamado Migrations) .

Una migration básica se vería así:

 class AddSystems < ActiveRecord::Migration def self.up create_table :systems do |t| t.string :name t.string :label t.text :value t.string :type t.integer :position, :default => 1 t.timestamps end end def self.down drop_table :systems end end 

Se ha creado una migration para cada cambio en la database, y se crean en order secuencial por timestamp. Puede ejecutar methods pnetworkingefinidos para ejecutar estas migraciones en el order correcto, de modo que siempre se pueda crear y / o retrotraer su database. Algunas de las funciones están a continuación:

 rake db:migrate #run all outstanding migrations rake db:rollback #roll back the last run migration rake db:seed #fill the database with your seed data 

Las migraciones tienen methods para crear tablas, eliminar tablas, actualizar tablas, agregar índices, etc. El set completo. Las migraciones también agregan automáticamente una columna de id . Y la sección t.timestamps genera automáticamente un campo "created_at" y un campo "updated_at".

La mayoría de los lenguajes tienen instalaciones ORM como estas, y permiten que la database se mantenga en un estado de código, lo cual es fácil de entender para los desarrolladores, además de ser lo suficientemente simple para que los DBA puedan usarla y mantenerla.