Administracja baza danych Microsoft SQL Server

From Training Material
Jump to: navigation, search
Title

Administracja baza danych Microsoft SQL Server
Author
Jarosław Sołoducha
Subfooter

Administracja baza danych Microsoft SQL Server          Jarosław Sołoducha


Contents

Agenda ⌘

  • SQL Server Usługi, Wersje, Edycje
  • Management Studio
  • Podstawowe polecenia T-SQL
  • Przeglad właściwości bazy danych
  • Stored Procedures, Views, Triggers
  • Indeksy
  • Log transakcyjny
  • Recovery Models

Agenda ⌘

  • Backup and Restore
  • Praca z bazą danych( copy, shrink...)
  • Jobs & Maintenance Plans
  • Bezpieczeństwo
  • Upgrade
  • Monitoring

Podstawowe wiadomości o bazach danych⌘


  • Bazy plikowe np. MS Access
  • Bazy Client/Server
  • Systemy zarządzania relacyjną bazą danych (RDBMS) :
  1. DB2
  2. Microsoft SQL Server
  3. MySQL
  4. Oracle
  5. PostgreSQL

Edycje SQL Serwer⌘


  • Enterprise
  • Standard
  • Web
  • Express
  • Developer

https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2016?view=sql-server-2017

Wersje SQL Serwer ⌘


  • Select @@version
  • SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel')
  • Configuration Manager

https://sqlserverbuilds.blogspot.com/

Narzędzia SQL Server⌘


  • SQL Server Management Studio
  • Configuration Manager
  • Visual Studio
  • SQL Server Profiler
  • Database Tuning Advisor

Usługi SQL Server ⌘

  • Database Services
  • Analysis Services
  • Reporting Services
  • Intergration Services

Database Services ⌘

W skład SQL Server Database Services wchodzi:

  1. Data files
  2. Replication
  3. Full-Text search

Analysis Services ⌘

Inaczej "Hurtownie Danych"

Zastosowanie:

  1. Przetwarzanie analityczne danych
  2. Wspomaganie decyzji
  3. Centralizacja danych
  4. Archiwizacja

Reporting Services ⌘


Zastosowanie:

  1. Tworzenie
  2. Zarządzanie
  3. Przedstawianie raportów
  4. Report Manager

Intergration Services ⌘


1. Zbiór narzędzi do zarządzania zbiorami w procesie ETL

 ETL - Extract, Transform , Load ( wydobywanie, przetwarzanie, ładowanie )

2. Wykonywanie innych zadań

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 uprawnień 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 większy 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

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
  • Każdorazowa zmiana ustawień protokołu wymaga restartu usługi SQL Serwer.

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
  • każda nowa baza danych jest kopią bazy model
  • możliwa modyfikacja, która wpłynie na tworzone w przyszłości bazy

Msdb ⌘

  • Baza pomocnicza wykorzystywana przez SQL Server Agenta
  • Zawiera informacje o:
job'ach
backup'ach
historię wykonywanych jobów, backup'ów i restore'ów

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 jeżeli na Serwerze jest replikacja
  • Zawiera metadata i dane historyczne dotyczące replikacji

Resource* ⌘


  • Baza w trybie READ-ONLY
  • Zawiera systemowe obiekty
  • 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)
  • Zwiększenie szybkości dostępu do danych
  • Łatwość zarządzania
  • Najczęściej 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   -  wstawianie 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

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

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 999 indexów nie-klastrowanych na tabeli
  • Moga byc oparte na indexach klastrowanych.


*Video

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ę niezależnie 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

Programowanie po stronie serwera⌘


  • Tworzenie procedur składowanych ( stored procedures )
  • Widoki ( views )
  • Funkcje
  • Wyzwalacze ( triggers )

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 ?


*demo

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

*demo

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


*demo

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.

Modele odtwarzania ⌘


Z ang."Recovery Model"

  • Simple
  • Full
  • Bulk logged


Modele odtwarzania ⌘

Recoverymodel.jpg

Kopie bezpieczeństwa ⌘


  • Full
  • Differential
  • Transaction log

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.

Pełna kopia bazy danych ⌘

  • Pełna nazwa, jak sama nazwa wskazuje, tworzy kopię zapasową wszystkiego.
  • To podstawa każdego rodzaju kopii zapasowej!
  • Jest to kompletna kopia, która przechowuje wszystkie obiekty bazy danych: tabele, procedury, funkcje, widoki, indeksyo raz DANE
  • Mając pełną kopię zapasową, będziesz mógł łatwo przywrócić bazę danych w dokładnie takiej samej formie, jak w momencie tworzenia kopii zapasowej.

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

