[SQLSRV2008] Log Shipping automatique

**rodbeck** · 30/08/2010, 17h48

Bonjour,

J'arrive bien à paramétrer mes bases en log shipping à l'aide de SSMS.
Je dispose d'une 50 aine de bases de données qui doivent toutes être paramétrées. Comment automatiser la mise en place de ce log shipping pour chacune des bases ?

J'ai essayé d'exporter le script SQL mais j'ai un petit problème avec la partie "serveur secondaire" : il faut que je conserve l'identifiant du job primaire tout en créant le job directement sur la machine.

Merci de m'éclairer

**kagemaru** · 30/08/2010, 18h55

salut

Il faudrait que tu décrives précisément ton environnement et également ton besoin (ex : il très important que les utilisateurs aient les bases à jour, ou non un rafraichissement quotidien ne suffit pas, etc).

Et bien sûr, les messages d'erreur correspondant à ton

petit problème avec la partie "serveur secondaire"

merci !

**rodbeck** · 30/08/2010, 19h24

En fait je pense que ça doit être mes yeux, la configuration fonctionne, il suffit que je lance chaque script sur chaque environnement.

**rodbeck** · 30/08/2010, 20h03

Ah tiens non, ça fonctionne bien lorsque je lance le script tout seul, mais dès que j'essaye d'itérer sur mes bases, ça ne donne rien.

Voici le script en mode "automatisation" :

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
DECLARE @LS_Secondary__CopyJobId	AS uniqueidentifier 
DECLARE @LS_Secondary__RestoreJobId	AS uniqueidentifier 
DECLARE @LS_Secondary__SecondaryId	AS uniqueidentifier 
DECLARE @LS_Add_RetCode	As int 
DECLARE @dbname as nvarchar(50)
DECLARE @cjn as nvarchar(100)
DECLARE @rjn as nvarchar(100)
 
DECLARE DBaseCursor CURSOR FOR SELECT [name] FROM sys.databases WHERE state_desc='ONLINE'
OPEN DBaseCursor
FETCH NEXT FROM DBaseCursor INTO @dbname
 
 
 
