Jaroslaw Soloducha at 09:18, 5 October 2015

2015-10-05T09:18:23Z

New page

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

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

* File databases np. MS Access
* Client/Server databases
* Relational Database Management Systems:
# DB2
# Microsoft SQL Server
# MySQL
# Oracle
# PostgreSQL

==SQL Server versions and differences between them⌘==
 

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

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

==SQL Server Tools⌘==
 

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

==Services in SQL Server 2012 ⌘==
* Database Services
* Analysis Services
* Reporting Services
* Intergration Services

=== Database Services ⌘===

As a part of SQL Server Database Services are:
# Data files
# Replication
# Full-Text search

=== Analysis Services ⌘===

Commonly known as „Data Warehouse"

For what it is being used:
# The processing of analytical data
# Decision support
# Centralization of data
# Archiving

=== Reporting Services ⌘===
 

# Creatation
# Management
# Presenting reports
# Report Manager

=== Intergration Services ⌘===
 

• The set of tools to manage collections in ETL
ETL - Extract, Transform , Load
• Performing other tasks

== Service Accounts for SQL Server ⌘==

http://technet.microsoft.com/en-us/library/cc281953%28v=sql.120%29.aspx
 
 
* '''"NT AUTHORITY\LOCAL SERVICE"'''
:The same privilege level as members of the Users group
:Local service account is not intended for SQL Server and SQL Server Agent

 
* '''"NT AUTHORITY\NETWORK SERVICE"'''
:Built-in service
:It has greater access to resources and objects

== Service Accounts for SQL Server II ⌘==
 
* '''"NT AUTHORITY\SYSTEM"'''
:Very high level of authority in the local system
 
* '''"Domain Account"'''
:cooperation with network services
:access to domain resources
:file-sharing
:connect to other SQL Servers

== Network protocols in SQL Server ⌘==
 
* Microsoft SQL Server can handle requests from several protocols at the same time.
* Configuration of protocols -> SQL Server Configuration Manager

 
'''Protocol types:'''
* Shared Memory
* TCP/IP
* Named Pipes
* VIA

== System Databases ⌘==
 
* master
* model
* msdb
* tempdb
 

* distribution*
* resource*
=== Master ⌘===

* The most important database
* SQL Server configuration is stored in this BD - information on:
:system objects
:logins
:location of individual files
:attached user databases

=== Model ⌘===

The "model database" for other databases

=== Msdb ⌘===

* A database used by SQL Server Agent
* Contains information on:
:jobs
:backups

=== Tempdb ⌘===
 

* Temporary Database
* It is created from a scratch after every reboot of SQL Service
* Contains temporary objects:
:tables
:cursors
:various objects that are created internally by SQL
 (not more than 8 files)

=== Distribution* ⌘===
 

* Created if SQL is using Replication
* Contains metadata and historical data relating to replication

=== Resource* ⌘===
 

* A database in read-only mode
* Contains system objects
* Easier to upgrade SQL Server
* No backup

==Creating databases==
 
=== Database files and their location⌘===
 

* .mdf
* .ndf
* .ldf
* FileGroup
* • Physical and logical database files

=== Size of DB files and their growth ⌘===
 

* Initial Size
* Model databaze
* autogrowth

==Data Manipulation Language (DML)⌘==
 

* SELECT
* INSERT
* UPDATE
* DELETE

*exercise

==Data Definition Language (DDL)⌘==
 

* CREATE
* ALTER
* DROP

*exercise

==Table design⌘==
 

: Text (char, varchar, nchar, ntext, nvarchar)
: Numbers (int, smallint, bigint, tinyint, float, real, decimal, numeric)
: Date and Time (datetime, smalldatetime)
: Binary (binary, varbinary)
: Currency (money, smallmoney )
: Special (text, image, xml, bit)
* Changing properties of table columns
* Integrity

*exercise

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

=== Indexes ⌘===

Indexes
:* Clustered
:* Non-Clustered

:* Primary Key
:* Foreign Key

* For index effectiveness the most important are the following:
:* high selectivity
:* up to date statistics
:* low levels of fragmentation

*Video

=== Indexes ⌘===
 Primary Key, 
: Each row in our table must have a value that uniquely identifies it.
: To qualify the attribute as the primary key, we need to check whether it has the following properties:
* must have a value for each line
* For each of these rows value must be unique
* This value can not change, nor can it be removed during the entire operation of the row in the table

 
=== Indexes ⌘===
 Foreign Key, 
:A foreign key is a copy of the primary key from another table.
:The association is formed between tables by checking that value of one table,
:where foreign key is related to the value of another table where the primary key resides.

=== Clustered Indexes ⌘===
 

* One clustered index for table
* Most often created on a column with high "selectivity"
* Primary key - clustered index

=== Non-Clustered Indexes ⌘===
 
* Up to 249 non-clustered indexes for a table
* Can be created upon Clustered Indexes

=== REBUILD vs. REORGANIZE ⌘===
 

* REBUILD removes and creates indexes from scratch,
* Reorganize only changes the structure of pages, do not remove them.
* (AVG_FRAGMENTATION_IN_PERCENT columns in view sys.dm_db_index_physical_stat)
 

Fragmentation: 
* 0 - 10% - nothing
* 11 - 30% - REORGANIZE
* 31 - 100% - REBUILD

=== REBUILD vs. REORGANIZE- script ⌘===
 

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 ⌘==
 
Tworzenie, usuwanie lub zmiana uprawnień ról serwera sa niemożliwe*
 
 

* bulkadmin
* dbcreator
* diskadmin
* processadmin
* public
* securityadmin
* serveradmin
* setupadmin
* sysadmin
 
*SQL SERVER 2012

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

MS SQL Server Administration - Revision history

Jaroslaw Soloducha at 09:18, 5 October 2015