View source for Administracja baza danych Microsoft SQL Server ENG

{{Template:SQL Links PL}}
[[Category:EN]] 
[[Category:SQL]] 
<slideshow style="nobleprog" headingmark="⌘" incmark="…" scaled="false" font="Trebuchet MS" >
;title: <small>SQL Server Administration</small><br />


;author: Jarosław Sołoducha (NobleProg)
</slideshow>
{{Can I use your material}} 
==Basic information about Databases⌘==
<br />

* 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⌘==
<br />

* Enterprise 
*  Standard
*  Workgroup
*  Mobile 
*  Express
*  Developer

[https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.110%29.aspx SQL Editions]

==Narzędzia SQL Server⌘==
<br />

* 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 ⌘===
<br />

Zastosowanie:
# Tworzenie
# Zarzadzanie
# Przedstawianie raportów
# Report Manager


=== Intergration Services ⌘===
<br />

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
<br />
<br>
* '''"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
<br>
* '''"NT AUTHORITY\NETWORK SERVICE"'''
:Wbudowana usługa
:ma wiekszy dostęp do zasobów i obiektów

== Konta Serwisowe dla SQL Servera cz.II ⌘==
<br>
* '''"NT AUTHORITY\SYSTEM"'''
:Wysoki poziom uprawnień w systemie lokalnym
<br>
* '''"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 ⌘==
<br><br>
{| class="wikitable"   style="width:150%"
|- style="text-align:center" 
! Usługa !! Stand-Alone !! Cluster
|-  style="text-align:center" 
| Database Engine   ||  NETWORK SERVICE  ||  Domain user account
|-style="text-align:center" 
| SQL Server Agent  ||  NETWORK SERVICE  ||  Domain user account
|-style="text-align:center" 
| SSAS  || NETWORK SERVICE || Domain user account
|-style="text-align:center" 
| SSIS || NETWORK SERVICE || NETWORK SERVICE 
|-style="text-align:center" 
| SSRS || NETWORK SERVICE || NETWORK SERVICE
|-style="text-align:center" 
| SQL Server Browser || LOCAL SERVICE || LOCAL SERVICE
|}

== Protokoly Sieciowe SQL Serwera ⌘==
<br>
* Microsoft SQL Server może obsługiwać żądania od kilku protokołów, w tym samym czasie.
* Konfiguracja protokołów - SQL Server Configuration Manager

<br>
'''Rodzaje protokołów:'''
* Shared Memory
* TCP/IP
* Named Pipes
* VIA

== Bazy systemowe ⌘==
<br />
* master
* model
* msdb
* tempdb
<br />

* 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 ⌘===
<br />

* Baza tymczasowa
* Tworzona jest na nowo po kazdym restarcie uslugi
* Zawiera obiekty tymczasowe:
:tabele
:kursory
:rozne obiekty tworzone wewnetrznie przez SQLa
<br />

* Oddzielny szybki dysk
* Utworzenie tylu plikow ile jest procesorow

=== Distribution* ⌘===
<br />

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

=== Resource* ⌘===
<br />

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

==Tworzenie bazy danych==
<br />
=== Pliki bazy danych i ich lokalizacja⌘===
<br />

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

=== Rozmiar plików oraz ich przyrost ⌘===
<br />

* rozmiar początkowy
* baza danych Model
* autogrowth

=== Partycjonowanie tabel ⌘===
<br />

* 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)⌘==
<br />

<big>Data Manipulation Language</big> <br />


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



<small>*ćwiczenie</small>

==Język definicji danych (DDL)⌘==
<br />

<big>Data Definition Language</big> 
<br />


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



<small>*ćwiczenie</small>

==Projektowanie tabel⌘== 
<br />

* 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



<small>*ćwiczenie</small>

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


<small>*Video</small>

=== Indeksy ⌘===
<b>Klucz główny - Primary Key, <br /></b>
:Każdy wiersz w naszej tabeli musi mieć wartość która go unikalnie zidentyfikuje. <br />
: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
<br />
=== Indeksy ⌘===
<b>Klucz obcy  - Foreign Key, <br /></b>
: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 ⌘===
<br />

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

=== Non-Clustered Indexes ⌘===
<br />
* Do 249 indexów nie-klastrowanych na tabeli
* Moga byc oparte na indexach klastrowanych.

=== REBUILD vs. REORGANIZE ⌘===
<br />

* 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 <br />

(kolumna AVG_FRAGMENTATION_IN_PERCENT w widoku sys.dm_db_index_physical_stat)