WHILE @@FETCH_STATUS = 0
	BEGIN
	print @dbname
	set @cjn = N'LSCopy_PRIM_' + @dbname
	set @rjn = N'LSRestore_PRIM_' + @dbname
 
	EXEC @LS_Add_RetCode = master.dbo.sp_add_log_shipping_secondary_primary 
			@primary_server = N'PRIM' 
			,@primary_database = @dbname
			,@backup_source_directory = N'\\toto\d$\titi\BACKUP_OUT\LOG' 
			,@backup_destination_directory = N'D:\titi\BACKUP_IN\LOG' 
			,@copy_job_name = @cjn
			,@restore_job_name = @rjn
			,@file_retention_period = 4320 
			,@overwrite = 1 
			,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT 
			,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT 
			,@secondary_id = @LS_Secondary__SecondaryId OUTPUT 
 
	IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
	BEGIN 
 
	DECLARE @LS_SecondaryCopyJobScheduleUID	As uniqueidentifier 
	DECLARE @LS_SecondaryCopyJobScheduleID	AS int 
 
 
	EXEC msdb.dbo.sp_add_schedule 
			@schedule_name =N'DefaultCopyJobSchedule' 
			,@enabled = 1 
			,@freq_type = 4 
			,@freq_interval = 1 
			,@freq_subday_type = 4 
			,@freq_subday_interval = 15 
			,@freq_recurrence_factor = 0 
			,@active_start_date = 20100827 
			,@active_end_date = 99991231 
			,@active_start_time = 0 
			,@active_end_time = 235900 
			,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT 
			,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT 
 
	EXEC msdb.dbo.sp_attach_schedule 
			@job_id = @LS_Secondary__CopyJobId 
			,@schedule_id = @LS_SecondaryCopyJobScheduleID  
 
	DECLARE @LS_SecondaryRestoreJobScheduleUID	As uniqueidentifier 
	DECLARE @LS_SecondaryRestoreJobScheduleID	AS int 
 
 
	EXEC msdb.dbo.sp_add_schedule 
			@schedule_name =N'DefaultRestoreJobSchedule' 
			,@enabled = 1 
			,@freq_type = 4 
			,@freq_interval = 1 
			,@freq_subday_type = 4 
			,@freq_subday_interval = 15 
			,@freq_recurrence_factor = 0 
			,@active_start_date = 20100827 
			,@active_end_date = 99991231 
			,@active_start_time = 0 
			,@active_end_time = 235900 
			,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT 
			,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT 
 
	EXEC msdb.dbo.sp_attach_schedule 
			@job_id = @LS_Secondary__RestoreJobId 
			,@schedule_id = @LS_SecondaryRestoreJobScheduleID  
 
 
	END 
 
 
	DECLARE @LS_Add_RetCode2	As int 
 
 
	IF (@@ERROR = 0 AND @LS_Add_RetCode = 0) 
	BEGIN 
 
	EXEC @LS_Add_RetCode2 = master.dbo.sp_add_log_shipping_secondary_database 
			@secondary_database = @dbname
			,@primary_server = N'PRIM' 
			,@primary_database = @dbname
			,@restore_delay = 0 
			,@restore_mode = 0 
			,@disconnect_users	= 0 
			,@restore_threshold = 45   
			,@threshold_alert_enabled = 1 
			,@history_retention_period	= 5760 
			,@overwrite = 1 
 
	END 
 
 
	IF (@@error = 0 AND @LS_Add_RetCode = 0) 
	BEGIN 
 
	EXEC msdb.dbo.sp_update_job 
			@job_id = @LS_Secondary__CopyJobId 
			,@enabled = 1 
 
	EXEC msdb.dbo.sp_update_job 
			@job_id = @LS_Secondary__RestoreJobId 
			,@enabled = 1 
 
	END 
	FETCH NEXT FROM DBaseCursor INTO @dbname
END
close DBaseCursor
deallocate DBaseCursor

**SQLpro** · 31/08/2010, 18h48

50 bases en LOG SHIPPING ou même en mirroring, c'est de la folie. Si vous voulez tuer les performances de votre serveur, y'a pas mieux. Il va passer son temps à faire des opérations de disque. La question est :
Pourquoi avez vous autant de bases ? Ne pouvez vous pas les synthétiser en beaucoup moins via les schémas SQL ?
Sinon, recourrez au cluster système. Ce sera le seul moyen de faire une haute dispo sans tuer le serveur.

A +

**rodbeck** · 31/08/2010, 18h50

Bonjour,

Nous avons une base par client, confidentialité des données oblige, le nombre de 50 (et croissant) est donc une nécessité.

**kagemaru** · 31/08/2010, 22h06

confidentialité des données oblige

Rien ne t'empêche de créer un schéma par client dans la même base.

Bis : Il faudrait que tu décrives précisément ton environnement et également ton besoin (ex : tolérance à la perte de données, temps maximum de remise en fonction du système etc.)

Et on ne sait toujours pas si tu as un message d'erreur à l'exec de ta commande ou dans l'historique des agents de log shipping.

Et 50 bases logshippées, ça commence à faire ...

**rodbeck** · 01/09/2010, 12h11

Envoyé par kagemaru

Rien ne t'empêche de créer un schéma par client dans la même base.

Bis : Il faudrait que tu décrives précisément ton environnement et également ton besoin (ex : tolérance à la perte de données, temps maximum de remise en fonction du système etc.)

Et on ne sait toujours pas si tu as un message d'erreur à l'exec de ta commande ou dans l'historique des agents de log shipping.

Et 50 bases logshippées, ça commence à faire ...

Hello,

Je ne suis pas maître de l'architecture de l'application, une remise en cause de celle-ci serait considérable, nous ne pouvons pas nous le permettre pour le moment en tous les cas.
Par contre, ça peut se planifier et s'arbitrer, si réellement vous pensez qu'il est primordial pour les performances futures de passer à une seule bdd comportant un schéma par client.

