1 / 30

SCUG.be

SCUG.be. Setting up & Configuring SQL Server to support your SCCM & SCOM environment. SCUG.BE introduction. Mission “To be the single point of entry for the Belgian System Center Community” Founders: Alexandre Verkinderen (SCUG.BE Founder, MVP, SCOM Expert) Kenny Buntinx (SCCM Expert)

wanda
Download Presentation

SCUG.be

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SCUG.be Setting up & Configuring SQL Server to support your SCCM & SCOM environment

  2. SCUG.BE introduction • Mission • “To be the single point of entry for the Belgian System Center Community” • Founders: • Alexandre Verkinderen (SCUG.BE Founder, MVP, SCOM Expert) • Kenny Buntinx (SCCM Expert) • Kim Oppalfens (MVP, SCCM Expert) • Yves Janssens (Webmaster) • Activities • Online presence • Events • Live Meetings

  3. Agenda • Introduction • Session objectives and Key Takeaways • Setting up SQL • Configuring SQL • Maintaining SQL • Useful SQL Queries • Summary • Q&A

  4. Session Objectives and Key Takeaways • How to setup SQL for System Center • Tips to have a good initial setup and configuration • What do you need to configure on your SQL • Best practices for maintaining your SQL

  5. Setting Up your SQL

  6. Installing SQL • Choose the correct Hardware Config • Choose the correct SQL Version • Clustering and Replica’s • SQL Reporting Services for SCCM/SCOM • Named Instances • Firewalls • SQL Services accounts • SQL Collations • Authentication modes

  7. Hardware config SCOM SCCM • X64 Processor • 3 disk Minimum • 1 OS, 1 SCOM + SQL; 1 Transaction logs • Ideal RAID 10 • Ideal • Separate box for SCOM & DB • X OS; Y SQL; Z TEMPDB; X SCOM • Multiple files for the db (equal to number of cpu’s) • Multiple files for tempdb (number of cpu’s /2) • Raid Controller with BBWC • Allocate 75% to write caching • 3 disk Minimum • 1 OS; 1 SQL + SCCM; 1 Transaction logs • Ideal RAID 10 • Ideal • Single box for SCCM & DB • X OS; Y SQL; Z TEMPDB; X SCCM • Multiple files for the db (equal to number of cpu’s) • Multiple files for tempdb (number of cpu’s /2)

  8. SQL Version SCOM 2007 SP1 SCCM • SQL 2005 SP1/SP2/SP3 • No hotfixes required • SQL 2005 SP2/SP3 • No hotfixes required • SQL 2008 RTM /SP1 • RTM - apply hot fix KB955229 • SP1  - apply hot fix KB955262 • SCCM With SQL technology • Standard edition should be fine for just about anybody SCOM 2007 R2 • SQL 2005 SP1/SP2/SP3 • No hotfixes required • SQL 2008 SP1 • No hotfixes required • SCOM With SQL technology • Standard edition should be fine for just about anybody exeption for ACS

  9. SQL Reporting Services SCOM SCCM • SSRS can only be used for SCOM • Don’t use Sharepoint Integration (KB946419) • ResetSRS.exe • Added to SCCM 2007 R2 for reporting functionality • Web reporting still available • Only reporting tool for SCCM v.Next

  10. Clustering and replicas SCOM SCCM • Log shipping • Clustering • Run DBCreateWizard.exe • Replica’s supported for MP, SLP and SUP • Clustering only for SQL • Installs VSS service • Requires sysadmin for Site server • WBEM provider not “clusterable”

  11. Firewall Configuration SCOM SCCM • TCP 1433 by default • Named Instance of SQL • UDP 1434 • Custom TCP port of named instance • If Static port is defined after installation • Edit the dbo.MT_ManagementGroup table with the static port number. • Edit the registry to configure the static port number on the RMS • TCP 1433 by default • Named Instance of SQL • UDP 1434 • Custom TCP port of named instance

  12. Named instances SCOM SCCM • Supported • Supported since ConfigMgr 2007 • Support for SQL Reporting  • Named SRS installed first • Default instance installed afterwards

  13. SQL Service Accounts • If SQL is running as a domain user • setspn –A MSSQLSvc/<SQL Netbiosname>:<SQLPORT> <Domain\Account> • setspn –A MSSQLSvc/<SQL FQDN>:<SQLPORT> <Domain\Account>

  14. SQL Collations SCOM SCCM • SQL_Latin1_General_CP1_CI_AS • Dictionary order, case-insensitive, for use with 1252 Character Set (default) • Dictionary order, case-insensitive, for use with 1252 Character Set (default) • Other collations including case-sensitive supported, yet impractical

  15. Authentication modes SCOM SCCM • Windows authentication mode • Minimizes attack surface • Reporting DW across a firewall • Create Run-As account • Use SQL Authentication • Windows authentication mode • Minimizes attack surface

  16. Configuring SQL

  17. Configuring SQL • Configure memory limit so SQL can’t use it all • Leave 2 GB minimum for the OS & SCOM or SCCM • Protocols (tcp/ip – named pipes)

  18. Configuring SCOM DB’s • Opsmgr DB grooming • Default 7 days => 2 days • p_PartitioningAndGrooming • PartitionAndGroomingSettings table • Opsmgr DW grooming • 10 to 400 days • Data is not always groomed row-by-row • Report Caching • Schedule reports with “Null Delivery Provider” • DB Hotfixes • KB 969130 • KB 960363

  19. Security • If administrators != SA • Create Login [Domain\siteserver$] from windows • Create Login [Domain\sqlserver$] from windows

  20. Demo

  21. Maintaining

  22. Healthchecks SCOM SCCM • SQL • Disk space of DB’s • SCOM • Regular healtchecks • Clean up LocalizedText table (boost UI performance ! ) • SCCM • Built-in tasks

  23. Backup SCOM SCCM • Opsmgr VSS Writer • SQL • Master, MSDB • ReportserverDB • SCOM • OperationsManagerDB • OperationsManagerDW • OperationsManagerAC • Encryptionkeys • Securestoragebackup • Management packs • Custom reports • Database Recovery models • Simple • Bulk-logged • Full • SCCM • Built-in task (once a day) • On demand start backup service

  24. Useful SQL Query’s • DBCC CHECKDB, sp_monitor, sp_spaceused, sp_who, xp_sqlmaint • Large table query, with rowcount • SELECT so.name, si.rowcnt as row_count, 8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END) AS data_kb, Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb, Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id) WHERE 'U' = so.type GROUP BY so.name, si.rowcnt  ORDER BY data_kb DESC • Examine Data Warehouse grooming settings • SELECT AggregationIntervalDurationMinutes, BuildAggregationStoredProcedureName, GroomStoredProcedureName, MaxDataAgeDays, GroomingIntervalMinutes FROM StandardDatasetAggregation • Is SQL broker enabled? • SELECT is_broker_enabled FROM sys.databases WHERE name = 'OperationsManager‘ • How to identify your version of SQL server • SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') • Update Statistics • EXEC sp_updatestats • Reindex the database • USE OperationsManagergo SET ANSI_NULLS ONSET ANSI_PADDING ON SET ANSI_WARNINGS ON SET ARITHABORT ON SET CONCAT_NULL_YIELDS_NULL ON SET QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF • EXEC SP_MSForEachTable "Print 'Reindexing '+'?' DBCC DBREINDEX ('?')"

  25. Demo

  26. Sumary SCOM SCCM • SQL • Configure memory limits • Separate from your SCOM box • SCOM • Grooming jobs • LocalizedText Table • Keep your DB’s small • ACS • Seperate ACS box • Tight filters • SQL

  27. Thanks • Cameron Fuller • Http://ops-mgr.spaces.live.com • System Center Operations Manager 2007 Unleashed • Arie de Haan • Kerrie Meyler • Http://ops-mgr.spaces.live.com • System Center Operations Manager 2007 Unleashed • Pete Zerger • Founder, System Center Central: http://www.systemcentercentral.com • Founder System Center Virtual User Group http://www.systemcenterusergroup.com

  28. Resources SCOM SCCM • Opsmgr Community • http://technet.microsoft.com/en-us/opsmgr/bb499673.aspx • Opsmgr Support Team Blog • http://blogs.technet.com/operationsmgr/ • Opsmgr Product Team Blog • http://blogs.technet.com/momteam/default.aspx • Contact Information • Email: Alexandre@Verkinderen.com • IM: Alexandre@Verkinderen.com • Blog: http://scug.be/blogs/scom • Configmgr community • http://technet.microsoft.com/en-us/configmgr/bb625749.aspx • ConfigMgr Team blog • http://blogs.technet.com/configmgrteam/ • ConfigMgr Forums • http://social.technet.microsoft.com/Forums/en-US/category/configurationmanager/ • Myitforum community • http://myitforum.com/articles/42/section.asp • Contact Information • Email: kim@oscc.be • IM: kim@oppalfens.be • Blog: http://scug.be/blogs/sccm

  29. Q&A

  30. SCUG.be Thank You!

More Related