Comment indexer les tables en aval, en production ?

Laisser une réponse

Pour indexer une base de données, utiliser l’assistant parametrage du moteur de bases de données sql serveur 2005.

Dans un premier temps, il est nécessaire de capturer une trace à l’aide du query analyser. On stocke l’information dans un fichier ou une table de bases de données.
exemple :
USE AdventureWorks
SELECT *
FROM Production.Product
ORDER BY Name ASC ;

Dans un second temps, on lance l’assistant parametrage du moteur de bases de données, on indique sur quel trace on va travailler, sur quel base et quel table portera l’analyse. On lance le conseiller et il nous fournit des recommandations.

Dans le cas présent, un index portant sur name.

Attention! sur des petites requetes ( 20 lignes ), le temps d’execution est trop court pour pouvoir mettre en place une optimisation. Meme si l’analyse de la requete permet d’imaginer la mise en place d’un index.

exemple :
USE Test
SELECT item,color,sum(quantity)FROM inventory2 GROUP BY item,color,quantity order by quantity

un webcast pour comprendre sur le site technet :

http://www.microsoft.com/France/Vision/ListTechNet.aspx?Qry=module+17&S=x&Did=56042EEA-FE57-4207-9FB0-538F1025C49A&Pid=&Nid=&Cid=64ed8bfb-e127-4346-ad6f-2c93ccd6f991&Tid=&x=24&y=13

un didactiel microsoft :
http://technet.microsoft.com/fr-fr/library/ms166575.aspx

Pour information, il existe aussi un outil de paramétrage des index sous sql serveur 2000.

[SQL Serveur 2005]Gerer la fragmentation des index.

Laisser une réponse

Toute l’information sur les index et la fragmentation est fourni par la procedure :

dm_db_index_physical_stats

Les administrateurs doivent s’intéresser à la colonne avg_fragmentation_in_percent pour déterminer si les index présentent une fragmentation externe. Celle ci est présente pour une valeur supérieur à 10.

Ils doivent examiner la valeur de la colonne avg_page_space_used_in_percent pour déterminer si les index souffrent d’une fragmentation interne. Celle ci est presente si la valeur est inférieur à 75.

Exemple :

DECLARE @database_id int
SELECT database_id,name FROM sys.databases;
Set @database_id = 1
SELECT index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(@database_id,NULL,NULL,NULL,’Detailed’)

Si vous constatez que vos index souffrent de fragmentation interne ou externe , vous devez executer regulierement les instruction ALTER INDEX … REBUILD et ALTER INDEX … REORGANIZE.

exemple :

USE Adventuresworks
ALTER INDEX PK_Employee_EmployeeID ON HumanRessource.Employee REORGANIZE;

USE Adventureworks
ALTER INDEX PK_Employee_EmployeeID ON HumanRessource.Employee REBUILD;

[SQL Serveur 2000] Comment Réindexer ?

Laisser une réponse

Une des taches d’ administration les plus simple que le développeur oublie systématiquement de mettre en place et qui entraîne des pertes de performance importante après plusieurs mois de mise à jour de la base.

Ptit Dje nous fournit un script qui utilise DBCC INDEX DEFRAG et évite de faire chuter les performances de la base lors de l’application. On définit le seuil de fragmentation MAXFRAG à partir de laquelle s’applique la réindexation.

  /*Perform a 'USE <database name>' to select the database in which to run the script.*/
-- Declare variables
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr   VARCHAR (255)
DECLARE @objectid  INT
DECLARE @indexid   INT
DECLARE @frag      DECIMAL
DECLARE @maxfrag   DECIMAL   -- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 10.0   -- Declare cursor
DECLARE TABLES CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE' --and table_name not in ('table_act_entry','table_case')   -- Create the table
CREATE TABLE #fraglist (
   ObjectName CHAR (255),
   ObjectId INT,
   IndexName CHAR (255),
   IndexId INT,
   Lvl INT,
   CountPages INT,
   CountRows INT,
   MinRecSize INT,
   MaxRecSize INT,
   AvgRecSize INT,
   ForRecCount INT,
   Extents INT,
   ExtentSwitches INT,
   AvgFreeBytes INT,
   AvgPageDensity INT,
   ScanDensity DECIMAL,
   BestCount INT,
   ActualCount INT,
   LogicalFrag DECIMAL,
   ExtentFrag DECIMAL)   -- Open the cursor
OPEN TABLES   -- Loop through all the tables in the database
FETCH NEXT
   FROM TABLES
   INTO @tablename   WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
   INSERT INTO #fraglist
   EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
   FETCH NEXT
      FROM TABLES
      INTO @tablename