Très précisément, mon environnement est constitué de 4 VM IIS 7 Win Server 2008 et de deux VM SQL Server 2008 entreprise.

Mon souhait est de réaliser un log shipping d'une des deux machines sur l'autre.

Lorsque je tente de lancer mon script automatisé plus haut : "il ne se passe rien" cad aucun message d'erreur, la query est correctement exécutée, mais les jobs ne sont pas créés non plus.
Lorsque j'exécute un script avec un nom de base "en dur" les jobs sont correctement créés (et fonctionnent).
Lorsque je réalise l'opération à la souris, les jobs sont correctement créés (et fonctionnent).
En ajoutant des traces à chaque étape, je ne constate pas d'anomalie particulière. Est-ce la constitution des noms de jobs et de base de données qui ne lui plait pas ?

Pour ce qui est du besoin : actuellement nous réalisons une sauvegarde full journalière, c'était suffisant jusqu'ici, mais nos nouveaux clients sont de plus en plus exigeants, la perte de données maximale que je souhaiterais avoir est donc de 30 minutes.

50 bases et ça continue de grimper

**Ptit_Dje** · 01/09/2010, 13h06

Hello,

Regrouper tous les clients sous une seule et meme base et differents schemas ?
Je suis mitige - ca depend de la taille des bases. Ca permettra moins de flexibilite tout de meme.
Si jamais vous pensez partir dans cette direction, je conseillerai d'avoir differents filegroups 1 pour chaque client au moins - Si jamais il faut "restaurer un client", tu n'impactes pas les autres - Bien tester la solution de backup/restore afin de pas se gaffer au moment venu.
A priori une DB par client est plus simple a gerer car tu peux les deplacer facilement et sans t'encombrer dans une certaine complexite.
De plus selon les differentes attentes de tes clients (SLA) tu peux adapter tes strategies de gestion base/base et c'est pas plus mal.

Concernant la perte de donnee maximale autorisee par tes clients de 30 minutes, cela pose comme contrainte de faire un log backup toutes les 30 minutes de leur base.

Peux tu nous expliquer quel est ton but avec la mise en place d'une solution de log shipping ? A quoi veux tu arriver avec une telle solution ?

**rodbeck** · 01/09/2010, 13h38

Envoyé par Ptit_Dje

Hello,

Regrouper tous les clients sous une seule et meme base et differents schemas ?
Je suis mitige - ca depend de la taille des bases. Ca permettra moins de flexibilite tout de meme.
Si jamais vous pensez partir dans cette direction, je conseillerai d'avoir differents filegroups 1 pour chaque client au moins - Si jamais il faut "restaurer un client", tu n'impactes pas les autres - Bien tester la solution de backup/restore afin de pas se gaffer au moment venu.
A priori une DB par client est plus simple a gerer car tu peux les deplacer facilement et sans t'encombrer dans une certaine complexite.
De plus selon les differentes attentes de tes clients (SLA) tu peux adapter tes strategies de gestion base/base et c'est pas plus mal.

Concernant la perte de donnee maximale autorisee par tes clients de 30 minutes, cela pose comme contrainte de faire un log backup toutes les 30 minutes de leur base.

Peux tu nous expliquer quel est ton but avec la mise en place d'une solution de log shipping ? A quoi veux tu arriver avec une telle solution ?

Je souhaite arriver à mettre en place une architecture robuste de base de données, qui nous permettrait des retour arrière plus simples et plus précis en cas de perte de données ou d'erreurs de saisie.
Ça couplé à la réplication du serveur de données afin de permettre une reprise sur erreur plus rapide en cas de perte du serveur primaire.

**Ptit_Dje** · 01/09/2010, 13h58

Envoyé par rodbeck

Je souhaite arriver à mettre en place une architecture robuste de base de données, qui nous permettrait des retour arrière plus simples et plus précis en cas de perte de données ou d'erreurs.
Ça couplé à la réplication du serveur de données afin de permettre une reprise sur erreur plus rapide en cas de perte du serveur primaire.

