Pro SQL Server 2008 Policy-Based Management- P6

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST

Báo xấu

96
lượt xem 10
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'pro sql server 2008 policy-based management- p6', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Pro SQL Server 2008 Policy-Based Management- P6

APPENDIX MICROSOFT SQL SERVER BEST PRACTICE POLICIES Windows Event Log I/O Delay I/O Delay Warning Check Server Warning Windows Event Log I/O Error I/O Error During Hard Page Fault Error Check Server During Hard Page Fault Error Windows Event Log Read Retry Read Retry Error Check Server Error Windows Event Log Storage Storage System I/O Timeout Error Check Server System I/O Timeout Error Windows Event Log System System Failure Error Check Server Failure Error 233
Index architecture, PBM, 134–136 Symbols Asymmetric Key Encryption Algorithm policy, 202, 215, 230 #SQLHelp asynchronous processing how #SQLHelp works, 210 On Change: Log Only evaluation mode, 135 auditing servers A best practice audit policies, 206 compliance, 202–206 accessing database see database access default trace, 204–206 Active Directory’s Group Policy, 1 login auditing, 203–204 ActiveX script job steps SQL Server Audit, 203 CmdExec Rights Secured policy, 216 SQL Server Default Trace policy, 206 Address field, Description page autoclose Create New Policy dialog, 20 Database Auto Close policy, 167, 217 administrative accounts autogrow Builtin\Administrators login, 197 Database Free Space policy, 150 disabling sa login, 197 File Growth for SQL Server 2000 policy, 220 security access to, 196–198 autoshrink advanced conditions, creating, 40–44 Database Auto Shrink policy, 50, 167, 168, Advanced Edit dialog 217 creating advanced conditions, 41, 42 evaluating single policy on demand, 50 creating conditions, 15 Data Purity Flag Check condition, 163 Database Free Space policy, 150 B SQL Server Agent Is Running condition, 158, Backup and Data File Location policy, 167, 216, 159 230 Advisory Services, Microsoft, 213 backups affinity mask Last Successful Backup Date policy, 167, 221 SQL Server policies, 222, 223 Successful Transaction Log Backup policy, Against Targets field, General page 153–157 Create New Policy dialog, 18 Beauchemin, Bob, 208 Alert System page behavior SQL Server Agent Properties dialog, 118 policy behavior, 7–8 alerts, 11 Best Practice Analyzer rules configuring SQL Server Agent alerts, 120– importing policies, 24, 27 124 best practice policies, 2, 149, 167, 215–233 creating for all policy violations, 125 Asymmetric Key Encryption Algorithm, 202, ALTER DATABASE command 215, 230 enabling Service Broker, 110 audit, 206 Analyzer rules, best practice Backup and Data File Location, 216, 230 importing policies, 24, 27 CmdExec Rights Secured, 198, 216, 230 235
INDEX best practice policies (cont.) Central Management Server groups conditions and facets, 230–233 adding servers to, 80–84 custom policies, creating, 150 creating, 78–80 Data and Log File Location, 216, 230 evaluating policies against, 84–86 Database Auto Close/Shrink, 217, 230 importing registered servers and groups, Database Collation, 218, 230 81–84 Database Page Status/Verification, 219, 230 registering server to group, 80–81 default trace, 204 Central Management Servers, 9–10 descriptions and HTML links, 215 creating, 77–78 encryption, 202 EPM Framework prerequisites, 169 File Growth for SQL Server 2000, 220, 230 evaluating policies using, 76–86 Guest Permissions, 198, 220, 230 PowerShell evaluating policies against, 105– Last Successful Backup Date, 167, 221, 230 107 maintenance, 40, 99, 101, 173 PowerShell script, EPM, 172 Microsoft SQL Server, 215–233 registering, 77 Public Not Granted Server Permissions, 198, setup script, EPM, 171 221, 231 Change evaluation modes Read-only Database Recovery Model, 221, On Change: Log Only, 7, 49, 67–73 231 On Change: Prevent, 7, 50, 74–76 reviewing other policies, 168 chats security, 197, 198–199 Microsoft Technical Communities web site, SQL Server Default Trace, 206, 223, 231 212 SQL Server Login Mode, 198, 224, 231 Check Condition field, General page SQL Server Password Expiration, 199, 226, Create New Policy dialog, 17 231 Check Number of Databases policy SQL Server Password Policy, 199, 226, 232 Create New Condition dialog, 43 SQL Server Xyz, 222–226, 231–232 CHECKDB command, DBCC, 163 Surface Area Configuration Xyx, 226–227, checklist, DBA, 149 232 checksum Symmetric Key Xyz, 202, 227, 232 Database Page Verification policy, 219 Trustworthy Database, 199, 227, 232 classes Windows Event Log Xyz, 228–229, 232–233 see also objects BIDS (Business Intelligence Developer Studio), Policy.Name class, SMO, 92 175 SQLStoreConnection class, 92 blocked process threshold CmdExec Rights for sysadmins Only condition, SQL Server Blocked Process Threshold 198 policy, 222 CmdExec Rights Secured policy, 198, 216, 230 blogs cmdlets DBA resources, 208 Get-Member, 93 Microsoft Technical Communities web site, Invoke-PolicyEvaluation, 95, 97 212 CodePlex Builtin\Administrators login, 197 EPM download, 169 collation Database Collation policy, 218 C communities categories see policy categories Microsoft Technical, 212 Categories dialog, 40 SQL Server, 208, 210 Category field, Description page compliance, 185–186 Create New Policy dialog, 19 auditing servers, 202–206 encryption, 199–202 security, 195–199 236
INDEX server configuration, 188–195 syspolicy_configuration_internal table, 139 log retention, 191–193 ConfigurationGroup parameter SQL Server service account, 189–191 PowerShell script, EPM, 172 Surface Area Configuration facet, 194– Configure SQL Server Error Logs dialog, 191, 195 192 viewing EPM Framework reports, 180 Connection Properties dialog, 178 Compliance Guide white paper, 209 context-sensitive help compliance regulations, 186–188 SQL Server Books Online, 211 Gramm-Leach-Bliley Act (GLBA), 186 Create New Category dialog, 38 Health Insurance Portability and Create New Condition dialog, 13, 14, 15 Accountability Act (HIPAA), 187 creating advanced conditions, 41, 43 Payment Card Industry Data Security Create New Policy dialog Standard (PCI DSS), 187 Description page, 9, 18, 19 Sarbanes-Oxley Act (SOX), 187 General page, 16, 17, 20 components, PBM, 3–6 Credentials option conditions Shared Data Source Properties dialog, 178 All SQL Server Agent Jobs Have Notification credit card data on Failure, 162 PCI DSS, 187 Check Condition field, Create New Policy custom policies, 150–164 dialog, 17 Data Purity Flag Enabled, 163–165 creating, 13–16 Database Free Space, 150–152 advanced conditions, 40–44 SQL Server Agent is running, 158–160 Data Purity Flag Enabled, 163, 164, 165 SQL Server Agent Jobs have notification on Database Auto Shrink policy, 168 failure, 160–162 Database Free Space, 151 Successful Transaction Log Backup, 153– Database Has Less than 10 Pct Free Space, 157 150 Databases in Full or Bulk Logged, 154, 155, 156 D defining for system databases, 44–47 Dashboard report, EPM, 179, 180 description, 5 Data and Log File Location policy, 216, 230 determining all policies using, 22 importing policies, 25 Log Backup More than 15 Minutes Old, 153, data encryption see encryption 154 data files Microsoft SQL Server best practice policies, Backup and Data File Location policy, 167, 230–233 216 Open Condition dialog, 5 Data and Log File Location policy, 216 SQL Agent Jobs with No Notification on Data Purity Flag Enabled condition, 163 Failure, 161 DATA_PURITY check SQL Server Agent Is Running, 158–160 DBCC CHECKDB command, 163 Successful Transaction Log Backup, 156 database access syspolicy_conditions view, 141 Guest Permissions policy, 220 syspolicy_conditions_internal table, 138 managing security permissions, 195 confidential information PCI DSS, 188 Sarbanes-Oxley Act (SOX), 187 Database Auto Close policy, 167, 217, 230 configuration Database Auto Shrink policy, 50, 167, 168, 217, protecting against unauthorized changes, 2 230 server configuration for compliance, 188– Database Collation policy, 218, 230 195 Database Engine Eventing, 135 Surface Area Configuration facet, 194–195 Database facet, properties of, 4 syspolicy_configuration view, 142 237
INDEX Database Free Space condition, 151 SQL Server web sites, 207, 212 Database Free Space policy, 150–152 training events, 209 Database Mail user groups, 212 adding profile, 110 webcasts, 211, 212 associating account with profile, 110 white papers, 208 checking error logs, 112 DBA Support operator, 117 cleaning up history, 113–114 DBAs configuring, 109–114 reasons for using PBM, 1 deleting all Database Mail log entries, 113 DBCC CHECKDB command, 163 receiving alert notifications for policy Declarative Management Framework (DMF), violations, 109 33 setup script, 111 see also Policy-Based Management testing Database Mail, 111–113 Declarative Management white paper, 209 Database Maintenance default trace, 204–206 creating conditions, 14 SQL Server Default Trace policy, 223 database owner (dbo) defragmentation subscribing to categories, 39 Windows Event Log Disk Defragmentation Database Page Status policy, 219, 230 policy, 228 Database Page Verification policy, 219, 230 Demand evaluation mode, On, 7, 49, 50–60 databases evaluating multiple policies, 55–57 Data Purity Flag Enabled, 163–165 evaluating policies against different Database Free Space policy, 150–152 instance, 57–60 DBA checklist, 149 evaluating single policy, 50–54 defining conditions for system databases, Dependent Conditions page 44–47 Facet Properties - Database dialog, 4 evaluating policies against different dependent policies instance, 57–60 deleting condition with, 22 Facet Properties - Database dialog, 4 viewing, 21–23 SQL Server Agent Is Running condition, Dependent Policies page 158–160 Facet Properties - Database dialog, 4 SQL Server Agent jobs have notification on Open Condition dialog, 5, 21 failure policy, 160–162 Description field, Description page Successful Transaction Log Backup policy, Create New Policy dialog, 19 153–157 Description page Surface Area Configuration Xyz policies, 226 Create New Condition dialog, 15 Symmetric Key Xyz policies, 202, 227 Create New Policy dialog, 18, 19 Trustworthy Database policy, 199, 227 Create Policy dialog, 9 Databases in Full or Bulk Logged condition, 156 Open Condition dialog, 5 Davidson, Tom, 101, 169 Open Policy dialog, 6, 27 DBA checklist, 149 creating policy categories, 38, 39 DBA Mail Account/Profile, 110 DFM namespace, 92 DBA resources, 207–213 dialog boxes blogs, 208, 212 Advanced Edit, 15, 41, 42 forums, 212 Categories, 40 Microsoft paid support options, 212 Configure SQL Server Error Logs, 191, 192 Microsoft support, 211–213 Connection Properties, 178 newsgroups, 212 Create New Category, 38 podcasts, 209 Create New Condition, 13 social networking, 210 Create New Policy, 16, 20 SQL Server Books Online, 211 Evaluate Policies, 51, 53, 54, 55, 85, 86, 87 SQL Server community, 210 238
INDEX evaluating multiple policies on demand, encryption 56 best practice policies, 202 evaluating policies against different compliance, 199–202 instance, 58 transparent data encryption, 199–200 evaluating single policy on demand, 51 encryption keys Export as Policy, 35 Asymmetric Key Encryption Algorithm Export Policy, 28 policy, 202, 215 Export Registered Servers, 82, 83 Extensible Key Management, 201–202 Facet Properties - Database, 4 managing, 201 Import, 24 Symmetric Key policies, 202, 227 Import Registered Servers, 83, 84 EncryptionEnabled property, Database facet, Job Properties, 123 199 Job Step Properties, 113, 182 Enterprise Policy Management see EPM Log File Viewer, 72 Enterprise Support web site, 213 Login Properties, 71 entity relationship diagram, PBM, 138 Manage Policy Categories, 9, 37, 38, 174 EPM (Enterprise Policy Management), 169–183 New Alert, 122 EPM Framework, 10 New Job, 123 automating, 182–183 New Job Schedule, 63, 64 downloading, 169 New Operator, 116 viewing reports, 179–181 New Server Group Properties, 79, 80 white paper, 209 New Server Registration, 77, 78, 80, 81 EPM Framework, setting up, 170–179 Open Condition, 5, 21 PowerShell script, 170, 171–175 Open Policy, 6, 26 Reporting Services reports, 170, 175–179 adding policies to existing schedule, 66 setup script, 170–171 creating schedules, 61, 63, 64, 65 error logs Operator Properties, 123 configuring log retention for SQL Server, Pick Schedule for Job, 66 191–193 Policy Management Properties, 131, 132 script returning number retained by SQL PolicyReports Property Pages, 175 Server, 192 Properties, 61 error numbers Results Detailed View, 44, 51, 52 policy violations, 121 Select Policy, 24, 25 errors Select Source, 59, 85 checking error logs, Database Mail, 112 Send Test E-Mail, 111 deleting condition with dependent policies, Server Properties, 203, 204 22, 23 Shared Data Source Properties, 177, 179 EvalMode parameter SQL Server Agent Properties, 118 PowerShell script, EPM, 174 View Facets, 34, 199, 200, 201 Evaluate Policies dialog View Policies, 128, 129 evaluating policies dynamic locks against Central Management Server SQL Server Dynamic Locks policy, 223 group, 85, 86, 87 against different instance, 58 multiple policies on demand, 56 E single policy on demand, 51, 53, 55 e-mail Evaluation Results page, 51, 56 deleting all e-mail messages, 113 Policy Selection page, 56, 58 Send Test E-Mail dialog, 111 script options in, 54 Enabled property warning flag for policies containing scripts, Policy Management Properties dialog, 132 56 evaluating policies see under policies 239
INDEX Evaluation Mode field, General page Expression field, General page Create New Policy dialog, 18 Create New Condition dialog, 14 evaluation modes, 7–8, 49–50 Extensible Key Management, 201–202 availability of, 49 ExtensibleKeyManagementEnabled property On Change: Log Only, 7, 49, 67–73 Server Configuration facet, 201 On Change: Prevent, 7, 50, 74–76 On Demand, 7, 49, 50–60 evaluating multiple policies, 55–57 F evaluating against other instance, 57– Facet field, General page 60 Create New Condition dialog, 14 evaluating single policy, 50–54 Facet Properties - Database dialog, 4 On Schedule, 7, 49, 60–67 facets, 4 adding policies to schedule, 66–67 best practice policies, 230–233 creating schedules, 61–65 creating advanced conditions, 41 Evaluation Results page, Evaluate Policies exporting current state as policy, 33–35 dialog managing facets, 34 evaluating multiple policies on demand, Policy Management Properties, 134 56 Server Configuration facet, 201 evaluating single policy on demand, 51 server-level facets, 33 Results section, 53 Surface Area Configuration facet, 194–195 Target Details section, 51 syspolicy_facet_events table, 139 Event Alert Definition area syspolicy_management_facets table, 139 configuring SQL Server Agent alerts, 122 Failed Policy Xyz reports, 181 event logs fail-safe operator On Change: Log Only evaluation mode, 49, SQL Server Agent notifications, 118 67–73 fiber mode policy violation shown in, 120, 121 SQL Server Lightweight Pooling policy, 223 Windows Event Log Xyz policies, 228, 229 File Growth for SQL Server 2000 policy, 220, 230 events file system configuring SQL Server Agent alerts, 120 adding policies to Invoke-PolicyEvaluation syspolicy_facet_events table, 139 cmdlet, 98 ExecuteSql function, 41, 44 searching for policy category on, 98, 99 check for Builtin\Administrators login, Files to Import field, Import dialog, 24 197 filters configuring log retention for SQL Server, PolicyCategoryFilter parameter, 173 193 reasons why policy not execute as expected, ensuring sa login disabled, 198 129 PBM security, 136, 137 financial information compliance server configuration for compliance, Gramm-Leach-Bliley Act (GLBA), 186 190 financial reporting compliance ExecuteWql function, 41 Sarbanes-Oxley Act (SOX), 187 execution modes fn_syspolicy_is_automation_enabled function, policy violation error numbers, 121 136 Export as Policy dialog, 35 forums Export Policy dialog, 28 Microsoft Technical Communities, 212 Export Registered Servers dialog, 82, 83 frameworks exporting policies, 27–35 Declarative Management, 33 existing policies, 28–33 Enterprise Policy Management, 10 exporting current state of facet as policy, free space 33–35 Database Free Space policy, 150–152 multiple policies, 28 240
INDEX Full Database Recovery Model policy HistoryRetentionInDays property creating policies, 17 Policy Management Properties dialog, 133 with T-SQL, 36 hyperlinks exporting existing policies, 28 Address field, Create New Policy dialog, 20 viewing dependent policies, 21, 22 Open Condition dialog, 21 full recovery model Last Successful Backup Date policy, 221 Full Recovery Model condition I creating conditions, 14 Import dialog, policies, 24 creating policies, 17 Import Registered Servers dialog, 83, 84 viewing dependent policies, 21 importing policies, 24–27 functions instances, databases creating advanced conditions, 40 evaluating policies against other, 57–60 fn_syspolicy_is_automation_enabled viewing EPM Framework reports, 180 function, 136 internals, PBM, 131–148 Invoke-PolicyEvaluation cmdlet G adding file system policies to, 98 OutputXML parameter, 96 General page Policy parameter, 98 Create New Condition dialog, 14, 15 running category of policies stored on Create New Policy dialog, 16, 17, 20 server, 100 Facet Properties - Database dialog, 4 running policy against SQL Server instance, New Alert dialog, 122 95, 97 Open Policy dialog, 6, 26 TargetServer parameter, 95, 96, 97, 98, 99, Get-Member cmdlet, 93 100, 101 Gramm-Leach-Bliley Act (GLBA), 186 I/O Affinity Mask GRC (governance, risk management, and SQL Server I/O Affinity Mask For Non- compliance), 185 enterprise SQL Servers policy, 223 Group Policy, Active Directory, 1 IsSystemObject property Guest Permissions policy, 198, 220, 230 defining conditions for system databases, 45 H help J SQL Server Books Online, 211 Job Properties dialog, 123 HIPAA (Health Insurance Portability and Job Step Properties dialog Accountability Act), 187 automating EPM Framework, 182 history cleaning up Database Mail history, 113 cleaning up Database Mail history, 113–114 jobs PowerShell script, EPM, 172 SQL Server Agent Is Running condition, 158 syspolicy_policy_execution_history view, 143 syspolicy_policy_execution_history_details K view, 144 key encryption see encryption keys syspolicy_policy_execution_history_details_ internal table, 139 syspolicy_policy_execution_history_interna L l table, 140 syspolicy_purge_history job, 133 Last Execution Status report, 181 viewing policy history, 126, 127–129 241
INDEX Last Successful Backup Date policy, 167, 221, Management node, Object Explorer, 191 230 Management.DFM namespace, 92 lightweight pooling policy, SQL Server, 223 Management.sdk.sfc namespace, 91 live chats Mandate Database check box Microsoft Technical Communities, 212 Manage Policy Categories dialog, 37 LocalSystem account mandate_database_subscriptions column running SQL Server service, 189, 190 syspolicy_policy_categories_internal table, locks 139 SQL Server Dynamic Locks policy, 223 manually creating policies, 13–23 Log File Viewer dialog creating conditions, 13–16 testing On Change: Log Only evaluation creating policies, 16–21 mode, 72 viewing dependent policies, 21–23 viewing dependent policies, 21, 22 message boxes viewing history based on policy, 126 Policy Evaluation Warning, 54 viewing policy history, 126 Microsoft Advisory Services, 213 viewing policy history based on objects, 129 Microsoft best practice policies, 2, 167 Log Only mode, On Change:, 7, 49, 67–73 Microsoft Enterprise Support web site, 213 log retention Microsoft Events podcast, 209 server configuration for compliance, 191– Microsoft Problem Resolution Services, 213 193 Microsoft SQL Server best practice policies, login mode 215–233 SQL Server Login Mode policy, 224 Asymmetric Key Encryption Algorithm, 215 Login Properties dialog, 71 Backup and Data File Location, 216 logins CmdExec Rights Secured, 216 Builtin\Administrators login, 197 conditions and facets, 230–233 login auditing, 203–204 Data and Log File Location, 216 sa login, disabling, 197 Database Auto Close/Shrink, 217 SQL Server Login Mode policy, 198 Database Collation, 218 LogOnSuccess property Database Page Status/Verification, 219 Policy Management Properties dialog, 133 descriptions and HTML links, 215 logs File Growth for SQL Server 2000, 220 checking error logs, Database Mail, 112 Guest Permissions, 220 Data and Log File Location policy, 216 Last Successful Backup Date, 221 default trace log file location, 205 Public Not Granted Server Permissions, 221 deleting all Database Mail log entries, 113 Read-only Database Recovery Model, 221 policy violation shown in Windows event SQL Server Xyz, 222–226 log, 120, 121 Surface Area Configuration Xyz, 226–227 SQL Server log entries, 65, 66 Symmetric Key Xyz, 227 Successful Transaction Log Backup policy, Trustworthy Database, 227 153–157 Windows Event Log Xyz, 228–229 Microsoft support DBA resources, 211–213 M Microsoft Technical Communities, 212 Mail Session area SQL Server Books Online, 211 SQL Server Agent Properties dialog, 118 SQL Server Troubleshooting and Support, Maintenance best practice policies, 40, 99, 101, 212 173 web site, 213 Manage Policy Categories dialog, 9, 37, 38, 174 webcasts, 211 Management Data Warehouse database, 170, Microsoft Technical Communities, 212 171 Microsoft.SQLServer.Management.DFM namespace, 92 242
INDEX Microsoft.SQLServer.Management.sdk.sfc newsgroups namespace, 91 Microsoft Technical Communities, 212 SQLStoreConnection class, 92 notifications MidnightDBA podcast, 209 SQL Server Agent, 118–119 mixed-mode authentication … Is Running condition, 158–160 disabling sa login, 197 … jobs have notification on failure policy, modes 160–162 Evaluation Mode field, Create New Policy dialog, 18 evaluation modes, 7–8, 49, 50 O On Change: Log Only, 49, 67–73 Object Explorer Details window, 28, 57 On Change: Prevent, 50, 74–76 Object Explorer window, 3, 191 On Demand, 49, 50–60 objects On Schedule, 49, 60–67 see also classes MS_PolicyEventProcessingLogin, 137 PolicyStore object, 92 MS_PolicyTsqlExecutionLogin, 136, 137 syspolicy_object_sets view, 142 msdb database syspolicy_object_sets_internal table, 139 checking for new tables, 141 viewing policy history based on, 127–129 checking for new views, 146 On Change: Log Only evaluation mode, 7, 18, PBM tables, 137 49, 67–73 MSSQLTips web site, 208 choosing for policy, 68, 69 PBM architecture, 135 N On Change: Prevent evaluation mode, 7, 18, 50, 74–76 Name field, General page PBM architecture, 135 Create New Condition dialog, 14 On Demand evaluation mode, 7, 49, 50–60 Create New Policy dialog, 17 evaluating multiple policies, 55–57 network packets evaluating policies against other instance, SQL Server Network Packet Size policy, 225 57–60 network security evaluating single policy, 50–54 PCI DSS, 188 PBM architecture, 134 New Alert dialog On Schedule evaluation mode, 7, 49, 60–67 configuring SQL Server Agent alerts, 122 adding policies to existing schedule, 66–67 General page, 122 Create New Policy dialog, 18 Options page, 123, 124 creating schedules, 61–65 Response page, 123 PBM architecture, 135 New Category dialog reasons why policy not execute as expected, Create New Category dialog, 38 129 New Condition dialog, 14 Open Condition dialog Create New Condition dialog, 15, 16 Data Purity Flag Enabled condition, 164 Database Free Space policy, 150 Database Auto Shrink policy, 168 transaction log backups, 153 Database Free Space policy, 151 New Job dialog, 123 Databases in Full or Bulk Logged condition, New Job Schedule dialog, 63, 64 155 New Operator dialog, 116, 123 defining conditions for system databases, 45 New Policy dialog Dependent Policies page, 5, 21 Create New Policy dialog, 9, 16, 17, 18, 19, Description page, 5 20 General page, 5 New Server Group Properties dialog, 79, 80 notification on failure condition, 161 New Server Registration dialog, 77, 78, 80, 81 transaction log backups, 154, 155 243
INDEX Open Policy dialog, 6 Public Not Granted Server Permissions adding policies to existing schedule, 66 policy, 198, 221 creating schedules, 61, 63, 64, 65 personal financial information Description page, 6, 27 Gramm-Leach-Bliley Act (GLBA), 186 creating policy categories, 38, 39 Pick Schedule for Job dialog, 66 General page, 6, 26 podcasts, DBA resources, 209 importing policies, 26 policies On Change: Log Only mode, 68, 69 see also best practice policies; policy On Change: Prevent mode, 74, 75 categories targets and evaluation modes, 8 adding file system policies to Invoke- transaction log backups, 155 PolicyEvaluation cmdlet, 98 Operator Properties dialog, 123 audit policies, 206 operators, 116 conditions, 5, 230–233 fail-safe operator, 118 creating, 13–16 SQL Server Agent, 116–117 creating advanced, 41 Options page, New Alert dialog, 123, 124 determining all policies using, 22 OutputXML parameter creating, 13–36 Invoke-PolicyEvaluation cmdlet, 95, 96 exporting policies, 27–35 Ozar, Brent importing policies, 24–27 how #SQLHelp works, 210 manually creating policies, 13–23 with T-SQL, 35–37 custom policies, 150–164 P defining conditions for system databases, packets, SQL Server 44–47 Network Packet Size policy, 225 dependent policies, viewing, 21–23 pages description, 6 Database Page Status/Verification policies, displaying, 6 219 encryption policies, 202 parallelism, SQL Server EPM Framework, 169 Max Degree of Parallelism policy, 224 viewing reports, 180 PASS (Professional Association for SQL Server), evaluating policies, 49–86 208 evaluation modes, 7–8, 49–50 passwords, SQL Server On Change: Log Only mode, 7, 49, 67–73 Password Expiration policy, 199, 226 On Change: Prevent mode, 7, 50, 74–76 Password Policy policy, 199, 226 On Demand mode, 7, 49, 50–60 patient information On Schedule mode, 7, 49, 60–67 HIPAA, 187 using Central Management Server, 76– PBM see Policy-Based Management 86 PCI DSS (Payment Card Industry Data Security warning flag for policies containing Standard), 187 scripts, 56 auditing for compliance, 202 evaluating policies on demand, 50–60 white papers, 209 against different instance, 57–60 performance multiple policies, 55–57 SQL Server Performance, 208 single policy, 50–54 permissions evaluating policies on schedule, 60–67 disabling sa login, 197 adding policies to schedule, 66–67 Guest Permissions policy, 198, 220 creating schedules, 61–65 managing security permissions to control Export as Policy dialog, 35 access, 195 Export Policy dialog, 28 exporting policies, 27–35 export multiple policies, 28 244
INDEX exporting current state of facet as policy, targets, 3 33–35 troubleshooting policies, 126–130 exporting existing policies, 28–33 policies, list of facets, 4, 230–233 All SQL Server Agent Jobs Have Notification File Growth for SQL Server 2000 policy, on Failure, 162 220, 230 Asymmetric Key Encryption Algorithm, 202, importing policies, 24–27 215, 230 Invoke-PolicyEvaluation cmdlet, 95 Backup and Data File Location, 167, 216, Maintenance category, 40, 99, 101, 173 230 Open Policy dialog, 6 Check Number of Databases, 43 policy behavior, 7–8 CmdExec Rights Secured, 198, 216, 230 policy history, viewing, 126 Data and Log File Location, 216, 230 based on objects, 127–129 Data Purity Flag Enabled, 165 based on policy, 126 Database Auto Close, 167, 217, 230 policy management, 9–11 Database Auto Shrink, 50, 167, 168, 217, 230 alerts, 11 Database Collation, 218, 230 categories, 9 Database Free Space, 150–152 Central Management Servers, 9–10 Database Page Status/Verification, 219, 230 EPM Framework, 10 descriptions and HTML links, 215 policy violations File Growth for SQL Server 2000, 220, 230 creating alerts for all, 125 Guest Permissions, 198, 220, 230 error numbers, 121 Last Successful Backup Date, 167, 221, 230 receiving alert notifications for, 109 Log Backups More than 15 Minutes Old for shown in Windows event log, 120, 121 Non-Simple Recovery DBs, 155 PowerShell querying and storing policy Public Not Granted Server Permissions, 198, execution results, 101–105 221, 231 creating staging table, 102 Read-only Database Recovery Model, 221, loading policy evaluation history, 102– 231 103 SQL Server Agent Is Running, 160 querying history, 103–105 SQL Server Default Trace, 206, 223, 231 PowerShell running multiple policies SQL Server Login Mode, 198, 224, 231 against SQL Server instance, 98–101 SQL Server Password Expiration, 199, 226, invoking category of policies from file 231 system, 99–100 SQL Server Password Policy, 199, 226, 232 invoking category of policies from SQL Server Xyz, 222–226, 231–232 instance, 100–101 Successful Transaction Log Backup, 153– invoking multiple policies from Cmdlet, 157 98–99 Surface Area Configuration for Xyx, 226– PowerShell running policy against SQL 227, 232 Server instance, 95–98 Symmetric Key Xyz, 202, 227, 232 invoking policy defined on server, 97–98 Trustworthy Database, 199, 227, 232 invoking policy from file, 95–96 Windows Event Log Xyz, 228–229, 232–233 receiving alert notifications for policy policy categories violations, 109 creating, 37–39 security policies, 197, 198–199 evaluating group of policies against group server restrictions, 8 of SQL Server instances, 105 SQL Server log showing failed policies, 66 invoking from file system, 99–100 syspolicy_policies view, 142 invoking from instance, 100–101 syspolicy_policy_xyz tables, 139, 140 Manage Policy Categories dialog, 9 syspolicy_xyz tables, 137–140 managing, 37–40 syspolicy_xyz views, 141–146 245
INDEX policy categories (cont.) internals, 131–148 mandating/not mandating subscriptions, MSDN Policy-Based Management blog, 208 37, 38 Open Condition dialog, 5 PolicyCategoryFilter parameter, 173 Open Policy dialog, 6 query to view policies by, 39 policies, 6 searching in SQL Server instance for, 98, 100 policy behavior, 7–8 searching on file system for, 98, 99 policy management, 9–11 subscribing to, 39–40 properties, 131–134 syspolicy_policy_categories view, 143 query listing stored procedures, 146 syspolicy_policy_categories_internal table, query listing tables, 141 139 query listing views, 146 syspolicy_policy_category_subscriptions reasons for using, 1 view, 143 requirements, 2 syspolicy_policy_category_subscriptions_in security, 136–137 ternal table, 139 server restrictions, 8 Policy Evaluation Warning message box, 54 SQL Server 2008, 2 Policy Event Handler stored procedures, 146–148 On Change: Log Only mode, 135 Surface Area Configuration facet, 33 On Change: Prevent mode, 135 tables, 137–141 Policy Health State icon targets, 3 viewing policy history based on objects, 128 using PowerShell, 89–108 Policy Management node, SQL Server 2008, 3 views, 141–146 Policy Management Properties dialog, 131, 132 web sites, 207 Enabled property, 132 webcasts, 211 HistoryRetentionInDays property, 133 white papers, 208 LogOnSuccess property, 133 PolicyCategoryFilter parameter Policy parameter PowerShell script, EPM, 173 Invoke-PolicyEvaluation cmdlet, 98 PolicyDashboard Policy Selection page, Evaluate Policies dialog, viewing EPM Framework reports, 179 56, 58 PolicyReports Property Pages dialog, 175 Policy.Name class, SMO, 92 PolicyStore object, 92 PolicyAdministratorRole pooling PBM security, 136, 137 SQL Server Lightweight Pooling policy, 223 querying system policy views, 141 PowerShell Policy-Based Management connecting to/querying SQL Server instance alerts, 11 using SMO, 92 architecture, 134–136 connecting to/querying SQL Server instance blogs, 208 using T-SQL, 90 categories, 9 evaluating policies against Central Central Management Servers, 9–10 Management Server, 105–107 checking for new views, 146 On Schedule evaluation mode, 135 combining views, 145 PBM using, 89–108 components, 3–6 querying and storing policy execution conditions, 5 results, 101–105 description, 1 creating staging table, 102 enabled/disabled states, 132 loading policy evaluation history, 102– EPM Framework, 10 103 entity relationship diagram, 138 querying history, 103–105 evaluation modes, 7–8 running multiple policies against SQL Facet Properties - Database dialog, 4 Server instance, 98–101 facets, 4 246
INDEX invoking category from file system, 99– querying history, 103–105 100 viewing policies by category, 39 invoking category from instance, 100– 101 invoking policies from Cmdlet, 98–99 R running policy against SQL Server instance, Read-only Database Recovery Model policy, 95–98 221, 231 invoking policy defined on server, 97–98 Rebbulke, Lara, 101 invoking policy from file, 95–96 recovery saving results to XML file, 96 Last Successful Backup Date policy, 221 PowerShell script Read-only Database Recovery Model policy, automating EPM Framework, 182–183 221 ConfigurationGroup parameter, 172 transaction log backups, 153 creating, 89–94 Registered Servers window, 77, 82, 85 interrogating class for registry values members/properties, 93–94 xp_regread extended stored procedure, 189 using SMO, 91–93 regulations see compliance regulations using T-SQL, 89–91 releases, upcoming, 207 EvalMode parameter, 174 Reporting Services PolicyCategoryFilter parameter, 173 EPM Framework prerequisites, 169 setting up EPM Framework, 170, 171–175 setting up EPM Framework, 170, 175–179 predefined policies, importing, 24–27 reports Prevent mode, On Change:, 7, 50, 74–76 viewing EPM Framework reports, 179–181 principals resources for DBAs see DBA resources security, SQL Server, 196 Response page, New Alert dialog, 123 Problem Resolution Services, Microsoft, 213 restrictions, server, 8 procedures see stored procedures Results Detailed View dialog properties creating advanced conditions, 44 facets, 4 evaluating single policy on demand, 51, 52 PBM, 131–134 evaluation results for SQL Server instance, Properties dialog, 61 191 Public Not Granted Server Permissions policy, results directory 198, 221, 231 PowerShell script, EPM, 172 Results section, Evaluate Policies dialog, 53 Q Rubbelke, Lara, 169, 208 queries see also T-SQL scripts S check for Builtin\Administrators login, 197 sa login, disabling, 197 condition/facet information, best practice Sarbanes-Oxley Act (SOX), 187 policies, 230 schedules ensuring sa login disabled, 198 adding policies to, 66–67 listing PBM stored procedures, 146 creating, 61–65 listing PBM tables, 141 On Schedule mode, 7, 18, 49, 60–67 listing PBM views, 146 Pick Schedule for Job dialog, 66 storing policy execution results, PowerShell, policy evaluation on, 61–67 101–105 script options, Evaluate Policies dialog, 54 creating staging table, 102 scripts loading policy evaluation history, 102– see also T-SQL scripts 103 creating PowerShell script, 89–94 247
INDEX scripts (cont.) Public Not Granted Server Permissions interrogating class, 93–94 policy, 198 using SMO, 91–93 SQL Server Audit, 203 using T-SQL, 89–91 SQL Server Default Trace policy, 206 enabling xp_cmdshell, 194 service account, SQL Server, 189–191 returning number of error logs retained by Service Broker, 110 SQL Server, 192 On Change: Log Only evaluation mode, 135 returning SQL Server service account, 189 Surface Area Configuration for Service warning flag for policies containing scripts, Broker Endpoints policy, 226 56 setup script sdk.sfc namespace, 91 Database Mail, 111 securables, SQL Server, 196 setting up EPM Framework, 170–171 security Shared Data Source Properties dialog, 177 access to administrative accounts, 196–198 Credentials option, 178 Builtin\Administrators login, 197 simple recovery mode disabling sa login, 197 transaction log backups, 153 best practice security policies, 198–199 Simple-Talk web site, 208 compliance, 195–199 single quotes, SQL features in SQL Server, 195 escaping single quotes, 42 managing permissions to control access, SL command 195 automating EPM Framework, 183 PCI DSS, 188 SMO (SQL Server Management Objects) PBM, 136–137 creating PowerShell script using, 91–93 principals and securables, SQL Server, 196 interrogating class, 93–94 server configuration for compliance, 188– On Demand evaluation mode, 134 195 Policy.Name class, 92 Security page, Server Properties dialog, 203, 204 SOAP Segarra, Jorge, 208 Surface Area Configuration for SOAP Select Policy dialog, 24, 25 Endpoints policy, 227 Select Source dialog, 59, 85 social networking, 210 Send Test E-Mail dialog, 111 SOX (Sarbanes-Oxley Act), 187 Server Configuration facet, 201 sp_add_operator stored procedure, 117 server configuration for compliance, 188–195 sp_configure stored procedure, 109, 194 LocalSystem account, 189, 190 sp_cycle_errorlog stored procedure, 193 log retention, 191–193 space SQL Server service account, 189–191 Database Free Space policy, 150–152 Surface Area Configuration facet, 194–195 SQL Server Management Objects, SQL see SMO escaping single quotes, 42 Server Properties dialog, 203, 204 SQL Agent Jobs with No Notification on Failure Server Restriction field, General page condition, 161 Create New Policy dialog, 18 SQL Down Under podcast, 209 server restrictions, 8 SQL Server server-level facets, 33 configuring log retention for, 191 serverprincipals catalog view connecting to/querying disabling sa login, 198 using SMO in PowerShell, 92 servers using T-SQL in PowerShell, 90 adding to Central Management Server Declarative Management white paper, 209 groups, 80–84 disabling sa login, 197 auditing for compliance, 202–206 evaluating group of policies, 105 Central Management Servers, 9–10 login auditing, 203–204 DBA checklist, 149 248
INDEX PowerShell running multiple policies SQL Server Agent Properties dialog, 118 against, 98–101 SQL Server Audit, 203 invoking category of policies from file SQL Server Blocked Process Threshold policy, system, 99–100 222, 231 invoking category of policies from SQL Server Books Online, 211 instance, 100–101 SQL Server Central web site, 208 invoking multiple policies from Cmdlet, SQL Server Community, 208, 210 98–99 SQL Server Connect web site, 207 PowerShell running policy against, 95–98 SQL Server Default Trace policy, 206, 223, 231 invoking policy defined on server, 97–98 SQL Server Dynamic Locks policy, 223, 231 invoking policy from file, 95–96 SQL Server events script returning number of error logs configuring SQL Server Agent alerts, 120 retained by, 192 SQL Server I/O Affinity Mask For Non- searching for policy category, 98, 100 enterprise SQL Servers policy, 223, 231 security features in, 195 SQL Server Lightweight Pooling policy, 223, 231 viewing trace files, 204 SQL Server log entries, 65, 66 SQL Server 2000 SQL Server Login Mode policy, 198, 224, 231 File Growth for SQL Server 2000 policy, 220 SQL Server Management Objects see SMO, 91 SQL Server 2005 SQL Server Management Studio Builtin\Administrators login, 197 adding operators, 116 SQL Server 2008 enabling SQL Server Agent notifications, Builtin\Administrators login, 197 118 Compliance Guide white paper, 209 evaluating policies against different EPM Framework white paper, 209 instance, 57 EPM Framework prerequisites, 169 Policy Management Properties dialog, 132 facets, 4 testing Database Mail, 111 Microsoft best practice policies, 215–233 viewing policy history based on objects, 127 PCM white paper, 208 SQL Server Max Degree of Parallelism policy, PCI DSS white paper, 209 224, 231 Policy Management node, 3 SQL Server Max Worker Threads Xyz policies, Policy-Based Management, 1, 2 224, 225, 231 PowerShell, 89 SQL Server Network Packet Size policy, 225, 231 upcoming releases, 207 SQL Server Open Objects for SQL Server 2000 SQL Server 2008 Reporting Services policy, 225, 231 EPM Framework prerequisites, 169 SQL Server Password Expiration/Policy SQL Server 32-/64-bit Affinity Mask Overlap policies, 199, 226, 231, 232 policies, 222, 231 SQL Server Performance web site, 208 SQL Server Affinity Mask policy, 222, 231 SQL Server Profiler, 205 SQL Server Agent SQL Server service account, 189–191 automating EPM Framework, 182, 183 SQL Server System Tables Updatable policy, configuring alerts, 120–124 226, 232 creating operators, 117 SQL Server Troubleshooting and Support web DBA checklist, 149 site, 212 enabling notifications, 118–119 SQL Server web sites, 207 jobs have notification on failure policy, 160– SQLCMD mode 162 setup script, EPM, 170, 171 On Schedule evaluation mode, 135 SQLHelp SQL Server Agent Is Running condition, 158– how #SQLHelp works, 210 160 SQLSaturday training event, 209 SQL Server Agent Is Running policy, 160 SQLServer.Management.DFM namespace, 92 SQL Server Agent operators, 116 SQLServer.Management.sdk.sfc namespace, 91 249
INDEX SQLServerPedia, 208, 209, 210 syspolicy_configuration view, 133, 136, 142 SQLStoreConnection class, 92 syspolicy_configuration_internal table, 139 SQLTeam web site, 208 syspolicy_execution_internal table, 139 SSWUG podcast, 209 syspolicy_facet_events table, 139 staging table syspolicy_management_facets table, 139 creating, 102 syspolicy_object_sets view, 142 loading policy evaluation history, 102–103 syspolicy_object_sets_internal table, 139 standards, 2 syspolicy_policies view, 142 Stasiuk, Colin, 208 syspolicy_policies_internal table, 139, 168 stored procedures syspolicy_policy_categories view, 143 Policy-Based Management, 146–148 syspolicy_policy_categories_internal table, 139 sp_add_operator, 117 syspolicy_policy_category_subscriptions view, sp_configure, 109 143 sp_cycle_errorlog, 193 syspolicy_policy_category_subscriptions_inter sysmail_add_xyz, 110 nal table, 139 sysmail_delete_xyz, 113 syspolicy_policy_execution_history view, 130, xp_cmdshell, 194 143 xp_regread, 189 syspolicy_policy_execution_history_details Successful Transaction Log Backup condition, view, 130, 144 156 syspolicy_policy_execution_history_details_int Successful Transaction Log Backup policy, 153– ernal table, 133, 139 157 syspolicy_policy_execution_history_internal support table, 140 Enterprise Support web site, 213 syspolicy_purge_history job, 133 Microsoft paid support options, 212 syspolicy_system_health_state view, 144 SQL Server Troubleshooting and Support syspolicy_system_health_state_internal table, web site, 212 140 Surface Area Configuration facet, 33 syspolicy_target_set_levels view, 144 server configuration for compliance, 194– syspolicy_target_set_levels_internal table, 140 195 syspolicy_target_sets view, 144 Surface Area Configuration tool, 24 syspolicy_target_sets_internal table, 140 Surface Area Configuration Xyz policies, 194, syspolicy_xyz tables 226, 227, 232 relationships between, 137 Symmetric Key Xyz policies, 202, 227, 232 system administrator (sa) syntax queries subscribing to categories, 39 SQL Server Books Online, 211 system databases sys.serverprincipals catalog view defining conditions for, 44–47 disabling sa login, 198 system policy views, querying, 141 sysjobs table, 133 sysmail_add_account_sp stored procedure, 110 sysmail_add_profile_sp stored procedure, 110 T sysmail_add_profileaccount_sp stored tables procedure, 110 checking for new tables, 141 sysmail_allitems view, 112 creating staging table, 102 sysmail_delete_log_sp stored procedure, 113 Policy-Based Management, 137–141 sysmail_delete_mailitems_sp stored procedure, relationships between syspolicy_xyz tables, 113 137 sysmail_faileditems view, 113 SQL Server System Tables Updatable policy, sysmail_sentitems view, 113 226 syspolicy_conditions view, 141 syspolicy_conditions_internal, 138 syspolicy_conditions_internal table, 138 250
INDEX syspolicy_configuration_internal, 139 T-SQL syspolicy_execution_internal, 139 creating policies with, 35–37 syspolicy_facet_events, 139 creating PowerShell script using, 89–91 syspolicy_management_facets, 139 defining conditions for system databases, syspolicy_object_sets_internal, 139 46 syspolicy_policies_internal, 139, 168 testing Database Mail, 112 syspolicy_policy_categories_internal, 139 T-SQL scripts syspolicy_policy_category_subscriptions_in see also scripts ternal, 139 configuring Database Mail, 109 syspolicy_policy_execution_history_details_ connecting to/querying SQL Server internal, 139 instance, 90 syspolicy_policy_execution_history_interna creating alerts for all policy violations, 125 l, 140 creating Database Mail cleanup job, 114 syspolicy_system_health_state_internal, 140 enabling Database Mail in SQL Server syspolicy_target_set_levels_internal, 140 Agent, 119 syspolicy_target_sets_internal, 140 returning policies with exception message, Target Details section, Evaluate Policies dialog, 130 51 sending e-mail message, 112 targets, 3 TweetDeck social networking, 210 Against Targets field, Create New Policy Twitter social networking, 210 dialog, 18 syspolicy_target_set_levels view, 144 syspolicy_target_sets view, 144 U TargetServer parameter, Invoke- user groups PolicyEvaluation cmdlet, 95, 96, 97, 98, 99, Microsoft Technical Communities web site, 100, 101 212 TargetServerURL property training event, 210 PolicyReports, 175 Tchikatilov, Dmitri, 101, 169 testing V Database Mail, 111–113 Text to Display field, Description page View Database Mail Log, 112 Create New Policy dialog, 19 View Facets dialog, 34, 199, 200, 201 The Voice of the DBA podcast, 209 View Policies dialog, 128, 129 threads views SQL Server Max Worker Xyz policies, 224, checking for new views, 146 225 combining, 145 tracing creating vw_PolicyResults view, 104 default trace, 204–206 Policy-Based Management, 141–146 SQL Server Default Trace policy, 206, 223 querying system policy views, 141 viewing trace files, 204 sysmail_allitems, 112 training events, DBA, 209 sysmail_faileditems, 113 transactions sysmail_sentitems, 113 Successful Transaction Log Backup policy, syspolicy_conditions, 141 153–157 syspolicy_configuration, 133, 142 transparent data encryption, 199–200 syspolicy_object_sets, 142 troubleshooting syspolicy_policies, 142 policies, 126–130 syspolicy_policy_categories, 143 SQL Server Troubleshooting and Support syspolicy_policy_category_subscriptions, web site, 212 143 Trustworthy Database policy, 199, 227, 232 251
INDEX views (cont.) windows syspolicy_policy_execution_history, 130, Object Explorer Details window, 28 143 Registered Servers window, 77 syspolicy_policy_execution_history_details, Windows Authentication 130, 144 disabling sa login, 197 syspolicy_system_health_state, 144 SQL Server Login Mode policy, 198, syspolicy_target_set_levels, 144 224 syspolicy_target_sets, 144 Windows event log virtual log files (VLFs) policy violation shown in, 120, 121 transaction log backups, 153 Windows Event Log Xyz policies, 228, 229, Voice of the DBA, The, 209 232, 233 WMI (Windows Management Instrumentation), 41 W WMI events web sites configuring SQL Server Agent alerts, Enterprise Support, 213 120 Microsoft Technical Communities, 212 WQL (WMI Query Language), 41 Policy-Based Management, 207 SQL Server, 207 SQL Server Connect, 207 X SQL Server Troubleshooting and Support, XML files 212 exporting existing policies, 28 webcasts invoking policy evaluation and saving DBA resources, 211 results to, 96 Microsoft Technical Communities web site, XML output 212 Full Database Recovery Model policy, WeFollow directory, 210 29 white papers xp_cmdshell procedure, 194, 195, 206 DBA resources, 208 xp_regread stored procedure, 189 252