Tuning SQL Server 2012 for

Tuning SQL Server 2012 for Tuning SQL Server 2012 for - Start

Added : 2018-11-10 Views :8K

Download Presentation

Tuning SQL Server 2012 for




Download Presentation - The PPT/PDF document "Tuning SQL Server 2012 for" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.



Presentations text content in Tuning SQL Server 2012 for

Slide1

Tuning SQL Server 2012 for SharePoint 2013 Jump Start

Bill Baer | Senior Product Marketing Manager, Microsoft

Brian Alderman | Chief Executive Officer |

MicroTechPoint

Slide2

Introduction

Bill Baer is a

Senior Product Marketing

Manager and Microsoft Certified Master for SharePoint in the SharePoint product group in Redmond, Washington; having previously worked at Hewlett-Packard Bill Baer has a proven background in infrastructure engineering and enterprise deployments of SharePoint Products and Technologies. While at Hewlett-Packard Bill Baer was awarded the MVP award for his contributions in the Technology Solutions Group, now known as HP Enterprise Business, which encompasses server and storage hardware, technology consulting, and software sales.

Twitter @williambaerLinkedIn /billbaerTechNet /b/wbaer

Bill Baer (ˈ

bɛər

)

Senior Product Marketing Manager

SharePoint Microsoft Corporation

Slide3

Introduction

Brian

has been focused on helping IT Pros and DBAs better understand core Microsoft technologies for over 25 years. As an industry-recognized consultant, author and conference speaker, Brian’s expertise and designs range across Microsoft operating systems, Active Directory, SQL Server, and SharePoint. A frequent presenter at SharePoint Conferences around the world, he has authored or contributed to several SharePoint and other technical books, and is a MCSE, MCT

, MCDBA, and MCITP. Brian has a BS and MS in Computer Information Systems where he graduated summa cum laude from Regis University of Colorado Springs and lives in Scottsdale, AZ where he enjoys playing golf year round and traveling around the world.Twitter @brianaldermanLinkedIn /brianaldermanBlog

http://brianalderman.wordpress.comBrian AldermanChief Executive OfficerFounder of MicroTechPoint

Slide4

Course Topics

Tuning SQL Server 2012 for SharePoint 2013 Jump Start

01 | Key SQL Server and SharePoint Server Integration Concepts (50 minutes)

Dedicated Server or Instance; SQL Database & database files; ldf to mdf checkpoint process; Recovery model (Simple vs. Full)02 | Best Practices: SQL Server Database Settings (50 minutes) Model Database; TempDB (Location);

Initial size, Autogrowth, and Collation; Recovery model03 | Server Settings for SQL Server (50 minutes) Default Database File Locations; Memory Settings; MAXDOP04 | SQL Server and SharePoint Availability (50 minutes) Avoiding large log files; Backups and Location of Backups; SQL Server 2012 AlwaysOn; Best Practices

Slide5

Setting Expectations

Experienced SharePoint Administrators and/or

SQL Server Database Administrators

Professional SharePoint experience; working knowledge of SQL ServerSuggested Prerequisites/Supporting MaterialHands-on experience with a Microsoft Learning Partner recommendedQuerying Microsoft SQL Server 2012 (course 10774)Administering Microsoft SQL Server 2012 Databases (course 10775)

Slide6

Join the MVA Community!

Microsoft Virtual Academy (MVA)

Free Online Learning Tailored for IT Pros and Developers

Over 1M Registered UsersUp-to-date, Relevant Training on Several Microsoft Products

Slide7

Course Topics

Tuning SQL Server 2012 for SharePoint 2013 Jump Start

01 | Key SQL Server and SharePoint Server Integration Concepts (50 minutes)

Dedicated Server or Instance; SQL Database & database files; ldf to mdf checkpoint process; Recovery model (Simple vs. Full)02 | Best Practices: SQL Server Database Settings (50 minutes) Model Database;

TempDB (Location); Initial size, Autogrowth, and Collation; Recovery model03 | Server Settings for SQL Server (50 minutes) Default Database File Locations; Memory Settings; MAXDOP04 | SQL Server and SharePoint Availability (50 minutes) Avoiding large log files; Backups and Location of Backups; SQL Server 2012

AlwaysOn

; Best Practices

Slide8

Module Agenda

SQL Server Deployment Options & Database Types

Understand SQL Server and SharePoint Integration

Schema Overview, Database Structure, Schema RestrictionsSharePoint Database DescriptionsFarm Configuration and Central AdministrationContent DatabasesService Application Databases

Slide9

SQL Server Deployment Options & Database Types

Slide10

Deployment

Multiple Instances of SQL Server on One Physical Server

One Default Instance and Multiple Named Instances

Create SQL Server Alias for SharePoint SQL Server InstanceEach Instance is Managed IndividuallyShare SQL Server Management ToolsEach Instance Shares Server Resources (RAM, CPU)

Slide11

SQL Server Database Types

System Databases:

Master – Configuration database of SQL Server

Msdb – SQL Server automationTempdb – Temporary storage areaModel – Template for all new databasesUser Databases:All Web app databasesAll Service app databasesAll other non-system databases

