brett bennett senior premier field engineer microsoft corporation

SQL Server For System Center 2012Santos MartinezPremier Field EngineerMicrosoft CorporationUD-B324

Brett BennettSenior Premier Field EngineerMicrosoft Corporation

Thinking About SQL for System Center?

SQL?

The System Center 2012 products have one thing in common…

...they all use Microsoft SQL Server!

Proper installation and configuration of Microsoft SQL Server is critical for System Center products.

Misconfiguration of SQL Server can cause issues:• High CPU• Memory shortages• Inbox/file backlogs• Workflow delays• Stale or inaccurate data in reports

Carefully consider and plan for the volume of data that System Center products generate and the load this will place on SQL server

Agenda1. SQL 20122. SQL Memory Settings3. Detecting SQL Memory Pressure4. Useful SQL-related Performance Monitor counters5. SQL AutoGrow Settings6. SQL Database Options7. SQL Clustering guidance8. Best Practices for System Center products

SQL 2012

• Increased performance and scalability• Powerview – SQL Reporting Services Add-in for

Sharepoint• Powerpivot - Excel Add-in• Powershell support to enable automation• AlwaysOn

SQL 2012: What’s new?

What is AlwaysOn?• Provides high availability and disaster recovery for SQL

server• Availability Groups for databases• Failover Cluster Instance for SQL server instances• Uses Windows Server Failover Clustering• Supported in several System Center 2012 products• Requires SQL 2012 Enterprise edition

SQL 2012 AlwaysOn

SQL Server 2012 AlwaysOn supported on:• App Controller• Operations Manager• Orchestrator• Service Manager• Virtual Machine Manager

SQL Server 2012 AlwaysOn not currently supported on:• Data Protection Manager• Configuration Manager

http://technet.microsoft.com/en-us/library/jj628198.aspx

SQL 2012 AlwaysOn & System Center 2012

SQL Memory

SQL MemoryBuffer Pool• Consists of 8kb pages• Data cache stored here• Execution plans that is less that

8kbSQL Engine• SQL components• Execution plan that is more than

8kb• SQL CLR (Common Language

Runtime) • Also called MemToLeave

13

Maximum Server Memory

Consider limiting SQL to 50%-80% of total memory if SQL will co-exist on server with another System Center product

example: one server running ConfigMgr primary site server & SQL database

Consider limiting SQL to 80%-90% if SQL server is dedicated

example: dedicated SQL server that only hosts databases or a SQL cluster

NOTE: Setting the Maximum and/or Minimum Server memory settings only controls the Buffer Pool Memory

SQL: Memory Settings

Minimum Server Memory• SQL does not allocate Minimum Server Memory at

start up • Grows until reaches Maximum• Once Buffer Pool hits Minimum Server Memory,

SQL will not release the memory

NOTE: Setting the Maximum and/or Minimum Server memory settings only controls the Buffer Pool Memory

SQL: Memory Settings

15

DEMOMemory SettingsSantos Martinez

• SQL Buffer Pool Memory Pressure happens when SQL does not have enough memory to hold data in memory

• Can result in excessive I/O• Check the following perfmon counters:

SQL: Buffer Manager -> Lazy writes/ Sec goes upSQL: Buffer Manager -> Page Life Expectancy goes downSQL: Buffer Manager -> Free Pages goes down

Detecting SQL Memory Pressure

• SQLServer:Buffer Manager ->Buffer cache hit ratio(>90-95%)->Free pages(>640)->Lazy writes/sec (<20)->Page life expectancy (>300)->Page reads/sec (<90)->Page writes/sec (<90)

• SQLServer:Memory Manager-> Target Server Memory (KB) (Target >= Total)-> Total Server Memory (KB) (Target >= Total)

SQL: Useful SQL Perfmon Counters

The SQLServer: Memory Manager: Target Server Memory (KB) perf counter tells how much memory SQL Server would like to use to operate efficiently

SQLServer: Memory Manager: Total Server Memory (KB) tells how much memory SQL Server is using

If Total Server Memory >= Target Server Memory, may indicate SQL Server is under memory pressure

Monitor SQL Performance

• CPU • Processor: %Processor Time – If 80-90% for a

sustained period, identify the process with Process: %Processor Time and determine if faster/more processors are needed

• Processor: % Privileged Time – If counter is consistently high and in-line with Physical Disk counters, disk sub-system is bottleneck

• Memory• SQLServer: Buffer Manager: Buffer Cache Hit

Ratio – less than 90% indicates memory pressure

• Memory: Pages/ sec – a high rate for this counter indicates excessive paging

Monitor SQL Performance

