SQL Azure: comment exécuter un script DDL commun sur tous les schémas?

Vous devez être très prudent avec un environnement multi-locataire. Il y a tellement de moyens faciles d'accélérer les choses. Comme vous l'avez indiqué, votre script DDL doit être édité par schéma et cela signifie changer les noms des objects dans le script. C'est effrayant, mais je comprends que ce soit nécessaire. Il introduit un vector pour l'injection SQL.

J'espère que vous avez un ou plusieurs schémas "sûrs" à tester avec. Heck, j'espère que vous avez tout un monde de test pour tester. Mais – tous les soucis mis à part, voici un échafaudage d'un script que j'ai utilisé dans le passé pour appliquer des modifications à un environnement multi-schéma:

 create type dbo.ObjectNamesType as table ( Name sysname ) go create procedure RunDDL( @scriptTemplate nvarchar( max ), @objName sysname, @objType nvarchar( 10 ), @schemas dbo.ObjectNamesType readonly ) as begin set nocount on declare @script nvarchar( max ) declare @objectName nvarchar( 256 ) declare c cursor for select N'[' + s.name + N'].[' + o.name + N']' from sys.objects o inner join @schemas s on o.schema_id = schema_id( s.Name ) where o.name = @objName and o.type = @objType open c while ( 1=1 ) begin fetch next from c into @objectName if ( @@fetch_status != 0 ) break; select @script = replace( @scriptTemplate, N'@objectName', @objectName ) exec sys.sp_executesql @script end close c deallocate c end go 

… et pour le tester …

 declare @script nvarchar( max ) declare @objName sysname declare @objType sysname declare @schemas dbo.ObjectNamesType insert @schemas values( 'dbo' ) select @objName = 'someTable' select @objType = 'u' select @script = 'select * from @objectName' --> not really ddl, eh? exec dbo.RunDDL @script, @objName, @objType, @schemas 

Celui que j'ai réellement utilisé est beaucoup plus compliqué – alors j'ai juste laissé les parties juteuses derrière. Quelques notes:

Les inputs sont configurées de telle sorte que le script peut être exécuté sur un groupe de schémas. Cela vous permet de l'exécuter d'abord sur votre schéma de test et de voir si tout va bien – et en supposant que vous l'aimiez, vous pouvez ensuite l'exécuter en masse sur les schémas restants.

Dans mon monde, les @templateScript , @objName et @objType résident dans une table à laquelle je rejoins – et ne sont pas transmises. Je ne reorderais pas d'exécuter une telle procédure avec des inputs du monde extérieur, car ceci est une invitation à catastrophe … mais pour illustrer / tester, cela sert le but. De plus, dans mon monde, la table d'input a un ID de version et une séquence. Pour tous les schémas de la version x , nous exécutons tous les scripts en séquence et en supposant que la version de ce schéma est réussie. Chaque script s'applique à un seul object.

Le point ici est que vous voulez faire un stream de travail de routine à partir des mises à jour de la database – et cette procédure est au cœur de ce stream de travail.

Notez qu'il sélectionne à partir de sys.objects plutôt que de simplement croire les inputs. Ceci est juste une autre petite sauvegarde pour garder vos scripts de barfing sur les fautes de frappe dans les noms. Si le nombre d'objects que nous éditons ne correspond pas au nombre d'objects spécifiés, nous enregistrons un avertissement pour nous-mêmes.

Une procédure de ce type devrait également essayer / attraper l'exécution réelle du script et devrait se connecter tout ce qu'il essaie le long du path. Cela devrait renvoyer les choses à l'erreur. Assurez-vous de disposer de beaucoup d'espace dans le journal des transactions, car même un DDL minuscule peut provoquer d'immenses changements.

Il fonctionne à travers les objects spécifiés, en éditant le @templateScript dans une variable @script , puis en exécutant la variable sys.sp_executesql avec sys.sp_executesql . De cette façon, il ne change jamais la variable source de sorte que la cible de rlocation rest intacte.

Il est regrettable que vous ne puissiez pas utiliser de variables pour les noms d'objects tsql, ou vous pourriez réduire la surface pour une attaque par injection. Ainsi, la recommandation pour une table d'inputs plutôt que des arguments. Cela signifie également que SQL ne peut vraiment rien faire pour paramétrer / réutiliser le plan d'exécution / d'exécution – mais là encore, ce n'est pas quelque chose qui est exécuté des milliards de fois, n'est-ce pas?