SQL Server In het kort:
Makkelijk deployen van database BI release Oplossing voor MDM probleem
Hoe aantrekkelijk is SQL Server 2008 R2? Pe ter
ter
Br a ake
Begin mei kwam de nieuwste versie van SQL Server beschikbaar: SQL Server 2008 R2. Dat is snel na de release van SQL Server 2008 en bovendien in een tijd dat velen van ons nog bezig zijn met migratie naar SQL Server 2008. Wat brengt deze tussenstap ons en welke features maken de nieuwste versie aantrekkelijk? Als eerste valt op dat er twee nieuwe edities van SQL Server zijn die de Enterprise editie ontstijgen. SQL Server 2008 R2 Datacenter is de editie die het meest te bieden heeft op het gebied van scalability. Datacenter ondersteunt tot 256 logische processors. Nieuw naast deze Datacenter-variant is SQL Server 2008 R2 Parallel Data Warehouse, een zogenaamde appliance-based solution. Deze editie is gemaakt in samenwerking met enkele hardwarevendors en kan dankzij de implementatie van een massively parallel processing (MPP) architecture datawarehouses aan tot enkele honderden terabytes groot. Een zeer welkome verandering is dat R2 in de standard edition nu ook backup-compressie ondersteunt. Tot nu toe was dat een enterprise feature. Application and Multi-Server management, de belangrijkste nieuwe feature – vanuit een DBA standpunt bekeken – wordt nu zowel in Datacenter, Enterprise als ook Standard en Workgroup ondersteund: Hiermee kunnen DBA’s centraal meer instances van SQL Server monitoren en beheren. Standard en Workgroup kunnen centraal beheerd worden, maar kunnen TechNet Magazine
zelf niet de centrale server (Utility Control Point, ofwel UCP) zijn. Een Enterprise instance kan wel een UCP zijn, maar met maximaal 25 geregistreerde servers. Application and Multi-Server Management In steeds meer bedrijven is er niet langer sprake van één SQL Server Instance, maar hebben we te maken met meer instances verspreid over meer, wellicht virtuele, servers. Daarbij is er waarschijnlijk sprake van meer databases per instance. Zowel het monitoren van de totale SQL Server infrastructuur als het behouden van overzicht over welke instance/computer hoe zwaar wordt ingezet, wordt dan langzaam maar zeker een uitdaging. Met de komst van SQL Server 2008 werd ons leven iets makkelijker gemaakt met de introductie van Policy Based Management, een samenwerking tussen het registered servers window in SQL Server Management Studio (SSMS) en Performance Data Collection. Met Multi-Server Management gaat Microsoft een aangename stap verder. Met SQL Server 2008 R2 worden we in staat gesteld een zogenaamde SQL Server Utility aan te
juli 2010
49
SQL Server
Figuur 1 De SQL Server Utility Architectuur.
maken. Dat is niets anders dan een aantal regi stered servers, ofwel managed instances, die hun Performance Data naar een centrale instance, het Utility Control Point (UCP), uploaden. Samen vormen deze servers een zogenaamde managed server group. SQL Server Management Studio is uitgebreid met een Utility Explorer en de nodige dashboards (viewpoints) die ons in staat stellen van alle geregistreerde servers te bekijken hoe de resources worden benut. De status van de resources wordt bepaald door policies die zowel globaal als per resource kunnen worden ingesteld. De resource-indicatoren waar we het dan over hebben zijn: CPU-belasting door de SQL Server Instance, CPU-belasting door de computer waar de instance op draait, de werkdruk van data- en logfiles en de werklast van de drives waar de SQL data- en logfiles op staan. Figuur 1 bevat een overzicht van de SQL Server Utility Architecture. Opzetten Multi-Server Administration Stap 1 in het opzetten van Multi-Server Administration is het aanmaken van het zogenaamde Utility Control Point (UCP). Om dat te doen gaat u in SSMS naar het menu View en klikt u vervolgens op Utility Explorer. De Utility Explorer opent aan de linkerkant van het scherm. De eerste keer dat u dit doet, opent ook het Getting Started venster. In beide vensters vindt u een knop (link) die de wizard opent die een nieuw Control Point aanmaakt. U moet in de wizard aangeven welke SQL Server Instance de UCP wordt, geeft hier een naam voor de Utility op en benoemt het gewenste windowsaccount waarmee de performance data van de UCP de Collection Set wordt opgehaald. Dit kan een windows-account zijn die als proxy dient binnen de SQL Server Agent. Het mag ook de agent serviceaccount zijn, zolang dat ook maar een windowsaccount is. Het maken van een UCP is enigszins vergelijkbaar met het opzetten van een performance-datawarehouse in SQL Server 2008. Eén van de stappen die de wizard uitvoert, is het aanmaken van het zogenaamde utilitymanagement datawarehouse (UMDW). In Books on-line (BOL) kunt u lezen dat het UMDW ongeveer 2 GB per jaar per geregistreerde SQL Server Instance zal groeien. Uiteraard speelt de MSDB database van de UCP een belangrijke rol. Deze heeft ongeveer 20MB aan storage nodig per managed instance van SQL Server. Beide getallen zijn uiteraard sterk afhankelijk van uw specifieke omgeving, maar houdt rekening met deze groei. Niet onbelangrijk is de aanwijzing waarbij voor de UCP wordt aangeraden een case-sensitive instance van
50
juli 2010
SQL Server te gebuiken. Doet u dat namelijk niet, dan kunt u problemen verwachten bij case-sensitive managed servers die later aan UCP worden toegevoegd. Instances toevoegen Na het aanmaken van een UCP moeten één of meer instances worden toegevoegd aan de utility. In de Utility Explorer zijn drie items: data-tier applications, managed instances en utility administration. Via enroll instance in het popup-menu onder managed instances start u de enroll instance wizard op. U kiest hier de instance die aan de managed server group moet worden toegevoegd en het account waaronder de collection set gaat draaien. Deze collection set is een job die onderdeel is van de lokale SQL Server Agent (dus niet centraal op de UCP). Er is een aantal beperkingen voor zowel de UCP als de managed servers: • Alle servers moeten versie 10.5, dus SQL Server 2008 R2 of hoger zijn. • Alleen database instances kunnen managed zijn, dus SQL Server Analysis Services (SSAS) niet. • Alle SQL-servers moeten in hetzelfde domein zitten of er dient een two-way trust tussen de domeinen te bestaan. • De SQL-server service account heeft leesrechten op users in Active Directory nodig. • Filestream kan niet worden gemonitord met de utility. Naast deze beperkingen controleert de wizard nog een aantal voorwaarden. Zie voor de volledige lijst “How to: Enroll an Instance of SQL Server (SQL Server Utility)” in BOL. Monitoren Als u één of meer servers heeft aangemeld, kunt u in het utility explorer contentvenster beginnen met het monitoren van uw servers. Als u in de utility explorer managed instances heeft geselecteerd, ziet u een lijst van geregistreerde servers met vier indicatoren die aangeven wat de status binnen de instance is van instance-CPU, server-CPU, Filespace en volumespace. Als u een instance selecteert, ziet u onderin het utility content venster de bijbehorende details (zie figuur 2). Zoals eerder gezegd wordt de status (OK, overutilized, underutilized) bepaald door policies. Op de derde tab ziet u of voor de geselecteerde server de globale policy geldt, of een specifieke policy. Ook kunt u hier de nu geldende grenswaarden aflezen. Voor CPUTechNet Magazine
gebruik gelden bijvoorbeeld de defaultwaarden van nul procent en 70 procent. Nul procent betekent underutilization en meer dan 70 procent overutilization. De zojuist genoemde defaultwaarden zijn via utility administration in de utility explorer aan te passen. Hier kunt u ook definiëren hoe u wilt omgaan met korte pieken. Stel dat de CPU slechts kort boven de 70 procent uitkomt. Moet dat meteen getoond worden als overutilized? Waarschijnlijk niet. Met behulp van volatile resource policy evaluation kunt u aangeven met welke demping de resource als overutilized moet worden aangegeven. Verder kunt u via utility administration aangeven wie de utility dashboards en viewpoints mag inzien en wat de bewaartijd voor gegevens in het data warehouse is. De default periode is één jaar. Data Access Application (DAC) De feature die we tot nu toe bespreken, heet Application and Multi-Server Management. In de utility explorer zit ook het onderdeel datatier-applicaties. En in figuur 1, dat een totaaloverzicht van de feature geeft, ziet u data access application databases (DAC-databases) naast de databases die geen onderdeel zijn van een DAC. Wat wordt in deze context bedoeld met Data Access Application? Het idee achter DAC is dat het deployen van databases makkelijker wordt. Maar ook de migratie van databases naar nieuwe versies van SQL en het verhuizen van databases naar andere instances worden makkelijker. Met name dat laatste geeft een extra dimensie aan de multiserver management feature. Als een bepaalde instance op een bepaalde server een te hoge werklast wordt, is het wel zo makkelijk, als met een eenvoudige rechttoe rechtaan handeling een database kan worden verhuisd naar een andere, rustiger instance. Tot nu toe zijn backup en restore en scripting de voor de hand liggende manieren om dit te doen. Een uitdaging daarbij is altijd het overzetten van zogenaamde serverdependencies. Zaken als logins en serverlevel objecten moeten apart verhuisd of gescript worden. Een datatier-applicatie is een file met daarin alle database objecten waaruit een database bestaat. De file (.dacpac) kan een DBA gebruiken om een database te deployen. De developers kunnen applicatie-executables als ook de datatier-applicatie component (.dacpac) opleveren. De DBA kan nu met de deploy data-tier application wizard met een rechter muisklik op SQL-instance in de object explorer binnen SSMS de database inclusief alle dependencies aanmaken. Als de SQL Server instance een managed instance is, wordt de informatie ook opgeslagen in de MSDB-database. TechNet Magazine
Figuur 2 Instance details in het utility content venster.
Data meeverhuizen Er zijn op dit moment twee manieren om een .dacpac-file aan te maken. Vanuit SSMS kunt u een .dacpac genereren. In SSMS vindt u in het context-menu van een database, onder tasks, vindt u de optie extract datatier-applicatie. Dit start de wizard die een .dacpac file aanmaakt. In Visual Studio is ook een nieuw datatier project-template opgenomen. Via dit type project kan een developer een datatierapplicatie maken, of een met SSMS gegenereerde .dacpac inlezen. Toch zijn hier nog wel enkele beperkingen. Niet alle database objecten worden ondersteund. Zo leert een eenvoudige test dat het laten genereren van een datatierapplicatie voor de bekende sample-database AdventureWorks maar liefst 82 fouten oplevert. De eenvoudigere Northwind sample-database laat zich wel makkelijk als datatier genereren. Verder moet u er bij het verplaatsen van een database rekening mee houden dat alleen het database-schema wordt gegenereerd. Wilt u de data meeverhuizen dan zult u dit bijvoorbeeld met behulp van SQL Server Integration Services (SSIS) moeten doen. Gezien de beperkingen van datatier-applicaties is het de vraag wanneer u deze feature succesvol kunt gebruiken. Ik denk vooral als de database in SQL Azure staat, de cloud versie van SQL Server. Hoewel dit een volledige relationele cloud implementatie is, is SQL Azure in functionaliteit beperkt vergeleken met de on-site SQLServer versies. Dit zijn grotendeels dezelfde beperkingen als in de dacpac’s. Voor kleine eenvoudige on-site databases kunnen dacpac’s ook handig zijn. SQL Server 2008 R2, de BI release SQL Server 2008 R2 is ook wel de business intelligence (BI) release genoemd. Wat mij betreft doet dat tekort aan de nieuwe Multi-Server Management-functionaliteit, maar gezien de gave nieuwe features in Reporting Services, de Excel en SharePoint PowerPivot en met het mas
juli 2010
51
SQL Server
Figuur 3 Reporting Services is nu meer mogelijk.
ter data management, is er op het BI-vlak wel veel leuks te melden. Laten we eerst eens kijken naar Reporting Services en Report Builder 3.0. Een punt van kritiek op Reporting Services, zeker in vergelijking met concurrerende producten, is geweest dat het een te technisch product is. Report Designer binnen Business Intelligence Development Studio (BIDS) biedt veel flexibiliteit maar is voor business-users een intimiderende omgeving. Report Builder, de drag-and-drop tool die werkt op basis van de metadata in Report Models, is weliswaar eenvoudig en schermt de rapportmaker volledig af van het schrijven van queries, maar mist erg veel van de functionaliteit van Reporting Services. Met Report Builder 2.0, nageleverd als gratis download, kwam er een tool tussen beide hierboven genoemde tools in. Eenvoudiger in gebruik dan BIDS, maar vele malen krachtiger dan Report Builder. Met Report Builder 3.0, nog altijd een download, is er nog een extra stap in de geboden functionaliteit gezet. Het schrijven van de juiste query is voor de business-user het moeilijkste in het maken van een goed rapport. Lastig is ook als blijkt dat verschillende gebruikers andere rapporten willen op basis van dezelfde gegevens. In SQL Server 2008 R2 is de mogelijkheid van shared datasets toegevoegd. Dit geeft een developer of een DBA de mogelijkheid een query, al dan niet geparameteriseerd, onder een naam op te slaan op de report server. Business-users kunnen dan deze shared datasets gebruiken om rapporten op te baseren, waarmee de noodzaak zelf moeizaam de onderliggende queries te schrijven verdwijnt. De beheerder kan van deze shared datasets run-time eigenschappen zoals geheugengebruik instellen ten behoeve van caching. Net zo mooi als de shared datasets zijn de report-parts. Deze nemen het concept van hergebruik nog een stap verder. Iemand kan bijvoorbeeld een grafiek maken gebaseerd op een dataset en deze grafiek als report-part publiceren. Anderen kunnen bij het ontwerpen van rapporten gebruikmaken van deze grafiek. De link met de oorspronkelijke grafiek blijft bewaard, maar het is toch mogelijk aanpassingen te doen. Als de oorspronkelijke grafiek wordt aangepast, kunt u kiezen of u die wijzigingen wilt overnemen.
52
juli 2010
Vanzelfsprekend zijn de grafische mogelijkheden verder uitgebreid. U kunt nu kaarten maken, spark-lines en data-bars toevoegen en tenslotte kunt u gebruikmaken van indicators. Figuur 3 toont de toolbar met de genoemde items. Verder is de moeite van het vermelden waard dat we rapporten kunnen renderen als data-feeds. Dat geeft ons de mogelijkheid gegevens met behulp van Reporting Services beschikbaar te stellen. Applicaties die datafeeds (atom-compliant) kunnen lezen kunnen deze gegevens dan gebruiken en verwerken. Master Data Services Met SQL Server 2008 R2 komt Microsoft eindelijk met een oplossing voor het master datamanagement (MDM) probleem. Elk bedrijf heeft immers te maken met meerdere databases. Verschillende systemen, zoals ERP, CRM en financiële pakketten draaien ieder op hun eigen database en dus met hun eigen instellingen en gegevens. Operationeel is dat wellicht niet echt een probleem, maar met rapportages wordt het lastiger. Welke gegevens zijn nu de basisgegevens, de master data die leading moeten zijn in het geval van twijfel? Hoewel dit probleem vergelijkbaar is met het consistent krijgen van dimensies in een dimensioneel datawarehouse, is het breder dan alleen datawarehousing. Master Data Services (alleen beschikbaar in 64 bits) biedt SQL Server 2008 R2 een oplossing. Drie onderdelen Master Data Services (MDS) bestaat uit drie onderdelen: MDS Configuration Manager, Master Data Manager (MDM) en MDS Web Service. U start een master data implementatie door met de MDS Configuration Manager een nieuwe database aan te maken (zie figuur 4). Als u op create database klikt, leidt een wizard u door de stappen voor het aanmaken van een database. Als de database er staat, maakt u een connectie en vult u het tweede deel van het scherm in. Hierin geeft u onder andere een database mailprofiel aan. Een belangrijk concept van MDM is data-stewardship. Iemand binnen een organisatie is verantwoordelijk voor de kwaliteit van de gegevens. Daar waar inconsistenties ontdekt worden, wordt de steward nu op de hoogte gebracht met een email, zodat er actie kan worden ondernomen. De volgende stap is het maken van de master data manager website op de tweede tab van de MDS Configuration Manager. Deze site wordt niet automatisch bij het installeren gemaakt, maar pas als u na het invullen van de website TechNet Magazine
parameters zoals Virtual Path en Application Pool, en daarna op de apply-knop klikt. Op de homepage vindt u vijf opties: explorer, version-management, integration-management, system-administration en user- en group-permissions. De eerste stap is nu het aanmaken van één of meer models in deze omgeving. De models definiëren de masterdata, ofwel de entiteiten die we opnemen, de attributen, hiërarchieën en de bijbehorende business-rules. Deze models maakt u aan op de system administration page. Het beheer van de data maakt u in de explorer-pagina. PowerPivot en de impact voor de DBA Wel de moeite waard om hier even te noemen, is het publiceren van PowerPivot-oplossingen naar SharePoint. Dat kan met SharePoint 2010 Enterprise Edition met Excel Services. Gebruikers kunnen dan via de SharePoint-portal gebruikmaken van PowerPivot via Excel Services. Er verandert echter wel één belangrijk ding. Bij een gewoon Excel-workbook zit alle data in de Excel-file. Zodra PowerPivot geüpload wordt naar een SharePoint server, zal onder de motorkap een kubus worden aangemaakt in SQL Server Analysis Services (SSAS). Vanaf dit punt is Analysis Services dus opeens een vereiste. PowerPivot haalt alle gegevens binnen in het Excel-workbook waarin we het gebruiken. Daarmee is een PowerPivot Excel-sheet disconnected en dus statisch. Als SharePoint bij alle onderliggende gegevensbronnen kan, is het mogelijk om de in SSAS gemaakt kubus op basis van een schedule te laten verversen. StreamInsight Na een verhaal dat met name het beheer van SQL Server omgevingen als focus had en een BI-gerelateerd stuk, sluiten we af met een meer developer geörienteerd onderwerp: Stream Insight: een platform om Complex Event Processing (CEP) applicaties te ontwikkelen.
Figuur 4 de master data repository TechNet Magazine
CEP is een techniek om events, en dus data te verwerken op het moment dat de events optreden. Dus in plaats van gegevens eerst in een database op te slaan en later queries op de gegevens los te laten, wordt de data geanalyseerd terwijl de events optreden, ofwel op het moment dat de data ontstaat. Click-stream analyse van een drukke website is een goed voorbeeld van waar dit toepasbaar is. De content van de site realtime aan de hand van deze gegevens aanpassen, en zo de manier waarop nieuwe gebruikers door de site heen klikken beïnvloeden, maakt de site veel toegankelijker en actueler. Voor een commerciële site kan dat omzetverhogend werken. In een CEP-applicatie worden events op het moment dat ze optreden direct aangeboden aan de CEP server. Binnen de applicatie is business-logica gedefinieerd in de vorm van LINQ-queries. In plaats van queries uit te voeren op (statische) gegevens, sturen we realtime data naar een ‘standing’-query. De resultaten van de query kunnen direct worden gebruikt in bijvoorbeeld dashboards om KPI-indicatoren aan te sturen. Daarmee creëren we een soort realtime BI. We kunnen deze nieuwe technologie dus in de BI-hoek plaatsen. Zoals gezegd is StreamInsight een platform om CEP-applicaties te ontwikkelen. U moet daarbij wel .NET-programmeren en LINQ beheersen. Tot slot Bovenstaand verhaal is lang niet compleet. Hoewel SQL Server 2008 R2 geen volledig nieuwe release is, is er te veel nieuws om alles in dit artikel op een rij te zetten. Nog niet genoemd, maar wel cool, is bijvoorbeeld dat R2 volledig kan omgaan met de Live Migration feature van Windows Server 2008 R2 Hyper-V release. Duidelijk is denk ik nu wel, dat Microsoft met SQL Server 2008 R2 een stap voorwaarts zet. De BI-stack is met de verbeterde Reporting Services en met PowerPivot aan de front-end sterk verbeterd. Beheer in multi-server omgevingen wordt makkelijker. En met StreamInsight begeeft Microsoft zich voor het eerst op de markt van CEP-applicaties. Kortom, veel nieuwe zaken waarmee we onze SQL-kennis kunnen aanscherpen en updaten. ■
Peter ter Braake is sinds 1996 werkzaam in de ICT. Sinds 2002 richt hij zich volledig op SQL Server, zowel op de database engine als op de BI onderdelen van SQL Server. Twee jaar geleden is Peter voor zichzelf begonnen. Hij werkt nu als freelance trainer/consultant op het gebied van SQL Server.
juli 2010
53