• Deploy the System Center Management Pack for SQL Server• Monitors SQL Server 2005, 2008, 2008 R2 and SQL Server

2012• Monitors the database instances, the databases themselves

and the SQL server agents• Provides a wealth of data around performance, availability

and configuration.

http://www.microsoft.com/en-us/download/details.aspx?id=10631

Monitoring via Operations Manager

Database AutoGrow

If Autogrow setting is too small:• SQL spends significant processing time autogrowing the database• Each file grow in log file creates a Virtual Log File (VLF)• Excessive VLF may cause performance issues:• When VLF>250, performance will be impacted• When VLF >10000, SQL will take longer to bring up database

Additional information on VLFs available at http://support.microsoft.com/kb/2455009

SQL: AutoGrow

If Autogrow setting is too large:• During autogrow, SQL will write zeroes in the space that is grown.• During autogrow, queries involving updates, inserts, deletes may be

blocked.• Can enable Instant File Initialization for the DB but possible security

risk as deleted data may still be viewable• Instant File Initialization does not work for log files

Additional information available at http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

SQL: AutoGrow

DEMOAuto GrowBrett Bennett

• TempDB database typically heavily used• Consider pre-sizing the data file, don’t rely on autogrow. • If you need to autogrow, use fixed amount to avoid frequent

growth intervals, which affects performance (10% of tempdb size good starting point)

• Divide TempDB into multiple files, one data file per CPU, and each file should be set to the same size

• Additional information here http://msdn.microsoft.com/en-us/library/ms175527.aspx

TempDB

• Isolate transaction log files from database files at disk level• Recommended to have at least 20% free disk space on all

disk drives. Important for performance and to prevent running out of space

Storage

Common customer question: Should I use physical or virtual servers for my SQL Server and/or System Center 2012 servers?

• SQL Server and System Center 2012 supported on physical and virtual servers

• More and more customers using virtual servers for their SQL and System Center infrastructure. Benefits include:• Reduced server hardware costs• Reduced data center costs• Easier to move virtual servers from one host to another • Easy to add/remove memory, CPU, etc. to/from guest OS • Snapshot technology allows rollback in case of issues, upgrade failure, etc.

Physical or Virtual Servers?

If you use virtual servers:• Verify host server can handle the additional load • Slow disks, low memory and high CPU utilization can have major

impact on SQL Server and System Center products• Avoid using dynamic virtual drives (VHDs) as they can decrease

performance• Allocate at least two virtual CPUs for the instance of SQL Server• Do not allocate more virtual CPUs than the number of available

logical CPUs

Ensure that recommended memory/CPU/disk requirements are at least met, if not exceeded – don’t reduce resources because it’s a VM

Physical or Virtual Servers?

Common customer question: For System Center 2012 product X, should I use local or remote SQL server?

The answer: It depends!

SQL: Local or Remote

Look at product specific planning guidance. Some links below:

• Configuration Manager: http://technet.microsoft.com/en-us/library/hh846235.aspx

• Service Manager: SM Sizing Helper Tool, part of SM job aids http://www.microsoft.com/en-us/download/details.aspx?id=13605

• Operations Manager: OM 2012 Sizing Helper Tool http://blogs.technet.com/b/momteam/archive/2012/04/02/operations-manager-2012-sizing-helper-tool.aspx

SQL Server Collation

• SQL Server Collation defines how SQL handles data in the database (indexing, sorting, storing, etc)

• Selecting the correct collation type for the System Center products is important

• Choosing wrong collation type could cause various issues around manipulating data, searching data, etc

Collation

• There are two types of collation – SQL Server collations and Windows collations