Les 2 VM SQL Server tournent elles sur le meme host ?
Si oui, dans le cas de la perte de ton host, tu perds tout.
Si non - Pourquoi ne pas penser a faire un cluster au niveau de tes hosts VM ou dans le cas de la perte d'un host, toutes tes VMs basculent vers le 2eme. Ainsi tu te proteges d'une defectuosite materielle.
Tu peux aussi penser a creer un cluster entre tes 2 VM SQL Server afin de descendre le scope de la protection (cluster au niveau des hosts - protege toutes les VMs du host - cluster au niveau de la VM - protege au niveau de la VM).

La methode la plus simple pour recuperer des donnees reste pour moi le backup/restore en point in time.
En effet, une erreur peut entrainer la suppression de donnes dans plusieurs tables "en meme temps". Garantir l'integrite des donnees en rechargeant les tables manuellement peut s'averer tres complexe selon les systemes - a toi d'en juger.
Si tu as du disque de libre, tu peux meme penser a restaurer la DB en parallele de celle qui tourne si jamais tu veux restaurer juste une table.
A mettre en place, il ne s'agit que de full/log backups.

Je pense que la gestion d'une solution pareille est plus simple qu'une 50 de bases log shippees

**rodbeck** · 01/09/2010, 14h03

Envoyé par Ptit_Dje

Les 2 VM SQL Server tournent elles sur le meme host ?
Si oui, dans le cas de la perte de ton host, tu perds tout.
Si non - Pourquoi ne pas penser a faire un cluster au niveau de tes host VM ou dans le cas de la perte d'un host, toutes tes VM basculent vers le 2eme. Ainsi tu te proteges d'une defectuosite materielle.
Tu peux aussi penser a creer un cluster entre tes 2 VM SQL Server afin de descendre le scope de la protection (cluster au niveau des hosts - protege toutes les VMs du host - cluster au niveau de la VM - protege au niveau de la VM).

Pas de soucis techniques de ce côté là.

La methode la plus simple pour recuperer des donnees reste pour moi le backup/restore en point in time.
En effet, une erreur peut entrainer la suppression de donnes dans plusieurs tables "en meme temps". Garantir l'integrite des donnes en rechargeant les tables manuellement peut s'averer tres complexe selon les systemes - a toi d'en juger.
Si tu as du disque de libre, tu peux meme penser a restaurer la DB en parallele de celle qui tourne si jamais tu veux restaurer juste une table.

C'est l'idée : actuellement nous avons un backup full par jour.
Nous voulons changer par : un backup full par jour + sauvegarde des logs toutes les 30 minutes, comme ça la perte de données ne sera que de 30 minutes.
Je ne compte pas m'embêter à restaurer table par table, mais sélectionner les backups à restaurer.

**Ptit_Dje** · 01/09/2010, 14h05

As tu un autre interet pour le log shipping ?

**rodbeck** · 01/09/2010, 14h16

Envoyé par Ptit_Dje

As tu un autre interet pour le log shipping ?

Non, c'est déjà pas mal

où voulez-vous en venir ?

**Ptit_Dje** · 01/09/2010, 14h19

Je pense que vous pouvez alors cliquer sur le bouton resolu a moins que pour la beaute du geste vous souhaitez continuer a travailler sur le script de mise en place de log shipping pour plusieures bases a la fois

**rodbeck** · 01/09/2010, 14h26

Envoyé par Ptit_Dje

Je pense que vous pouvez alors cliquer sur le bouton resolu a moins que pour la beaute du geste vous souhaitez continuer a travailler sur le script de mise en place de log shipping pour plusieures bases a la fois

Je vais marquer résolu, bien qu'il ne le soit pas : mon souhait n'a pas changé.

Sauf à modifier radicalement l'architecture de mon soft (inimaginable dans l'immédiat comme déjà évoqué plus haut), je ne peux pas ignorer le nombre de bases de données, résultat, je vais donc configurer ce log shipping à la main pour les 50 bases.

Merci de votre aide.

