SQL Server 2008 sovelluskehitys - uudet ominaisuudet Pekka Korhonen Seniorikonsultti FC Sovelto Oyj

2. SQL Server 2008 sovelluskehitys - uudet ominaisuudet Pekka Korhonen Seniorikonsultti FC Sovelto Oyj

3. SQL Server 2008 • SQL Server 2008 sisältää 4 eri palvelintuotetta • relaatiotietokanta (SQL Server) • analyysipalvelin (Analysis Services, SSAS) • raportointipalvelin (Reporting Services, SSRS) • integrointipalvelin (Integration Services, SSIS) • Tuotteita ei voi hankkia irrallisina. Ne ovat osa SQL Serverin paketointeja, joita ovat: • Enterprise Ed. • sisältää kaikki komponentit ja ominaisuudet • Standard Ed. • sisältää kaikki komponentit, mutta ominaisuuksia on karsittu • Workgroup Ed. • Sisältää vain relaatiokanta- ja raporttipalvelimen • Express • Hieman edellistä rajoitetumpi, mutta ilmainen versio • max. 1 GB keskusmuisti, 1 Prosessori, 4 GB kannan koko, ei SQL Server Agent -palvelua • Lisäksi Developer ja Compact Editionit

4. SSDS – SQL Server Data Services • On-demand Data Storage, beta • Tietokanta ostopalveluna • Skaalatuu käytön ja tarpeen mukaan • SLA • Korkea käytettävyys, suojaus, suorituskyky • Web-liityntä • SSL, SOAP, REST, LINQ • Joustava tietomalli

5. 1989 v. 1.0 (OS/2-käyttöjärjestelmä) • porttaus Sybasen tietokannasta • 1993 v. 4.2 (NT 3.1) • 1994 Yhteistyö Sybasen kanssa päättyy • 1995 v. 6.0 • huomattavia muutoksia (replikointi, kursorit, ...) • 1996 v. 6.5 • 1998 v. 7.0 "uusi" tuote • vanha arkkitehtuuri kokonaan uusiksi, uusi tuote! • 2000 (8) v. 2000 • XML, omat funktiot jne. • 2005 (9) v. 2005 • uusi käyttöliittymä,tietokantaschemat, .NET yhteensopivuus, natiivi xml-tietotyyppi, Service Broker, Web Services jne. • 2008 (10) v. 2008 • Kryptaus, pakkaus, spatial-data, auditointi, autom. monitorointi, … SQL Serverin vaiheita

6. "Your Data Any Place, Any Time" SQL Server 2008 ominaisuudet • Enterprise Data Platform • tietojen suojaus (mm. kannan kryptaus, auditointi) • optimoitu ja ennustettavissa oleva suorituskyky • Policy Based Management • Beyond Relational • monen tyyppisen datan käsittely (mm. isot tiedostot esim. hd elokuvat) • paikkatiedon käsittely (geometrinen ja maantieteellinen data) • Dynamic Development • Entity-pohjainen sovelluskehitys (ADO.NET Entity Framework, LINQ) • tietojen synkronointi (tietojen tallennus paikallisesti ja synkronointi takaisin palvelimelle) • Pervasive Insight • kaikenlaisen datan integrointi tietovarastoon • skaalautuva tietojen analysointi ja raportointiympäristö • Käyttäjän näkökulma tietoihin (office integraatio, omien raporttien teko jne.)

7. Business Intelligence • Analysis Services • uusi kuution suunnittelutyökalu • Aggregation Designer • MOLAP Writeback • uudet Data Mining menetelmät • Excel Data Mining Add-ins • Reporting Services • ei tarvita IISiä • Internet deployment ja word raportit sekä parannettu excel-raportointi • tablix-raportit (taulukon ja matriisin yhdistelmä) • forms autentikointi • Integration Services • persistent lookups • MERGE • Change Data Capture

8. Sovelluskehitys • Entity Data Model • määritellään ja käsitellään entiteettejä (business objekteja) eikä tauluja ja rivejä • LINQ • Language Integrated Query • Object Relational Mapper • mahdollistaa tietokantaobjektien mappauksen CLR objekteiksi • Data Change Capture • mahdollistaa tietomuutosten keräämisen muutostauluun • Data Synchronization Services • mahdollistaa tietojen säilyttämisen paikallisessa Cachessa kun tietokantayhteyttä ei ole ja myöhemmin datan synkronoinnin tietokantaan