• In past System Center products only supported SQL_Latin1_General_CP1_CI_AS collation (default when install SQL Server on English US Windows Server

• Trend in SQL is to use Windows collations, phase out SQL collations

• System Center products starting to make that change

Collation

Great blog article from Travis Wright on SQL collation requirements for System Center 2012: • http://blogs.technet.com/b/servicemanager/archive/2

012/05/24/clarification-on-sql-server-collation-requirements-for-system-center-2012.aspx

Collation

SQL Clustering

What is needed?• 2 Nodes• Minimum of 16 GB of RAM for each Node• 2 Network Cards, Internal Network and Heartbeat Network• SAN Storage

• Is the building process Easy?

Cluster: Building the Cluster

Common customer question: Can I use my existing SQL cluster for System Center 2012 product X?

The answer: It depends!

SQL Clustering

• If SQL cluster already busy hosting other heavily utilized databases, adding the System Center databases could be bad idea

• If SQL cluster has adequate resources then may be an acceptable option

SQL Clustering

And then the follow-up question: Can I use one SQL Server/cluster for some/all of the System Center 2012 products?

The answer: Maybe! Depends on the System Center products being used

SQL: One for All?

Assuming you had a SQL Server/cluster with enough resources to handle the demand, one issue you may run into relates to the SQL requirements:• Some System Center 2012 products need dedicated SQL

Reporting Services instances (Operations Manager, Service Manager)

• Some products do not support clustering the database (DPM)• Some products do not support SQL 2012 AlwaysOn• Some products may have different collation requirements• Some products may have specific version or cumulative

update requirements

Review the specific SQL requirements for each System Center 2012 product you will use to determine if possible

SQL: One for All?

DEMOThe One for All ClusterSantos Martinez

SQL Server Resource Properties->Policy• Ensure the option “If restart is unsuccessful, fail over all resources in

this service or application” is enabled. • Ensure failover option is disabled for SQL Agent• By default, SQL Resource will try to restart before trying to failoverSQL Server Service Properties ->Failover• Ensure Maximum Failures in the specified period is set correctly• By default, the value is n-1 where n is the number of nodes• If set to one, SQL will never failover since the cluster will try to restart

at first failure and only failover at second failure

Cluster: Restart Settings

43

Some System Center 2012 Best Practices for Databases

Configuration Manager

• In Configuration Manager 2012 the Rebuild Indexes Site Maintenance task is disabled by default. Consider enabling to run once per week or use a SQL maintenance plan

• Enable the backup and schedule to run daily (or use SQL maintenance plan, or DPM)

• Default SQL Recovery model for the Configuration Manager database is Simple. Not supported or recommended to change to Full

• Avoid enabling Auto Shrink for the Configuration Manager database, may cause transactions to be blocked and may cause index fragmentation

Configuration Manager

DemoMaintenance PlanBrett Bennett

Operations Manager

• Operations Manager activity mostly consists of data writes, not reads

• Factors that can affect this activity are:• Number and frequency of performance collection rules• Number of events collected and alerts generated per day• Discovery frequency• Configuration changes (importing MP’s, tuning MP’s, etc.)

Operations Manager

• Operations Manager databases require high performance storage; consider RAID 1+0 (aka RAID 10)

• Separate data file and transaction logs onto separate physical volumes

• Use battery-backed write-caching disk controller for Operations Manager database servers; configure 100% write caching

Operations Manager

Service Manager

• Service Manager Data Warehouse stores data for reporting and analysis purposes

• The Service Manager Data Warehouse includes 3 databases:• DWStagingAndConfig• DWRepository• DWDataMart

• The Extract/Transform/Load (ETL) process handles data movement from Service Manager database (aka CMDB) thru the Data Warehouse databases

• DWDataMart optimized to deliver reporting data quickly and efficiently

• All report data is obtained from the DWDataMart database

Service Manager Databases

S&C REP

Host all 3 Data Warehouse databases on same SQL server

OR

Place DWStagingAndConfig and DWRepository databases on one SQL server and DWDataMart database on another SQL server. DWStagingAndconfig and DWRepository db’s must be on same SQL server.Configure DWStagingAndConfig and DWRepository server RAID hardware for 50% Read/50% Write I/O Configure DWDataMart server for 100% Read I/O

Service Manager Databases

S&C REP DM

DM

Orchestrator

• Orchestrator database used heavily by Orchestrator components• Runbook servers• Management servers • Web components

• Poor database performance can affect Runbook execution

• Orchestrator database has two types of data: Configuration Data and Log Data

• Configuration Data typically not a significant source of database growth, but Log Data can cause significant database growth

Orchestrator

• Runbooks have two optional logging features• Common Published Data• Activity-specific Publisted Data

• Activity-specific published data logging can affect performance and increase database growth

• Activity-specific published data logging typically only enabled for debugging purposes

Orchestrator Log Data

Summary• Remember to evaluate the Minimum and Maximum SQL

memory settings for your environment• Adjust Autogrow settings appropriately• Begin reviewing the additional high availability that the SQL

2012 AlwaysOn feature provides• Consider amount and frequency of System Center 2012

data flow as you locate your SQL databases• Use Operations Manager to monitor your SQL servers

Evaluation

Complete your session evaluations today and enter to win prizes daily. Provide your feedback at a CommNet kiosk or log on at www.2013mms.com.Upon submission you will receive instant notification if you have won a prize. Prize pickup is at the Information Desk located in Attendee Services in the Mandalay Bay Foyer. Entry details can be found on the MMS website.

We want to hear from you!

Resources

http://channel9.msdn.com/Events

Access MMS Online to view session recordings after the event.

© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows 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.