END   -- Close and deallocate the cursor
CLOSE TABLES
DEALLOCATE TABLES   -- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
   SELECT ObjectName, ObjectId, IndexId, LogicalFrag
   FROM #fraglist
   WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0   -- Open the cursor
OPEN indexes   -- loop through the indexes
FETCH NEXT
   FROM indexes
   INTO @tablename, @objectid, @indexid, @frag   WHILE @@FETCH_STATUS = 0
BEGIN
   PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
       + RTRIM(CONVERT(varchar(15),@frag)) + '%'
   SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
       ' + RTRIM(@indexid) + ')'
   EXEC (@execstr)       FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
END   -- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO
Une solution plus lourde utilisant DBCC Reindex donc entrainant une perte de performance du serveur mais trés efficace car elle recree la totalité des index
USE Mabase
go
DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FORSELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
go

KIT DE FORMATION MICROSOFT SQL SERVEUR 2005 MCTS 70-431.

Laisser une réponse

chapitre 1 – installation de sql serveur 2005.
chapitre 2 – configuration de sql serveur 2005.
chapitre 3 – Création de tables, de contraintes et de types définis par l’utilisateur.
Chapitre 4 – Création d’index.
Chapitre 5 – Travail avec Transact SQL.
Chapitre 6 РCr̩ation de partition.
Chapitre 7 – Mise en oeuvre de vues.
Chapitre 8 РGestion des donn̩es XML.
Chapitre 9 РCr̩ation de fonctions, de proc̩dures stock̩es et de d̩clencheurs.
Chapitre 10 – Travail avec les fichiers plats
Chapitre 11 -Sauvegarde, restauration et déplacement d’une base de données.
Chapitre 12 – Gestion de bases de données à l’aide de Transact SQL
Chapitre 13 – Travail avec les points de terminaison HTTP.
Chapitre 14 – Travaux de l’agent SQL Server.
Chapitre 15 РSurveillance et d̩pannage des performances de SQL Server
Chapitre 16 РGestion de captures instantann̩es de base de donn̩es.
Chapitre 17 РMise en miroir de base de donn̩es.
Chapitre 18 – Mise en oeuvre de l’envoi de journaux.
Chapitre 19 -Gestion de la réplication.

En téléchargement, un outil de préparation d’examen offrant 150 questions d’un niveau facile par rapport à la certification.

Personnellement, j’ai trouvé un grand intérêt à la lecture de cet ouvrage qui référence de nombreuses connaissances basiques en plus de permettre une préparation de la certification.

Comment Attacher/Détacher une base à une instance ?

Laisser une réponse

Savoir faire INDISPENSABLE A SAVOIR POUR MIGRER D’une version à une autre.

Attacher:

Aprés téléchargement et décompactage .msi de la base Adventure works. Il reste à attacher cette base à l’instance SQLEXPRESS de sql serveur.

Pour cela, j’utilise SQL Serveur management studio express.

Je me connecte sous mon compte administrateur windows pour éviter les problème de sécurité avec la base Adventure works puisque mon compte sa/pwd n’existe pas sous Adventure works.

Je sélectionne l’onglet database et avec un clic droit, je sélectionne attache…

Il ne reste plus qu’à indiquer le chemin du fichier MDF de la base adventure works.

En ligne de commande: <br />
EXEC sp_attach_db @dbname ='AdventureWorks',@filename1 = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLData\AdventureWorks_Data.mdf',@filename2 = 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQLData\AdventureWorks_Log.ldf' <br />
 <br />
Détacher: <br />
En ligne de commande: <br />
EXEC SP_DETACH_DB 'AdventureWorks'

[SQL Serveur 2000] Comment effectuer simplement une migration entre SQL Serveur 2000 et 2005 ?

Laisser une réponse

De nombreuses entreprises ont encore leurs serveurs de production sur sql serveur 2000, voir même dans certains cas, sur sql serveur 7. Il devient important de migrer vers sql serveur 2005. D’abord, sql serveur 2008 sortira en août. Ensuite, Microsoft a annoncé la fin de son support sur sql serveur 2000 pour avril 2008.
Lire la suite →

Quelle est l’architecture interne de la sécurité sql serveur ?

Laisser une réponse

Il s’agit d’une présentation succinte de la sécurité d’une base de données sql server 2005. Elle vous sera utile pour comprendre comment remplir le connexionstring de C#. Cette présentation vous permettra également d’affiner la sécurité coté serveur SQL, à l’aide d’un script, si vous souhaitez sécuriser plus fortement qu’avec SA ou l’administrateur windows en fonction de votre mode de connexion.

