Jarosław Sołoducha at 09:55, 11 September 2019

2019-09-11T09:55:08Z

New page

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

;author: Jarosław Sołoducha (NobleProg)
</slideshow>
{{Can I use your material}}
==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

[https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.110%29.aspx 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 ⌘==
 
{| 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 ⌘==
 
* 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)⌘==
 

<big>Data Manipulation Language</big> 

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

*ćwiczenie

==Język definicji danych (DDL)⌘==
 

<big>Data Definition Language</big>
 

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

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

* Simple
* Full
* Bulk logged

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

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

Jarosław Sołoducha at 09:55, 11 September 2019