SCUG.be

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!