Discussion :

[Nuffspirit]

Bonjour,

J'aurai besoin de vos conseils : je souhaite désynchroniser mes livraisons de mon site Web avec mes livraisons SQL (Procédure, script de l'architecture, trigger).
Soit la version A de mon site Web et de mon paquet SQL livrée sur mes serveurs de production
Soit la version B de mon site Web et de mon paquet SQL que je dois livrer

D'habitude je livre mon site Web et mon paquet SQL en même temps dans la soirée ce qui permet que le lendemain matin la version B soit OK.
Aujourd'hui je souhaite désynchroniser la livraison de mon paquet SQL avec celle de mon site Web :
- N : Web en version A + SQL en version A
- N+1 : Web en version A + SQL en version B
- N+2 : Web en version B + SQL en version B

Je me suis dit qu'il fallait que je fasse transiter un numéro de version en mon site Web et mon SQL afin d'appeler la procédures SQL correspondant à la bonne version de mon site Web.
J'aurai une procédure comme point d'entrée qui s'occuperait de faire le routage sur la bonne procédure en fonction de la version du site Web.

Cette solution n'est pas forcement géniale mais au vu de mes connaissances et de mes recherches sur le Net, je n'en voit pas d'autre.
Savez vous si des fonctionnalités MS SQL peuvent gérer ce cas ou si vous avez déjà travaillé sur ce sujet ?

Merci d'avance.

[aieeeuuuuu]

Bonjour,

ça va vite devenir une usine à gaz...

Pour quelle raison ne voulez vous pas tout livrer en même temps ?

[Nuffspirit]

Tout d'abord toutes nos livraison sont réalisées en automatique la nuit et sont uniquement surveillés par une équipe dédié à ça.

Nos paquets SQL sont englobés dans une transac générale : cas de problème on effectue d'erreur un rollback est fait.
Du coup si l'installation du site Web est lancé dernière... Ca risque de planter.
- Site Web en version B et SQL e version A = KO

De ce fait on nous conseille de livrer notre paquet SQL une semaine avant le Web (nous livrons 1 fois par semaine à part les correctifs urgents).
Une des solution serait de gérer le numéro de version dans le nom de la procédure stockée et de laisser le Web piloter l'appel.

Effectivement il faudra faire du ménage une fois la livraison effectuée.

Avez vous d'autre idée ?
Merci

[SQLpro]

Il est possible de jouer sur une particularité peu connues de SQL Server. La gestion des versions des procédures. En effet, lors de sa création, une procédure est créée avec un numéro de version.

Exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE PROCEDURE P0;1
AS
SELECT 1 AS P0_VERSION1
GO
 
EXEC P0;
GO
 
 
CREATE PROCEDURE P0;2
AS
SELECT 2 AS P0_VERSION2
GO
 
EXEC P0
GO
 
EXEC P0;2
GO

Je ne sais pas si cela peut vous aider…. Et en plus c'est considéré comme deprecated, mais pas encore obsolète…

A +

[Nuffspirit]

Ok merci.
Je vais regarder ce point sur le numéro de version même si c'est déprécié.
Est ce que vous auriez de la documentation la dessus car je ne trouve rien sur ce sujet ?

Merci d'avance.

[Nuffspirit]

Effectivement dans la Doc MS SQL j'ai trouvé ça

; number
S’applique à : SQL Server 2008 à SQL Server 2017 et Azure SQL Database.

Entier facultatif utilisé pour regrouper des procédures de même nom. Ces procédures groupées peuvent être supprimées en même temps par le biais d'une seule instruction DROP PROCEDURE.

Note

Cette fonctionnalité est en mode de maintenance et risque d’être supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité.

Les procédures numérotées ne peuvent pas utiliser les types xml ou CLR définis par l’utilisateur, et ne peuvent pas être utilisées dans un repère de plan.

Dommage qu'il ne précise pas comment reconduire le comportement...

[aieeeuuuuu]

Pour reconduire le comportement, il suffit de passer un paramètre indiquant la version à chaque procédure stockée, et de traiter les différents cas par des instructions de branchement dans la procédure.

ou alors spécifier la version du client dans CONTEXT_INFO lors de la connexion, et le récupérer au besoin dans les procédures.

