TechEd 2005 Preconference
Evenals andere jaren is het SDN ook dit jaar weer te ontdekken tussen de vele bezoekers aan TechEd. Dit jaar voor de 2e achtereenvolgende keer in Amsterdam, en als ik juist geïnformeerd ben, dan verhuist het evenement voor de twee komende jaren naar Berlijn in Duitsland.
Zelf heb ik vandaag de gehele pre-conference bijgewoond, een dagvullend programma met alle nieuwe features van SQL Server 2005 (voorheen Yukon). Verdeeld over een vijftal sessies werd een publiek van rond de 300 bezoekers bijgepraat onder het motto ’Weinig slides en veel demo’. Microsoft had Kimberly Tripp bereid gevonden dit dagvullende programma te presenteren. Kimberly heeft vijf jaar bij Microsoft gewerkt en is inmiddels actief voor SQLSkills.com waar zij oprichter van is.
Het aardige van een onderwerp dat een hele dag behandeld wordt, is dat je verder komt dan alleen het aanstippen van onderwerpen. De eerste sessie werd dan ook compleet gebruikt om een overview te geven van alle onderdelen die vandaag besproken zouden gaan worden. Verder had Kimberly een ‘Lab’ document samengesteld, zodat alle demonstraties nog eens na te bouwen waren na afloop van de conferentie of op een van de vele lab PC’s die opgesteld stonden in de centrale communication-hall. Wij informeren nog of dit document en bijbehorende demo code ook beschikbaar mag worden gesteld aan alle SDN leden.
SQL Server 2005
SQL Server 2005 beta 2 is de laatste beta-versie die we kunnen verwachten. Er wordt nu naar de definitieve RTM release toe gewerkt. De builds die nog komen zijn CTP (Community Technology Preview) dumps, allemaal op basis van beta 2. Deze dumps worden aangeduid met een IDW (Internal Developer Workstation) nummer waarbij IDW14 de release 9.0.1116 was, de Team System release die je gratis via de SDN website bij Microsoft kon aanvragen.
Met RTM wordt de versie aangeduid die naar fabricage gaat (Release To Manufacturing) en hierop volgen eventueel nog hotfixes (alleen voor specifieke klanten) en in een later moment gevolg door een SP (Service Pack) Beta en de uiteindelijke SP-release.
Tools
Een hele serie tools uit SQL Server 2000 (7 stuks) zijn aangepast en samengevoegd tot 4 nieuwe tools: de SQL Management Studio, de Business Intelligence Management Studio, de Profiler en de SQL Configuration Manager. Onderstaande afbeelding geeft de herschikking van de tools aan.
SQL Management Studio: Uit de afbeelding blijkt onder andere dat de 2000 Query Analyzer en de Enterprise Manager nu samengevoegd zijn binnen SQL Management Studio. Een aanpassing die ik persoonlijk zeer kan waarderen!
SQL Configuration Manager: Deze tool voegt de client configuration tool, server configuration tool en een aantal configuratie-delen uit de Enterprise manager samen. Gebruik deze tool bij voorkeur boven alle afzonderlijke instellingen in onder andere ‘Services’. Deze tool houdt rekening met bijzondere omstandigheden zoals clustering en mirroring. Als er instellingen gewijzigd worden, zullen die door deze tool ook worden doorgezet in andere instances. Dat is iets wat een standaard Windows MMC niet kan.
Alle tools van SQL Server 2000 kunnen ook SQL Server 2005 instanties benaderen (vanaf SQL Server 2000 SP3). Programmatisch is alle 2005 functionaliteit aan te roepen via TSQL commando’s. Alleen wanneer er gebruik gemaakt wordt van 2005 via user interfaces in een SQL 2000 tool, zal niet alle functionaliteit toepasbaar zijn.
Installatie
Maak eerst een lokaal account aan waaronder SQL Server en bijbehorende services straks gaan draaien. Dit wordt een normaal (non-administrator) account. Gebruik van het ‘Local Service’ account is nogal riskant, andere services zouden immers ook gebruik kunnen maken van Local Service. Die hebben wellicht rechten aangepast van dit account waardoor SQL Server onnodig extra rechten krijgt. Mocht SQL Server vervolgens onverhoopt een keer gehackt worden, dan heeft die gebruiker overbodig veel rechten. Als voorbeeld voor de account-name gaf Kimberly ‘SQLService’. Tijdens de installatie van SQL Server 2005 worden aan dit acccount door de installatie extra rechten toegekend. Geef tijdens de installatie aan dat als authentication mode alleen ‘Windows authenticated’ is toegestaan.
Tip: In de praktijk blijkt nogal vaak dat de groep ‘Builtin\Administrators’ als eerste na een installatie wordt verwijderd. Windows gebruikers met Administrator rechten kunnen dan standaard niet meer bij de database komen. Stel daarom altijd een SA password in en noteer deze zorgvuldig. Als Windows Authentication niet meer mogelijk omdat Builtin\Administrator verwijderd is en er ook geen geldige gebruikers meer bestaan, dan kun je via de Windows registry de SQL Server configuratie aanpassen naar mixed mode authentication. Daarna kun je met SA altijd nog inloggen via SQL Login.
Een voordeel van de accounts Local Service en Network Service is wel dat deze accounts geen password mogelijkheid hebben. Het wachtwoord kan dus niet ‘per ongeluk’ veranderd worden, en evenmin kan het account geblokkeerd worden, maar vanwege bovenstaande argumenten is het desondanks toch erg af te raden om een van deze accounts te gebruiken. De rechten van deze accounts kunnen sterk variëren en op een Domain Controller computer heeft het Local System account zelf administrator rechten. Als SQL Server ‘gecompromitteerd’ wordt, dan heeft die gebruiker administrator rechten! Gebruik in ieder geval NOOIT het Local System account!
De instance naam van een SQL Server is niet veranderd ten opzichte van 2000, bijvoorbeeld MSSQL$DEV01. De directory naam is echter wel aangepast, die is nu gelijk aan de instance ID, standaard MSSQL.1 (MSSQL.). In SQL Server 2000 was dat C:\Program files\Microsoft SQL Server\MSSQL$Dev01\Data. Nu is dat C:\Program files\Microsoft SQL Server\MSSQL.1\Data geworden. De reden voor de nieuwe directory structuur is dat een installatie van SQL Server 2005 nu side by side (gelijktijdig) naast een bestaande SQL 2000 server uitgevoerd kan worden. Op het allerlaatste moment kunnen de 2000 databases dan worden overgenomen en kan de downtime beperkt worden tot enkele minuten. Kimberly merkte daarbij wel op dat Microsoft ondanks deze feature er de voorkeur aan geeft om SQL Server 2005 op nieuwe hardware te installeren, zodat side by side geen issue is.
SQL Surface Area Configuration manager (SAC)
Diverse SQL Server features staan standaard ‘uit’ sinds build IDW14. Dat kan de reden zijn dat een werkende SQL Server 2005 omgeving na de installatie van build IDW14 of later niet meer of slechts deels functioneert. Het idee achter dit besluit is dat de installatie standaard zo veilig mogelijk moet zijn. Allerlei features worden daarom standaard uitgezet en verschillende services worden ‘disabled’ in ‘Services’. Met deze tool kunnen diverse onderdelen geactiveerd worden, zowel Windows Services als SQL features.
Let op: Verschillende services worden niet alleen niet geactiveerd in ‘Services’, maar worden bovendien ‘disabled’. De services moet daarom eerst expliciet vrijgegeven worden, en pas daarna is de service te starten. Wanneer je dit niet door hebt, kun je lang zoeken waarom een service niet wil starten vanuit de SAC.
Een nieuwe feature die aan SQL Server 2005 is toegevoegd is DAC ondersteuning. DAC staat voor ‘Dedicated Administrator Connection’. Een server die niet meer benaderbaar is, kan via DAC (al dan niet op afstand) alsnog beheerd worden. DAC moet wel geactiveerd worden in de SAC! Er zijn diverse threads en resources binnen SQL Server speciaal gereserveerd voor deze feature. DAC is primair gebaseerd op SQLCMD, waarmee SQL Server via de commandline benaderd kan worden:
SQLCMD –S -A
De –A parameter geeft aan dat SQLCMD moet communiceren via DAC technology. Verder wordt er maximaal 1 connectie ondersteund via SQLCMD –A. DAC is ook toepasbaar vanuit de Management Studio; geef in dat geval voor de server naam de tekst ‘admin:’. Dat geeft aan dat Management Studio dient te communiceren via DAC.
In SAC is ook instelbaar welk soort mail transport systeem gebruikt mag worden. SQL Mail (Mapi gebaseerd) is nu vervangen door Database Mail (SQL Mail is overigens nog wel beschikbaar vanwege backward compatibility). Database mail vereist een geïnstalleerde SMTP component, zodat vermoedelijk gebruik gemaakt kan worden van CDO (Collaborative Data Objects).
Een laatste interessante feature in SAC is het kunnen blokkeren dan wel vrijgeven van het uitvoeren van commando’s via SQLCMD. Dat kunnen allerlei commando’s zijn, inclusief SQL script bestanden.
Niet bij veel gebruikers bekend maar toch … er kunnen commando’s uitgevoerd worden vanuit SQL Server
Tip: Niet bij veel gebruikers bekend maar toch … er kunnen commando’s uitgevoerd worden vanuit SQL Server zoals !!dir c:\ of erger nog, een Del- of Format-commando. Standaard worden commando’s in Management Studio niet uitgevoerd via SQLCMD. Om die reden werkt het !!dir c:\ commando uit bovenstaand voorbeeld standaard niet. Dit is alsnog mogelijk door in de opties in te stellen dat alle commando’s moeten worden uitgevoerd via SQLCMD: Options, Query Execution, Execute commandos by SQLCMD.
Security & troubleshooting
Zoals al gemeld in de paragraaf over installatie is het absoluut af te raden om de groep ‘Builtin\Administrators’ te verwijderen. Gebruikers met Administrator rechten kunnen dan standaard niet meer bij de database komen. Het verwijderen van deze groep is een van de vaakst gestelde vragen bij Microsoft PSS (Product Support Services). Let wel: als alle account info van een SQL database verloren is gegaan, dan kan de data in principe als verloren worden beschouwd!
Fast File Initialization (FFI)
SQL Server heeft een nieuwe feature met de naam Fast File Initialization. Databases die worden aangemaakt zijn normaliter ‘Zero Initialized’. Dat houdt in dat bij aanmaken van een database een bestandsomvang moet worden opgegeven en het bestand meteen deze omvang krijgt door hem op te vullen met nul-codes. Dat kost uiteraard veel tijd. Bij gebruik van FFI wordt dat opvullen niet gedaan. Onduidelijk is mij op dit moment nog wat de ‘penalty’ is die je hiervoor zou moeten betalen - je krijgt per slot van rekening niets voor niets - maar duidelijk is wel dat het aanmaken van een database vele malen sneller gaat. De FFI feature staat standaard uit, maar kan bij activeren een behoorlijke versnelling opleveren, voornamelijk bij het restoren van data. Vooral VLDB’s (Very Large Databases) zijn met FFI veel sneller aan te maken. FFI is alleen voor data files en alleen beschikbaar onder XP en Windows 2003. Verder moet de policy ‘Perform volume maintenance tests’ wel toegekend worden aan het account waaronder de SQL Server instance actief is; deze policy is standaard niet toegestaan. Door deze policy te activeren wordt deze feature vanzelf toegepast, je hoeft er geen extra handelingen voor uit te voeren.
Detach & Attach
SQL Server 2000 files kunnen in de SQL Server 2000 Enterprise Manager worden ‘detached’, waarna ze in 2005 weer ‘attached’ kunnen worden. De database wordt tijdens de attach in 2005 automatisch geconverteerd!
Let op: Na een attach in 2005 is die database daarna nooit meer in SQL Server 2000 te attachen. Zorg dus voor een backup van deze databases!
Als je werkt met script code, kun je de attach in 2005 zelfs uitvoeren vanuit de SQL Server 2000 Query Analyzer. Een voordeel van uitvoeren van een attach via scriptcode is dat de voortgang beter zichtbaar is; het systeem toont dan ook de voortgang van de verschillende conversie-fases.
Scripting
Tip: Er zijn nieuwe SQLCMD commando’s waaronder :ON ERROR EXIT.
Met dit commando in een script zal het script afgebroken worden, wanneer er een fout optreedt. Het grote voordeel is dat wanneer bijvoorbeeld de CREATE DATABASE mislukt, het script niet vervolgd wordt. Voorheen werder de overige commando’s in de huidige database worden uitgevoerd, en dat is vaak de Master database. Die laatste zou dan vol met foutieve objecten raken.
Het grote voordeel is dat wanneer bijvoorbeeld de CREATE DATABASE mislukt, het script niet vervolgd wordt
Er kunnen met :SETVAR nu ook variabelen gezet worden in SQL script code. Deze variabelen kunnen echter ook leeg gelaten worden, waarna ze via SQLCMD met de parameter –v alsnog toegekend kunnen worden. Op deze manier is script code te schrijven die database- en server-onafhankelijk is, als je de instance en database naam via variabelen toekent.
Undocumented command: dbo.xp_regread
Met dit commando kan de registry worden uitgelezen. Een nadeel van de nieuwe bestand- en directory-structuur van SQL Server 2005 is dat de naam van de instance niet meer gelijk is aan de directory waar de instance staat (zie paragraaf ‘Installatie’). De path info is echter wel beschikbaar in de registry. Met bovenstaand (undocumented) commando kan de registry worden uitgelezen en kan daaruit de fysieke plek van een database bepaald worden.
Het enige probleem dat daarbij optrad, is dat variabelen in een SQL commando slechts bestaan gedurende de query zodat het uitlezen van de variabele op de volgende regel niet mogelijk is (een probleem waar veel SQL ontwikkelaars tegenaan lopen). Kimberly liet aan handige truc zien hoe je die variabele toch kan uitlezen. Zie hiervoor het SQL script backup20000restore2005.sql.
Verder zijn er verschillende nieuwe omgevingsvariabelen geïntroduceerd:
- SET SQLCMDINI= Met deze environment setting kan een script worden aangegeven dat standaard uitgevoerd moet worden bij het starten van SQLCMD.
- SET SQLCMDSERVER= Standaard SQL Server instance en database name; voorkomt gebruik van –S parameter
- SET SQLCMDEDITOR= De standaard te starten editor bij het ingeven van het :ed commando in SQLCMD
De functietoets F7 geeft in een standaard Dos Shell een overzicht van de laatste 10 opgegeven commando’s. In SQLCMD laat F7 de laatste 10 SQL commando’s zien, ook als SQLCMD afgesloten geweest is!
SQL Server 2005 Management Studio
Let op: In SQL Server 2005 bestaat een ‘Open Table’ functie. In SQL Server 2000 kon je een beperking geven op het aantal rijen dat geretourneerd moest worden. In SQL Server 2005 worden standaard alle rijen opgehaald en dat is niet vooraf te beperken. Klik daarom op ‘Stop Retrieving’ zodat je (SQL Server) werkgeheugen niet volloopt!
Template Explorer
In de SQL Server Management Studio kan een Template Explorer gestart worden. Hierin staan allerlei scripts voor veelgebruikte handelingen met databases. Er worden allerlei soorten scripts meegeleverd voor database acties. Het enige wat in een vanuit een template aangemaakt SQL Script nog ingevuld moet worden, is een aantal variabelen waaronder de naam van de eventueel aan te maken stored procedure.
Summary Window
Sinds build IDW15 is een ‘Report’ button aanwezig waarmee allerlei statistieken over de SQL Server instance en alle objecten daarbinnen weergegeven kunnen worden. Voorwaarde is wel dat de database in 9.0 (2005) compatibility mode staat. De rapportages geven zowel grafisch als tekstueel allerlei info weer over de databases, maar ook over historisch performance van het systeem, benodigde tijdsduur voor het uitvoeren van queries, etc., etc.
NB: Deze feature is alleen beschikbaar als er connectie gemaakt is met de server.
Bijzonder fraai aan partitioning is dat je tijdens het werken met een tabel niet merkt dat de data in werkelijkheid afkomstig is uit verschillende achterliggende filegroups
Partitioning
Partitioning is het splitsen van data over fysiek verschillende tabellen (partities). Dat vindt geheel automatisch plaats op basis van regels die vooraf gedefinieerd moeten worden. Een regel kan bijvoorbeeld zijn dat een aantal jaren wordt opgegeven van een verkoopdatum-veld in een tabel waarbij aan elk jaar een specifieke filegroup wordt gekoppeld. Bijzonder fraai aan partitioning is dat je tijdens het werken met een tabel niet merkt dat de data in werkelijkheid afkomstig is uit verschillende achterliggende filegroups. Ook bij het muteren van data zorgt SQL Server 2005 zelf voor het verspreiden van de gegevens naar de juiste filegroups.
Partitioning vereist wel wat extra administratief werk voor de DBA en wordt om die reden pas geadviseerd bij tabellen boven de 10 Gb. Het hangt immers niet af van de omvang van de database maar van de omvang van de tabellen daarin. Maak een filegroup aan per ‘partitie’ (bijvoorbeeld per maand) en maak vervolgens een schema aan.
Partities zijn bovendien herbruikbaar: meerdere tabellen kunnen hetzelfde partitie-schema gebruiken en de tabel-data in diezelfde partities opslaan.
Profiler
De profiler is een tool die qua naamgeving één op één overgenomen is vanuit SQL Server 2000. Er zijn echter wel verschillende verbeteringen aangebracht.
Server side profiler: Alle events die plaatsvinden op een SQL server kunnen ook afgevangen worden in trace-files die later door de profiler ingelezen kunnen worden. Op die manier kun je met de profiler behalve client events ook een SQL Server zelf analyseren.
In de 2005 profiler is een heel interessant event toegevoegd, namelijk XML ShowPlan. Door het registreren van dit event kun je live zien hoe een query uitgevoerd wordt, waarna je elke afzonderlijke XML ShowPlan transactie kunt opslaan als een XML bestand (Event Extraction Settings tabblad in SQL Profiler). Erg mooi is dat je dit XML SQL bestand weer kunt openen in de SQL Server Management Studio, zodat je inzicht krijgt in hoe queries in een live situatie verlopen zijn!
Belangrijke Profiler events zijn o.a. XML ShowPlan, Deadlock Graph, en Disk Queue Length.
In de 2005 profiler is een heel interessant event toegevoegd, namelijk XML ShowPlan
Met Perfmon kan een performance log geanalyseerd worden. Die wordt gemaakt vanuit de Windows Performance routine (Counter Logs). De resultaten van Performance en een trace uit de profiler kunnen daarna gecombineerd worden in Perfmon, zodat er een grafische weergave ontstaat van de performance waarbij op elk gedeelte van de performancegrafiek de lopende acties uit de trace-statistieken worden weergegeven. Je krijgt hiermee een vergelijking tussen machine performance en de werkelijke processen die uitgevoerd worden. Ideaal om de piekbelasting op je SQL Server te analyseren en te verhelpen!
Options
Als laatste: neem eens de tijd om door alle ‘Options’ heen te lopen in de Management Studio software. Het kan een goede oefening zijn om alle opties uit SQL Server 2000 op te zoeken in 2005. Je komt daarbij veel nieuwe mogelijkheden tegen. Een erg handige nieuwe feature is de instelling dat de namen van velden kunnen worden meegenomen bij het kopiëren van data naar bijvoorbeeld Excel. Dat was niet mogelijk in SQL Server 2000.
Conclusie
Kimberly sloot de dag af met een uur lang Questions & Answers. Deze mogelijkheid werd door verschillende bezoekers aangegrepen om een aantal interessante cases voor te leggen en een levendige discussie te voeren. Samengevat is SQL Server 2005 de zeer interessante en lang verwachte opvolger van SQL Server 2000. De release biedt volop mogelijkheden voor VLDB’s en voorziet in tal van mogelijkheden om een live systeem goed te monitoren en te beheren. Ook de integratie met Visual Studio 2005 en het .Net framework opent een heel scala van nieuwe mogelijkheden.