Choix du mode d’authentification de sql serveur (Trés Important).

authentification windows : seuls les utilisateurs windows authentifiés peuvent accéder à sql serveur. Ce mode est hautement recommandé dans le cas d’un domaine car il permet de mettre en place toute une politique de sécurité à l’aide d’active directory ( audit…).

authentification mixte : Ce mode d’authentification permet la connexion à sql serveur à des utilisateurs non windows.

Le mode d’authentification du serveur, choisi lors de l’installation, peut être modifié à l’aide de sql server management studio.

Les connexions ( sécurité externe ).

Les connexions, appelé aussi identifiant par microsoft, ou login par les utilisateurs peuvent être crée par sql server management studio ou par T-SQL.

Pour créer une connexion windows : CREATE LOGIN [DomaineUtilisateur] FROM WINDOWS
Pour créer une connexion windows à un groupe : CREATE LOGIN[DomaineGroupe] FROM WINDOWS
Le groupe administrateur de windows possède une connexion de ce type.

Pour créer une connexion sql server : CREATE LOGIN nom_utilisateur WITH PASSWORD=’password’
L’utilisateur SA possède une connexion de ce type.

Pour supprimer une connexion windows : DROP LOGIN [DomaineUtilisateur]
Pour supprimer une connexion windows à un groupe : DROP LOGIN [DomaineGroupe]
Pour supprimer une connexion sql serveur : DROP LOGIN nom_utilisateur

La propriété WITH DEFAULT_DATABASE=nom base_données permet de définir une base par défaut au login mais n’accorde aucunement le droit d’y accéder. Pour les droits d’utilisation d’une base de données, lisez le chapitre ci dessous relatif aux utilisateurs de bases de données et le compte GUEST.

Rôle serveur de la connexion.

SYSADMIN : toute activité est possible sur le serveur.
DBCREATOR : les membres peuvent créer, modifier, supprimer et restaurer toute base de données.
DISKADMIN : Ce rôle est utilisé pour gérer les disques physiques.
PROCESSADMIN : Les membres peuvent interrompre les processus en cours dans une instance SQL Serveur.
SECURITYADMIN : Les membres peuvent gérer les connexions et leurs propriétés. Ils peuvent accorder ( GRANT ), refuser ( DENY ) et révoquer ( REVOKE ) les autorisations au niveau serveur, au niveau base de donnée. ils peuvent réinitialiser les mots de passes. Ce rôle n’a aucune permission pour affecter des autorisations de bases de données.
BULKADMIN : les membres peuvent executer l’instruction BULK INSERT.
SERVERADMIN : les membres peuvent changer les options de configuration coté serveur et arrêter le serveur.
SETUPADMIN : les membres peuvent ajouter ou supprimer des serveurs liés et executer certaines procédures systèmes.

PUBLIC : Toutes les connexions valides sont membre de ce groupe.

Il est possible d’affecter à une connexion un rôle serveur par la commande suivante :

EXECUTE sp_addsrvrolemember nom_login, role_serveur

Par défaut, le groupe des administrateurs windows a le rôle SYSADMIN.
Par défaut, l’utilisateur SA a le rôle SYSADMIN.

Par conséquent, les utilisateurs qui se connectent sous ces identités peuvent tout faire sur le serveur ( configuration, création/destruction objets ).

En général, si l’on souhaite mettre en place d’une sécurité spécifique, un compte possède simplement le rôle par défaut PUBLIC et ne possède par conséquent aucun droit sur son environnement. Pour permettre à un rôle PUBLIC d’agir, il va falloir relié le login/la connexion à un utilisateur SQL Serveur auquel on affectera des droits.

Un cas particulier permet à un utilisateur ayant le rôle PUBLIC d’agir sur une base de données, lorsque l’utilisateur de bases de données GUEST possède des droits, comme le droit de se connecter à une base de données, ou le droit de lire les données d’une base.

Les utilisateurs. Niveau interne de sécurité

Pour créer un utilisateur en T-SQL , on utilise la commande :

CREATE USER nom_utilisateur FOR LOGIN nom_login WITH DEFAULT_SCHEMA = schema_par_defaut

si vous n’utilisez pas la commande optionnel WITH DEFAULT_SCHEMA, alors le schéma par défaut sera dbo, le schéma de la base de données. Le ou les schémas d’un utilisateur sont utilisés par sql serveur pour résoudre les noms d’objet référencés sans leur nom complet.

