title: SQL Server Administration

author: Jarosław Sołoducha (NobleProg)

Copyright Notice

This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise.

Basic information about Databases⌘

Bazy plikowe np. MS Access
Bazy Client/Server
Systemy zarządzania relacyjną bazą danych (RDBMS):

DB2
Microsoft SQL Server
MySQL
Oracle
PostgreSQL

SQL Server Versions & Editions⌘

Enterprise
Standard
Workgroup
Mobile
Express
Developer

SQL Editions

Narzędzia SQL Server⌘

SQL Server Management Studio
Business Intelligence Development Studio
SQL Server Agent
SQL Server Profiler
Database Tuning Advisor

Usługi w SQL Server ⌘

Database Services
Analysis Services
Reporting Services
Intergration Services

Database Services ⌘

W skład SQL Server Database Services wchodzi:

Data files
Replication
Full-Text search

Analysis Services ⌘

Inaczej "Hurtownie Danych"

Zastosowanie:

Przetwarzanie analityczne danych
Wspomaganie decyzji
Centralizacja danych
Archiwizacja

Reporting Services ⌘

Zastosowanie:

Tworzenie
Zarzadzanie
Przedstawianie raportów
Report Manager

Intergration Services ⌘

1. Zbiór narzędzi do zarzadzania zbiorami w prcesie ETL

 ETL - Extract, Transform , Load ( wydobywanie, przetwarzanie, ladowanie )

2. Wykonywanie innych zadan

Konta Serwisowe dla SQL Servera ⌘

http://technet.microsoft.com/en-us/library/cc281953%28v=sql.120%29.aspx

"NT AUTHORITY\LOCAL SERVICE"

Ten sam poziom uprawnien co członkowie grupy Users

Konto usługi lokalnej nie jest przeznaczone dla SQL Server czy SQL Server Agent

"NT AUTHORITY\NETWORK SERVICE"

Wbudowana usługa

ma wiekszy dostęp do zasobów i obiektów

Konta Serwisowe dla SQL Servera cz.II ⌘

"NT AUTHORITY\SYSTEM"

Wysoki poziom uprawnień w systemie lokalnym

"Konto Domenowe"

współdziałanie z usługami sieciowymi,

dostep do zasobów domeny

file-share

połączenie z innymi serwerami SQL

Konta Serwisowe dla SQL Servera cz.III ⌘

Usługa	Stand-Alone	Cluster
Database Engine	NETWORK SERVICE	Domain user account
SQL Server Agent	NETWORK SERVICE	Domain user account
SSAS	NETWORK SERVICE	Domain user account
SSIS	NETWORK SERVICE	NETWORK SERVICE
SSRS	NETWORK SERVICE	NETWORK SERVICE
SQL Server Browser	LOCAL SERVICE	LOCAL SERVICE

Protokoly Sieciowe SQL Serwera ⌘

Microsoft SQL Server może obsługiwać żądania od kilku protokołów, w tym samym czasie.
Konfiguracja protokołów - SQL Server Configuration Manager

Rodzaje protokołów:

Shared Memory
TCP/IP
Named Pipes
VIA

Bazy systemowe ⌘

master
model
msdb
tempdb

distribution*
resource*

Master ⌘

najważniejsza baza
zapisywana jest w niej konfiguracja SQL Servera – informacje o:

obiektach systemowych

loginach

lokalizacji poszczególnych plików

podpiętych bazach użytkownika

Model ⌘

Baza wzorcowa dla innych baz danych

Msdb ⌘

Baza pomocnicza wykorzystywana przez SQL Server Agenta
Zawiera informacje o:

job'ach

backup'ach

Tempdb ⌘

Baza tymczasowa
Tworzona jest na nowo po kazdym restarcie uslugi
Zawiera obiekty tymczasowe:

tabele

kursory

rozne obiekty tworzone wewnetrznie przez SQLa

Oddzielny szybki dysk
Utworzenie tylu plikow ile jest procesorow

Distribution* ⌘

