LAB OF SOFTWAREARCHITECTURES
AND INFORMATION SYSTEMS
FACULTY OF INFORMATICS
MASARYK UNIVERSITY
PV226/MSSQL
Úvodníinformacek předmětu
Mgr. David Gešvindr
MVP | MCT | MSP | MCSE | MCSD
Materiálykekurzu
• Budou průběžně dostupné v IS ve studijních
materiálech
• Prezentace
• Zadání úkolů
• Doplňující informace
• SQL Server Books Online
• https://technet.microsoft.com/en-
us/library/ms130214(v=sql.130).aspx
Průběhvýuky
• 5 cvičení po 4 hodinách
29.2., 14.3., 11.4., 25.4. a 9.5.
• Teoretická i praktická část
• Vše co bude probráno si vyzkoušíte ve virtuálním prostředí
• Aktivní zapojení studentů do případné diskuze k dané
problematice
Hodnocení/ udělenízápočtu
• Podmínky zápočtu:
• Aktivní účast na cvičeních
• Povolená neomluvená absence: 4 hodiny = 1 seminář
• Administrátor (1. a 2. cvičení)
• Kapitola 1: Instalace a konfigurace SQL Serveru
• Kapitola 2: Správa databází
• Kapitola 3: Zálohování a obnova po havárii
• Kapitola 4: Bezpečnostní model
• Vývojář (3. – 5. cvičení)
• Kapitola 5: Návrh a tvorba databáze
• Kapitola 6: Tvorba pokročilýchT-SQL dotazů
• Kapitola 7: XML, JSON a geografická data v databázi
• Kapitola 8: Zámky, transakce a izolační úrovně
• Kapitola 9: Index Internals aneb jak optimalizovat
výkon dotazů prakticky
Osnovakurzu
Certifikace
• Microsoft Certified Solutions Associate: SQL Server
2012
• Exam 70-461: Querying Microsoft SQL
Server 2012
• Exam 70-462: Administering Microsoft SQL Server 2012
Databases
• Exam 70-463: Implementing a DataWarehouse with Microsoft
SQL Server 2012
• Microsoft Certified Solutions Expert: Data Platform
• Exam 70-464: Developing Microsoft SQL Server 2012
Databases
• Exam 70-465: Designing Database Solutions for
Microsoft SQL Server 2012
LAB OF SOFTWAREARCHITECTURES
AND INFORMATION SYSTEMS
FACULTY OF INFORMATICS
MASARYK UNIVERSITY
1.InstalaceakonfiguraceMSSQL
ArchitekturaSQLServeru
• Nejedná se jen o relační databázový server
• Kompletní sada nástrojů pro správu a analýzu
podnikových dat
• Vysoký výkon
• Vysoká dostupnost
• Zabezpečení dat
• Škálovatelné řešení
• Vysoce integrovaná platforma
KomponentySQLServeru
SQL Server Components
Database Engine Analysis Services
Integration Services Reporting Services
Master Data Services StreamInsight
Data Mining Full-Text Search
PowerPivot Replication
Data Quality Services Advanced Analytics Extensions („SQL
Server R Services“
EdiceSQLServeru2012-2014
• Hlavní edice
• Standard
• Business Intelligence
• Enterprise
• Speciální edice
• Express
• Developer
• Web
• Pro rozhodování: https://technet.microsoft.com/en-
us/library/ms144275(v=sql.120).aspx
LicencováníSQLServeru
• „Procesorová“ licence
• Pro každé jádro procesoru
• Serverová licence + Device CAL
• Na server a pro každé zařízení s ním komunikující
• Serverová licence + User CAL
• Na server a pro každého uživatele s ním komunikujícího
• Pozor na multiplexing
InstanceSQLServeru
• Výchozí instance (Default Instance)
• Identifikována jménem počítače na kterém běží
• Statický portTCP 1433
• mssql.fi.muni.cz
• Pojmenovaná instance (Named Instance)
• Identifikována jménem počítače a navíc i jménem instance
• Dynamický port
• mssql.fi.muni.cz\web
• mssql.fi.muni.cz\studenti
• Browser Service
• Poskytuje informace klientům, na kterém portu běží
• Naslouchá na portu UDP 1434
Bezpečnostníopatření
• Fyzické zabezpečení serveru
• Použití firewallů
• Izolace služeb
• Virtualizace jednotlivých serverů
• Servisní účty mají minimální oprávnění
• Domain User
ZnakovésadyvrámciMSSQL
• Windows Collations
• SQL Server Collations
• Neobsahují unicode, má oddělený datový typ
• Case-sensitive (CS) / Case-insensitive (CI)
• Accent-sensitive (AS) / Accent-insensitive (AI)
• Kana-sensitive (KS) / Kana-insensitive (KI)
• Width-sensitive (WS) /Width-insensitive (WI)
http://technet.microsoft.com/en-us/library/ms143726(v=sql.110).aspx
Komunikaces klienty
CLIENT SERVER
SNAC
SQL Native Access Layer
OLE DB ODBC
Network Libraries
Endpoints
SQLOS
Relational Engine
Storage Engine
Průběhinstalace
1. Kontrola systému
2. Výběr komponent
3. Konfigurace vybraných komponent
4. Konfigurace po dokončení instalace
• Bezobslužná instalace
• Možnost načíst .INI soubor s konfigurací instalace
• Tento soubor umí vygenerovat průvodce instalací
• https://msdn.microsoft.com/en-
us/library/ms144259(v=sql.120).aspx
Nastavenípo instalaci
• SQL Server Configuration Manager
• Nastavení služeb (změny účtů, automatický start)
• Nastavení komunikačních protokolů
• Nastavení SQL Native Client
• SQL Server Management Studio
• Nastroj pro konfiguraci „všeho ostatního“
• Nástroj pro vývojáře
Dalšínástroje
• SQL Server Profiler
• Zachycení zátěže odeslané klienty na SQL Server
• Database EngineTuning Advisor
• Generuje doporučení pro optimalizaci DB na základě
předané zátěže v podobě SQL dotazů
• sqlcmd
• Utilita příkazové řádky
• S pouštění adhoc SQL dotazů a dávek příkazů
Úkol1:Přípravabezobslužnéinstalace
1. Za pomocí SQL Server instalátoru vygenerujte
konfigurační .INI soubor pro bezobslužno instalaci,
která instaluje SQL Server v následující podobě
• Nová instance s názvem WEB
• Povolit Windows ověřování
• Instalovat pouze komponenty SQL Server Database
Engine + management nástroje
• U zbytku voleb použít zdravý rozum, v případě nejasností
se ptát
Úkol2:Spuštěníbezobslužnéinstalace
1. S pomocí návodu na:
http://msdn.microsoft.com/enus/library/ms144259(v=sql.110).aspx
proveďte
bezobslužnou instalaci za pomoci vygenerovaného
.INI souboru.
Úkol3:Konfiguraceinstalace
1. Přes SQL Server Configuration manager zařiďte,
aby služba SQL Agent startovala automaticky
2. Na serveru povolte protokoly Shared Memory a
TCP/IP
3. Zjistěte na kterémTCP portu SQL Server
komunikuje
• Vyzkoušejte zastavit službu SQL Browser, jestli se při
vynucení protokoluTCP připojíte k instanci WEB
• Zkuste nastavit instanci WEB statickýTCP port 55000
LAB OF SOFTWAREARCHITECTURES
AND INFORMATION SYSTEMS
FACULTY OF INFORMATICS
MASARYK UNIVERSITY
2.Správadatabází
Jakjsoudatauložena?
• V datových souborech jsou data uložena ve stránkách -
Page
• Stránka je nejmenší množství paměti, které lze alokovat
• Její velikost je 8 KB
Jakjsoudatauložena?
• Volné místo se alokuje po větších blocích – Extent
• Jejich velikost je 64 KB
• Rozdělujeme je na mixed a uniform
Typydatabázovýchsouborů
Typ souboru Koncovka Popis
SQL Server
Database Primary
File
.mdf 1x
Obsahují informace potřebné pro start
databáze, datové stránky a odkazy na
další soubory
SQL Server
Database
Secondary File
.ndf 0..n
Mohou být použity pro rozdělení
databáze do víc souborů na jiné disky
kvůli výkonu (s využitím File Groups)
SQL Server
Transaction Log
File
.ldf 1x
Obsahují informace potřebné pro
obnovení databáze po havárii
Jakpracujetransakčnílog?
1. Změny jsou odeslány z aplikace na server
2. Datové stránky potřebné pro danou operaci jsou
načteny do vyrovnávací paměti
3. Informace o změnách a transakci jsou zapsány do
transakčního logu na disk
4. Je provedena daná transakce
5. Po checkpointu se změněné datové stránky zapíší
z vyrovnávací paměti na disk
Systémovédatabáze
System Database Popis
master Uchovává konfiguraci instance SQL Serveru
msdb
Konfigurace SQL Server Agenta, historie
záloh a obnov databáze
model Šablona nové databáze
tempdb
Ukládá dočasné tabulky, tabulky v
proměnných, hashovací tabulky, verze řádků
resource
Skrytá databáze, která uchovává systémové
objekty, které jsou mapovány do ostatních
databází
Doporučeníproumístěnísouborů
• Datové soubory
• Oddělit často používané tabulky na jiné disky
• tempdb
• Používáte-li dočasnou databázi tempdb je vhodné pro
zvýšení výkonu ji oddělit na jiný disk
• Transakční logy
• Je vhodné je umístit na jiný disk nebo RAID
Vytvořenídatabáze
• Uživatelská databáze vznikne okopírováním databáze
model
• Při vytváření databáze se definuje několik parametrů,
většinu lze změnit po vytvoření
Změnyvelikostídatovýchsouborů
• Databáze roste podle svého nastavení
• Můžeme databázi manuálně rozšířit
• Parametrem MAXSIZE můžeme omezit maximální
velikost databáze
• Transakční log může být oseknut
• Pokud databázi dojde volné místo, vrátí chybový stav
„Error 1105“
Zmenšovánísouborů
• Každý soubor, který je součástí databáze může být
zmenšen odstraněním volného místa
• Nemůže dojít k odstranění dat kvůli operaci DBCC
SHRINKFILE
Odpojeníapřipojenídatabáze
• Operace Detach odpojí databázi z instance
• Databázové soubory zůstanou na disku nedotčené
• Operace Attach znovu načte databázi do instance
• Je třeba předat cesty na všechny soubory, které tvoří
databázi
• Těchto operací se využívá k migraci databází mezi
instancemi nebo mezi servery
Úkol1:Vytvořenídatabáze
1. Vytvořte databázi „Eshop“ v instanci web
• Full Recovery režim
• Nejvyšší Compatibility level
2. Tuto databázi odpojte z SQL Serveru
3. Přesuňte její soubory do složky s databázemi
výchozí instance (MSSQL)
4. Znovu databázi připojte do výchozí instance jako
databázi Eshop
Úkol2:Prácesdatovýmisoubory
1. V reportu Disk Usage ověřte velikost datových
souborů databáze a jejich využité místo
2. Ručně expandujte datový soubor na 10 MB
3. Přidejte druhý datový soubor do této databáze
4. Okopírujte tabulky Sales.SalesOrderHeader a
Sales.SalesOrderDetail z databáze
AdventureWorks (SELECT INTO)
5. Zkontrolujte jak se obsadilo místo v datových
souborech
6. Odeberte sekundární datový soubor
Filegroups
• Filegroup je mechanizmus, který nám umožňuje
určit, kde je uložená konkrétní tabulka
Other
Filegroups
Primary
Filegroup
Database
Data File(s)
*.mdf , *.ndf
Log File(s)
*.ldf
Data File(s)
*.ndf
Úkol3:Práces Filegroups
1. V databázi Eshop vytvořte filegroup Archive
2. Do této filegroup přesuňte tabulku
SalesOrderDetail
3. Zkontrolujte dopad na obsazení souborů, že se
skutečně tabulka uložila do jiných datových
souborů