Slide12

Understand SQL Server and SharePoint Integration

Slide13

SQL Server and SharePoint Integration

93.8% of SharePoint content stored in SQL Server

Farm Configuration information

stored in configuration dbCentral Administration content stored in own content dbMost Service Applications have at least one content dbAll Web Apps have at least one content dbDuring SQL Server installation, Set Default Collation Setting to Latin1_General_CI_AS_KS_WS

Slide14

SQL Server and SharePoint Integration

Farm has

s

everal databases; >20 if spousal installationSite Collections only reside in one databaseContent database contains multiple site collections (2,000 Default Setting)If Site Collection > 100GB store in own content databaseSoft limit maximum size <= 200 GBUse SharePoint to control size of content database Quota Templates Maximum Number of Site Collections

Slide15

SQL Server Management Studio

Slide16

Schema Overview, Database Structure, Schema Restrictions

Slide17

Database Structure

Database

Data File

.MDF (1).NDF (0-n)Log File.LDF (1-n)

Latin1_General_CI_AS_KS_WS

Slide18

Content Database Basic Tables

Name

Description

FeaturesTable that holds information about all the activated features for each site collection or site.SitesTable that holds information about all the site collections for this content database.WebsTable that holds information about all the specific sites (webs) in each site collection.UserInfoTable that holds information about all the users for each site collection.GroupsTable that holds information about all the SharePoint groups in each site collection.RolesTable that holds information about all the SharePoint roles (permission levels) for each site.

All ListsTable that holds information about lists for each site.GroupMembershipTable that holds information about all the SharePoint group members.AllUserDataTable that holds information about all the list items for each list.AllDocsTable that holds information about all the documents (and all list items) for each document library and list.RoleAssignmentTable that holds information about all the users or SharePoint groups assigned to roles.Sched SubscriptionsTable that holds information about all the scheduled subscriptions (alerts) for each user.

ImmedSubscriptions

Table that holds information about all the immediate subscriptions (alerts) for each user.

Slide19

Pages

Fundamental Unit of Data Storage in SQL Server

8 KB of Data Which Can be Index or Data Related, Large Object Binary (LOB’s, e.g. BLOB) etc...

Slide20

Pages cont...

Page

Page Header

Data Row 1

Data Row 2Data Row 31

2

3

Data rows are inserted serially immediately following the header.

A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page.

Slide21

Extents

Group of Eight (8) Consecutive Pages

Aligned on (8) Eight Page Boundaries or 64KB

Slide22

Schema Implications (Perceptions)

SharePoint Tables Too Wide, Wraps Rows

SharePoint Manages Own (NVP) Indexes

SharePoint Adds Force-Order, Query HintsMissing Indexes for Common OperationsExcessive Use of Dynamic QueriesNo SQL Referential Integrity OR Key ConstraintsDBCC with Data Loss Not SupportedMissing Integration of Back-up/Restore

Slide23

Supportability Constraints on Schema Modifications

Some Examples of Such Database Changes Include:

Adding Database Triggers

Adding Indexes or Modifying Existing Indexes Within Tables Adding, Modifying, or Deleting Primary or Foreign Key Relationships Modifying or Deleting Existing Stored Procedures Adding New Stored Procedures Making Modification to Database Schema Adding Tables to a Database of Products Listed in the "Applies to" Section Changing the Database CollationMore Information: http://support.microsoft.com/kb/841057

Slide24

Understanding Support Policies and Imposed Limitations

Single Data Platform

Web Content Management (WCM):

Predominantly READ / Structured Queries and SearchEnterprise Content Management (ECM): 80/20 READ/WRITE Distribution / Ad-hoc QueriesUpgrade and Patch ManagementRequires Consistency and IntegrityApplication Logic Expectations on SchemaEnforced Integrity and Constraints

Slide25

SharePoint Database Descriptions

Slide26

Database Descriptions

23 Unique Databases Created in a Complete SharePoint Server 2013 Installation

Distributed Light > Heavy IO and Scale Up/Out Options

Slide27

Configuration Database

Single Database: Stores Farm Configuration Data, Solutions, and Farm Specific Settings

Default Name

SharePoint_ConfigCapacity PlanningScale Up / 0-5GBIO PatternRead (90/10)

Slide28

Central Administration Content

Content Database for Central Administration

Default Name

SharePoint_AdminContent_<GUID>Capacity PlanningScale Up / 0-2GBIO PatternRead (90/10)

Slide29

Content Databases

Stores all Site Content, Documents, Files, and Data

Default Name

WSS_ContentCapacity PlanningScale Up, Scale Out / 0-16TBIO PatternRead/Write (80/20)

Slide30

UPA: Profile Database

Stores and Manages Users and Social Information

Default Name

User Profile Service Application_ProfileDB_<GUID>Capacity PlanningScale Up, Scale Out / 0-100GBIO PatternRead/Write (90/10)

Slide31

UPA: Synchronization Database

Stores Configuration and Staging Data Used During Profile Synchronization

Default Name