Dans les deux cas, comme je le disais, ça va vite devenir usine à gaz, et je pense qu'avec une telle solution, vous allez créer plus de problèmes que vous n'allez en résoudre...

Est-ce que vos migrations sont faites en ligne ou hors ligne ? Car sinon, une sauvegarde de la base avant migration, a restaurer en cas de problème, est tout de même bien plus simple...

[Nuffspirit]

Après le fait de passer un numéro de version dans la PS est un peu compliqué car si j'ai une différence dans la structure notamment en suppression, cela risque de planter lors de la création de la PS.
J'ai pensé à concaténer le numéro de la version dans le nom de la PS et gérer l'appel dans mon site Web. J'aurai :
- AJOUT_OBJET_X.YYY
- AJOUT_OBJET_X.ZZZ

Dans tout les cas il faudra faire du ménage entre chaque version.

[aieeeuuuuu]

Une autre solution serait de passer par un modèle externe de données, propre à chaque version :
un schéma (spécifique à chaque version), avec des vues pour l’accès aux tables, et des procédures qui appellent les procédures du schéma actuel.
On peut ensuite jouer sur le schéma par défaut en fonction de la version du client.

[Nuffspirit]

Salut,

Je ne comprends pas ce point

un schéma (spécifique à chaque version), avec des vues pour l’accès aux tables, et des procédures qui appellent les procédures du schéma actuel.

J'aurai donc mon schéma classique "dbo" qui contient mes tables avec mes data
- Je créée un nouveau schéma comportant uniquement les modifications entre chaque versions ?
- Je créée un nouveau schéma contenant toutes les procédures / vues / fonction par version ?
- Que se passe t'il en cas d'update d'une structure de table (ajout ou suppression) ?

Merci beaucoup de ton aide

[aieeeuuuuu]

oui, l'idée est bien d'avoir un (ou plusieurs) schéma(s) qui contiendront les tables, mais également les procédures stockées.

Vous pouvez ensuite créer un schéma par version, avec des procédures stockées (appelant les procédures existantes), des vues faisant appels aux tables...

en cas de suppression d'une colonne dans une table, vous pouvez modifier la vue du schéma n-1 comme ceci :
(suppression de la colonne téléphone de la table client)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE VIEW V1.client 
AS
SELECT Nom, prenom, NULL AS Telephone
FROM dbo.Client

(il sera sans doute nécessaire de caster le NULL dans le type de la colonne supprimée)

L'idée étant de garder un modèle externe stable malgré des modifications dans le modèle physique

[Nuffspirit]

Ok merci de ta réponse, je pense que c'est la meilleur solution est surtout la plus propre.
Super conseil en tout cas !!!

[Nuffspirit]

Bonjour,

Je reviens vers vous concernant cette mise en oeuvre :

en cas de suppression d'une colonne dans une table, vous pouvez modifier la vue du schéma n-1 comme ceci :
(suppression de la colonne téléphone de la table client)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
CREATE VIEW V1.client 
AS
SELECT Nom, prenom, NULL AS Telephone
FROM dbo.Client

(il sera sans doute nécessaire de caster le NULL dans le type de la colonne supprimée)

L'idée étant de garder un modèle externe stable malgré des modifications dans le modèle physique

Reprenons votre exemple : si j'utilise la vue V1 ci dessus pour insérer directement via la vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

INSERT INTO V1.client VALUES( 'Mon Nom', 'Mon prénom', '0601020304')

Sachant que dans la V2, j'aurai

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE VIEW V2.client 
AS
SELECT Nom, prenom
FROM dbo.Client
 
INSERT INTO V2.client VALUES( 'Mon Nom', 'Mon prénom')

J'ai l'erreur suivante

Update or insert of view or function 'v1.client ' failed because it contains a derived or constant field.

Est ce que vous auriez une idée du problème ?
J'ai regardé sur le net mais je n'ai rien trouvé de concluant.

Merci d'avance.

[aieeeuuuuu]

Vous avez dû faire une erreur dans vos tests.

L'erreur que vous indiquez a pu être provoquée en tentant un l'ordre d'insertion sur l'ancienne vue (et non la nouvelle), contenant les trois colonnes.
Comme vous ne spécifiez explicitement les colonnes visées par l'ordre d'insertion, SQL server ne sait pas ou mettre quoi...

