SqlServer 2005¶

Installation¶

L’installation ce fait via le lancement de l’executable d’installation. Suite à l’installation vous pouvez utiliser le SQL Server Configuration manager

C:\WINDOWS\system32\mmc.exe /32 "C:\WINDOWS\system32\SQLServerManager.msc"

Il faut vérifier qu’il existe bien un service SQLServerAgent Il est possible sur cet outil de paramétrer les services comme la sélection:

des utilisateurs qui lancent les services
options au démarrage (ajout de -T4616 dans les paramètres de démarrage de MSQLSERVER)

L’outil qui va par la suite beaucoup nous servir est le Microsoft SQL Server Management Studio. Pour pouvoir ce connecter sur une base distante il faut

Activez les connexions à distance sur l’instance de SQL Server à laquelle vous voulez vous connecter à partir d’un ordinateur distant.
Activez le service SQL Server Browser.
Configurez le pare-feu afin d’autoriser le trafic réseau associé à SQL Server et au service SQL Server Browser.

Il existe un excellent article : http://support.microsoft.com/kb/914277/fr. Il faut vérifier

Configuration de la surface d’exposition SQL Server (connection locale ou distante)
service SQL Server Browser soit activée (gère la méthode de connection au serveur)
Création d’exceptions dans le pare-feu Windows (dans le parfeu ajouter une exception pour le programme sqlservr.exe et sqlbrowser.exe)

Cela ne suffit pas il faut encore, la plupart du temps, créer un utilisateur et souvent autoriser le double mode de connection:

windows
sqlserver

Pour le mode de connection en local ouvrir Microsoft SQL Server Management Studio, ce connecter puis

clic droit sur le serveur
Sélectionner “Propriétés”,
Dans la page “Sécurité”,
Sélectionner “Mode d’authentification SQL Server et Windows”
Cliquer sur OK.

Pour l’ajout d’un tuilisateur il faut toujours dans Microsoft SQL Server Management Studio sélectionner Serveur/Sécurité/connexion, puis clic droit nouvelle connexion.

Note

sur l’ajout d’une connection il n’y rien de particulier hors mis la sélection du type de connection windows ou sqlserver

la création via interface permet de créer connexion et utilisateur (utilisateur lie connexion à base/schema) On peut aussi faire l’ajout de cet connexion par script sql

create login TEST with password='TESTDB', DEFAULT_DATABASE=master
create user TEST

Création, maintenance, utilisation¶

On peut tout faire à partir de Microsoft SQL Server Management Studio mais il est souvent plus utile d’utiliser les commandes sql.

Création DB¶

un clic droit sur base de donnée suffit. Les principaux paramètres à verifier sont:

les logs
- type (data ou log)
- emplacement
- accroissement
- nombre
statistique
unicode

Exemple en sql pour créer la base DBFRED

USE [master]
GO
CREATE DATABASE [DBFRED] ON  PRIMARY
( NAME = N'DBFRED_Data',
    FILENAME = N'C:\Data\DBFRED_Data.mdf' ,
    SIZE = 88064KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
LOG ON
( NAME = N'DBFRED_Log',
    FILENAME = N'C:\Data\DBFRED_Data_log.LDF' ,
    SIZE = 52416KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
GO
--niveau de compatibilité
EXEC dbo.sp_dbcmptlevel @dbname=N'DBFRED', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DBFRED].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DBFRED] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DBFRED] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DBFRED] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DBFRED] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DBFRED] SET ARITHABORT OFF
GO
ALTER DATABASE [DBFRED] SET AUTO_CLOSE ON
GO
ALTER DATABASE [DBFRED] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DBFRED] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [DBFRED] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DBFRED] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DBFRED] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DBFRED] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DBFRED] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DBFRED] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DBFRED] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DBFRED] SET  ENABLE_BROKER
GO
ALTER DATABASE [DBFRED] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DBFRED] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DBFRED] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DBFRED] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DBFRED] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DBFRED] SET  READ_WRITE
GO
ALTER DATABASE [DBFRED] SET RECOVERY FULL
GO
ALTER DATABASE [DBFRED] SET  MULTI_USER
GO
ALTER DATABASE [DBFRED] SET PAGE_VERIFY TORN_PAGE_DETECTION
GO
ALTER DATABASE [DBFRED] SET DB_CHAINING OFF

Création de deux tables¶

CREATE TABLE dbo.Table_1
    (
    id int NOT NULL IDENTITY (1, 1),
    name nchar(10) NULL,
    forname nchar(20) NULL
    )  ON [PRIMARY];

ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED
    (
    id
    ) ON [PRIMARY];