User Profile Service Application_SyncDB_<GUID>Capacity PlanningScale Up, Scale Out / 0-100GBIO PatternRead/Write (50/50)

Slide32

UPA: Social Tagging Database

Stores Social Tags, Notes, and Ratings

Default Name

User Profile Service Application_SocialDB_<GUID>Capacity PlanningScale Up, Scale Out / 0-100GBIO PatternRead (80/20)

Slide33

Search: Administration Database

Stores Search

A

pplication Configuration and ACL for Crawl ComponentDefault NameSearch_Service_Application_DB_<GUID>Capacity PlanningScale Up, Scale Out / 0-10GBIO PatternRead (80/20)

Slide34

Search: Analytics Reporting Database

Stores Results for Usage Analysis Reports

Default Name

Search_Service_Application_AnalyticsReportingStoreDB_<GUID>Capacity PlanningScale Out / 0-200GBIO PatternWrite (90/10)

Slide35

Search: Crawl Database

Stores

S

tate of Crawled Data and Crawl HistoryDefault NameSearch_Service_Application_CrawlStoreDB_<GUID>Capacity PlanningScale Out / 0-200GBIO PatternRead (90/10)

Slide36

Search: Link Database

S

tores Information Extracted by Content Processing and Click-Through Information

Default NameSearch_Service_Application_LinkStoreDB_<GUID>Capacity PlanningScale Out / 0-200GBIO PatternWrite (90/10)

Slide37

Summary

SharePoint Deployment Should Have Dedicated Instance of SQL Server

Unique SharePoint Database Schema Does Not Support Modification

Several Databases Created for Web Apps and Service Apps with Different IO Impact

Slide38

Slide39

Appendix A

SharePoint Foundation 2013 and SharePoint Server 2013 Database Descriptions

Slide40

Apps: App Management Database

Stores App Licenses and Permissions

Default Name

App_Management_<GUID>Capacity PlanningScale Out / 0-10GBIO PatternRead (80/20)

Slide41

Apps: Apps for SharePoint Database

Stores Information About Apps

for SharePoint and Access Apps

Default NameApps_<GUID>Capacity PlanningScale Up / 0-10GBIO PatternRead (80/20)

Slide42

Secure Store Service Database

Stores App Licenses and Permissions

Default Name

Secure_Store_Service_DB_<GUID>Capacity PlanningScale Up / 0-10GBIO PatternRead (80/20)

Slide43

Usage Database

Stores Health Monitoring and Usage Data

Default Name

WSS_LoggingCapacity PlanningScale Up / 0-500GBIO PatternWrite (80/20)

Slide44

Subscription Settings Service Database

Stores Features and Settings for Hosted Customers

Default Name

SettingsServiceDBCapacity PlanningScale Up / 0-50GBIO PatternRead (90/10)

Slide45

Business Data Connectivity Database

Stores External Content Types and Objects

Default Name

Bdc_Service_DB_<GUID>Capacity PlanningScale Up / 0-10GBIO PatternRead (90/10)

Slide46

Project Server 2013 Database

Stores Data for Project Web App Sites

Default Name

ProjectWebAppCapacity PlanningScale Up / 0-100GBIO PatternRead (80/20)

Slide47

PowerPivot Service Database

Stores Data Refresh Schedules and

PowerPivot

Usage DataDefault NameDefaultPowerPivotServiceApplicationDB_<GUID>Capacity PlanningScale Up / 0-100GBIO PatternRead (80/20)

Slide48

PerformancePoint Services Database

Stores Temporary Objects

and

Persisted User Comments and SettingsDefault NamePerformancePoint Service _<GUID>Capacity PlanningScale Up / 0-100GBIO PatternRead (80/20)

Slide49

State Service Database

Stores Temporary

S

tate Information for InfoPath Forms Services, Exchange, Visio Services, and Chart Web PartDefault NameSessionStateService_<GUID>Capacity PlanningScale Out / 0-100GBIO PatternRead (80/20)

Slide50

Word Automation Services Database

Stores

I

nformation About Pending and Completed Document Conversions and UpdatesDefault NameWordAutomationServices_<GUID>Capacity PlanningScale Up / 0-10GBIO PatternRead/Write (50/50)

Slide51

MMS: Managed Metadata Database

Stores Managed Metadata

and

Syndicated Content TypesDefault NameManaged Metadata Service Application_Metadata_<GUID>Capacity PlanningScale Out / 0-100GBIO PatternRead (80/20)

Slide52

MMS: Taxonomy Database

Stores Hierarchical Structure of Terms Used

for

Tagging Content and Building Site CollectionsDefault NameManaged Metadata Service_<GUID>Capacity PlanningScale Up / 0-10GBIO PatternRead (80/20)


About DocSlides
DocSlides allows users to easily upload and share presentations, PDF documents, and images.Share your documents with the world , watch,share and upload any time you want. How can you benefit from using DocSlides? DocSlides consists documents from individuals and organizations on topics ranging from technology and business to travel, health, and education. Find and search for what interests you, and learn from people and more. You can also download DocSlides to read or reference later.
Youtube