[Nuffspirit]

Dans mon code j'ai ceci :

==> Vue V1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW v1.ViewThirdParty

AS
	SELECT Id
		, SIRET
		, CompanyName
		, DateUpdate
		, DateCreation
		, GuidThirdParty
		, NULL AS CompanyAddress
		FROM dbo.ThirdParty

GO

==> Insert V1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
ALTER PROCEDURE [v1].[CreateThirdParty]
	@SIRET					NVARCHAR(14)
	, @CompanyName		NVARCHAR(40)

AS

SET NOCOUNT ON

-- Déclaration des variables locales
DECLARE @CodRet INT	; SET @CodRet = 0
DECLARE @GuidTmp UNIQUEIDENTIFIER

BEGIN TRAN

SET @GuidTmp = newid()

INSERT INTO v1.ViewThirdParty (SIRET, CompanyName, DateUpdate, DateCreation, GuidThirdParty, CompanyAddress)
	VALUES (@SIRET, @CompanyName, NULL, GETDATE(), @GuidTmp, 'test')

SET @CodRet = @@ERROR
....

==> Et ma table 'dbo'

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TABLE [dbo].[ThirdParty](
	[Id] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[SIRET] [nvarchar](14) NOT NULL,
	[CompanyName] [nvarchar](40) NOT NULL,
	[DateUpdate] [datetime] NULL,
	[DateCreation] [datetime] NOT NULL,
	[GuidThirdParty] [uniqueidentifier] NOT NULL,

Lors de l'EXEC de la PSV1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
EXEC	[v1].[CreateThirdParty]
		@SIRET = N'123455',
		@CompanyName = N'TESTTEST'

Msg 4406, Level 16, State 1, Procedure CreateThirdParty, Line 18
Update or insert of view or function 'v1.ViewThirdParty' failed because it contains a derived or constant field.

[aieeeuuuuu]

ok, j'avais compris que c'était l'insertion dans V2 qui posait problème.

Pour insérer dans V1, il ne faut plus spécifier la colonne supprimée, pour les même raisons que celles évoquée plus haut.

Si vous ne voulez/pouvez pas modifier le programme faisant l'insertion dans V1, vous pouvez ajouter un trigger instead of qui va prendre en charge l'insertion dans la table, en zappant sciemment la colonne CompanyAddress ( ou en l'insérer dans une autre table si cette colonne a été "déplacée" dans une autre table).
En effet, l'insertion dans des vues n'est possible que pour les cas sans ambiguïté. Dans le cas présent, SQL Server ne sait pas quoi faire des données insérées dans la colonne CompanyAddress, il faut donc un trigger pour implémenter votre règle de gestion.

[Nuffspirit]

Ok merci pour votre réponse.
Je pense que dans le cas d'une suppression de colonne car c'est uniquement ce cas qui pose problème (à part les grosses modif de structure de schéma de base), je modifierai la structure de la table dans la version N+2.

N : schéma de base
N+1 : script qui intègre la suppression de la colonne mais uniquement dans les vue /PS du schéma N+1
N+2 : script qui intègre la suppression physique de la colonne ==> Comme dans cette version je serai obligé de supprimer le schéma N histoire de faire du ménage, je pense que cette solution est pas mal.

Quand pensez vous ?
Merci.

[aieeeuuuuu]

je pense que c'est source de bug supplémentaires...
la solution avec trigger instead of répond je pense à vos problèmes, et est très simple à mettre en œuvre

[Nuffspirit]

Ok merci de votre conseil.
Je vais regarder du côté du trigger.

[Nuffspirit]

Bonjour,

Je me permets de ré-ouvrir le sujet car j'ai commencé à mettre en place cette solution mais j'avais une autre question concernant les performances des vues : en effet nous avons potentiellement d'autres bases qui elles possèdent plusieurs millions d'enregistrement.

Est ce que le sujet que tu préconises (table - vues - PS) sera performant ?
Une de nos équipes à des tables partitionnées. Quelles peuvent être les impactes sur le telles bases ?

Je sais que la meilleur solution reste de générer un plan d'exécution pour chaque procédure qui utilise les vues. L'idée est d'avoir une vision globale sur le sujet pour mettre en place cette solution pour plusieurs de nos bases SQL.

Merci d'avance.