Managing SQLserver for the reluctant DBA

Managing Microsoft SQL Server:For the “Reluctant” DBADon JonesSenior Partner and TechnologistConcentrated Technology, LLCRequired SlideSESSION CODE: DAT203

This slide deck was used in one of our many conference presentations. We hope you enjoy it, and invite you to use it within your own organization however you like.For more information on our company, including information on private classes and upcoming conference appearances, please visit our Web site, www.ConcentratedTech.com. For links to newly-posted decks, follow us on Twitter:@concentrateddon or @concentratdgregThis work is copyright ©Concentrated Technology, LLC

IntroductionsMe: Don Jones, Concentrated TechnologyMicrosoft MVP Award recipientContributing Editor, TechNet MagazineAuthor of 45+ IT booksBlogger at http://ConcentratedTech.comYou: The “Microsoft Person” in your environmentNot primarily concerned with supporting Microsoft SQL Server……but more or less forced to do so anyway

AgendaPeeking Under the Hood: How SQL Server WorksBackup and Restore OperationsAbout the Query OptimizerIndex Maintenance and TuningKey Database Configuration OptionsSQL Server Security ModelKey Server Configuration OptionsHigh Availability and Replication Options

How SQL Server Works, Part 1SQL Server stores data in 8KB chunks called “pages”It always deals with data in these 8KB chunksGenerally speaking, all data for a single table row must fit onto a single pageIt’s actually possible for certain types of data to spread across multiple pagesSometimes, the page can contain a pointer to a file on disk – the FILESTREAM data typeThe pages live in one or more files (.MDF, .NDF) on disk, but are always read or written in 8KB units

How SQL Server Works, Part 2When a query is issued that results in data being changed, added, or deleted:SQL Server makes a note of the query in its transaction logThe database engine determines what page(s) are affected by the changeThe affected pages are loaded into server memoryThe changes are made in memoryAt this stage, the changes have been completed only in RAM.

If something else reads those pages, they’re already handy in memory.How SQL Server Works, Part 3Eventually, the modified pages are written to disk.When this happens, SQL Server “checks off” the associated query in the transaction log.In the event of a sudden failure:When SQL Server restarts, it looks to see if any “un-checked” queries are in the log.If so, it “re-plays” those queries to bring the database up to speed.If you have the transaction log, you can recover the data!

SQL Server BackupsThree types:Full – Backs up the entire database and removes (“truncates”) all “checked” entries from the transaction logDifferential: Only what’s changed since the last Full; also truncates the logTransaction log: Only grabs the current transaction log; truncates the active log afterward

Restoring a DatabaseStart with the most recent Full backupLeave the database in “restore mode” if you have other filesApply the most recent differentialStay in “restore mode” if you have more filesApply any transaction logs since the differential was madeWhen finished, allow recovery to begin

Maximum amount of data at-risk: Any changes made since the most recent transaction log backup

Moral: Frequent T-Log backups!Configuring Backups, Performing RestoresDEMO

IndexesTwo types:ClusteredEvery table has one, even if it’s just on the row ID number.Governs the order in which data is logically storedShould be on whatever column is used most for lookups or table joinsNon-ClusteredEvery table can have zero or moreActs as a literal index, keeping entries in order by the indexed column and pointing to the full data rowA “covering” index is one that contains entries for every column being queried

Indexes: Pros and ConsProsIndexes can speed up the time it takes to find dataAlso speed up sort timesIdeally, index on all columns that are frequently used in a WHERE or ORDER BY clauseConsIndexes slow down data add/change/delete operations, because indexes must also be updatedIdeally, index nothingThe reality is a balancing act: Building indexes on columns that deliver the most benefit, with the least downside

Index Problem: FragmentationOccurs when index pages become full and data needs to be inserted into the middleA “page split” occurs to make room for the new entry, causing the pages to be “out of order” – this decreases performanceNEW!AndrewAndrewsBatistaBarbaraCharlesCharlieFrankGaryGrossmanHughInaJackJacksonJonesKimberlyDonavanEricErinDerek

Fixing FragementationReorganizing or Rebuilding the index will put the entries and pages back into the correct order, improving performanceYou can specify a “fill factor” that leaves empty space for new entries without splitting pages as oftenHowever, emptier pages mean SQL Server has to work harder to read the same amount of data – so this is a balancing act

Rebuilding and Reorganizing IndexesDEMO

Index TuningThe indexes that made sense for a database on day 1 might not make sense after it has been in use for some timeYou can use SQL Profiler to capture real-world, representative query traffic……and feed that to the Database Engine Tuning Advisor to get recommendations on index improvementsThe Advisor can even help you implement those changes that you decide to proceed with

Using the Database Engine Tuning WizardDEMO

Key Database Options and Best PracticesAllow statistics to Auto-Create/UpdateThis makes sure the Query Optimizer knows which indexes exist and what kind of condition they are inConsider disabling auto-growth, or at least monitor itAuto-Grow can consume time; better to manually size the database files appropriatelyDisable Auto Close except on infrequently-used databasesClosed databases incur a performance hit when someone queries them since SQL Server has to open the file

Configuring Key Database OptionsDEMO

SQL Server Security ModelLogin: Map to a Windows user/group, or an in-server credential; gets you access to the serverServer Role: Contains logins, and defines server-wide privilegesDatabase user: Maps to a login, and gets you into a databaseDatabase Role: Contains database users, and defines in-database privilegesCustom Database Role: Same as the built-in ones, but you define the permissionsApp Role: Shortcut login+databaseuser+permissions designed to be activated by an application that defines its own security layer for the data.

Server OptionsAuthentication Mode: “Windows” or “Windows+SQL Server”Memory and Processor options (typically, leave alone)InstancesEach “instance” is an independent installation of SQL ServerHas its own “server-wide” options (“instance-wide” is a better term)Default instance: Connect using server nameOther instances: Connect using SERVER\INSTANCE formatReview list of services to see instances – each instance has its own SQL Server service

Configuring Key Server and Security OptionsDEMO

High Availability OptionsLog ShippingDatabase MirroringWindows Clustering

Where to Set Up Database MirroringDEMO

Replication OptionsSnapshotTransactionalTransactional with Updating SubscribersMergeOff-Topic: Microsoft Sync Framework / Sync Services for ADO.NET

Final ThoughtsSQL Server Agent (and Jobs, Operators, and Alerts)Multi-Server Admininstration

Agent, Jobs, and Multi-Server AdministrationDEMO

ConclusionA few basic admin skills can help you perform the most often-needed SQL Server tasksKeep in mind that the SQL Server Management Studio can connect to multiple servers for single-seat administration

Track ResourcesRequired Slide Track PMs will supply the content for this slide, which will be inserted during the final scrub. Resource 1Resource 2Resource 3Resource 4

ResourcesRequired SlideLearningSessions On-Demand & CommunityMicrosoft Certification & Training Resourceswww.microsoft.com/techedwww.microsoft.com/learningResources for IT ProfessionalsResources for Developershttp://microsoft.com/technethttp://microsoft.com/msdn

Related ContentRequired SlideSpeakers, please list the Breakout Sessions, Interactive Sessions, Labs and Demo Stations that are related to your session.Breakout Sessions (session codes and titles)Interactive Sessions (session codes and titles)Hands-on Labs (session codes and titles)Product Demo Stations (demo station title and location)

Required SlideComplete an evaluation on CommNet and enter to win!

© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Managing SQLserver for the reluctant DBA

More Related Content

What's hot

Viewers also liked

Similar to Managing SQLserver for the reluctant DBA

More from Concentrated Technology

Recently uploaded

Managing SQLserver for the reluctant DBA