The database is the heart of every organization, and like a heart it needs constant exercise and some healthy nutrition. So how do you keep your database humming like brand new? The process is actually not that complicated, but understanding the importance and interdependence of each step is critical:
TABLE OF CONTENTS
[IMAGE] Monitoring and alert automation
[IMAGE] Cross-platform performance tuning
[IMAGE] Archiving
[IMAGE] Space management
[IMAGE] Administration
[IMAGE] Backup and recovery
[IMAGE] Security
[IMAGE] Conclusion
These seven steps will lead to some very recognizable and desired benefits, including improved productivity of your people and availability of the business services; lower total cost of ownership across the enterprise; and aligned IT operations and business objectives.
[IMAGE][IMAGE] Monitoring and alert automation[IMAGE] Return to Table of Contents
Your database management strategy must include a comprehensive system of monitoring and alerting on key database parameters that would indicate problems. Specifically, you need to monitor every aspect of your SQL Server environments, such as instance and server availability; database performance, including cross-server data contention; and job management, including failed and overdue jobs. Anything that might indicate performance degradation, an outage or perhaps even an impending problem needs to be recognized and prioritized in an automat...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
ed fashion, and an appropriate resource must be notified.
Through monitoring and alert automation, you can view "big picture" and drill-down perspectives of system, database and SQL performance; monitor databases across numerous vendors from a common console anywhere on the Web; and meet service-level objectives in terms of performance and business availability. More specifically, you can determine database status and navigate through data and graphs, quickly identifying and correcting performance issues. Alerts and notifications can automatically run scripts to resolve issues before they become problems apparent to end users. You can also maximize database availability by isolating and proactively resolving problems through information analysis and root-cause diagnosis functionalities.
[IMAGE][IMAGE] Cross-platform performance tuning[IMAGE] Return to Table of Contents
SQL optimization and analysis tools allow you to closely examine system and application issues for the root cause of a performance problem. To identify the problem, these tools visually represent the underlying performance data, analyze the running application SQL, and review the physical structure and configuration of the database. This enables you to quickly determine bottlenecks and hotspots for diagnostics and problem resolution, and ultimately manage data growth and complexity.
IT managers and industry experts agree that poor performing SQL is responsible for approximately 80% of response-time SLA failures. Performance-tuning tools can help increase programmer productivity and efficiency by providing access path analyses and easy-to-use tuning utilities. You can quickly and easily pinpoint resource-consuming SQL statements without running, for example, a DB2 SQL trace. You'll also get in-depth index optimization recommendations, which identifies unused indexes and "what-if" analyses that can significantly improve database-response time.
[IMAGE][IMAGE] Archiving [IMAGE] Return to Table of Contents
Database archiving allows you to archive and remove precise subsets of rarely accessed data from a production database safely and accurately, based on user-defined specifications and business rules. Archive processing saves not only the data, but also the metadata describing tables, columns and relationships used to create an archive file. With this information, archived data always can be restored in its business context – weeks or years later – even if the data model changes over time.
When developing an archival strategy, it is important to understand the dynamic nature of relational databases and how the business value and access requirements change over time. When data is first acquired, the business value and access rates are high. However, as the data loses its vitality, access frequency declines and so does its business value. Data can be separated into access profiles, such as acquisition, heavy access, medium access, rare access and disposal. At the point of diminishing returns, it makes sense to move the data out of the high-cost, fast-response system and into lower-cost, slower response systems to better match business value.
[IMAGE][IMAGE] Space management[IMAGE] Return to Table of Contents
Space management is the process of managing and maintaining underlying database structures and their physical representation in terms of files and disk structures. By examining the growth of data in terms of the objects and their placements, it helps you ensure that the system can continue to grow and expand while remaining efficient.
Space management detects when an object is either running out of capacity or not using the capacity efficiently. To allow the addition of new data, a growing database may require occasional restructuring. This process -- called reorganization -- is performed not only for space management reasons, but often for performance-tuning reasons as well.
[IMAGE][IMAGE] Administration[IMAGE] Return to Table of Contents
Database administration broadens your reach and scope by simplifying and streamlining workflows, and by controlling and managing change. Database administration tools enable effective and timely management of database growth in increasingly complex IT environments. The growing scarcity of skilled DBA administrators and IT professionals will place a premium on centralized, heterogeneous, easy to use and integrated administration console. Having an integrated console that works the way you work will help you manage more databases and adapt to business changes with existing skill sets and less effort.
According to a study from Forrester Research, 80% of IT business-critical downtime is the result of unmanaged changes to the IT environment. Change management is often one of the most difficult tasks DBAs face because it involves unloading and loading data, changing objects, removing and adding objects – all of which have complex and often-unclear interdependencies. DBAs must set baselines and establish change and access control to definitively answer these questions. Change-management tools provide you with the execution controls and the detailed information you need for these processes within the database environment.
[IMAGE][IMAGE] Recovery management[IMAGE] Return to Table of Contents
Recovery management simplifies backup and recovery operations, enabling organizations to achieve fast, reliable, right-sized database recoveries by automatically detecting and restoring missing files. These tools enable you to implement a comprehensive data recovery strategy across the enterprise by coordinating data recovery for multiple databases, operating systems and storage systems.
[IMAGE][IMAGE] Security[IMAGE] Return to Table of Contents
Databases contain critical and confidential data that is vital to business operations. This data often consists of private information collected from customers and employees.You must make sure this data is not compromised – for your sake and the sake of your IT organization. In addition, database and data security requirements are specifically called out in Sarbanes-Oxley (SOX), GLBA, HIPAA, 21 CFR 11, FISMA, the European Data Protection Directive and other similar regulations.
Companies not only need to define and monitor security configurations and best practices, but also watch for suspicious or fraudulent access. This helps customers protect databases from external and internal security threats. By finding and fixing security "holes" in the database environment, efficiently monitoring and auditing security-related database changes, and monitoring user-level behavior, customers satisfy strict data security, data privacy and data integrity requirements.
[IMAGE][IMAGE] Conclusion[IMAGE] Return to Table of Contents
Data volumes are increasing 30% per year. IT executives are being asked to deliver ever-higher levels of data availability, performance and reliability with fewer people and flat budgets. These seven steps will help you manage database assets in a centralized, automated and standardized fashion, eliminating redundancies and inefficiencies and reducing the specialized skills required to manage different databases.
About the author: Kausik Dasgupta is a senior manager of product management at BMC Software Inc. Over the course of his eight years with BMC, Dasgupta has gained significant expertise in the areas of distributed systems database management, specializing in database backup and recovery and IT service continuity management. Kausik Dasgupta received an MS in Computer Applications from Jorhat
Engineering College, India.
More information from SearchSQLServer.com
Tip: Maintenance checks for SQL Server
Tip: SQL Server backups made easy using Maintenance Plans
RSS: Sign up for our RSS feeds to receive new SQL Server expert advice every day
