Was man als .Net Entwickler von SQL Server wissen muss

1
Was man als .Net Entwickler von
SQL Server wissen muss
Dieter Rüetschi ([email protected])
2
Stored Procedure (SP)
3
Was ist eine Stored Procedure
 SQL Statement
 Wird als String vom Client zum DBMS
übertragen
 Interpretiert
 Ausgeführt
 Stored Procedure
 «kompilierte Einheit»
 Funktionale Schnittstelle
 Kann Implementationslogik enthalten
 Wird auf dem DBMS optimiert
 Schneller
 Zusätzliche Abstraktion
 Definierte Schnittstelle
 Security
 Etwas komplexer zum Erstellen (mit
TSQL)
4
SP Grundlagen
 Die Erstellung
 Die Ausführung
5
Transaktionen
6
Die ACID Regel
 Atomic
 Consistent
 Independent
 Durable
7
Transaktionen
 Mehrere Operationen in eine Transaktion:
 Transaktionsinstanz kreieren mittels BeginTransaction Methode der Connection Klasse.
Verbindung muss offen sein.
 Transaktionsinstanz den Transaction Properties der betroffenen Commands übergeben.
 Commands ausführen.
 Aufrufen von Commit oder Rollback Methode der Transaktionsinstanz.
 Wichtig:
 Auf jeden Fall Commit oder Rollback aufrufen (try - catch benutzen).
 Transaktion möglichst kurz halten. Commands vorbereiten.
8
Codebeispiel einer Transaktion
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open(); // Connection öffnen
// Transaktion starten
using (SqlTransaction transaction = conn.BeginTransaction())
{
try
{
// Transaktion zuweisen
categUpdateCommand.Transaction = transaction;
// Commands ausführen
categUpdateCommand.ExecuteNonQuery();
transaction.Commit(); // wenn bis hierher gekommen: Erfolg
}
catch (Exception ex) // irgend ein Fehler ist passiert
{
transaction.Rollback();
}
}
}
9
Verteilte Transaktionen
using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection cn = new SqlConnection(connectionString1))
{
DbCommand cmd = cn.CreateCommand();
cmd.CommandText = "Insert into Monkey(MonkeyName) Values ('Bill Ryan')";
cn.Open();
cmd.ExecuteNonQuery();
}
using (SqlConnection cn = new SqlConnection(connectionString2))
{
DbCommand cmd = cn.CreateCommand();
cmd.CommandText = "Insert into Monkey(MonkeyName) Values ('Frans Bouma')";
cn.Open();
cmd.ExecuteNonQuery();
}
ts.Complete();
}
10
Verteilte Transaktionen auf logischer Ebene
 DTC: Distributed Transaction Coordinator
11
Entity Framework
12
Aufbau Entity Data Model
 Logisches Modell (Storage Model) SSDL
 Konzeptionelles Modell (Conceptual Model) CSDL
 Zuordnungsschicht (Mapping) MSL
 Physikalisches Modell (Datenbank Tabellen, Sichten und
Abfragen)
13
Entity Framework Komponenten
 O/R Mapper für sauberes Businessmodell (weniger normalisiert)
14
Vorgehensweisen
Neue
Datenbank
Bestehende
Datenbank
Model First (EF 4.0 , .NET 4)
• Datenmodell im Designer
erstellen
• Datenbank erzeugen
• Klassen werden aus dem
Modell automatisch erstellt
Code First (EF 4.1)
• Klassen und
Mapping im Code
erzeugen
• Datenbank aus
Model erzeugt
• Migration (EF 4.3) ,
um Dateenbank zu
erweitern
Database First (EF 1.0, .NET 3.5
SP1)
• Erzeuge Modell aus
Datenbank (Reverse
Engineer)
• Klassen werden aus dem
Modell automatisch erstellt
Code First
• Klassen und
Mapping im Code
erzeugen
15
1. Abfrage über LINQ
using (NorthwindEntities context = new NorthwindEntities())
{
var customerWithOrders = from c in context.Customers
where c.Orders.Count > 0
select c;
foreach (Customer cust in customerWithOrders )
{
lbResult.Items.Add(cust.CompanyName);
}
}
C#
Visual Basic .NET
16
Database Tooling
 Dieter Rüetschi ([email protected])