CREATE TABLE dbo.Table_2
    (
    id int NOT NULL IDENTITY (1, 1),
    id2 int NOT NULL,
    lib nchar(20) NULL
    )  ON [PRIMARY];

ALTER TABLE dbo.Table_2 ADD CONSTRAINT
    PK_Table_2 PRIMARY KEY CLUSTERED
    (
    id
    ) ON [PRIMARY];

Note

dans ce code on stipule que cette table sera gérer sur le fichier data [PRIMARY]

on peut donc répartir comme on le souhaite sur un groupe fichier les tables d’une base

chargement de ces tables¶

insert into dbo.Table_1(name, forname) values('name0','forname0');
insert into dbo.Table_2(id2, lib) values( 0,'lib0');
insert into dbo.Table_1(name, forname) values('name1','forname1');
insert into dbo.Table_2(id2, lib) values( 1,'lib1');
insert into dbo.Table_1(name, forname) values('name2','forname2');
insert into dbo.Table_2(id2, lib) values( 2,'lib2');
insert into dbo.Table_1(name, forname) values('name3','forname3');
insert into dbo.Table_2(id2, lib) values( 3,'lib3');
insert into dbo.Table_1(name, forname) values('name4','forname4');
insert into dbo.Table_2(id2, lib) values( 4,'lib4');
insert into dbo.Table_1(name, forname) values('name5','forname5');
insert into dbo.Table_2(id2, lib) values( 5,'lib5');
insert into dbo.Table_1(name, forname) values('name6','forname6');
insert into dbo.Table_2(id2, lib) values( 6,'lib6');
insert into dbo.Table_1(name, forname) values('name7','forname7');
insert into dbo.Table_2(id2, lib) values( 7,'lib7');
insert into dbo.Table_1(name, forname) values('name8','forname8');
insert into dbo.Table_2(id2, lib) values( 8,'lib8');
insert into dbo.Table_1(name, forname) values('name9','forname9');
insert into dbo.Table_2(id2, lib) values( 9,'lib9');
insert into dbo.Table_1(name, forname) values('name10','forname10');
insert into dbo.Table_2(id2, lib) values( 10,'lib10');
insert into dbo.Table_1(name, forname) values('name11','forname11');
insert into dbo.Table_2(id2, lib) values( 11,'lib11');
insert into dbo.Table_1(name, forname) values('name12','forname12');
insert into dbo.Table_2(id2, lib) values( 12,'lib12');
insert into dbo.Table_1(name, forname) values('name13','forname13');
insert into dbo.Table_2(id2, lib) values( 13,'lib13');
insert into dbo.Table_1(name, forname) values('name14','forname14');
insert into dbo.Table_2(id2, lib) values( 14,'lib14');
insert into dbo.Table_1(name, forname) values('name15','forname15');
insert into dbo.Table_2(id2, lib) values( 15,'lib15');
insert into dbo.Table_1(name, forname) values('name16','forname16');
insert into dbo.Table_2(id2, lib) values( 16,'lib16');
insert into dbo.Table_1(name, forname) values('name17','forname17');
insert into dbo.Table_2(id2, lib) values( 17,'lib17');
insert into dbo.Table_1(name, forname) values('name18','forname18');
insert into dbo.Table_2(id2, lib) values( 18,'lib18');
insert into dbo.Table_1(name, forname) values('name19','forname19');
insert into dbo.Table_2(id2, lib) values( 19,'lib19');

select * from dbo.Table_1;
select * from dbo.Table_2;

Sauvegarde complète¶

BACKUP DATABASE DBFRED TO DISK='C:\SVG\DBFREDSQL.back'

ajout de nouvelles lignes

insert into dbo.Table_1(name, forname) values('name21','forname21');
insert into dbo.Table_2(id2, lib) values( 21,'lib21');
insert into dbo.Table_1(name, forname) values('name22','forname22');
insert into dbo.Table_2(id2, lib) values( 22,'lib22');
insert into dbo.Table_1(name, forname) values('name23','forname23');
insert into dbo.Table_2(id2, lib) values( 23,'lib23');
insert into dbo.Table_1(name, forname) values('name24','forname24');
insert into dbo.Table_2(id2, lib) values( 24,'lib24');
insert into dbo.Table_1(name, forname) values('name25','forname25');
insert into dbo.Table_2(id2, lib) values( 25,'lib25');
insert into dbo.Table_1(name, forname) values('name26','forname26');
insert into dbo.Table_2(id2, lib) values( 26,'lib26');
insert into dbo.Table_1(name, forname) values('name27','forname27');
insert into dbo.Table_2(id2, lib) values( 27,'lib27');
insert into dbo.Table_1(name, forname) values('name28','forname28');
insert into dbo.Table_2(id2, lib) values( 28,'lib28');
insert into dbo.Table_1(name, forname) values('name29','forname29');
insert into dbo.Table_2(id2, lib) values( 29,'lib29');

