Comment inclure des migrations de données personnalisées et des données statiques / de reference dans un projet SSDT?

Nous avons un projet SSDT de taille moyenne (environ 100 tables) déployé sur des douzaines d'instances de database différentes. Dans le cadre de notre process de génération, nous générons un file .dacpac, puis lorsque nous sums prêts à mettre à niveau une database, nous générons un script de publication et l'exécutons sur la database. Certaines instances db sont mises à jour à des moments différents, il est donc important que nous ayons un process structuré pour ces mises à jour et versions.

La plupart des scripts de migration générés suppriment et (re) créent des procs, des fonctions, des index et effectuent des changements structurels, plus quelques scripts de données inclus dans un script Post-Deployment. Ce sont ces deux éléments liés aux données que j'aimerais savoir comment structurer au mieux le projet:

1. Migrations de données personnalisées nécessaires entre les versions

2. Données statiques ou de reference

   • Exportation de SQLite vers SQL Server
   • La string SQL Server ou datatables binarys sont tronquées

Migrations de données personnalisées nécessaires entre les versions

Parfois, nous voulons effectuer une migration de données ponctuelle dans le cadre d'une mise à niveau et je ne suis pas sûr de la meilleure façon d'intégrer cela dans notre projet SSDT. Par exemple, récemment, j'ai ajouté une nouvelle colonne bit dbo.Charge.HasComments pour contenir des données dérivées (redondantes) basées sur une autre table et qui seront conservées en synchronisation via des triggersurs. Une amélioration des performances ennuyante mais nécessaire (seulement ajouté après examen et mesure). Dans le cadre de la mise à niveau, le script de publication généré par SSDT contiendra les instructions ALTER TABLE et CREATE TRIGGER nécessaires, mais je souhaite également mettre à jour cette colonne en fonction des données d'une autre table:

 update dbo.Charge set HasComments = 1 where exists ( select * from dbo.ChargeComment where ChargeComment.ChargeId = Charge.ChargeId ) and HasComments = 0 

Quelle est la meilleure façon d'inclure ce script de migration de données dans mon projet SSDT?

Actuellement, j'ai chacun de ces types de migrations dans un file séparé inclus dans le script Post-Deployment, mon script Post-Deployment finit par ressembler à ceci:

 -- data migrations :r "data migration\Update dbo.Charge.HasComments if never populated.sql" go :r "data migration\Update some other new table or column.sql" go 

Est-ce la bonne façon de le faire, ou existe-t-il un moyen de mieux relier SSDT et son suivi de version, de sorte que ces scripts ne sont même pas exécutés lorsque la publication SSDT est exécutée sur une database déjà plus récente . Je pourrais avoir ma propre table pour suivre les migrations qui ont été effectuées, mais je préférerais ne pas rouler moi-même s'il y a une façon standard de faire ça.

Données statiques ou de reference

Certaines tables de database contiennent ce que nous appelons datatables statiques ou de reference, par exemple la list des fuseaux horaires possibles, les types de parameters, les devises, les diverses tables de types, etc. Nous avons actuellement un script séparé pour chaque table. le script de post-deployment. Chaque script de données statiques insère toutes datatables statiques «correctes» dans une variable de table, puis insère / met à jour / supprime la table de données statiques selon les besoins. En fonction de la table, il peut être approprié d'insert ou de seulement insert et supprimer mais de ne pas mettre à jour les loggings existants. Donc chaque script ressemble à ceci:

 -- table listing all the correct static data declare @working_data table (...) -- add all the static data that should exist into the working table insert into @working_data (...) select null, null null where 1=0 union all select 'row1 col1 value', 'col2 value', etc... union all select 'row2 col1 value', 'col2 value', etc... ... -- insert any missing records in the live table insert into staticDataTableX (...) select * from @working_data where not exists ( select * from staticDataTableX where [... primary key join on @working_data...] ) -- update any columns that should be updated update staticDataTableX set ... from staticDataTableX inner join @working_data on [... primary key join on @working_data...] -- delete any records, if appropriate with this sort of static data delete from staticDataTableX where not exists ( select * from staticDataTableX where [... primary key join on @working_data...] ) 

et puis mon script Post-deployment a une section comme celle-ci:

 -- static data. each script adds any missing static/reference data: :r "static_data\settings.sql" go :r "static_data\other_static_data.sql" go :r "static_data\more_static_data.sql" go 

Existe-t-il une manière meilleure ou plus conventionnelle de structurer de tels scripts de données statiques dans le cadre d'un projet SSDT?

 EXEC sys.sp_addextendedproperty @name = N'EP_Charge_HasComments', @value = N'Initialized', @level0type = N'SCHEMA', @level0name = dbo, @level1type = N'TABLE', @level1name = Charge, @level2type = N'COLUMN', @level2name = HasComments; 

 SELECT objtype, objname, name, value FROM fn_listxtendedproperty (NULL, 'SCHEMA', 'dbo', 'TABLE', 'Charge', 'COLUMN', 'HasComments'); 

 MERGE INTO staticDataTableX AS Target USING ( VALUES ('row1_UniqueID', 'row1_col1_value', 'col2_value'), ('row2_UniqueID', 'row2_col1_value', 'col2_value'), ('row3_UniqueID', 'row3_col1_value', 'col2_value'), ('row4_UniqueID', 'row4_col1_value', 'col2_value') ) AS Source (TableXID, col1, col2) ON Target.TableXID = Source.TableXID WHEN MATCHED THEN UPDATE SET Target.col1 = Source.col1, Target.col2 = Source.col2 WHEN NOT MATCHED BY TARGET THEN INSERT (TableXID, col1, col2) VALUES (Source.TableXID, Source.col1, Source.col2) WHEN NOT MATCHED BY SOURCE THEN DELETE;