Enterprise e-Business

1. SQL Server 2008 R2 Enterprise e-Business Teodor BLIDĂRUŞ Managing Partner Softelligence

2. Cuprins • Enterprise e-Business Applications • Definitie, caracteristici • Exemple • Provocari • Master Data Management • Abordare • Master Data Services • Performantaaplicatiilor online • Nivelulbazei de date • Testaresimasurare • Changes management • Despre Softelligence

3. Definitie enterprise e-Business • Enterprise • Companie de maridimensiuni, procese de business dejafunctionale • Sisteme multiple in-house • ERP, SCM, CRM, billing • Numar mare de tranzactii • Numar mare de utilizatori • Procesecomplexe, segregate

4. Definitie enterprise e-Business • Aplicatii e-Business • Customer web self-service • B2C e-Commerce (e-Shop) • B2B e-Commerce • e-Procurement • e-Marketing • EDI

5. Provocareaenterprise e-Business • Enterprise e-Business e complicat • Integrare ERP, CRM, SCM, LOB, ETC. !!! • Dispersiadatelor • Sutesaumii de useriinterni • Sute de miisaumilioaneuseriexterni • Workflow-urisemiautomate, ceincludinterventiemanuala • Implementareproces online “as a whole”

6. Enterprise e-BusinessProblema nr. 1 • Misiunea e-Business • Procesesiinformatiiexistenteremodelatesireorientatecatre internet • “Am nevoiesapreieidatele din baza de date de clienti” • Care din ele?

7. Scenariu real • Obtinereaunui loc de parcare • Depundosarul (ghiseu 1) • Audientapentruvalidarearezervarilor (ghiseu 2) • Plata abonamentanual (ghiseu 3) • Eliberare talon (ghiseu 4) • Sistemeidentificate • Management documente • Rezervari • Financiar • Gestiuneabonamente

8. Enterprise e-Business e-Business CRM e-Commerce Web Self Care e-Marketing EDI e-Procurement Enterprise Procurement Billing Inventory ERP HR

9. Master Data Management • Master Data • Clienti, produse, facturi, adrese, etc. • De ceavemnevoie de management? • Modificariexterne • Conflicte • Coruptiadatelor • Inconsistenta

10. SQL Server 2008 R2 Master Data Services • Solutii Master Data Management • Baze de date consolidate, replicare, e-Hub • MDS • Master Data Hub • Web UI • Servicii & API • Integrat in licenta de SQL Server

11. SQL Server 2008 R2 Master Data Services

12. SQL Server 2008 R2 Master Data Services • Input in MDS • Staging tables • Members • Attributes • Hierarchy • Batch load • Correct errors and re-run • Output din MDS • Subscription views

13. ResurseMaster Data Services • http://nickbarclay.blogspot.com/ • http://msdn.microsoft.com/en-us/library/bb190163.aspx • http://msdn.microsoft.com/en-us/library/ee633763%28SQL.105%29.aspx

14. Enteprise e-BusinessProblema nr. 2 • “Bate fierul cat e cald…” • Care fier? • SQL Server Fast Track Data Warehouse 2.0 • Configuratii validate, create special pentruservere de baze de date • Stripe size, RAID, partition alignment, etc. • HP, Dell, IBM, EMC2

15. Enterprise e-Business e-Business CRM e-Commerce Web Self Care e-Marketing EDI e-Procurement Enterprise Procurement Billing Inventory ERP HR

16. Enterprise e-BusinessProblema nr. 3

17. Enterprise e-BusinessProblema nr. 3 • “Se miscagreu… mi-e imposibilsalucrez” • E o problema hardware • Daasa se misca la noi, darei au un server maiputernicsi o sa fie ok • 2.000.000 reads = aprox. 16 GB • Suntprea multi utilizatori ! • … mmm … • 80% din problemele de performantasuntcauzate de software • 80% sunt situate la nivelulbazei de date • Design prost al tabelelor, query-urilor, indecsilor • Lipsa de monitorizaresi control a lock-urilor

18. Performanta:Query Design • Unelte de masure • SQL Server Profiler • Perfmon • Execution plan • Anatomiaunui query SQL • Parsare • Standardizare • Optimizare • Analiza query-ului • Alegereaindecsilor • Alegereaordinii de join • Compilare • Executie

19. Performanta: Analiza query-ului • Search arguments (SARG-able) • DA: pot duce la utilizareaindescilor (SEEK) • Operatorii <,>,=,<=,>=, AND • Opertaorii LIKE ‘xx%’, BETWEEN) • Operatoriderivati (ex: balance * 1.17 > 100) • Conditii de join • NU: producscanari • Operatorii NOT IN, <>, NOT EXISTS, OR, IN • Functii (ISNULL, UPPER, LOWER, CEILING, SUBSTRING, ABS) • Coloane ca operanzi • Expresiievaluabile la rulare

20. Performanta: Analizaindecsilor • Cuvantulcheie: selectivitate, indecsiunique • Mai multi saumaiputini ? • Mai acoperitori • Key distribution statistics • In lipsalor • = 10% • <,> 33% • Between 12% • dbccshow_statistics • Testatiperformanta “la rece” • DBCC DROPCLEANBUFFERS • DBCC FREEPROCCACHE • DBCC FLUSHPROCINDB(<db_id>)

21. Performanta:JOIN • LOOP • Default • Aplicabilsipentruoperatorialtiidecat = • Beneficiaza de peurmaindecsilor • Celmai rapid pentrutabelemici • MERGE • Aplicabildoarpentru = • Beneficiaza de peurmaindecsilor • Utilpentru table sauseturimari de date, cu dimensiunisimilare, dejaordonatesau care se vorordonate

22. PerformantaHASH JOIN • HASH • Celmaifolosit • ATENTIE: folositchiarsi in lipsaindecsilor • Aplicabildoarpentru = • Consumatorsporit de memoriesi CPU • Mai bun decat MERGE candseturile de date difera ca dimensiune • Mai bun decat LOOP candseturile de date suntmari ca dimensiune

23. Performanta:Locks • S, X, Sch • Lock • row, page, extent, partition, table • Escalation: TABLE, AUTO, DISABLE • sp_lock + syslockinfo; sys.dm_tran_locks • NOLOCK, HOLDLOCK, READPAST • Modificari in SQL Server 2008 R2 • Aloritmul de hash din Lock Manager • Efect • Imbunatireaconcurentei in conditii de volume mari de date

24. Performanta:Validare • Testare de performanta, sizing • Parte din procesul de development • Setup • Visual Studio for Testers • Excel • SQL Server Profiler • Perfmon • Implicare • Developeri + IT Manageri

25. DEMO

26. Performanta:Changes management • Static code analysis • Single unit of deployment • Mutearea de pe staging peproductie • Backup, restore, SSIS, TSQL scripts • Dependentefata de obiecte din server (logins) • Data tier Application in VS 2010 • Wizard in Management Studio • Pack and Deploy

27. Single unit of deployment

28. Single unit of deployment

29. Single unit of deployment

30. Single unit of deployment

31. Single unit of deployment

32. Softelligence

33. Softelligence

34. Intrebări? Vămulţumesc