Mirroring : Modification des privilèges

Version imprimable

22/07/2015, 14h52
Kropernic

Mirroring : Modification des privilèges

Hello,

Une question me taraude...

Nous avons chez nous quelques bases en mirroring histoire d'avoir un semblant de always-on en cas de pépin.

Si je crée un nouveau user sur la base DB1 du server S1, j'imagine qu'il n'est pas créé sur la base DB2 du server S2 non ? Vu que le user dépend du login et que le login ne sera pas créé automatiquement sur le server S2.

J'ai juste ?

Quelle est la bonne pratique ??
22/07/2015, 16h03
SQLpro

L'utilisateur sera bien créé, le compte de connexion non. Il faut simplement le créer aussi si le miroir.

A +
22/07/2015, 16h07
Kropernic

Tiens c'est marrant ça.

Ca ne lui pose de problème d'avoir des users sans logins ?

Quoi qu'il en soit, merci pour l'info :)
22/07/2015, 18h03
SQLpro

Non...

Extrait de la syntaxe 2005, toujours valide en 2016 :

CREATE USER user_name
WITHOUT LOGIN

https://msdn.microsoft.com/fr-fr/lib...sql.90%29.aspx

Le mappage étant dynamique

A +
22/07/2015, 18h50
Kropernic

Les db sont bien faites quand même :)
26/07/2015, 12h32
elsuket
Attention tout de même : il faut que les SecurityID (SID) des logins et des utilisateurs soient identiques sur les deux serveurs.
En effet, SQL Server ne fait pas correspondre un login et un utilisateur sur la base de leur nom, mais sur la base du SID, qui est une valeur hexadécimale.
Celle-ci est générée par Active Directory et est unique au domaine pour un login de type Windows, et générée par le serveur lorsque le login est de type SQL.

Si donc vous utilisez les login de type Windows, vous n'avez pas à vous soucier de cela.
Si ce sont des logins de type SQL, il vous faut créer le login sur la deuxième instance en spécifiant son SID, que vous aurez récupéré sur la première instance.
On récupère cette valeur à l'aide de la requête suivante :
Code:

1 2 3 SELECT name, sid FROM sys.server_principals WHERE name = 'myLogin'
Et on crée le login avec un SID spécifique à l'aide de la requête suivante :
Code:

1 2 CREATE LOGIN myLogin WITH SID = <valeur du SID récupéré avec la requête précédente>
@++ ;)
27/07/2015, 08h04
Kropernic

Tout est basé sur l'AD chez nous donc c'est une bonne chose :).

Donc si je vous ai bien suivi, je n'ai "qu'à" scripter tous mes logins et exécuter le script obtenu sur le miroir.

Quand sqlpro dit que le mapping est dynamique, ça veut dire qu'il se fait automatiquement une fois les logins créés ou bien qu'on peut le faire par après et que je dois donc le faire manuellement ?

Enfin, je verrai bien une fois que j'aurai créé les logins en fait ^^
27/07/2015, 08h13
mikedavem

Citation:

Donc si je vous ai bien suivi, je n'ai "qu'à" scripter tous mes logins et exécuter le script obtenu sur le miroir.

C'est exactement cela.

Citation:

Quand sqlpro dit que le mapping est dynamique, ça veut dire qu'il se fait automatiquement une fois les logins créés ou bien qu'on peut le faire par après et que je dois donc le faire manuellement ?

Comme dit par Elsuket un peu plus tôt, les SID étant gérés par l'AD directement, les SID des utilisateurs correspondront aux SID des logins que tu vas créé vu qu'ils sont uniques et valables pour l'ensemble du domaine.
SQL Server utilise les SID des comptes Windows dans ton cas.

++
27/07/2015, 08h50
Kropernic
C'est encore moi ^^.

J'ai donc scripté tous les logins de mon serveur de prod et cela donne une série de ligne du genre
Code:

1 2 CREATE LOGIN [LE_LOGIN] FROM WINDOWS WITH DEFAULT_DATABASE=[UNE_DB], DEFAULT_LANGUAGE=[us_english] GO
Evidemment, vu que [UNE_DB] est en restoring, ça ne marche pas... J'imagine bien que je peux retirer la db par défaut et que le login va se créer mais j'ignore totalement ce que cela engendre pour plus tard...

Si c'est uniquement pour dire que par défaut si rien n'est précisé à la connexion, le contexte sera la DB [UNE_DB], alors je m'en tape vu que je précise toujours mais bon, j'aimerais autant être sûr...
27/07/2015, 09h05
Kropernic

