Archives pour la catégorie EcoSystème

Le changement de version entre SQL serveur 2000 et 2005 est il nécessaire ?

Laisser une réponse

En mars 2007, quand j’ai décidé de me plonger dans l’étude approfondie des bases de données pour en faire un métier, je me suis intéressé à SQL Serveur 2005 qui était le produit le plus récent. Après 2 mois d’étude du produit sur la version developper : J’écrivais cet article qui vantait les nouveautés SQL de cette version à juste titre:
Référence : ici

SQL Serveur 2005 est plus performant en terme de haute disponibilité mais SQL Serveur 2000 sait déjà faire les 3 sortes de réplications.

SQL Serveur 2005 est un trés bon produit OLAP mais SQL serveur 2000 possède déjà les lots dts et un noyau Analysis service.

D’autre part, SQL Serveur 2005 est encore mieux sécurisé en comparaison des autres sgbd du marché et il offre une scalabilité encore plus grande à la montée en charge.

Néanmoins, Aujourdhui, je travaille à mi-temps sur SQL Serveur 2000 et je trouve que ce SGBD est une trés bonne base de données suffisante pour la majorité des bases internet.

Lire la suite →

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.

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/

Qu’apporte l’outil « Best Practice Analyser » de SQL Server 2005 ?

Laisser une réponse

Je recommande de passer chaque semaine le BPA sur l’ ensemble des serveurs de production. Cela permet de s’ assurer qu’ aucune base n’ a pas de sauvegarde, que les logs ne comportent pas d’ anomalies, de détecter d’ éventuels pannes de disque car il exige un contrôle d’ intégrité des bases (DBCC CheckDB)… Cela règle les problèmes de sécurité lors du premier passage comme le compte de service de SQL Serveur. A ce propos, il est préciser que l’ on ne doit pas utiliser le compte System. Pour régler ce point comme pour régler l’ ensemble des points de BPA, après désignation de l’ anomalie, vous devez vous reporter à l’ aide en ligne de BPA qui vous renvoie vers MSDN. Il faut savoir qu’ il existe une quarantaine de règle de bonne pratique que votre serveur doit respecter, de plus, de nouvelles règles peuvent être téléchargées. Toutes ces règles de bonnes pratique sont expliquées dans l’ aide de BPA ce qui doit renforcer la formation du DBA/Développeur.

Pour télécharger  SQL Serveur Best Practice Analyser cliquez ici

A quel moment doit avoir lieu l’optimisation d’une base ?

Laisser une réponse

L’ importance de la normalisation de la base est essentiel. Le modèle relationnelle a fait passer le gros du travail de la partie développement à la partie conception.
L’ Architecte de la base, premier intervenant du projet, qui utilise POWER AMC ou Toad Data Modeler doit respecter absolument la 3 ème forme normale dans la définition de son modèle. On ne peut pas faire l’ économie d’un modéle relationnel dans la conception d’ une application.

Le deuxième pôle de l’ optimisation est le soin apporté à l’ écriture du SQL. Privilégié le plus possible l’ écriture de SQL à l’ écriture de Transact SQL. L’ utilisation de SQL permet l’ indexation et des performances supérieurs. Par exemple, la semaine dernière, mon patron me signale une application dont les temps de réponses dépassés 30 secondes. Fier de mes connaissances, je propose d’ optimiser la requête par une indexation adéquate. Je passe 3H00 sur la requête pour finalement découvrir que les 30 secondes sont perdus par l’ utilisation en cascade de fonctions. Évidemment, en tant que développeur, on doit répondre à des besoins fonctionnelles et l’encapsulation en fonction répond bien à nos besoins mais en production, avec 100000 lignes dans une table, on perd un temps précieux à l’ exécution.

Le troisième pôle de l’ optimisation est l’ indexation. Sur des tables de plus de 1000 lignes, il devient important d’ indexer. Un produit comme POWER AMC vous facilite le travail puisqu’ il génére la majorité des index à la conception pour des tables bien normalisé.

On découvre donc bien que l’ essentiel de l’ optimisation d’ une base de données ne se passe pas donc pas en production, par le DBA, mais lors de la fabrication, par le développeur et l’architecte de la base.

Ne simplifions pas trop, les développeurs règlent généralement très bien les problèmes apparents et les problèmes de performances apparaissent souvent très tard dans le cycle de vie et entraîne des interventions des DBA. Néanmoins, la montée en charge future d’ une application peut être prévu très tôt par le respect de normes.

Qu’apporte SQL Serveur 2005 dans la résolution des problèmes SQL ?

Laisser une réponse

Je dois avouer que les ingénieurs de Microsoft ont fait un travail remarquable dans l’interrogation des données ( T-SQL ) pour la version 2005.

La première révolution, c’est la gestion de la récursivité dans les tables hiérarchiques. Un problème, hier, complexe qui impliquait l’utilisation d’un langage de programmation, la connaissance de la récursivité en .net par exemple ou l’utilisation d’une organisation intervallaire ( voir l’article remarquable de sql pro sur ce sujet : http://sqlpro.developpez.com/cours/arborescence/ )
Aujourd’hui, grace aux CTE ( Common Table Expression ), on peut gérer des arbres hierarchiques simplement dans une requête.

La deuxième révolution, c’est les fonctions de fenetrage et de classement. Un problème résolu sous oracle depuis la version 8 qui trouve enfin sa solution chez microsoft. La question qui est résolu est la suivante :
Vous avez une université composé de modules, les modules sont composés de TD, les TD sont remplis d’élèves noté par un professeur.
Pour chaque module de l’université, vous voulez connaitre le nom des 3 premiers lauréat de chaque TD. Seul les fonctions de classement ROW_NUMBER, RANK(), PARTITION BY permettent de gérer ce problème.

BCP

Laisser une réponse

Export des données d’une table vers un fichier.

-S précise le nom de l’instance
-T utilise le mode d’authentification windows
-c fichier origine au format texte

exemple :

bcp ssms.dbo.clients out c:clients.txt -c -S Serveur -T
bcp Adventureworks.person.contact out « d:sql serveuradventureworks_person_contact.txt » -S Pluton -U SA -P ******* -T -c

Exporter le résultat d’une requête avec bcp

exemple :

bcp « select ville from ssms.dbo.clients » queryout c:lesvilles.txt -c -S Serveur -U id_utilisateur -P Password

Importer des données avec bcp.

-F La première ligne contient le nom de colonnes, la deuxième ligne les données.
-t précise la tabulation de separation.

exemple :

bcp ssms.dbo.codepostaux in c:codepostal.txt -c -F 2 -t ; -S Serveur -T