SQL Server 2008 R2 Enterprise e-Business Teodor BLIDĂRUŞ Managing Partner Softelligence
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
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
Definitie enterprise e-Business • Aplicatii e-Business • Customer web self-service • B2C e-Commerce (e-Shop) • B2B e-Commerce • e-Procurement • e-Marketing • EDI
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”
Enterprise e-BusinessProblema nr. 1 • Misiunea e-Business • Procesesiinformatiiexistenteremodelatesireorientatecatre internet • “Am nevoiesapreieidatele din baza de date de clienti” • Care din ele?
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
Enterprise e-Business e-Business CRM e-Commerce Web Self Care e-Marketing EDI e-Procurement Enterprise Procurement Billing Inventory ERP HR
Master Data Management • Master Data • Clienti, produse, facturi, adrese, etc. • De ceavemnevoie de management? • Modificariexterne • Conflicte • Coruptiadatelor • Inconsistenta
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
SQL Server 2008 R2 Master Data Services
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
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
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
Enterprise e-Business e-Business CRM e-Commerce Web Self Care e-Marketing EDI e-Procurement Enterprise Procurement Billing Inventory ERP HR
Enterprise e-BusinessProblema nr. 3
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
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
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
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>)
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
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
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
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
DEMO
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
Single unit of deployment
Single unit of deployment
Single unit of deployment
Single unit of deployment
Single unit of deployment
Softelligence
Softelligence
Intrebări? Vămulţumesc