Wytwarzana jezeli na Serwerze jest replikacja
Zawiera metadata i dane historyczne dotyczace replikacji

Resource* ⌘

Baza w trybie READ-ONLY
Zawiera systemowe obiekty
Prostszy upgrade SQL Serwera
Brak backup'u

Tworzenie bazy danych

Pliki bazy danych i ich lokalizacja⌘

.mdf
.ndf
.ldf
FileGroup
Fizyczne i logiczne pliki baz danych

Rozmiar plików oraz ich przyrost ⌘

rozmiar początkowy
baza danych Model
autogrowth

Partycjonowanie tabel ⌘

Partycjonowanie dostępne tylko w edycjach Enterprise i Developer SQL Server (oraz w wersjach ewaluacyjnych)
Zwiekszenie szybkosci dostepu do danych
Latwosc zarzadzania
Najczesciej wykorzystywane przy podziale danych wg "wieku"
Wymagane uprawnienia: sysadmin , db_owner i db_ddladmin.

Język modyfikacji danych (DML)⌘

Data Manipulation Language

SELECT - wylistowuje wiersze
INSERT - wstawia nowy wiersz
UPDATE - zmienia istniejący wiersz
DELETE - usuwa wiersz z repozytorium

*ćwiczenie

Język definicji danych (DDL)⌘

Data Definition Language

CREATE - tworzenie nowego obiektu
ALTER - wstawanie obiektu do istniejącego systemu
DROP - usuwanie istniejącego obiektu

*ćwiczenie

Projektowanie tabel⌘

Kolumny i atrybuty
Określenie typu danych

- Tekstowe (char, varchar, nchar, ntext, nvarchar)

- Liczbowe (int, smallint, bigint, tinyint, float, real, decimal, numeric)

- Daty i czasu (datetime, smalldatetime)

- Binarne (binary, varbinary)

- Waluty (money, smallmoney )

- Specjalne (text, image, xml, bit)

Zmiana własności kolumn
Więzy integralności

*ćwiczenie

Indeksy ⌘

Indeks w bazie danych to rodzaj struktury ściśle związanej z tabelą lub widokiem, która pomaga w znaczny sposób przyspieszyć pobieranie danych z tych źródeł.
Indeks zawiera klucze zawierające jedną, bądź kilka połączonych kolumn tabeli lub widoku.
Klucze w indeksie przechowywane są w strukturze zwanej B-drzewem

Indeksy ⌘

Indeksy

Clustered
Non-Clustered

Klucze obce

Klucz główny - Primary Key
Klucz obcy - Foreign Key

Dla skuteczności indeksu najistotniejsze są następujące kwestie:
- wysoka selektywność indeksu
- aktualność statystyk
- niski poziom fragmentacji

*Video

Indeksy ⌘

Klucz główny - Primary Key,

Każdy wiersz w naszej tabeli musi mieć wartość która go unikalnie zidentyfikuje.

Aby zakwalifikować dany atrybut jako klucz główny, musimy sprawdzić, czy posiada następujące własności:

musi posiadać wartość dla każdego z wierszy
dla każdego z tych wierszy wartość musi być unikalna
wartość ta nie może się zmienić, ani nie może zostać usunięta podczas całego funkcjonowania wiersza w tabeli

Indeksy ⌘

Klucz obcy - Foreign Key,

Klucz obcy jest kopią klucza głównego z innej tabeli.

Asocjacja jest utworzona pomiędzy tabelami poprzez zaznaczenie, iż wartość z jednej tabeli,

w której jest kluczem obcym, jest powiązana z wartością z innej tabeli, gdzie jest kluczem głównym

Clustered Indexes ⌘

Jeden index klastrowany na tabeli
Najczesciej zakladany na kolumnie o wysokiej "selektywnosci"
Klucz główny – klastrowany index

Non-Clustered Indexes ⌘

Do 249 indexów nie-klastrowanych na tabeli
Moga byc oparte na indexach klastrowanych.

REBUILD vs. REORGANIZE ⌘