9. SQL Server Change Tracking • Synchronized Programming Model • Visual Studio Support • SQL Server Conflict Detection • FILESTREAM data type • Integrated Full Text Search • Sparse Columns • Large User Defined Types • Date/Time Data Type • LOCATION data type • SPATIAL data type • Virtual Earth Integration • Partitioned Table Parallelism • Query Optimizations • Persistent Lookups • Change Data Capture • Backup Compression • MERGE SQL Statement • Data Profiling • Star Join • Transparent Data Encryption • External Key Management • Data Auditing • Pluggable CPU • Transparent Failover for Database Mirroring • Declarative Management Framework • Server Group Management • Streamlined Installation • Enterprise System Management • Performance Data Collection • System Analysis • Data Compression • Query Optimization Modes • Resource Governor • Entity Data Model • LINQ • Visual Entity Designer • Entity Aware Adapters • Enterprise Reporting Engine • Internet Report Deployment • Block Computations • Scale out Analysis • BI Platform Management • Export to Word and Excel • Author reports in Word and Excel • Report Builder Enhancements • TABLIX • Rich Formatted Data • Personalized Perspectives • … and many more

10. Enterprise Data Platform • Datan Kryptaus • olemassa olevan datan (kannan) kryptaus ilman sovellusmuutoksia • ulkoinen kryptausavainten hallinta keskitetysti • Auditointi • tietomuutosten kirjaaminen (kuka teki mitä ja milloin) • Datan ja varmistusten pakkaus • pienentää mm. tilan tarvetta huomattavasti • Suorituskykyyn liittyvän informaation automaattinen kerääminen ja analysointi • kyselyiden optimoinnin "jäädyttäminen" • resurssikulutuksen leikkaaja (govenor) • hallinta policyjen kautta (vs. skritpit), myös servereiden välillä

11. SQL Server 2005 SQL Server 2008 Mitä relaationaalisen datan jälkeen? • Large UDTs • Flexible Columns • Wide Tables • Filtered Indices • HierarchyID • User Defined Types • Remote BLOB Store API • FILESTREAM • Integrated FTS • Full Text Indexing • Fully supported Geometry and Geography data types and Functions Documents & Multimedia Relational Data Spatial XML • XML Data Type and Functions • XML Upgrades

12. Koko kanta kryptataan • Ei tarvita erikseen koodia kryptaamisen toteuttamiseen • Ei rajoituksia datan tai indeksien suhteen (poikkeus: FILESTREAM) • Pieni suorituskykykustannus • Backupit myös kryptattuja • Tarvitaan avain palauttamiseen Tietokannankryptaus

13. TDE – Transparent Data Encryption • Tietokantasivut kryptataan ennen levylle kirjoittamista • Page protection (esim. checksums) sovelletaan tämän jälkeen • Page protection (esim. checksums) tarkistetaan ennen kryptauksen purkua • Kryptaus puretaan kun sivu luetaan muistiin • Kun TDE otetaan käyttöön tapahtuu kryptausprosessi tausta-ajona • Vastaavasti purettaessa TDE • Prosessin etenemistä voi tutkia sys.dm_database_encryption_keys -näkymällä • encryption_state column = 2, tausta-ajo keksken • encryption_state column = 3, kanta kokonaisuudessaan kryptattu Kryptauksentoimintaperiaate

14. Luo master Key • CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPwdHere>'; • Luo tai hae varmenne, joka suojataan edellisellä • CREATE CERTIFICATE MyDEKCert WITH SUBJECT = 'My DEK Certificate'; • Luo kryptausavain, joka suojataan edellisellä • CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyDEKCert; • Aseta kantaan kryptaus päälle • ALTER DATABASE MyDatabase SET ENCRYPTION ON; Transparent Data Encryption: käyttöönotto

15. TDE – käyttöliittymän kautta

16. Relaationaalisessa datassa yksinkertaiset datatyypit • Atomaarisia (1 arvo) • Geneeriset operaatiot toimivat hyvin • Relaationaalinen tallennus/kysely ei aina ole optimaalinen • Hierarkinen data • Harva, muuttuva, ominaisuusjoukko (property bag) • Tietyn tyyppinen data hyötyy mm. • Omien kirjastojen käytöstä • Omien tyyppien käytöstä (mm. kompleksiset tyypit) • Oman tallennustavan käytöstä ja ei-SQL API:sta • ei-relaationaalisista kyselyistä ja indekseistä Relationaalinenjaei-relaationaalinen Data

17. Spatial data antaa vastauksia paikkaan perustuviin kysymyksiin • Mitä katuja on kampissa? • Minkä kuntien alueelle maavaatimus osuu? • Mitä elokuvateattereita on kilometrin säteellä • Paikkatieto on hyvin yleistä • …jos kannassa on osoite Spatial Data - paikkatieto

