MS SQL Server Administration

From Training Material
Jump to navigation Jump to search
title
MS SQL Server Administration


author
Jarosław Sołoducha (NobleProg)

Basic informations about Databases⌘


  • File databases np. MS Access
  • Client/Server databases
  • Relational Database Management Systems:
  1. DB2
  2. Microsoft SQL Server
  3. MySQL
  4. Oracle
  5. PostgreSQL


SQL Server versions and differences between them⌘


  • Enterprise
  • Standard
  • Workgroup
  • Mobile
  • Express
  • Developer

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:

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

Analysis Services ⌘

Commonly known as „Data Warehouse"

For what it is being used:

  1. The processing of analytical data
  2. Decision support
  3. Centralization of data
  4. Archiving


Reporting Services ⌘


  1. Creatation
  2. Management
  3. Presenting reports
  4. 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

<br /

  • Separate fast hard drive
  • Create as many files as there are processors (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 ⌘


Z ang."Recovery Model"

  • Simple
  • Full
  • Bulk logged


Modele odtwarzania ⌘

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⌘

Retrieved from ‘https://training-course-material.com/index.php?title=MS_SQL_Server_Administration&oldid=28117’