création d’un lieu de stockage des diff

exec sp_addumpdevice 'DISK', 'DIFFSQL', 'C:\SVG\DBFREDDIFFSQL.back'

Sauvegarde différentielle

BACKUP DATABASE DBFRED TO DIFFSQL WITH DIFFERENTIAL

ajout de nouvelles lignes

insert into dbo.Table_1(name, forname) values('name31','forname31');
insert into dbo.Table_2(id2, lib) values( 31,'lib31');
insert into dbo.Table_1(name, forname) values('name32','forname32');
insert into dbo.Table_2(id2, lib) values( 32,'lib32');
insert into dbo.Table_1(name, forname) values('name33','forname33');
insert into dbo.Table_2(id2, lib) values( 33,'lib33');
insert into dbo.Table_1(name, forname) values('name34','forname34');
insert into dbo.Table_2(id2, lib) values( 34,'lib34');
insert into dbo.Table_1(name, forname) values('name35','forname35');
insert into dbo.Table_2(id2, lib) values( 35,'lib35');
insert into dbo.Table_1(name, forname) values('name36','forname36');
insert into dbo.Table_2(id2, lib) values( 36,'lib36');
insert into dbo.Table_1(name, forname) values('name37','forname37');
insert into dbo.Table_2(id2, lib) values( 37,'lib37');
insert into dbo.Table_1(name, forname) values('name38','forname38');
insert into dbo.Table_2(id2, lib) values( 38,'lib38');
insert into dbo.Table_1(name, forname) values('name39','forname39');
insert into dbo.Table_2(id2, lib) values( 39,'lib39');

Sauvegarde différentielle

BACKUP DATABASE DBFRED TO DIFFSQL WITH DIFFERENTIAL

ajout de nouvelles lignes

insert into dbo.Table_1(name, forname) values('name41','forname41');
insert into dbo.Table_2(id2, lib) values( 41,'lib41');
insert into dbo.Table_1(name, forname) values('name42','forname42');
insert into dbo.Table_2(id2, lib) values( 42,'lib42');
insert into dbo.Table_1(name, forname) values('name43','forname43');
insert into dbo.Table_2(id2, lib) values( 43,'lib43');
insert into dbo.Table_1(name, forname) values('name44','forname44');
insert into dbo.Table_2(id2, lib) values( 44,'lib44');
insert into dbo.Table_1(name, forname) values('name45','forname45');
insert into dbo.Table_2(id2, lib) values( 45,'lib45');
insert into dbo.Table_1(name, forname) values('name46','forname46');
insert into dbo.Table_2(id2, lib) values( 46,'lib46');
insert into dbo.Table_1(name, forname) values('name47','forname47');
insert into dbo.Table_2(id2, lib) values( 47,'lib47');
insert into dbo.Table_1(name, forname) values('name48','forname48');
insert into dbo.Table_2(id2, lib) values( 48,'lib48');
insert into dbo.Table_1(name, forname) values('name49','forname49');
insert into dbo.Table_2(id2, lib) values( 49,'lib49');

Nous avons donc maintenant au niveau sauvegarde deux fichiers:

C:/SVG/DBFREDSQL.bak contient une suavegarde complète
C:/SVG/DBFREDDIFFSQL.bak contient 2 sauvegardes partielles

Note

les dernières commandes ne font pas partie pour l’instant de fichier de sauvergarde

Nous allons maintenant simuler une restore de base, pour cela nous alons droper la base Tout d’abord on s’assure que personne ne travaille sur la base puis on sauvegarde les journaux à partir de la base master

ALTER DATABASE DBFRED SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BACKUP LOG DBFRED TO DIFFSQL;
--destruction base qui ne doit pas être prise en compte (içi dans une connection DBFRED)
ALTER DATABASE DBFRED SET MULTI_USER;
TRUNCATE TABLE dbo.Table_1;

