MSSQL 2008
1.1.1 Key Areas
One of the first items noticed when reviewing Microsoft documentation on SQL Server 2008 is the new lingo classifying features and benefits into groups, or Key Areas. There are four Key Areas, Enterprise Data Platform, Dynamic Development, Beyond Relational Database, and Pervasive Insight. This article will follow these groupings in an effort to make cross referencing with Microsoft articles easier.
1.1.2 Enterprise Data Platform
The Enterprise Data Platform grouping, sometimes referred to as the Mission Critical Platform, contains the core SQL Server Engine features and benefits including Data Encryption methods, Resource Management, System Analysis, and Server Management Features.
1.1.3 Encryption
1.1.4 Related Articles
There are several encryption options; the first is called Transparent Data Encryption. In SQL Server 2008, the entire database can be encrypted by the SQL Engine. This method encrypts all database data and log files for a database. By using this all-encompassing method, all Indexes and Tables are encrypted. Changes to programming applications are not required.
The next encryption feature is Backup Encryption. SQL Server 2008 has a method of encrypting backups to prevent data disclosure or tampering. In addition, Restoring of backups can be limited to specific users.
Lastly, there are new options for External Key Management. If you are involved with credit card processing or PCI (payment card industry) compliance, SQL 2008 will support Hardware Security Modules (HSM). Hardware Security Modules are third party hardware solutions used to store Keys in a location separate from the data they protect.
1.1.5 Auditing
In addition to the standard auditing of logon / logoffs and permission changes, SQL Server 2008 allows for monitoring of data changes or access. Auditing is configured by TSQL statements as in this example: AUDIT UPDATE(Salary) ON Employee TO MyAuditFolder WHERE Salary>200000
1.1.6 Data Compression
Usually, data compression is associated with general hard disk savings, and with smaller physical files, backup times are reduced. While this holds true for SQL Server Data Compression, the main goal is Fact Table size reduction. The stated advantages for Data Compression include the following:
Improves query performance by reducing I/O and increasing buffer-hit rates
Provides compression ratios of 2X to 7X for real DW fact data
Is orthogonal to other features
Is available for both data and indexes
According to Microsoft, while using compression will slightly increase CPU usage, overall system performance will be improved because of less IO.
1.1.7 Resource Governor
New in SQL Server 2008 is the Resource Governor. The Governor is used to restrict users or groups of users from consuming high levels of resources. Items that can be monitored include CPU bandwidth, timeout waits, execution times, blocking times, and idle times. If a Resource Governor threshold level is reached, the system can trigger an event or stop the process. There are a series of steps involved in utilizing the Resource Governor that will be reviewed in an upcoming article.
1.1.8 Hot Plug CPU
In SQL Server 2008, additional CPUs can be added on the fly if the underlying hardware supports it.
1.1.9 Performance Data
The amount of Performance Counters in SQL Server 2008 has been expanded compared to earlier versions. IO and memory usage counters are just a couple of the items that can be collected to monitor performance. The data collected by the counters is stored in a centralized data warehouse. Microsoft states that running the default set of performance related monitors will consume less than 5% of CPU and memory resources.
There is also now a Performance Dashboard tool that can read saved performance data. In addition, historical and baseline comparisons can be made and used to create action triggers. For example, if memory use exceeds a threshold for more than five minutes, a more detailed data collection can be automatically triggered.
New in SQL Server 2008 is the Performance Studio. The Studio is a collection of performance tools. Together they can be used for monitoring, troubleshooting, tuning and reporting. The Data Collector component of the studio is configurable and low overhead. It supports several collecting methods including TSQL queries, SQL Trace, and Perfmon Counters. Data can also be collected programmatically. Once data is collected, there are drill-down and aggregate reporting options. Microsoft lists these six client side features of the Performance Studio:
SQL Server dashboard
Performance monitoring
Current and historical data analysis
Suggestions for potential performance tuning
Data collection sets-based reports
MDW-based reports
1.1.10 Installation
Setup and Installation of SQL Server 2008 has also been enhanced. Configuration data and engine bits have been separated so it will be possible to create a disk image of a basic unconfigured system making distribution to several servers easier. Also, the installation will be able to retrieve the latest updates from the Microsoft web site. Another feature is the ability to install SQL, service pack, and patches as a single step. On the other end of this feature, is the ability to uninstall service packs.
1.1.11 Conclusion
There are many significant features and benefits coming in SQL Server 2008. In Part 2 of this series, we’ll examine Development, Integration, Business Intelligence, and new Data Types.
1.1.12 Introduction
This article will highlight some of the new features and benefits found in SQL Server 2008. Some of the new features include Development changes, new Business Intelligence features, Integration additions, and new Data Types. Listed below are some of the items covered that were covered in Part 1 of this series.
Encryption - Transparent Data Encryption, which enables an entire database to be encrypted. Backup Encryption for secure database maintenance. And lastly External Key Management.
Auditing of data changes.
Data Compression for Fact Table size reduction.
Resource Governor - The Resource Governor can be used to trigger an event or stop a runaway or resource intensive process.
Performance Data - There is a new Performance Dashboard tool that can read saved performance data. In addition, there are new reports, monitoring, and tuning options.
SQL Server 2008 will be released approximately February of 2008, along with a new version of Visual Studio and Windows. A CTP (Community Technology Preview) of SQL 2008 is currently available for download from the Microsoft URL http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx.
1.1.13 Dynamic Development
SQL 2008 leverages the new Dot Net Framework 3.0 with LINQ (Language Integrated Query). In addition, there is more efficient support for Business Data Entities along with data synchronization options. Also, there are new ADO and Visual Studio development options. Collectively, these are labeled Dynamic Development and are reviewed below.
1.1.14 Entity Data Services
SQL Server 2008 and ADO.NET now allow for high level business objects to be created, such as Customers or Parts. These entities can be used rather than the standard method of returning individual rows and tables. If you’re using E-R (entity relationship) modeling, your objects in SQL will now match your modeling. There are several new ADO.NET frameworks that can access these entities such as the Line-of-Business (LOB) framework and the Entity Query Language (eSQL).
1.1.15 LINQ
1.1.16 Related Articles
LINQ provides a standard development syntax for accessing data, regardless of where the data resides. For example, the same syntax can access either SQL Server or XML data. LINQ is used rather than TSQL inside the application language, such as C# or VB.
1.1.17 Data Synchronizing Features
The combination of SQL 2008, Visual Studio, and ADO.NET bring together new methods of creating synchronizing or frequently disconnected applications, making it easier to create client applications that synchronize with a central database. SQL 2005 started by providing support for change tracking by using triggers. SQL 2008 synchronizing is better integrated and optimized.
1.1.18 Beyond Relational Databases
These next groups of features are collectively grouped as “Beyond Relational”. They include new location, geometry, data and time data types. In addition, there are new Full Text and File Stream options built into SQL Server 2008.
1.1.19 Large UDT
Previously, in SQL 2005, User Defined Types (UDT) could not be larger than 8,000 bytes. In SQL 2008 there is no longer any size restriction, allowing storage of very large UDTs.
1.1.20 Dates and Times
There are new Date and Time data types in SQL 2008.
Date. This is a data type with a date only, no time.
Time. A Time data type without a date component. Precision can be up to 100 nanoseconds.
Date Time Offset. This data type will store a Universal Coordinated Time (UTC) time-zone aware value.
1.1.21 File Stream
The new data type VarBinary(Max) FileStream allows for a way to manipulate binary data using TSQL Select, Insert, Update, and Delete statements. In the past, to store binary data a BLOB, accessed by a Dot.Net application was typically used. Now, SQL functions such as triggers, Full Text Search, and backup restore can be applied to binary data.
1.1.22 Spatial Data
The new Spatial Data type allows Latitude, Longitude, and GPS-based data entries to be natively stored inside SQL Server. The data type conforms to several industry standards such as Open Geospatial Consortium (OGC) Simple Features for SQL and ISO 19125 Simple Feature Access.
1.1.23 Table Value Parameters
In previous versions of SQL Server, there wasn’t a native way to pass a table to a stored procedure. The usual workaround was to pass a large varchar or XML type and parse through it. Now, in SQL Server 2008, Table Parameters are available. The following provides a simple example of passing a table into a Stored Procedure.
CREATE TYPE PartType
AS Table (PartID varchar(50), Descr varchar(100), createdate datetime);
CREATE PROCEDURE AddPart(@PartList PartType READONLY)
AS
SELECT * FROM @PartList
DECLARE @PartTable PartType;
INSERT INTO @PartTable values('Part1', N'Table Test', '2007-08-20');
EXEC AddPart @PartTable
1.1.24 Full Text Search
There are Full Text Search changes in SQL Server 2008 including native indexes, thesaurus files stored as metadata, and the ability to perform a Backup.
1.1.25 Reporting Server
Memory management in SQL Server 2008 Reporting Service is improved. So running large reports will not consume all available memory. In addition, report rendering has more consistency than before.
1.1.26 SQL 2000 Support Ends
As explained in Part 1 of this series, Mainstream Support for SQL 2000 is coming to an end in April 2008. This includes the CE version.
1.1.27 Conclusion
SQL Server 2008 has many practical and useful improvements. The new Date and Time data types will help simplify some applications. Listed below is a summary of the features and improvements reviewed so far:
Transparent Data Encryption allows for an entire database, all tables and data, to be encrypted on the fly without application programming.
Backups can be encrypted to prevent data disclosure or tampering.
Data changes and access can now be audited.
Fact Tables can be compressed for performance benefits.
The Resource Governor can prevent runaway resource usage.
SQL 2008 supports Hot Plug CPU.
Performance Counters have been greatly expanded.
Installation has been simplified.
In Part 3 of this series, we’ll cover the following SQL Server 2008 topics:
Data Integration Features such as the MERGE statement, Parallelism, SSIS multiple processor improvements, and look up performance improvements.
Analysis Service Improvements including BI Stack performance, Scale out analysis, Block computations and Perspectives.
Microsoft Office 2007 Integration such as Exporting Reporting Service reports as Word docs, SSRS format and font improvements, and the Office Tool Bar.
1.1.28 SQL Server Integration Services
SSIS (SQL Server Integration Services) is a built in application for developing and executing ETL (extraction, transformation, and load) packages. SSIS replaced SQL 2000 DTS. Integration Services includes the necessary wizards, tools, and tasks for creating both simple import export packages, as well very complex data cleansing operations. SQL Server 2008 SSIS includes a number of improvements and enhancements such as better parallel execution. In SSIS 2005, the pipeline didn’t scale past two processors. SSIS 2008 will scale past two processors on multiprocessor machines. Also, the newly redesigned pipeline improves performance on large packages that contain long sub-trees. In addition, the SSIS engine is reported to be more stable with fewer incidents of deadlocks.
The Lookup component has been improved. Lookups are a very common SSIS operation that fetches a related piece of information. Such as a lookup obtaining the Customer Name from the CustomerID and brining that value into the dataset being worked on. Because Lookups are very common in SSIS and can be performed on large million row datasets, performance could be poor. Improvements have been made in SQL 2008 to increase performance. In addition, Lookups can be done on a variety of data sources including ADO.NET, XML, OLEDB, and other SSIS packages.
1.1.29 Merge
SQL 2008 includes the TSQL command MERGE. Using this statement allows a single statement to UPDATE, INSERT, or DELETE a row depending on its condition. The example below demonstrates the MEGRE being used to combine a new list of Inventory Item descriptions into the existing Inventory Master. In addition to new Descriptions, there are some new parts included in the NewInventory table. Without the Merge statement, two commands would need to run. The first would look for a match then upgrade the Description. The second statement would look for non matches and then INSERT. With MERGE, one statement can perform both tasks as shown below.
MERGE InventoryMaster AS im
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON im. InventoryID = src. InventoryID
WHEN MATCHED THEN
UPDATE SET im.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);
1.1.30 Analysis Service
1.1.31 Related Articles
A number of improvements and enhancements have been made to SSAS (SQL Server Analysis Server). The BI Stack has been improved for increased performance. Commodity hardware can be by utilized by scale out management tools. Also, Block Computation can provide significant performance improvements in cube analysis.
1.1.32 Reporting Services
Processing and performance have been improved in SSRS (SQL Server Reporting Server). Large reports will no longer consume all available memory. In addition, there is greater consistency between layout and render. Also, the TABLIX, a cross between a table and a matrix is included in SQL SSRS 2008. Application Embedding allows URLs in reports to point to a calling application.
1.1.33 Microsoft Office 2007
SQL Server 2008 can tightly integrate with Microsoft Office 2007. For example, in SQL Server Reporting Server reports can now export directly to Word. In addition, both Word and Excel can be used as templates for SSRS reports by using the Report Authoring tool. Excel SSAS performance has been improved and there is a data mining add-in.
1.1.34 Conclusion
SQL Server 2008 contains many new features and enhancements, a large numbers of which were not covered in this series. Additional information can be found at the main SQL 2008 Microsoft page: http://www.microsoft.com/sql/2008/default.mspx. Listed below is a concise bulleted list of the SQL Server 2008 features reviewed in this series.
· Transparent Data Encryption. The ability to encrypt an entire database.
· Backup Encryption. Executed at backup time to prevent tampering.
· External Key Management. Storing Keys separate from the data.
· Auditing. Monitoring of data access.
· Data Compression. Fact Table size reduction and improved performance.
· Resource Governor. Restrict users or groups from consuming high levels or resources.
· Hot Plug CPU. Add CPUs on the fly.
· Performance Studio. Collection of performance monitoring tools.
· Installation improvements. Disk images and service pack uninstall options.
· Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.
· Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)
· LINQ. Development query language for access multiple types of data such as SQL and XML.
· Data Synchronizing. Development of frequently disconnected applications.
· Large UDT. No size restriction on UDT.
· Dates and Times. New data types: Date, Time, Date Time Offset.
· File Stream. New data type VarBinary(Max) FileStream for managing binary data.
· Table Value Parameters. The ability to pass an entire table to a stored procedure.
· Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
· Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
· Reporting Server. Improved memory management.
· SQL Server Integration Service. Improved multiprocessor support and faster lookups.
· MERGE. TSQL command combining Insert, Update, and Delete.
· SQL Server Analysis Server. Stack improvements, faster block computations.
· SQL Server Reporting Server. Improved memory management and better rendering.
· Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.
· SQL 200 Support Ends. Mainstream Support for SQL 2000 is coming to an end. Feature and benefits included in Mainstream Support include the ability to submit requests for product feature changes, Security Updates, Non Security Hotfixes, Complimentary support, and Paid Support. This Mainstream Support will expire on 4/8/2008 for SQL Server 2000 64-bit Edition, SQL Server 2000 Developer, SQL Server 2000 Enterprise, SQL Server 2000 Standard, SQL Server 2000 CE, and SQL Server 2000 Workgroup Edition. Extended Support, consisting of Security Updates and Paid Support will continue until 2013. A full description of support phases can be found at these Microsoft URLs: “Microsoft Support Lifecycle” and “Microsoft Support Lifecycle Policy FAQ”. Many resellers will discontinue selling SQL 2000 in December of 2007. Also, no version of SQL 2000 will be supported on Vista, including SQL 2000 Express.