Datenbankprogrammierung

1. Datenbankprogrammierung Best Practices Jörg Neumann, Resco GmbH Marcel Gnoth, Avanade Deutschland GmbH

2. Marcel Gnoth • Dipl. Inf. Marcel Gnoth, MCSD • marcel@gnoth.net , www.gnoth.net (Code+Folien) • Senior Consultant, Avanade Deutschland GmbH • Office Business Application Group • Autor • dotnetpro, dotnet-magazin, Developerworld, MSDN • Sprecher • BASTA!, TechTalk, SQL Server Days • Themen • Verteilte nachrichtenbasierte Informationssysteme • MS Message Queueing, SQL Server Service Broker • Office System 2003 Integration • Workshop gleich im Anschluss • VB6, COM, SQL Server, .Net

3. Szenario vorstellen • Verarbeitung von Worddokumenten auf dem Server • Große Firma, zu Stoßzeiten treffen viele Dokumente ein • Bearbeitung kostet Zeit • Abhängig von der Last, werden : • Dokumente entgegengenommen • Oder gleich bearbeitet • Dokument Daten werden gespeichert: • Als XML Dokument • Als Daten in Tabellen

4. Szenario Bild

5. Transaktionen in .Net

6. Überblick • ADO.Net 1.1 • SQLConnection, SQLTransaction • Transaktionen mit mehr als einem Server • Distributed Transaction Coordinator (DTC) • Enterprise Services • ADO.Net 2.0 • TransactionScope • Committable Transaction • Serverseitige Transaktionen • Linked Server