--retour dans la base master
ALTER DATABASE DBFRED SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--RESTORE DATABASE DBFRED FROM DIFFSQL WITH REPLACE, RECOVERY;
RESTORE DATABASE DBFRED
    FROM DISK = 'C:\SVG\DBFREDSQL.back'
    WITH REPLACE,
    NORECOVERY;
RESTORE DATABASE DBFRED
    FROM DISK = 'C:\SVG\DBFREDDIFFSQL.back'
    WITH REPLACE,
    RECOVERY;
ALTER DATABASE DBFRED SET MULTI_USER;

Warning

Pour pouvoir utilisé les LOG il faut absolument avoir comme modèle de sauvegarde full

Il peut être obtenu vi un alter table: ALTER DATABASE [DBFRED] SET RECOVERY FULL On peut utiliser des différentiels en identifiant clairement le fichier

BACKUP DATABASE DBFRED TO DISK=’C:SVGDBFREDDIFF1SQL.back’ WITH DIFFERENTIAL

Mise en place d’une sauvergarde et d’une restauration .. warning:

Quelques soit la politique de sauvegarde il faut sauvegarder la base master en full régulièrement

Cette base contient toutes les infos sur les autres bases Nous allons partir du principe que nous avons une base DBFRED les commandes de sauvegarde sont des sauvegardes

full
incrémentale
log (juste au moment du crash)

script de sauvegarde

BACKUP DATABASE DBFRED TO DISK='C:\SVG\DBFREDSQL_ALL01.back'
BACKUP DATABASE DBFRED TO DISK='C:\SVG\DBFREDSQL_DIF01.back' WITH DIFFERENTIAL
BACKUP DATABASE DBFRED TO DISK='C:\SVG\DBFREDSQL_DIF02.back' WITH DIFFERENTIAL
BACKUP LOG DBFRED TO DISK='C:\SVG\DBFREDSQL_LOG01.back'

Nous avons donc 4 fichiers de sauvegarde au moment du crash .. figure:: data20100811_57.png

script de restauration executé sur la base master du serveur

ALTER DATABASE DBFRED SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE DBFRED
    FROM DISK = 'C:\SVG\DBFREDSQL_ALL01.back'
    WITH REPLACE,
    NORECOVERY;
RESTORE DATABASE DBFRED
    FROM DISK = 'C:\SVG\DBFREDSQL_DIF02.back'
    WITH REPLACE,
    NORECOVERY;
RESTORE DATABASE DBFRED
    FROM DISK = 'C:\SVG\DBFREDSQL_DIF02.back'
    WITH REPLACE,
    NORECOVERY;
RESTORE DATABASE DBFRED
    FROM DISK = 'C:\SVG\DBFREDSQL_LOG01.back'
    WITH REPLACE,
    RECOVERY;
ALTER DATABASE DBFRED SET MULTI_USER;

En faite nous utilisons les sauvegardes différentielles à partir d’une sauvegarde complète. Seule la sauvegarde des LOG à l’option RECOVERY permet par la suite à la base d’être utilisable. Si nous n’avons pas de sauvegarde des LOG il est possible d’utiliser cette option sur n’importe qu’elle autre sauvegarde .. figure:: data20100811_58.png

Pour créer automatiquement les sauvegardes vous pouvez utiliser les Travaux.

Les travaux de base pour la sauvegarde peuvent ce décomposer en étape:

suppression de l’ancien fichier log
- si ok aller à l’étape suivante
- si ko aller à l’étape 3
backup (diff ou full) de la base
- si ok finir le travail en envoyant succès
- si ko aller à l’étape 3
envoi mail erreur
- si ok finir le travail en envoyant erreur
- si ko finir le travail en envoyant erreur

Pour l’envoi d’email vous pouvez aller voir le document dans windows , envoi mail

dans les notifications ajouter l’écriture dans le gestionnaire d’évènement windows. .. figure:: data20100811_59.png

Warning

il faudrait aussi trunquer les fichiers log (.ldf) qui sonon grossissent. Il faut pour cela lancer une suite de commande après la sauvegarde de la base

BACKUP LOG [nomDataBAse] WITH TRUNCATE_ONLY
GO
--SHRINKFILE, Réduit la taille physique du fichier.
DBCC SHRINKFILE(logicalName_log, 2)

Note

vous pouvez obtenir le logicalName_log en demandant le script de création de la base

Si le shrink ne fonctionne toujours pas c’est qu’une transaction est toujours en cours. Pour vérifier exécuter un

DBCC OPENTRAN.

et aussi