17
Inhalt
 SQL Server Management Studio
 Visual Studio / Data Tools
 Tipps und Tricks
 ALM in Applikationen (FTS)
18
SQL Server Management Studio (SMS)
 Vorwiegend für administrative
Tasks (z.B. Database
Recovery)
 Für Datenanalyse und Ad-Hoc
Abfragen
 Konfiguration von Instanz,
Datenbanken und DB Objekten
 Security Verwaltung
 usw.
19
Database Projekt im Visual Studio
 Erstellung von Datenbanken
 Erstellung von Abfrage Scripts
20
VS Server Explorer
21
Table Data
 Refresh
 Filtern
 Neue Rows einfügen
 Anzahl Rows
 Scripting
 Executing
 Darstellung
 SQLCmd
22
Table Definition
 Komplette Informationen
 Vollständiges Scrip
 Gute Übersicht
23
Server Object Explorer
 Zeigt zusätzlich Instanzobjekte
an
 Ermöglicht Veränderungen
24
Datenbank Administration
 Dieter Rüetschi ([email protected])
25
SQL Server und Services
 Dauernd kommen neue Services
hinzu
 Nur das installieren was gebraucht
wird
 Performance
 Security
26
SQL Server und Services
27
SQL Server Editionen
 https://msdn.microsoft.com/en-us/library/cc645993(v=sql.130).aspx
28
SQL Server Instanzen
 Die meisten Services (auch das DBMS) sind mehr-Instanzfähig
 Instanzen bieten Isolation für
 Die Administration
 Performance
 SLAs
 Versionen Collations
 Security
 Die zwei Instanztypen
 Default Instanz (nur für Entwicklungsumgebung empfohlen)
 Named Instanz
29
Datenbank und Storage
30
Die Datenbankfiles
 Datenfiles
 1 Primäre Datei
 0-n Sekundäre Dateien
 Transaktions-Logfile
31
Filegruppen
32
Buffer Erweiterung
 Der Speichercache wird durch SSD Platten erweitert
 Verbessert die Lesegeschwindigkeit
 Keine Verbesserung beim Schreiben
33
Performance und Security
 Dieter Rüetschi ([email protected])
34
Vorgehen
 Entwicklungsphase
 Design und Implementaion logisch korrekt
 Testen der Performance (z.B. Im Rahmen eines Feldtests)
 Architektur analysieren
 Zielvorgabe beachten oder bestimmen (was ist schnell?)
 Messungen vornehmen (z.B. mit Performance Monitor)
 Selektion und Priorisierung der Bottlenecks
 Gezieltes Lösen bis Ziel erreicht ist
35
Architektur
36
Analyse
37
Query Store
 Speichert
 Query Text
 Query Plans / Choices
 Laufzeitstatistik vorallem
performance Metriken
 Wird für gezielte Performance
Opmitierungen eingesetzt
38
Ergebnisse der Auswertungen
39
Auswertungen zur Laufzeit
 Mit Live Drill Down
 Query Analyse im Detail zur Laufzeit
40
Indexing
41
Wie SQL Server auf die Daten zugreift
42
Eine Index Struktur
43
Indexarten
 Clustered Index
 Row basiert
 Die Daten werden physikalisch entsprechend dem Index abgegelegt und verwaltet
 Es kann nur einen Clustered Index pro Tabelle geben
 Nonclustered Index
 Row basier
 Im Index werden Refernzen auf die tatsächlichen Daten organisiert
 Mehrere pro Tabelle möglich
 Columne Index
 Kolonnen basiert
 Als Clustered und Nonclustered Index
44
In Memory Techniken
45
Security
46
Die Neuerungen im Bereich Security
47
Always encrypted