Une fois l’utilisateur créé et relié à une connexion, vous pouvez lui accorder un rôle de bases de données.

les rôle de bases de données de l’utilisateur.

Afin de limiter le travail de l’administrateur de bases de données dans le cas d’une base de plusieurs dizaines d’utilisateurs, au lieu d’affecter des droits sur chaque objet, on peut affecter des droits globaux. Il est aussi possible de créer ses propres rôle de bases de données.

PUBLIC : par defaut.
DB_ACCESSADMIN : l’utilisateur qui possède ce droit pour modifier un USER ou créer un schéma.
DB_BACKUPOPERATOR : Les membres peuvent sauvegarder la base de données.
DB_DATAREADER : les membres peuvent lire les données dans l’ensemble des tables.
DB_DATAWRITER : les membres peuvent ajouter, supprimer, ou modifier des données dans les tables utilisateurs.
DB_DDLADMIN : les membres peuvent executer toute commande DDL dans une base de données.
DB_DENYDATAREADER : les membres ne peuvent lire les tables utilisateurs.
DB_DENYDATAWRITER : les membres ne peuvent écrire dans les tables utilisateurs.
DB_OWNER : les membres peuvent effectuer toute les opérations de maintenance et de configuration y compris la suppression.
DB_SECURITYADMIN : les membres peuvent modifier l’appartenance à un rôle et gérer les autorisations.

On affecte un rôle à un utilisateur avec la procédure stockée :

EXECUTE sp_addrolemember nom_role,nom_utilisateur

Création d’un rôle de base de données spécifique et affectation de droits à ce rôle.

USE mabase
GO
CREATE ROLE Rolemabase;
GRANT CREATE TABLE,CREATE VIEW,CREATE PROCEDURE TO Rolemabase;

On autorise la création d’objet de bases de données pour les utilisateurs possédant le rôle RolemaBase.

Les schémas.

Un schéma est un ensemble d’objets de bases de données appartenant à un utilisateur interne. Marcel dans notre cas. La différence entre sql serveur 2000 et sql serveur 2005. c’est qu’avec sql serveur 2005, le schéma est indépendant de l’utilisateur. Ainsi, lors qu’un créateur de schéma quitte l’entreprise, on peut réaffecter son schéma à un autre utilisateur trés simplement. On utilise les schémas pour créer des espaces de noms, l’équivalent d’une librairie C#, avec les objets de bases de données.

Autorisations d’instruction à un user sur une base de données.

L’authorisation de création d’objet de bases de données est gérée par l’autorisation d’instruction

GRANT/REVOKE ALLInstructions TO user

GRANT : affecte
REVOKE : supprime

Instructions : BACKUP DATABASE, BACKUP LOG, CREATE DATABASE ,CREATE PROCEDURE, CREATE TABLE, CREATE VIEW

Affectation de droit d’accès à un user sur un objet.

GRANT ALLAutorisations ON Table(colonne)Vue(colonne)Procedure_stockee TO User WITH GRANT OPTION AS User

ALL Accorder tous les droits.
Autorisations : CREATE, ALTER, DROP, CONTROL, CONNECT, SELECT, EXECUTE, UPDATE, DELETE , INSERT, TAKE OWNERSHIP, VIEW DEFINITION, BACKUP
WITH GRANT OPTION : Permet à l’utilisateur bénéficiaire de transmettre ces droits à un autre.
AS User : User est un compte possédant les droits nécessaires pour accorder des autorisations.

lien : http://www.sqlfr.com/tutoriaux/SECURITE-SQL-SERVER_720.aspx
lien : http://pcaboche.developpez.com/article/sql-server/gestion-droits/
lien : http://sql.developpez.com/sqlserver/securisation/

Mon serveur SQL est trop long à répondre : Comment changer le Timeout de mon applicatif C# ?

Laisser une réponse

Lorsque l’on travaille avec asp.net 2.0 et une base de données comme sql serveur 2005, il arrive que le serveur de bases de données n’ait pas le temps de répondre à la connexion car les 30 secondes du time out se sont écoulées. Pour éviter cela, faites cela :

Dim cmd As SqlCommand = SConnAloes.CreateCommand()
cmd.CommandText = SqlAnalyseInfLMR
cmd.CommandType = CommandType.Text
cmd.CommandTimeout = 300000
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
da.Fill(DTAnalyseInfLMR)

Il existe deux types de time out à gérer : connection.timeout et command.timeout