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

.. code-block:: bash

    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

.. code-block:: bash

    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

.. code-block:: bash

    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
------------------------

.. code-block:: bash

    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
------------------------

.. code-block:: bash

    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
--------------------

.. code-block:: bash

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

ajout de nouvelles lignes

.. code-block:: bash

    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

.. code-block:: bash

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

Sauvegarde différentielle

.. code-block:: bash

    BACKUP DATABASE DBFRED TO DIFFSQL WITH DIFFERENTIAL

ajout de nouvelles lignes

.. code-block:: bash

    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

.. code-block:: bash

    BACKUP DATABASE DBFRED TO DIFFSQL WITH DIFFERENTIAL

ajout de nouvelles lignes

.. code-block:: bash

    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

.. code-block:: bash

    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

.. code-block:: bash

    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

.. code-block:: bash

    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:: data\20100811_57.png

script de restauration executé sur la base master du serveur

.. code-block:: bash

    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:: data\20100811_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:: data\20100811_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

.. code-block:: bash

    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

.. code-block:: bash

    DBCC OPENTRAN.

et aussi