Administracja baza danych Microsoft SQL Server
Jump to navigation
Jump to search
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) :
- DB2
- Microsoft SQL Server
- MySQL
- Oracle
- PostgreSQL
Edycje SQL Serwer⌘
- Enterprise
- Standard
- Web
- Express
- Developer
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:
- Data files
- Replication
- Full-Text search
Analysis Services ⌘
Inaczej "Hurtownie Danych"
Zastosowanie:
- Przetwarzanie analityczne danych
- Wspomaganie decyzji
- Centralizacja danych
- Archiwizacja
Reporting Services ⌘
Zastosowanie:
- Tworzenie
- Zarządzanie
- Przedstawianie raportów
- 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 ⌘
- "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.
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 ⌘
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 ⌘
Odtwarzanie Bazy Danych ⌘
"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