<b>Fragmentacja: </b>
* 0 - 10%    - nic
* 11 - 30%   - REORGANIZE
* 31 - 100%  - REBUILD

=== REBUILD vs. REORGANIZE- skrypt ⌘===
<br />

 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


<small>*ćwiczenie</small>

==Tworzenie aplikacji bazodanowych⌘==
<br />

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


==Programowanie po stronie serwera⌘==
<br />

* 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 ?
<br />

<small>*cwiczenie</small>

=== Widoki ⌘===
<br />

* 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

<small>*cwiczenie</small>

=== Funkcje ⌘===
<br />
* 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 ⌘===
<br />

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

<small>*ćwiczenie ?</small>

== Modele odtwarzania ⌘==
<br />

<big>
Z ang."Recovery Model"</big>
<br />

* Simple
* Full
* Bulk logged


== Modele odtwarzania ⌘==
[[File:recoverymodel.jpg]]

=Kopie bezpieczeństwa ⌘=
<br />

=== Pełna kopia bazy danych ⌘===
 Full Database Backup<br />

 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),<br />
 
jak i w plikach dziennika transakcyjnego - tu tylko dane z aktywnej części dziennika.

=== Przyrostowa kopia bazy danych ⌘===
 Differential Database Backup<br />

 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,<br />
które zostały zmodyfikowane od czasu wykonania ostatniej pełnej kopii bazy danych.

=== Kopia dziennika transakcyjnego ⌘===
 Transaction Log Backup<br />

 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.<br />
Plik kopii dziennika transakcyjnego jest bezużyteczny bez aktualnego pliku pełnej kopii tej bazy danych.

<small>*ćwiczenie</small>

= Strategie kopii zapasowych ⌘=
<br />
* Pełna kopia
* Pełna kopia + kopia dziennika transakcyjnego
* Przyrostowa kopia
* Kopia plików

=== Strategia pełnych kopii bazy danych ⌘===
<br />

Wykorzystywana w przypadku niewielkich lub rzadko modyfikowanych baz danych.
<br />


Zalety:
:Mała liczba backupow
:Krótki proces odtwarzania bazy
<br />

Wady:
:Ryzyko utraty danych od ostatniego pełnego backupu
:Częste wykonywanie kopii bazy

=== Strategia pełnych kopii i dziennika transakcyjnego ⌘===
<br />
Strategia zalecana w przypadku niewielkich i często modyfikowanych baz danych.
<br />


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 ⌘===
<br />
Strategia zalecana w przypadku średniej wielkości baz danych.
<br />

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 ⌘===
<br />
Strategia zalecana w przypadku średniej wielkości baz danych.
<br />


Zaley:
:Skrócenie czasu potrzebnego na wykonanie kopii zapasowych.
:Ograniczenie liczby potrzebnych do przechowywania kopii nośników.

Wady:
:Skomplikowany proces odtwarzania bazy danych.
<br />

----
''*cwiczenia - autogrowth,transaction log, shrinking''

= Kopiowanie Bazy Danych ⌘=
<br />

* Backup / Restore
* Detach / Attach
* Kreator


<small>*cwiczenie</small>

=Automatyzacja⌘=
<br />

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



<small>*ćwiczenie i video</small>

==Maintenance Plan ⌘==
<br />

* 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⌘=
<br />

* Tworzenie kont:
:Active Directory
:SQL Account
* Schematy
* Tworzenie ról
* Konto SA
* Role Serwerowe
* Role bazodanowe

== Tworzenie kont ⌘==
== Konto SA ⌘==
== Role Serwerowe ⌘==
<br />
<br />

* bulkadmin
* dbcreator
* diskadmin
* processadmin
* public
* securityadmin
* serveradmin
* setupadmin
* sysadmin
<br />

== Role bazodanowe ⌘==
<br />
W przeciwieństwie do ról serwerowych,
możliwe jest tworzenie własnych ról bazodanowych.
<br />
<br />

* db_accessadmin
* db_backupoperator
* db_datareader
* db_datawriter
* db_denydatareader
* db_denydatawriter
* db_owner
* db_securityadmin
* public

== Schematy ⌘==
<br />
* 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 ⌘=
<br>
* In-Place vs. Migracja
* Service-Pack
* Cluster

= TEST :) ⌘=

=Best Practices⌘=
<br />

* 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⌘=
<br />

 http://www.wss.pl/ 
 http://www.virtualstudy.pl/ 
 http://www.sqlservercentral.com/ 
<br />

=Ankieta i Certyfikaty⌘=