18. OGC - Open Geospatial Consortium on määritellyt paikkatietotyypit (Microsoft kuuluu OGC:hen) • Point • Linestring • Polygon • MultiPoint • MultiLinestring • MultiPolygon • GeomCollection • Non-instanciable classes based on these • Mm. erilaiset karttapalvelut käyttävät näitä Spatial tietotyypit

19. OGC Spatial Types

20. SQL Serverissä on kaksi paikkatietotyyppiä • GEOMETRY - flat earth model - tasogeometriaa • GEOGRAPHY - round earth model – pallogeometriaa • Maantieteelliset objektit voivat sijaita vain joko pohjoisella tai eteläisellä maapallon puoliskolla • Molemmat tyypit tukevat OGC tyyppejä • 2-Dimensioinen data • X ja Y tai Lat ja Long jäsenet • Z jäsen - elevation • M jäsen – measure (esim. Koordinaatisto) SQL Server 2008 ja Spatial Data

21. Spatial data tallennetaan binäärisessä muodossa • Voi olla myös NULL • Voidaan antaa useassa yleisessä formaatissa: • Well Known binary - ST[Type]FromWKB • Well Known text - ST[Type]FromText • Geography Markup Language (GML) - GeomFromGml • Voi käyttää SQLCLR-funktioita, kuten • Parse • Point • Inputina SQLCLR-tyypistä - SqlGeometry, SqlGeography Input

22. Spatial Data saadaan ulos useassa yleisessä formaatissa • Well Known binary - STAsBinary • Well Known text - STAsText • GML - AsGml • Text with Z and M values - AsTextZM • Kyseisen tyypin SQLCLR-metodit • ToString - palauttaa Well Known textin • SQLCLR-objektina - SqlGeometry, SqlGeography • Muita yleisiä formaatteja GeoRSS, KML • SQL Server ei suoraan tuota näitä, tuotetaan ohjelmallisesti Output

23. Jokaisella paikkatietotyypillä pitää olla referenssi • Spatial Reference Identifier -SRID • SRID määrittelee laskentatavat: • SRID 4326 - GPS • SRID 4269 - usually used by ESRI • SRID 0 – ei referenssiä, oletus GEOMETRY-tyypille • Metodit jotka käsittelevät useaa paikkatyypiä (esim., STDistance) vaativat samat SRID:t • Muuten tulos on NULL • Tallennetaan sys.spatial_reference_systems-tauluun SRID

24. Suurten (binääristen) objektien tallentaminen kantaan ei ole aina optimaalista • Kuluttavat muistipuskureita • Päivitysoperaatiot aiheuttavat tietokannan fragmentoitumista • tiedostojärjestelmässä "update“ on delete ja insert • "Before image" ei tuhoudu heti • Kaiken tallentaminen kantaan tuo myös etuja • Tapahtumaeheys • point-in-time backup ja restore • Yksi tallennus- ja kyselykone Filestream storage

25. Filestream-datalle määritellään filegroupDDL:llä • Liittyy aina yhteen kantaan • mappautuu hakemistoon! (pitää olla NTFS) • Huom! Tiedostot tuhottavissa jos oikeudet riittävät • VARBINARY(MAX) –sarake voidaan määritellä FILESTREAM attribuutilla • Taulussa oltava UNIQUEIDENTIFIER • Ei muille tyypeille • Data tallentuu tiedostojärjestelmään • Ei 2 GB rajoitusta SQL Server 2008 Filestreamtoteutus

26. Voidaan käsitellä normaalsiti SQL:llä • Tällöin ei eroa tavalliseen varbinary(max)-käsittelyyn • Voidaan käsitellä myös file IO:lla • PathName-funktio hakee polun • Tarvitaan konteksti • GET_FILESTREAM_TRANSACTION_CONTEXT • OpenSqlFilestream avataan tiedostokahva • File Name • Required Access • Access Options • FilestreamTransaction context Filestream-käsittely

27. Uusi datatyyppi - HierarchyID • SQLCLR System UDT • .NET-clientit voivat käyttää: SqlHierarchyId • Path Enumeration malli • “sijaintitietoinen” • Tehokas hauissa • Pävitys monimutkaisempaa Hierakiat

28. Indeksoitu • Metodeja • GetRoot • GetLevel • IsDescendant • GetDescendant, GetAncestor • Reparent • Ei pakota puurakennetta • Voidaan pakottaa constrainteilla HierarchyID