Odtwarzanie Bazy Danych ⌘

Restore.JPG

Odtwarzanie Bazy Danych ⌘

Restore bazy.JPG

"Shrinking" ⌘

  • DBCC SHRINKDATABASE
  • DBCC SHRINKFILE
  • Po pierwsze - NIE RÓB TO !!!!
  • "Shrink" powinno być rzadką operacją i nie powinno być częścią regularnej konserwacji, którą wykonujesz.
    Zmniejszanie plików danych powinno być wykonywane jeszcze rzadziej, jeśli w ogóle.
    Nie jest zalecane włączanie automatycznego zmniejszania bazy danych.

*cwiczenie

Kopiowanie Bazy Danych ⌘


  • Backup / Restore
  • Detach / Attach
  • Kreator


*cwiczenie

Automatyzacja⌘


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


*ćwiczenie

Maintenance Plan ⌘


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

*ćwiczenie

Podstawy bezpieczeństwa i administracji SQL Server⌘


  • Tworzenie kont: Active Directory , SQL Login
  • Schematy
  • Tworzenie ról
  • Konto SA
  • Role Serwerowe
  • Role bazodanowe

Konto SA ⌘

  • Konto „sa” jest dobrze znanym kontem SQL Server i służy jako domyślne konto administracyjne do SQL Server.
  • Hasło SA jest używane tylko podczas instalacji i migracji.
  • Wg zaleceń ( best practise ) nazwa powinna być zmieniona ponieważ to konto jest często "atakowane".

Role Serwerowe ⌘

  • bulkadmin -> może używać polecenia BULK INSERT.
  • dbcreator -> może tworzyć, zmieniać, usuwać i przywracać dowolną bazę danych.
  • diskadmin -> rola służy do zarządzania plikami dyskowymi
  • processadmin -> może zakończyć procesy uruchomione w instancji SQL Server.
  • securityadmin -> zarządza loginami i ich właściwościami, może resetować hasło.
  • serveradmin -> może zmienić konfigurację SQL serwera jak i zatrzymać usługę.
  • setupadmin -> może dodawać i usuwać połączone serwery za pomocą instrukcji T-SQL
  • sysadmin -> może wykonywać dowolne czynności na serwerze.
  • public -> Każde logoin SQL Server należy do roli public.


Role bazodanowe ⌘

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

  • db_accessadmin -> może dodawać lub usuwać dostęp do bazy danych dla loginów Windows, grup Windows i loginów SQL Server
  • db_backupoperator -> może wykonać kopię zapasową bazy danych.
  • db_datareader -> może odczytać wszystkie dane ze wszystkich tabel użytkowników.
  • db_ddladmin -> może uruchomić dowolną komendę DDL (Data Definition Language) w bazie danych.
  • db_datawriter -> może dodawać, usuwać lub zmieniać dane we wszystkich tabelach użytkowników.

Role bazodanowe ⌘


  • db_denydatareader -> nie może odczytać żadnych danych w tabelach użytkowników w bazie danych.
  • db_denydatawriter -> nie może dodawać, modyfikować ani usuwać żadnych danych w tabelach użytkowników w bazie danych.
  • db_owner -> może wykonywać wszystkie czynności związane z konfiguracją i konserwacją bazy danych, a także usuwać bazę danych
  • db_securityadmin -> może modyfikować członkostwo w rolach i zarządzać uprawnieniami
  • public -> użytkownik dziedziczy uprawnienia przyznane publicznie na danym obiekcie.
    Użytkownicy bazy danych nie mogą zostać usunięci z roli publicznej

Schematy ⌘


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

*security - ćwiczenie

Monitorowanie aktywności SQL Server ⌘

  • Activity Monitor
  • DMVs
  • Performance counters + PAL
  • Extended events
  • Performance Dashboard Reports
  • Data Collector

http://www.sqlcoffee.com/SQLServer2008_0009.htm

  • SQL Server Profiler

Upgrade ⌘


  • 'InPlace' vs 'Side by side' ( 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⌘


  • www.youtube.com
  • www.sqlservercentral.com
  • www.udemy.com


Ankieta i Certyfikaty⌘