REBUILD usuwa i tworzy indeksy od nowa,
REORGANIZE tylko sortuje strony, nie usuwa ich,
zarówno REBUILD jak i REORGANZIE zmniejsza fragmentację niezaleznie od tego ile ona wynosi

(kolumna AVG_FRAGMENTATION_IN_PERCENT w widoku sys.dm_db_index_physical_stat)

Fragmentacja:

0 - 10% - nic
11 - 30% - REORGANIZE
31 - 100% - REBUILD

REBUILD vs. REORGANIZE- skrypt ⌘

SELECT DB_NAME(PS.database_id) AS dbName,
S.name  AS SchemaName,
O.name AS TableName,
b.name,
ps.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
INNER JOIN sys.objects O  ON PS.object_id = O.object_id
INNER JOIN sys.schemas S ON S.schema_id = O.schema_id 
WHERE  ps.avg_fragmentation_in_percent >= 20 -- Indexes having Fragmentation >=20
AND PS.index_type_desc IN ('CLUSTERED INDEX','NONCLUSTERED INDEX') -- Only get clustered and nonclustered indexes
AND b.is_hypothetical = 0 -- Only real indexes
AND O.type_desc = 'USER_TABLE' -- Restrict to user tables
AND PS.page_count > 8 --- ignore tables less than 64K
ORDER BY ps.avg_fragmentation_in_percent,name DESC

*ćwiczenie

Tworzenie aplikacji bazodanowych⌘

Podstawy języka T-SQL
Składnia zapytań
Sortowanie
Łączenie tabel
Grupowanie danych
Podzapytania
Indeksy full-text

Programowanie po stronie serwera⌘

Tworzenie procedur składowanych (stored procedures)
Widoki (perspektywy)
Funkcje
Wyzwalacze

Procedury ⌘

Zbior instrukcji języka Transact-SQL zapisanych pod wspólną nazwą i wywoływanych jak pojedyncza instrukcja.
Nowe procedury moga wytwarzac osoby posiadajace takie prawa:

sysadmin

db_owner

db_ ddladmin

Są najczęściej wykorzystywanym przez programistów baz danych typem obiektów.
Do czego sie wykorzystuje procedury skladowane ?

*cwiczenie

Widoki ⌘

Jest to zdefiniowane i zapisane zapytanie po stronie serwera, ktorego wynik moze byc wielokrotnie wykonywany.
Uproszczenie prezentacji danych dla uzytkownikow
Ograniczneie dostepu do tabel
Ukrycie struktury tabel

*cwiczenie

Funkcje ⌘

Funkcja nie tylko wykonuje pewne operacje, ale także zwraca obliczony na podstawie przekazanych parametrów wynik.
Zwracane mogą być zarówno dane skalarne, jak i zbiory danych.

Wyzwalacze ⌘

Z ang. "Triggers"

Wyzwalacze są specjalnym typem procedur składowanych powiązanych z wybranymi tabelami

i wywoływanych wykonaniem ( zarówno po - AFTER , jak i zamiast - INSTEAD OF )

instrukcji języka Transact-SQL INSERT, UPDATE albo DELETE.

Podstawowym zastosowaniem wyzwalaczy jest wymuszenie integralności danych, zwłaszcza ich zgodności z regułami logiki biznesowej

*ćwiczenie ?

Modele odtwarzania ⌘

Z ang."Recovery Model"

Simple
Full
Bulk logged

Modele odtwarzania ⌘

Kopie bezpieczeństwa ⌘

Pełna kopia bazy danych ⌘

Full Database Backup

BACKUP DATABASE master TO 
DISK = 'D:\Program Files\Microsoft SQL Server\Mssql\Backup\master.bak'

Zawiera wszystkie informacje zapisane zarówno w plikach bazy danych (strukturę obiektów bazodanowych oraz dane tabel i indeksów),

jak i w plikach dziennika transakcyjnego - tu tylko dane z aktywnej części dziennika.

Przyrostowa kopia bazy danych ⌘

Differential Database Backup

