SQL Server 2008 R2

 By Matt Nicholson

A new release of Microsoft’s flagship database brings some important enhancements. Matt Nicholson investigates.

HardCopy Issue: 49 | Found In: Database | Published: 14/09/2010 | Last Revision: 17/01/2011

SQL Server is Microsoft’s flagship database server product. In contrast to its main competitor, Oracle Database, SQL Server tightly integrates with Windows Server and is only available on the Windows platform. One of its many strengths is the sophisticated reporting and analysis tools that come built-in, rather than as optional extras; and although in the past it has been seen as less scalable than Oracle, SQL Server 2008 introduces features that make it capable of handling the needs of the largest enterprise. As its name suggests, SQL Server 2008 Release 2 is not a new version but rather adds some important functionality to the existing SQL Server 2008 database core, which otherwise remains unchanged. We have made it clear which features are specific to R2.

Security and scalability

SQL Server failover clustering provides transparent movement to another node in a failover cluster should the current node fail, without interrupting the application that is using the database. It is built on Microsoft Cluster Service, which is part of Windows Server. As far as the application is concerned, the whole cluster is treated as a single instance of the database. SQL Server 2008 introduces a new architecture that can support up to 16 failover nodes in the Enterprise edition (two in the Standard edition). Furthermore, improvements to database mirroring make the process more reliable and more transparent.

The 2008 release brings a number of enhancements to improve the handling of large databases. With many databases, current data is primarily subject to INSERT, UPDATE or DELETE operations, while older data is more likely to be the subject of a query. With SQL Server 2008, large tables and their associated indexes can be partitioned horizontally so that, for example, data relevant to each month is stored separately, even though the table as a whole is still treated as a single logical entity. This makes the data far more manageable and speeds up many everyday operations. Other enhancements include transparent data and backup compression, transparent data encryption, and Sparse Columns which optimise storage of columns where a large portion of the entries have a NULL value. Another problem that SQL Server 2008 addresses is that of integrating unstructured data, such as images or documents, with structured data. This is achieved through FILESTREAM storage which integrates the database engine with the Windows NTFS file system. BLOB (Binary Large Object) data is stored as files within the file system, while remaining available to standard database operations such as insert, update, search and query, and to SQL Server management tools.

Application development

SQL Server 2008 introduces new data types which open up new possibilities for developers. For a start there are the two new spatial data types. The geometry data type is tailored to the storage of planar or Euclidean data, while the geography type is suitable for ellipsoidal data such as latitude and longitude coordinates. Developers can easily integrate geography data with Microsoft’s Virtual Earth application. Then there is the hierarchyid data type, designed to capture hierarchical relationships such as those expressed in XML documents. Hierarchyid data represent positions within a tree hierarchy, and there is support for inserting, changing and comparing such data in a number of ways. SQL Server 2008 also supports a number of enhancements to the .NET Framework, including LINQ (Language Integrated Query), the ADO.NET Entity Framework and Synchronisation Services.

Database management

It has always been possible to monitor multiple servers from SQL Server Management Studio, but SQL Server 2008 R2 makes it easier through new dashboard displays that give you centralised control of server groups, effectively allowing you to treat a group of servers as a single entity. You can now, for example, apply commands such as ADD USER to the whole group, rather than having to apply it server by server. Alongside Multi-Server Management, Microsoft has also introduced a new unit of deployment in the Data-tier Application. This contains everything relating to a database application, including the code, the databases, user logins, management tasks, agent jobs and so forth. Once packaged, an application can be moved from one location to another transparently, without the user being aware that anything has changed. Visual Studio 2010 supports the development of Data-tier Applications and deployment to SQL Server 2008 R2. Multi-Server Management also works with Hyper-V technology as featured in Windows Server 2008. Also new to SQL Server 2008 R2, Master Data Services is concerned with maintaining “a single version of the truth.” Built using technology acquired when Microsoft purchased Stratature in 2007, Master Data Services helps IT departments maintain data integrity between multiple databases which, although representing the same business entities, may do so in incompatible ways. Different departments may use different schema to define a ‘customer’, for example, or may have different values stored for a particular customer’s date of birth. Master Data Services allows such anomalies to be identified and reconciled, and for changes to data to be properly audited.

