Free eBook “Brad’s Sure Guide to SQL Server 2008: The Top Ten New Features for DBAs” by Brad McGehee. SQL Server 2008 has hundreds of new features and improvements for Production DBAs, Developer DBAs, and Business Intelligence specialists. Some of the changes are significant, while others are minor, but still important.
• Management Studio Improvements – SSMS 2008 starts to rebuild its reputation in the DBA community with the introduction of IntelliSense and a debugger for T-SQL, the ability to run multi-server queries as well as improved performance and numerous other enhancements.
• Policy-Based Management – every DBA knows the frustration of trying to manage tens of servers, each of which has a subtly different configuration. Policy-based management could ease a lot of this pain.
• Data Compression – the first immutable law of database administration is that databases will grow (and grow) over time. SQL 2008 enterprise brings with it data and backup compression, thus reducing physical file sizes as well as disk I/O.
• Resource Governor – everyone knows it’s not “best practice” but most DBAs, nevertheless, deal with situations whereby a SQL Server that supports OLTP applications is also used extensively for reporting. Often this can cause enough resource contention to affect production activity. Resource Governor promises to be a valuable tool in curtailing this problem
• Performance Data Collector – historically, DBAs have used a mishmash of different tools to get performance data out of SQL Server, including Profiler, System Monitor, DMVs, and more. Performance Data Collector is one of the tools that starts to bring together all this information, moving more towards a single-location, single-format model of collecting, storing and analyzing performance data.
• Transparent Data Encryption – whether we like it or not, Security is an issue with which DBAs are going to become more and more familiar. 2008 Enterprise adds full database-level encryption to column-level encryption.
• SQL Server Audit – again, auditing is an area in which DBAs have tended to use several different methods to track the activities they need to monitor, from DDL triggers, to SQL Trace to third-party tools. The new SQL Server Audit attempts to expand the range of activities that can be easily monitored, as well as being easier for the DBA to use and more granular.
• New Data Types – it’s a long time since SQL Server has been a simple “relational data store”, and SQL 2008 pushes boundaries again, introducing spatial data types, hierarchical data, as well as unstructured data such as videos, Excel files and so on.
• Extended Events – These have been introduced in order to help “centralize” performance data collection in SQL. They provide a generic event-handling system aimed at making it much easier to capture, store and act on disparate troubleshooting data from SQL Server, the Windows OS, and applications.
• Change Data Capture –DBA always strive to separate OLTP and OLAP activities cleanly onto separate servers. However, moving data between the two, while keeping them synchronized, has proved a difficult problem. Change Data Capture could well be the answer.
- Management Studio Improvements
- Policy-Based Management
- Data Compression
- Resource Governor
- Performance Data Collector
- Transparent Data Encryption
- SQL Server Audit
- New Data Types
- Extended Events
- Change Data Capture
File size: 3.36 MB
Number of pages: 113