BACKUP DATABASE Northwind TO 
disk='D:\Program Files\Microsoft SQL Server\Mssql\Backup\Northwind_diff.BAK' WITH DIFFERENTIAL

Do pliku przyrostowej kopii bazy danych zostaną zapisane wszystkie dane,
które zostały zmodyfikowane od czasu wykonania ostatniej pełnej kopii bazy danych.

Kopia dziennika transakcyjnego ⌘

Transaction Log Backup

BACKUP LOG Northwind TO 
disk='D:\Program Files\Microsoft SQL Server\Mssql\Backup\Northwind_log.TRN'

SQL Server do pliku kopii dziennika transakcyjnego zapisuje wszystkie wpisy z dziennika danej bazy danych.
Plik kopii dziennika transakcyjnego jest bezużyteczny bez aktualnego pliku pełnej kopii tej bazy danych.

*ćwiczenie

Strategie kopii zapasowych ⌘

Pełna kopia
Pełna kopia + kopia dziennika transakcyjnego
Przyrostowa kopia
Kopia plików

Strategia pełnych kopii bazy danych ⌘

Wykorzystywana w przypadku niewielkich lub rzadko modyfikowanych baz danych.

Zalety:

Mała liczba backupow

Krótki proces odtwarzania bazy

Wady:

Ryzyko utraty danych od ostatniego pełnego backupu

Częste wykonywanie kopii bazy

Strategia pełnych kopii i dziennika transakcyjnego ⌘

Strategia zalecana w przypadku niewielkich i często modyfikowanych baz danych.

Zalety:

Możliwość odtworzenia danych z dowolnego momentu sprzed awarii.

Prosty proces odtwarzania.

Mniejsza liczba potrzebnych do przechowywania kopii nośników — Ostatnia pełna kopia bazy danych i codzienne kopie dziennika transakcyjnego.

Wady:

Częste (co najmniej codziennego) wykonywanie pełnej kopii.

Dłuższy czas odtwarzania bazy z powodu wiekszej lpiczby kopii dziennika transakcyjnego.

Strategia przyrostowych kopii bazy danych ⌘

Strategia zalecana w przypadku średniej wielkości baz danych.

Zalety:

Krótszy czas wykonania kopii zapasowych.

Skrócenie, w porównaniu do poprzedniej strategii, czasu potrzebnego na odtworzenie bazy danych.

Możliwośc powortu do dowolengo pounktu w czasie.

Strategia przyrostowych kopii bazy danych ⌘

Wady:

Trudniejszy proces odtwarzania bazy danych.

Konieczność przechowywania:

plików pełnej kopii
przyrostowej kopii zapasowej
wszystkich kopii dziennika transakcyjnego od czasu ostatniej przyrostowej kopii bazy

Strategia kopii plików bazy danych ⌘

Strategia zalecana w przypadku średniej wielkości baz danych.

Zaley:

Skrócenie czasu potrzebnego na wykonanie kopii zapasowych.

Ograniczenie liczby potrzebnych do przechowywania kopii nośników.

Wady:

Skomplikowany proces odtwarzania bazy danych.

*cwiczenia - autogrowth,transaction log, shrinking

Kopiowanie Bazy Danych ⌘

Backup / Restore
Detach / Attach
Kreator

*cwiczenie

Automatyzacja⌘

SQL Agent
Wytwarzanie i planowanie zadań automatycznych - SQL Jobs
Powiadamianie o wynikach zadań

*ćwiczenie i video

Maintenance Plan ⌘

Sprawdzanie spójności bazy - DBCC CHECKDB
Statystyki
Sprawdzenie wskaźników indeksów - skrypt Rebuild vs Reorganize

Podstawy bezpieczeństwa i administracji SQL Server⌘

Tworzenie kont:

Active Directory

SQL Account

Schematy
Tworzenie ról
Konto SA
Role Serwerowe
Role bazodanowe

Tworzenie kont ⌘

Konto SA ⌘

Role Serwerowe ⌘

