68
Part I: Laying the Foundations
Patches
After a freshly installed SQL Server instance, it is always recommended to review available
updates. SQL Server updates may be available in the form of hotfi xes, cumulative updates,
and service packs. To avoid negatively impacting your applications, all updates need to
be reviewed carefully before they are applied. It is not recommended to enable automatic
updates on production SQL Server instances. All updates should be tested in a controlled
testing environment before they are applied in production.Other updates, on the other hand, may be critical security fi xes against newly discovered
threats such as software worms and other vulnerabilities.Monitoring
Being on top of what’s going on in your SQL Server instances is one of the most important
job functions of database administrators. With so many areas to monitor including resource
utilization, performance, availability, critical events, errors, jobs, and so on, you must
implement a monitoring framework to capture real-time metrics and keep the history of
them to analyze trends and patterns.SQL Server 2012 includes real-time monitoring through the Activity Monitor that can be
launched from SQL Server Management Studio (SSMS). In addition, SQL Server 2012 comes
packed with a performance metric collection and monitoring framework called Management
Data Warehouse that can help you keep track of you environment along with some of the
included performance dashboards. For more details on how to implement the Management
Data Warehouse refer to Chapter 43.Other tools and features included with SQL Server 2012, such as SQL Server Profi ler,
Extended Events, and Dynamic Management Views (DMVs), can help you uncover details of
running processes and pinpoint issues. You can also use Microsoft products such as System
Center Operations Manager (SCOM) and other third-party tools to monitor SQL Server 2012
instances. For more details on Performance Monitoring, refer to Part 7, “Monitoring and
Auditing.”Model Database
The model database is a system database that serves as a template for all user databases. On
creation, every user database inherits database objects from the model database including
database options, such as recovery model, database initial size, and autogrowth settings,
permissions, roles, tables, functions, stored procedures, and so on.Modifying the model database ensures that all user databases have a standardized set of
database objects when they are created, which can greatly simplify manual processes.c03.indd 68c03.indd 68 7/30/2012 4:10:09 PM7/30/2012 4:10:09 PM
http://www.it-ebooks.info