**Ptit_Dje** · 01/09/2010, 14h45

Je comprend pas le point:
- Sauf à modifier radicalement l'architecture de mon soft

Rien de ce que j'ai dit ci-dessus ne vous fait changer l'architecture de votre soft. C'est juste des points d'infrastructure. Et cela vous evite de devoir gerer 50 bases en log shipping.
Qu'est ce qui vous empeche de mettre cela en place ?
Je ne vous suis pas trop la.

**rodbeck** · 01/09/2010, 14h49

Envoyé par Ptit_Dje

Je comprend pas le point:
- Sauf à modifier radicalement l'architecture de mon soft

Rien de ce que j'ai dit ci-dessus ne vous fait changer l'architecture de votre soft. C'est juste des points d'infrastructure. Et cela vous evite de devoir gerer 50 bases en log shipping.
Qu'est ce qui vous empeche de mettre cela en place ?
Je ne vous suis pas trop la.

Je ne dois pas avoir compris alors.

Concernant le cluster :
I faudra bien synchroniser les bases de données entre les deux serveurs ?
Pour cela j'ai pensé au log shipping qui permet à la fois la synchronisation et la récupération.

Concernant le second point :
Qu'entendez-vous par backup "point in time" ?

**Ptit_Dje** · 01/09/2010, 14h58

Envoyé par rodbeck

Je ne dois pas avoir compris alors.

Concernant le cluster :
I faudra bien synchroniser les bases de données entre les deux serveurs ?
Pour cela j'ai pensé au log shipping qui permet à la fois la synchronisation et la récupération.

Non, si un noeud tombe, toute l'instance bascule (configuration de l'instance, login, ...) ce qui est plus facile a maintenir. Avec du logshipping, vous devriez penser a utiliser des alias DNS afin de pouvoir facilement changer celui-ci pour pointer vers votre nouveau serveur sans devoir changer la connection string definie dans votre application, ou alors changer cette derniere manuellement si vous n'utilisez pas d'alias DNS. La procedure de basculement est nettement plus complexe. Vous devez aussi maintenir a jour tous les logins et vos jobs SQL et ...
Le clustering, c'est transparent pour l'application - la connexion string pointe sur un nom de serveur virtuel, elle sait pas sur quelle machine physique le serveur de base de donnees tourne.

Envoyé par rodbeck

Concernant le second point :
Qu'entendez-vous par backup "point in time" ?

Avec des logs backups reguliers, vous perdez au maximum les donnees depuis le dernier log backup.
Vous pouvez revenir a tous les instants d'avant.
On parle de restore point-in-time car vous pouvez definir jusque quand restaurer vos donnees.

**rodbeck** · 01/09/2010, 15h28

Envoyé par Ptit_Dje

Non, si un noeud tombe, toute l'instance bascule (configuration de l'instance, login, ...) ce qui est plus facile a maintenir. Avec du logshipping, vous devriez penser a utiliser des alias DNS afin de pouvoir facilement changer celui-ci pour pointer vers votre nouveau serveur sans devoir changer la connection string definie dans votre application, ou alors changer cette derniere manuellement si vous n'utilisez pas d'alias DNS. La procedure de basculement est nettement plus complexe. Vous devez aussi maintenir a jour tous les logins et vos jobs SQL et ...
Le clustering, c'est transparent pour l'application - la connexion string pointe sur un nom de serveur virtuel, elle sait pas sur quelle machine physique le serveur de base de donnees tourne.

Avec des logs backups reguliers, vous perdez au maximum les donnees depuis le dernier log backup.
Vous pouvez revenir a tous les instants d'avant.
On parle de restore point-in-time car vous pouvez definir jusque quand restaurer vos donnees.

Ok, votre préco est donc de configurer SQL Serveur en cluster failover, et d'effectuer des backup log réguliers ?
Ce process n'aura pas le même inconvénient que le log shipping de générer de l'IO si l'on travaille sur 50+ bases ?

[SQLSRV2008] Log Shipping automatique

Administration SQL Server

Discussions similaires

Partager

Partager