29. TSQL muutoksia • Uusia tietotyyppejä (CLR) • Date • Time • DateTimeOffset • DateTime2 • HierarchyID • Merge –komento • Päivitys, muutos, poisto yhdellä komennolla • Grouping Sets • Taulu-tyyppiset parametrit • UDT max. koko 2GB • Sparse columns (harvaa dataa)

30. SQL Server 2008: System UDTs • SQL Server 2008:n uudet tyypit on toteuettu SQLCLR-assemblyna • HierarchyID • Geography • Geometry • Assembly on Resurssitietokannassa • Käytettävissä kaikissa muissa kannoissa • Microsoft.SqlServer.Types • Voi asentaa ja käyttää clientissa

31. DATE Data Type • Vain päivämäärä • 01-01-0001 to 31-12-9999 Gregorian Calendar • TIME Data Type • Vain aika • Tarkkuus määriteltävissä - 0 - 7 desimaalisena aina 100 nanosekuntiin DATE ja TIME -tyypit

32. DATETIME2 Data Type • 01-01-0001 - 31-12-9999 Gregoriaaninen kalenteri • Tarkkuus määriteltävissä 100 nanosekuntiin asti • DATETIMEOFFSET • 01-01-0001 - 31-12-9999 Gregoriaaninen kalenteri • Tarkkuus määriteltävissä 100 nanosekuntiin asti • Säilyttää aikavyöhykkeen (Time Zone) (UTCTime’en verrattuna) • Mutta ei ole aikavyöhyketietoinen (ei kesä/talviaikaa) DATETIME2 ja DATETIMEOFFSET

33. 1:N-tyyppisten objektien käsittelyongelma • Tilaus-tilausrivit • Ei ole taulukko TSQL-tyyppiä • Miten välitetään esim. Proseduurille • Kömpeloitä ratkaisuja: • Muunnetaan merkkijonoksi ja takaisin ja … • Parempia: • XML-dokumenttina (ei kovin tehokas) • Taulu-tyyppinen parametri ratkaisee tämän ongelman Taulu-tyyppisetparametrit

34. määrittely • DECLARE @t TABLE (id int); • Vahva tyypitys • CREATE TYPE mytab AS TABLE (id int); • DECLARE @t mytab; • Parametrien tulee olla vahvasti tyypitettyjä Taulu-tyyppi

35. Määrittely ja alustus DECLARE @t mytab; INSERT @t VALUES (1), (2), (3); EXEC myproc @t; • Proseduurin määrittelyssä Readonly pakollinen CREATE PROCEDURE usetable ( @t mytab READONLY ...) AS INSERT INTO lineitems SELECT * FROM @t; UPDATE @t SET... – EI KÄY! Taulu-muuttujatovatreadonlyja

36. Muuttujan määrittely ja alustaminen DECLARE @iint = 4; • Arvon kasvattaminen SET @i += 1; • Rivikonstruktorit DECLARE @t TABLE (id int, name varchar(20)); INSERT INTO @t VALUES (1, 'Fred'), (2, 'Jim'), (3, 'Sue'); T-SQL Syntaksimuutoksia

37. Grouping Sets mahdollistaa usean GROUP BY -määreen yhteen SQL-komentoon • COMPUTE BY –määre poistuu • Tehokas (yksi read pass) Grouping Sets

38. Useita joukko-operaatioita yhdessä komennossa • Operaatiot voivat olla: INSERT, UPDATE, DELETE • Operaatioiden ehdot: • WHEN MATCHED • WHEN [TARGET] NOT MATCHED • WHEN SOURCE NOT MATCHED • ANSI SQL 2006 yhteensopiva - laajennoksilla MERGE-komento

39. Uudet dependency-näkymät korvaavat sp_depends-proseduurin • Aina ajan tasalla • sys.dm_sql_referenced_entities • Mihin tämä objekti viittaa • sys.dm_sql_referencing_entities • Mitä objektit viittaavat tähän • Näkee viittaukset OBJECT, DATABASE DDL TRIGGER, SERVER DDL TRIGGER -tasoilla Riippuvuuksienseuraaminen

40. MERGE ja GROUPING SETS ovat tehokkaita • Vähemmän table scaneja • Taulu-muuutujat • Tehokkaita parametrivälityksessä • DW kannat • Optimointimuutoksia (aikaisempi predikaattien filtterointi, bitmap iteroinnit) • Suodatetut indeksit • Plan Guide • Optimoijan ohjaaminen helpompaa • Jäädytetyt suoritussunnitelmat Suorituskykyparannuksia