Business Intelligence

The most prominent of the new features introduced with R2 plays a part in an initiative that Microsoft has dubbed ‘Managed Self Service Business Intelligence’. The problem is that desktop tools such as Microsoft Excel are being used more and more to generate increasingly sophisticated reports from an ever widening range of data sources. While ordinary business users find access to such sophisticated Business Intelligence (BI) tools very attractive, their ad-hoc use can become a major management and security issue as reports are passed around and copied in an uncontrolled fashion. Furthermore, the information such reports contain is static, and so quickly ceases to reflect the current situation.

PowerPivot in action, letting users create powerful reports on live data without jeopardising integrity and security.

What is needed is a system that allows end-users to generate and share reports as and when required, while still allowing IT managers to control performance and distribution. Previously codenamed Project Gemini, Microsoft’s solution is PowerPivot which is implemented as two separate add-ins: one for Excel 2010 and the other for SharePoint 2010. PowerPivot for Excel 2010 is a free download that allows users to build complex reports from multiple and disparate sources. It turns Excel 2010 into a powerful BI tool for analysing and understanding data which can come from relational databases, other spreadsheets, reports, text files and even datafeeds from the Internet. PowerPivot for SharePoint 2010 allows those working with the Excel 2010 add-in to publish their reports to SharePoint 2010 from where they can be made available to others in a managed and secure fashion, while allowing administrators to monitor performance and ensure that best use is made of available hardware. Central to this is the Operations Dashboard, which is only available if SharePoint 2010 is working with SQL Server 2008 R2. Furthermore, SQL Server 2008 R2 comes with Report Builder 3.0 which includes new charts and dials thanks to technology recently acquired from Dundas, and new facilities for displaying geo-spatial data. You can also create Report Parts and Shared Datasets that can be stored in SharePoint for use in other reports. It is worth noting that Report Builder 3.0 can be downloaded separately for use with previous versions of SQL Server.

Streaming data

On a rather different tack, SQL Server 2008 R2 includes new facilities for handling high-speed streams of real-time data, such as financial data feeds, server events in a large data centre, and feeds from RFID tags or automated production lines. Such data needs to be analysed quickly so that decisions can be made before the data actually reaches the database. StreamInsight works in-memory, supporting Complex Event Processing (CEP) applications that can respond to specific conditions. Workflows can be triggered if a stock price reaches a certain value, for example, or the memory available in a server falls below a certain level. Such applications may need to be able to handle thousands, or even hundreds of thousands, of data events a second. StreamInsight presents a .NET class, which means that CEP applications are best built using Visual Studio. Again, this represents new opportunities for specialist business partners.

Choosing your edition

SQL Server 2008 comes in a wide range of editions, all available on Volume Licensing and all with the option to license per processor. All except the Web and Datacenter editions can be licensed on a Server/CAL basis. Free to download, the Express edition supports operation on a single processor within 1Gb of memory, and a maximum database size of 4Gb. The Workgroup edition can be used with two processors and up to 4Gb of memory, while the Standard edition supports up to four CPUs and 64Gb of memory. The Standard edition also introduces support for mirroring, but single-threaded only, and 2-node failover clustering. To take full advantage of all the features introduced by SQL Server 2008 R2, you need the Enterprise edition which allows up to 25 instances and 64 processors, and 16-node failover clustering. Alternatively there is the Developer edition which offers the same features but is licensed per developer for designing, developing and testing purposes only – it cannot be used as a production server. There is also the Web edition which supports up to four CPUs and is intended for use behind a public Web site. Finally, SQL Server 2008 R2 introduces two new top-end editions. The Datacenter edition has been designed to match Windows Server 2008 R2 Datacenter edition, and allows unlimited instances together with support for 256 logical processors. It also includes the Premium version of StreamInsight which is necessary if your CEP application needs to handle more than 5,000 transactions a second. Then there is the Parallel Data Warehouse edition. Previously codenamed Project Madison, this is designed for massively parallel architectures than can scale to hundreds of terabytes of data. The Parallel Data Warehouse edition is currently in beta.

Be the first to comment about this article...