SQL Server 2008: IT to Business – Part 1

Unlike its predecessor, SQL Server 2008 delivered by Microsoft enables us to deliver more real-time information due to the enhanced capabilities of spatial data handling and geographical information. We can manage, access, and deliver information across, not just within the organization, but also worldwide with its extension of SQL Azure.

SQL Azure offers a high availability and scalability of data through its distributed data services. Thus, to quote from the Microsoft’s official site, SQL Server 2008 along with SQL Azure is mainly built upon the following four pillars:

1.Mission-critical platform
2.Empowered IT
3.Dynamic Development
4.Pervasive Insight.

Mission-Critical Platform:

It is crucial to link our application between our IT department and our business. In the context of such “line-of-business” applications, the data should be securely and reliably stored and distributed –  which is a key point, yet critical to do. SQL Server 2008 takes the advancements of the latest hardware technology. Microsoft Windows Server works jointly with SQL Server 2008 in such scenarios. The New Hyper-V Live Migration System available in Microsoft’s Windows Server helps in achieving this. It allows an organization to move a virtual machine between host servers without much processing requirement as well as without interrupting the service.

On the managing front of a mission-critical application at an enterprise level, SQL Server 2008 comes with policy-based management. We can define and enforce the policies and the principle of such policy based management is defined on four terminologies: Target, Facet, Condition and Policy.

Target is an entity that is going to be managed and ideally a target could be a database, a table, an index, etc. Facet is a set of properties that the target is managed upon. Condition takes up the boolean value that specifies the state of the facet, while Policy checks and enforces the condition upon the target. Once Policy is defined, it can be executed on anyone of the following mode:

1.On Demand
2.On Schedule
3.On Change – Log Only
4.On Change – Prevent

Out of the above four, the last two, on change – log only and on change – prevent are very useful for a DBA to track and prevent any violation of rules on real-time because On Change – Prevent uses DDL triggers to check the policies and On Change – Log Only uses event notification to check the policy and logs any policy violation that occurs.

Data encryption is yet another important feature available in mission-critical platform of SQL Server 2008. SQL Server 2005 provides data encryption feature on individual data whereas SQL Server 2008 enables it at database level. Both the data and log files are encrypted and upon reading out from the disk it is decrypted automatically. Moreover, enforcing data encryption can be done on DDL commands (CREATE/ALTER/DROP) by turning on-off the feature using data encryption key. As far as encryption strength is concerned, cryptographically both the data encryption and cell encryption has the same encryption strength.

Empowered IT

The new features such as resource governor and data compression comes under this head.

In real-time environment, there could be a sudden shortage in CPU memory space due to long-running T-SQL,  background database backup happening, etc , which ultimately results in reduced query performance. Overcoming this problem becomes critical for a database administrator. A database administrator can set priorities on application basis and allocate memory space accordingly using the new feature called resource governor. Resource governor acts on three fundamental concepts: Resource pool, workload group, and classification. In a resource pool, a DBA can specify minimum and maximum memory in percentage. A workload group is a container that accepts sessions from SQL users. Classification defines the internal rules that classify the requests and routes them to workload group.

Data compression was first introduced in SQL Server 2005 Service Pack 2 in which a new data type called vardecimal to store decimal and numeric types. This feature is now extended to fixed length storage types such char, float, and integer in SQL Server 2008. Data compression’s main advantage is that it increases the storage capacity and improves query performance. However SQL Server 2008 supports both row-level and page-level compression. There are two ways available to estimate the storage spaces needed for tables and indexes. One is with the help of built-in stored procedure “sp_estimate_data_compression_savings” and another is using SQL Server 2008 data compression wizard. Please note that this feature available only SQL Server 2008 Enterprise Edition and Developer Edition.

One more type of compression is backup compression that allows to compress the backup of the database alone to be compressed rather than compressing the whole database and automatic decompression facility is provided upon restore.

So far, we discussed the SQL Server 2008, about its new features (available platform wise).  In the Part 2 of this post, we will deal at length about the other two main pillars of SQL Server 2008 – dynamic development and pervasive insight.