bulkadmin
dbcreator
diskadmin
processadmin
public
securityadmin
serveradmin
setupadmin
sysadmin

Role bazodanowe ⌘

W przeciwieństwie do ról serwerowych, możliwe jest tworzenie własnych ról bazodanowych.

db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
public

Schematy ⌘

Obiekty, które zawierają obiekty innych typów ( np.tabele,procedury)
Uproszczenie czynności adminstracyjnych
Wlasciciel Schematu
Transfer uprawnień

Monitorowanie aktywności SQL Server ⌘

Przechwytywanie aktywności za pomocą SQL Server Profiler
Aktywny monitoring
Praca z opcjami śledzenia
DMV
Data Collector

Upgrade ⌘

In-Place vs. Migracja
Service-Pack
Cluster

TEST :) ⌘

Best Practices⌘

Oddzielne dyski dla baz systemowych, aplikacyjnych i TEMP DB
Regularne kopie bezpieczeństwa
Ograniczony dostep do Serwera baz danych
Monitorowanie przyrostu baz
"Porządek" w tworzeniu procedur, widkow, tabel itd
Procedury systemowe w administracji
Dokumentacja
"każdy ma własne..."

Linki⌘

http://www.wss.pl/ 
http://www.virtualstudy.pl/ 
http://www.sqlservercentral.com/

Ankieta i Certyfikaty⌘

Administracja baza danych Microsoft SQL Server ENG

Copyright Notice

Basic information about Databases⌘

SQL Server Versions & Editions⌘

Narzędzia SQL Server⌘

Usługi w SQL Server ⌘

Database Services ⌘

Analysis Services ⌘

Reporting Services ⌘

Intergration Services ⌘

Konta Serwisowe dla SQL Servera ⌘

Konta Serwisowe dla SQL Servera cz.II ⌘

Konta Serwisowe dla SQL Servera cz.III ⌘

Protokoly Sieciowe SQL Serwera ⌘

Bazy systemowe ⌘

Master ⌘

Model ⌘

Msdb ⌘

Tempdb ⌘

Distribution* ⌘

Resource* ⌘

Tworzenie bazy danych

Pliki bazy danych i ich lokalizacja⌘

Rozmiar plików oraz ich przyrost ⌘

Partycjonowanie tabel ⌘

Język modyfikacji danych (DML)⌘

Język definicji danych (DDL)⌘

Projektowanie tabel⌘

Indeksy ⌘

Indeksy ⌘

Indeksy ⌘

Indeksy ⌘

Clustered Indexes ⌘

Non-Clustered Indexes ⌘

REBUILD vs. REORGANIZE ⌘

REBUILD vs. REORGANIZE- skrypt ⌘

Tworzenie aplikacji bazodanowych⌘

Programowanie po stronie serwera⌘

Procedury ⌘

Widoki ⌘

Funkcje ⌘

Wyzwalacze ⌘

Modele odtwarzania ⌘

Modele odtwarzania ⌘

Kopie bezpieczeństwa ⌘

Pełna kopia bazy danych ⌘

Przyrostowa kopia bazy danych ⌘

Kopia dziennika transakcyjnego ⌘

Strategie kopii zapasowych ⌘

Strategia pełnych kopii bazy danych ⌘

Strategia pełnych kopii i dziennika transakcyjnego ⌘

Strategia przyrostowych kopii bazy danych ⌘

Strategia przyrostowych kopii bazy danych ⌘

Strategia kopii plików bazy danych ⌘

Kopiowanie Bazy Danych ⌘

Automatyzacja⌘

Maintenance Plan ⌘

Podstawy bezpieczeństwa i administracji SQL Server⌘

Tworzenie kont ⌘

Konto SA ⌘

Role Serwerowe ⌘

Role bazodanowe ⌘

Schematy ⌘

Monitorowanie aktywności SQL Server ⌘

Upgrade ⌘

TEST :) ⌘

Best Practices⌘

Linki⌘

Ankieta i Certyfikaty⌘

Navigation menu

Search