Et pour les jobs alors ?

Une question corolaire...

J'ai bien scripté tous mes jobs de prod vers le miroir en les désactivant directement dans le script. J'ai donc bien tous mes jobs sur le miroir avec leur petite flèche rouge qui signale qu'ils sont désactivés.

Y a-t-il moyen de détecter le basculement d'une db pour que je puisse activer le job adéquat si nécessaire ? Ou bien est-ce une maintenance à faire à la main (ça m'étonnerait ^^) ?
27/07/2015, 09h11
SQLpro

Par déclencheur DDL sur ALTER_DATABASE au niveau serveur...

A +
27/07/2015, 09h17
Kropernic

Ok je regarde à ça alors.

Après ça, je crois que j'suis bon. Plus qu'à ajouter la clause failover partner dans les connectionstrings des applications...

D'autres choses auxquelles il faut faire gaffe et auxquelles on ne pense pas en général ? Vous avez plus d'xp que moi :)

Un gros merci à tous en tout cas !

J'y suis presque !

Hello,

J'aimerais valider mon trigger DDL avant de le mettre en place car je n'ai pas vraiment d'infrastructure pour le tester avant qu'il ne doivent vraiment servir...

Voici où j'en suis :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 CREATE TRIGGER TR_MIRRORING ON ALL SERVER FOR ALTER_DATABASE AS BEGIN DECLARE C CURSOR FOR SELECT name, CASE WHEN [ENABLED] = 0 THEN 1 ELSE 0 END FROM MSDB.DBO.sysjobs WHERE name NOT IN ('syspolicy_purge_history','Database Mirroring Monitor Job'); DECLARE @JOB VARCHAR(100); DECLARE @STATUS BIT; OPEN C; FETCH NEXT FROM C INTO @JOB, @STATUS; WHILE @@fetch_status = 0 BEGIN EXEC MSDB.DBO.sp_update_job @job_name = @JOB, @enabled = @STATUS FETCH NEXT FROM C INTO @JOB, @STATUS; END END
Mais en l'état, si j'ai bien compris, il va s'activer à chaque ALTER DATABASE qu'il y aura. Il y a moyen de filtrer pour que ça n'arrive que sur celui que le mirroring fait (celui qui change le statut de la DB en fait si j'dis pas de connerie) ?

Ou bien c'est bon comme ça ?

EDIT : En même temps, j'suis en train de me dire que les ALTER_DATABASE ne sont pas si fréquent que ça... En fait, personnellement je ne fais jamais il me semble... Y a-t-il des ordres ALTER_DATABASE qui s'opère en arrière-plan d'actions d'aministrations courantes ?

28/07/2015, 08h53
mikedavem

Citation:

Après ça, je crois que j'suis bon. Plus qu'à ajouter la clause failover partner dans les connectionstrings des applications

Elle n'est pas forcément obligatoire bien que conseillé ;)

Citation:

J'aimerais valider mon trigger DDL avant de le mettre en place car je n'ai pas vraiment d'infrastructure pour le tester avant qu'il ne doivent vraiment servir...

La détection du basculement doit se faire au niveau de l'instance et pas au niveau de la base de données ici. Généralement je me sers de l'événement DATABASE_MIRRORING_STATE_CHANGE dans une alerte SQL Server de type WMI. Dans ton cas tu peux exécuter le reste de ton code destiné à activer ou désactiver les jobs SQL Server en fonction du contexte dans un job de réponse.

++
28/07/2015, 08h58
Kropernic

Citation:

Envoyé par mikedavem

Elle n'est pas forcément obligatoire bien que conseillé ;)

Bin si je ne la mets pas, les applications ne vont plus fonctionner en cas de basculement... Du coup, c'est un peu obligatoire selon moi. Pourrais-tu développer stp ?

Citation:

Envoyé par mikedavem

La détection du basculement doit se faire au niveau de l'instance et pas au niveau de la base de données ici. Généralement je me sers de l'événement DATABASE_MIRRORING_STATE_CHANGE dans une alerte SQL Server de type WMI. Dans ton cas tu peux exécuter le reste de ton code destiné à activer ou désactiver les jobs SQL Server en fonction du contexte dans un job de réponse.

Un trigger DDL sur le server, c'est quand même bien au niveau de l'instance non ? Sinon, je ne me suis jamais servi d'alerte WMI et d'évènement (ou alors sans le savoir). Je vais creuser le sujet mais si jamais tu passes par là et que t'as 5 minutes à tuer, ne te gênes pas pour expliciter aussi ^^
28/07/2015, 14h00
mikedavem
Citation:

Bin si je ne la mets pas, les applications ne vont plus fonctionner en cas de basculement... Du coup, c'est un peu obligatoire selon moi. Pourrais-tu développer stp ?

Juste pour évitier tout quiproquo, quelques explications en effet :). Lorsqu'une connexion est effectuée sur une architecture mirroring, SQL Server envoie automatiquement dans le cache du client le failover Partner associé (même si celui-ci n'est pas renseigné dans la chaîne de connexion - d'ailleurs à ce moment là, ce paramètre est ignoré par SQL Server pendant l'envoi de l'information). Le client pourra utiliser ce paramètre en cache lors d'un failover.

Ce paramètre a bien entendu une utilité dans quelques scénarios. Par exemple, lors de la 1ère connexion, si le serveur renseigné dans Data Source n'est plus le serveur principal, le client n'a aucun moyen d'identifier le serveur partenaire. Dans ce cas le paramètre FailoverPartner est utile ici et sera utilisé par le client. Il y a encore quelques subtilités avec les connexions mis en pool en fonction du contexte.

Citation:

Un trigger DDL sur le server, c'est quand même bien au niveau de l'instance non ? Sinon, je ne me suis jamais servi d'alerte WMI et d'évènement (ou alors sans le savoir). Je vais creuser le sujet mais si jamais tu passes par là et que t'as 5 minutes à tuer, ne te gênes pas pour expliciter aussi ^^

Tout à fait sauf que les DDL triggers se déclencheront pour chaque événement lié à une modification de n'importe quelle base de données et ce n'est pas forcément ce que l'on veut.
Code:

1 2 3 4 CREATE TRIGGER TR_MIRRORING ON ALL SERVER FOR ALTER_DATABASE AS ...
Comment vas tu gérer le cas où tu as plusieurs sessions miroir de bases de données et qu'un le serveur principal d'une session sera le serveur miroir d'une autre ? Tu vas activer ou désactiver tous les jobs ?
Je n'ai pas forcément l'habitude de travailler avec les triggers DDL et il serait probablement possible de récupérer les informations intéressantes avec EVENTDATA() dans ce cas. Possible que les triggers DDL fonctionnent dans ce cas mais il faudrait le modifier.

L'alternative que je choisis en général c'est de récupérer les événements liées aux sessions miroir via DATABASE_MIRRORING_STATE_CHANGE dans des alertes WMI SQL Server et qui permettent d'appliquer un premier filtre sur les bases de données concernées. Par exemple :

Code:

SELECT * FROM Database_Mirroring_State_Change WHERE DatabaseName = ‘<mabase>’ AND State = 8

En fonction je créé un job de réponse avec utilisation des tokens pour les actions à mener en fonction du contexte client.

++
05/08/2015, 11h01
Kropernic

Hello,

Je reviens sur le sujet et je suis un peu perdu du coup.

Si j'ai bien tout suivi, utiliser un trigger DDL FOR ALTER DATABASE pourrait convenir dans l'hypothèse où il n'y aurait jamais d'autres changement au niveau DB que le status (principal/restoring). Ce que je ne peux garantir donc au final, ce n'est pas une bonne idée.

Du coup, on me parle d'alertes WMI. J'ai bien essayé de me renseigné mais ça reste très flou... J'ai finalement trouvé une page qui est un peu plus explicites mais reste dans les grandes lignes...

De ce que j'ai compris, corrigez moi si je me trompe svp, le fait d'exécuter une requête du genre SELECT * FROM DDL_DATABASE_LEVEL_EVENTS WHERE DatabaseName = 'AdventureWorks2008R2' va créer un truc qui va réagir sur les évènements lié à la DB et les stocker dans une table.

Du coup, je pourrai effectivement savoir si ma DB a été basculée au niveau du mirroring mais à condition que j'aille voir dans la table en question si une ligne me l'indique et je pourrais faire un outil de basculement automatique des job via un trigger sur cette table.

Je suis dans le bon jusque là ?

Sauf que j'ai tenté d'exécuter la requête ci-dessus (dans le contexte de la DB master) pour une DB qui traine et n'est plus utilisée (histoire d'être sûr de rien casser d'important) mais pas moyen.

Citation:

Invalid object name 'DDL_DATABASE_LEVEL_EVENTS'.

Des suggesions / compléments d'informations ?