7. Transaktionen mit ADO.Net 1.1 • SQLConnection, OLEDBConnection • BeginTransaction • SQLTransaction, OLEDBTransaction • Commit, Rollback cnScotty = new SqlConnection("data source = .....); cmdScotty = new SqlCommand(query,cnScotty); cnScotty.Open(); Txn = cnScotty.BeginTransaction(ReadCommitted); cmdScotty.Transaction = Txn; cmdScotty.ExecuteNonQuery(); Txn.Commit();

8. Transaktionen mit ADO.NET • Tx – Anweisungen bedeuten Roundtrip zum Server (Begin, Commit, Rollback) • keine eigene Unterstützung von verteilten Transaktionen • Transaktionen mit zwei SQL Servern • andere Mechanismen benutzen Client Server Accounts Local Transaction ADO.NET Account A Account B OLE DB SQL TDS

9. Verteilte Transaktionen • Über Transaktionen wird auf Ressourcen zugegriffen • Ressourcen Manager steuern transaktionalen Zugriff • Mehrere Ressourcen Manager müssen koordiniert werden Transaktion Server Sparkasse Server Hypobank Konten Konten Konto A + 10 Konto C – 10 SQL Server MSMQ Oracle Konto B Konto D

10. Win Client Coordinating DTC Server A Server B Participating DTC Participating DTC Ressource Manager Ressource Manager Ressource Manager Distributed Transaction Coordinator • Koordinierender DTC (Win-Client) • Partizipierenden DTC • jeder DTC steuert die Transaktionen der von ihm verwalteten Ressourcen Manager • Zwei Phasen Commit Protokoll • Windows Dienst • Nötiger Overhead • Mit ADO.Net 2.0 • Lightweight TransactionManager

11. Enterprise Services • Bibliothek für DTC und COM+ Funktionen • COM+ Komponenten nehmen an Tx teil • Wenn COM+ Komponenten auf RM zugreifen, nehmen diese an Tx teil • Dll Projekte • regsvcs.exe TXComponentDTC.dll • Assembly als COM Komponente registrieren • Eintragen in COM+ Katalog • durch Reflection wird Assembly auf Attribute untersucht und der Eintrag im COM+ Katalog aktualisiert [Transaction(TransactionOption.Required)] Class MyTxClass : ServicedComponent {…}

12. Beobachten des Transaktionsverhalten • Start -> Verwaltung -> Komponentendienste

13. Transaktionen mit ADO 2.0

14. Implizite Tx mit TransactionScope • Erstellen einer umgebenden (ambient) Tx • Using-Block • Alle DB Anweisungen innerhalb des Blocks sind Teil der Tx • Auch innerhalb von Methodenaufrufen • Wenn alles ok, dann nur SetComplete aufrufen • Bei EndUsing wird Dispose aufgerufen • Dispose prüft ob SetComplete aufgerufen wurde • Dispose führt Commit oder Rollback durch

15. Implizite verteilte Tx • Tx kann mehrere RM umfassen • (z.B.: 2x DB und 1x MSMQ) • Erst LTM, kein DTC • Wird automatisch zu DTC Tx hochgestuft wenn • Ein durable RM der kein Single Phase Commit unterstützt • Alle außer SQL 2005 zur Zeit • Mehr als ein durable RM in der Tx • Tx wird an andere AppDomain oder Prozess übergeben (serialisiert)

16. TransactionScope Beispiel Using scope As TransactionScope = _ New TransactionScope(TransactionScopeOption.Required) Using myCon As New SqlConnection("server=chihiro;… ;") Dim myCommand As New SqlCommand() myCon1.Open() myCommand.Connection = myCon1 myCommand.CommandText = "Insert into … VALUES …" myCommand.ExecuteNonQuery() myCon1.Close() End Using scope.Complete() End Using

17. Explizite Transaktionen • CommitableTransaction Klasse • Manuelle Steuerung der Tx • Entwickler muss kann / muss sich um Details kümmern • Connection wird in Tx aufgenommen (enlist)

18. Explizite Tx – Beispiel Dim txExplicit As CommittableTransaction txExplicit = New CommittableTransaction Dim myConnection As New SqlConnection("server=...") Dim myCommand As New SqlCommand() myConnection.Open() myConnection.EnlistTransaction(txExplicit) myCommand.Connection = myConnection myCommand.CommandText = "Insert into Reisen ..." myCommand.ExecuteNonQuery() myCommand.CommandText = "Insert into Reisen ..." myCommand.ExecuteNonQuery() If chkTxExplicit.Checked Then txExplicit.Commit() Else txExplicit.Rollback() End If myConnection.Close() txExplicit = Nothing

19. Demo DTC TransactionScope CommitableTransaction

20. Transaktionen zwischen mehreren Servern • SQL-Server ist ein Ressourcenmanager • verwaltet seine Daten über Transaktionen • SQL-Server kann mit Transaktions-Managern zusammenarbeiten • X/Open XA-Spezifikation unterstützen • Oder OLE Tx wie DTC

21. Transaktionen mit TSQL • Fremden Server als Linked Server einbinden • BEGIN DISTRIBUTED TRANSACTION • BEGIN TRANSACTION • OLE DB-Datenquelle ITransactionJoin-Schnittstelle • TX wird von lokal zur verteilten TX heraufgestuft EXEC sp_addlinkedserver @server='TXTest', provider='SQLOLEDB', @datasrc='SPOCK' EXEC sp_addlinkedsrvlogin 'TXTest', 'false', NULL, 'sa', ''

22. XACT_ABORT • automatisches Rollback bei Laufzeitfehler • SET XACT_ABORT ON • tritt in einer TX ein Laufzeitfehler auf, wird Rollback für die gesamte TX durchgeführt • SET XACT_ABORT OFF • nur die Anweisung, die den Laufzeitfehler auslöst wird zurückgesetzt, die Tx läuft weiter • werden keine verschachtelten Tx verwendet, dann: SET XACT_ABORT ON setzen

23. Code Set XACT_ABORT On BEGIN DISTRIBUTED TRANSACTION INSERT INTO [pubs].[dbo].[authors] ([au_id], [au_lname], [au_fname], [phone]) VALUES ('111-22-3333', 'Duck', 'Donald', '123456') INSERT INTO [TXTest].[pubs].[dbo].[authors] ([au_id], [au_lname], [au_fname], [phone]) VALUES ('111-22-3333', 'Duck', 'Donald', '123456') COMMIT

24. Demo Transaktionen mit T-SQL

26. SQL Server Service Broker

27. Service Broker • Neu in SQL2005 • Ermöglicht asynchrones Messaging innerhalb des SQL-Servers • Warteschlangen sindversteckte Tabellen • Können mit SQL verwendet werden CREATE QUEUE MittwochMessages Select message_body FROM MittwochMessages

28. Einsatzszenarien • Entkoppeln von Transaktionen • Verteilung der Arbeit auf mehrere SQL Server • Garantierte Nachrichtenzustellung • Entlastung bei Spitzenlast • Batch-Verarbeitung • Automatische Aktivierung von SP

29. SSB Security • Lokale Kommunikation einfach • Kommunikation zwischen Servern • Security basiert auf Zertifikaten • Transport Security • Dialog Security • Endpoints für SSB • Routen für SSB

30. Die Kommunikation • Nicht zwischen zwei Queues, Datenbanken oder Servern • Zwischen Diensten (Services) • Abstrahieren Infrastruktur • Änderungen an der Infrastruktur können vorgenommen werden, ohne Clients zu beeinträchtigen • SOA 

31. Message Type • Alle Nachrichten müssen einen definierten Typ haben • Typ ist ein Label für den Empfänger • Kann mit XML Schema verknüpft werden • Nachrichten, die nicht dem Schema entsprechen werden beim Empfang abgewiesen create message type [mtAskKyoto] validation = well_formed_xml create message type [mtResponseTokyo] validation = well_formed_xml

32. Contract • Menge von Message Types • Definieren wer welche Nachrichten senden darf create contract [ctrOrderTanuki]([mtAskKyoto] sentby initiator,[mtResponseTokyo] sentby target)

33. Queue • Queue erstellen • Status • Aktiv / Inaktiv • Activation • Eintreffende Nachrichten werden automatisch einer Stored Procedure Instanz übergeben und verarbeitet • Mehrere Instanzen möglich create queue [quKyoto] with status = on

34. Lokalen Service anlegen • Ordnet Contract einer Queue zu • Dialoge verwenden Serivces als Endpunkte • Beim Deployment können den Services physische Queues zugeordnet werden create service [svcKyotoTanukiRequest] on queue [quKyoto]( [ctrOrderTanuki] ) grant send on service::[svcKyotoTanukiRequest]to remcert

36. Demo Nachrichteninfrastruktur anlegen

37. Nachrichten senden

38. Conversations • Kommunikation zwischen zwei Services erfolgt in Conversations • Beliebig viele Nachrichten können zu einer Conversation gehören • Feste Reihenfolge • Mehrere Conversations können zu logischen Gruppen zusammengefasst werden • Aktuell nur ein Typ: Dialog

39. Begin Conversation • FromService muss lokal existieren • ToService wird als Text übergeben • Wird über eine Route lokalisiert • Lifetime in sekunden • Encryption steuert Dialog Security begin dialog conversation @hfrom service [svcTokyoTanukiResponse]to service 'svcKyotoTanukiRequest‚on contract [ctrOrderTanuki]WITH LIFETIME=1, ENCRYPTION=On;

40. Senden • Conversation Handle, Nachrichtentyp, Nachricht • Nachrichten landen erst in Transmission Queue • Interne Ack-Messages send on conversation @hmessage type [mtAskKyoto](@msg) select * from sys.transmission_queue

41. Empfangen • Select • Receive • Body ist varbinary -> casten select cast(message_body as XML), * From quKyoto Receive cast(message_body as nvarchar(MAX)),* From quKyoto

42. End Conversation • Eine Seite möchte Kommunikation beenden • WITH CleanUp • Löscht alle Überreste der Conversation • WITH ERROR = failure_code (>0) • DESCRIPTION = failure_text • Liste der Conversation Endpoints end conversation '019F856F-F525-DA11-B373-505054503030‚WITHCLEANUP select * from sys.conversation_endpoints

43. Demo Nachrichten senden und empfangen

44. Queue Aktivierung • Beim Eintreffen einer Nachricht kann eine Aktion ausgelöst werden • Stored Proc (Internal Activation) ALTER QUEUE quKyoto WITH ACTIVATION( STATUS = On,PROCEDURE_NAME = [QueueActivation], MAX_QUEUE_READERS = 1,EXECUTE AS SELF) ALTER QUEUE quKyoto WITH ACTIVATION( STATUS = Off)

45. Queue Aktivierung - SP • Normale Stored Procedure • Mehrere Instanzen möglich CREATE PROCEDURE [dbo].[QueueActivation]WITH EXECUTE AS CALLER AS DECLARE @dh uniqueidentifierDECLARE @msg XML WAITFOR(RECEIVE TOP(1) @dh = conversation_handle, @msg = message_body FROM [quKyoto] ), TIMEOUT 15000 INSERT INTO XML_MESSAGES (Nachricht) VALUES (cast(@msg as XML))

46. Ressourcen • Blogs • Rushi Desai: http://blogs.msdn.com/rushidesai/ • Niels: http://www.sqljunkies.com/WebLog/nielsb/ • Newsgroup • SQL Server 2005 Beta News Groups: http://msdn.microsoft.com/SQL/2005/ • SSB Devolopers Spot • www.sqlservicebroker.com • dotnetpro Marcel Gnoth, 6+7 2005 • www.gnoth.net

47. Query Notifications und ADO 2.0 SQLDependency Service Broker SQLNotificationRequest

48. SQL Server informiert • Select Statement wird zum Server geschickt • Server überwacht die Ergebnismenge • Treten Änderungen auf, dann informiert der Server den Client • Verwalten von Daten Caches (ASP, Middletier) • Immer aktuelle Informationen auf dem Desktop ohne pollen

49. Userdaten • Anwender A erhält Daten aus DB • Anwender B ändert Daten • Anwender A arbeitet mit nicht aktuellen Daten

50. Lösungen (klassisch) • Pollen der DB in Intervallen • Erzeugt Last bei vielen Anwendern (Roundtrips) • SQL Server Trigger • Stößt irgendeine Aktivität an • File erzeugen • COM Komponente • Exe starten • Trigger läuft in der Transaktion des DML Befehls • Schreiboperationen werden teurer