Introducing Microsoft® SQL Server 2005

Yet another Microsoft product?  What is it and why is it better than what I use now?  You may have asked yourself these questions after reading the title of this article.  However, this innovative software is worth investigating.  

November 2005 marked a landmark software release for Microsoft.  After five years of intense development, Microsoft finally released the latest version of Microsoft SQL Server.  This article will highlight what is new in Microsoft SQL Server 2005, discuss reasons for upgrading, and discuss the implications for data recovery. 

Microsoft suggests what is new in SQL 2005 can be broken into three categories; Enterprise Data Management, Developer Productivity, and Business Intelligence.[1] 

What is new in SQL Server 2005?

Enterprise Data Management
Enterprise Data Management can be broken down further to help understand all of the features added to SQL Server 2005.  Enterprise Data Management includes manageability, availability, scalability, and security.

Manageability
“Creating integrated, scalable tools to manage more systems and more types of systems was one of the key goals for SQL Server 2005.” [2]  Enterprise Manager, Query Analyzer, and Analysis Manager have been replaced in Microsoft SQL Server 2005 with SQL Server Management Studio.[3]  This management studio is a plug-in for Visual Studio 2005 which is installed by default when SQL Server 2005 is installed.  The general idea is to provide one console to monitor and manage the SQL servers.

Availability
Database Mirroring – Allows for the failover to a secondary server on the primary server’s failure.

Figure 1: Basic Configuration of Database Mirroring[4]

 

Database Failover Clustering – Allows for the failover to alternate servers on the primary’s failure.  This type of clustering allows for up to eight nodes.

 

Availability Feature Database Mirroring Failover Clustering Automatic Failover Yes Yes Transparent Client Redirection Yes, auto-redirect Yes, reconnect to same IP Impact on Overall Throughput No impact to minimal No impact Zero Work Loss Yes Yes Requires Certified Hardware No Yes Provides Redundant Data Yes No

Table 1: Basic Configuration of Database Mirroring[5]

Database Snapshots – This allows for a read-only, "virtual" copy of a database, representing its state at a particular time.[6]  For more information on this topic please see http://www.databasejournal.com/features/mssql/article.php/3447711.

Database Fast Recovery – Allows for the connection to a recovering database while it is being recovered as soon as the transaction log is rolled forward.

Dedicated Administrator Connection – “administrators can use [the dedicated administrator connection] to access a running server even if the server is locked or otherwise unavailable. This capability will enable administrators to troubleshoot problems on a server by executing diagnostic functions or Transact-SQL statements”.[7] 

Online Operations

Index Operations – Administrators can create, rebuild and drop indexes online.[8]

Online Restore – This allows users to access all data on a server being restored except what is being actively restored.  So if you are restoring three tables from a backup, the users can access the first restored table while SQL is restoring the other two.[9]

Replication – “Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.”[10]

Scalability
“Scalability advancements such as table partitioning, snapshot isolation, and 64-bit support will enable you to build and deploy your most demanding applications using SQL Server 2005. The partitioning of large tables and indexes significantly enhances query performance against very large databases.”[11]  The most important of these to discuss is the Partitioning.

Table and Index Partitioning – “Table and index partitioning eases the management of large databases by facilitating the management of the database in smaller, more manageable chunks. While the concept of partitioning data across tables, databases, and servers is not new to the world of databases, SQL Server 2005 provides a new capability for the partitioning of tables across filegroups in a database. Horizontal partitioning allows for the division of a table into smaller groupings based on a partitioning scheme. Table partitioning is designed for very large databases, from hundreds of gigabytes to terabytes and beyond.”[12]

For more information on partitioning please see http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm or http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

Security
New security enhancements for SQL 2005 can be broken into four categories; Authorization, Authentication, Native Encryption, and Trustworthy Computing.

Authorization - SQL 2005 allows the administrator to administer permissions at a granular level, similar to Microsoft Windows Server 2003.  For more information please see SQL Server 2005 Security - Part 2 Authorization by Marcin Policht at http://www.databasejournal.com/features/mssql/article.php/3481751.

Authentication – Microsoft added support for Kerberos style authentication (Windows style).  For more information please see SQL Server 2005 Part 1 - Security (Authentication) by Marcin Policht at http://www.databasejournal.com/features/mssql/article.php/3461471

Encryption – SQL 2005 now has native encryption services that allow the administrator to encrypt data at a column level within the database, eliminating the need for third party data encryption products.  “SQL Server 2005 encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys. As shown in the following illustration, the encryption hierarchy is parallel to the hierarchy of securable objects that are described in Permissions Hierarchy.

 

The following illustration shows that each layer of the encryption hierarchy encrypts the layer beneath it. The top layer, the Service Master Key, is encrypted with the Windows DP API.”[13]

 

For more information please see: SQL Server 2005 Security - Part 3 Encryption by Marcin Policht at http://www.databasejournal.com/features/mssql/article.php/3483931

Trustworthy Computing – A term coined by the Microsoft Corporation to illustrate their focus on security.  In relation to SQL 2005, this means three things.  Secure by Design, Secure by Default and Secure in Deployment.[14]

That basically covers what’s new in SQL 2005 for Data Management, now let us turn our attention to what is new in Developer Productivity.

Developer Productivity
Microsoft has focused a lot of time and energy on making a product that is not only easy to administer, but is also easy to develop for.  “SQL Server 2005 includes many new technologies that bring significant increases in developer productivity. From .NET Framework support to tight integration with Visual Studio, these features provide developers with the ability to more easily create secure, robust database applications at a lower cost. SQL Server 2005 enables developers to take advantage of existing skills across a variety of development languages while providing an end-to-end development environment for the database. Native XML capabilities also allow developers to build new classes of connected applications across any platform or device.”[15]  Enhancements include:

Extended Language Support – Traditionally, SQL server only allowed developers Transact-SQL as a way to communicate with the database.  If the developer wanted to incorporate a feature that was not available in Transact-SQL, the developer had to go outside the database, write the function into another application and then use a COM object to connect to the database server.  By integrating the Common Language Runtime, SQL 2005 allows developers to use Transact-SQL, Microsoft Visual Basic.NET, and Microsoft Visual C#.NET natively from within the SQL server.[16]

Microsoft Visual Studio 2005 – “One of the key barriers to developer productivity has been the lack of integrated tools for database development and debugging. SQL Server 2005 breaks down this barrier by providing tight integration with Microsoft Visual Studio 2005.”[17]

Extensibility – User Defined Types and Aggregates, SQL Management Objects, Analysis Management Objects and Improved Data Access and Web Services through XML and ADO additions and improvements.[18]

Service Broker – “Service Broker is basically an asynchronous programming framework for database applications.  This means (among many other things) that you can establish reliable, asynchronous, bi-directional communications sessions between a client and server. Aside from the obvious benefits of a reliable connection - client able to run even if the network is temporarily unavailable, the asynchronous, bi-directional nature of the communications means that the client can queue up work as fast as the user can enter it for the server to process when it has processor cycles available and the server can send data to the client without the client requesting it, even if the client is not on line at the time.”[19]

Business Intelligence
Microsoft has added a lot of support for developing and supporting Business Intelligence from the integrated Business Intelligence Development Studio to SSI, Analysis Services and Reporting Services, even going so far as to provide integration with Microsoft Office.  Microsoft describes it as follows:

“SQL Server 2005 provides many new and enhanced business intelligence (BI) features designed to give you a competitive advantage. These advantages include integrating multiple data sources with Integration Services; enriching data and building complex business analytics with Analysis Services; and writing, managing, and delivering rich reports with Reporting Services. Review the following product and technical resources and other information designed to help you improve BI in your organization.

Integrate: Easily integrate data from a wide range of operational and enterprise data sources, and gain competitive advantage through a holistic view of your business.

Analyze: Gain an integrated view of all your business data as the foundation for your traditional reporting, OLAP analysis, scorecards, and data mining.

Report: Deliver the information employees need to make better business decisions—in virtually any business environment.

Data Mining: Explore data, discover patterns, and apply these patterns to business operations with an easy-to-use, extensible, accessible, and flexible platform.”[20]

Why Upgrade to SQL Server 2005

There are a lot of reasons to upgrade and I hate to sound like a commercial for Microsoft.  Here are a few of the biggest reasons:

Performance (Speed and Reliability):  Microsoft SQL Server 2005 is far and away the fastest SQL server released by Microsoft to date.  SQL Server 2005 is also the most scalable product released to date. 

Microsoft currently has announced a new initiative called Project REAL.  “Project REAL is a cooperative effort between Microsoft and a number of technology partners in the business intelligence (BI) industry to build on actual customer scenarios to discover best practices for creating BI applications based on SQL Server 2005. The term REAL in Project REAL is an acronym for Reference implementation, End-to-end, At scale, and Lots of users. Find out which partners are participating in Project REAL.  Project REAL uses authentic customer data to work through customer deployment issues and to address the full range of real-world challenges that companies face when analyzing large data sets.”[21]

Part of their dataset includes a 25TB (that is not a typo, it really is 25TB) database with data from Barnes and Noble.

Reliability:  Microsoft has made a lot of changes that should make the database more reliable, from the database mirroring and log shipping to changes in the page structure that allow for self correction internally.

Ease of Use:  Complete integration with Microsoft Visual Studio 2005 makes management and development under SQL Server 2005 a breeze.

Painless Upgrades:  Microsoft has taken almost all of the work out of upgrading by bundling the Upgrade Advisor with the installation CD.  “Microsoft SQL Server 2005 Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.” [22]  Once the outstanding issues are resolved, the Upgrade Wizard will update your databases automatically.  Be sure to make good backup before attempting an upgrade of any type.  Failure to do so could result in data loss.

Data Recovery Challenges and Opportunities

Redesigned System Tables.  Forget what you know about the system tables from SQL 7.0 and 2000.  Microsoft has re-written the system tables and replaced what most know from SQL 2000 with system views.  A copy of the system views map can be found at http://download.Microsoft.com/download/0/d/f/0dfe488e-a335-4480-8a8a-b405e32f4368/SQL2005_Sys_Views.pdf.

“System Views are predefined Microsoft created views for extracting SQL Server metadata. There are over 230 various System Views. To display all the views in SQL 2005, launch the SQL Management Studio; expand Databases, System Databases, and select master, Views, System Views.”[23]

Partitioning:  As stated earlier, database data can be distributed across multiple logical or physical file groups, making it potentially harder to find data if it goes missing.

New Data Types:  SQL Server 2005 has many new data types including:

·        XML (data and schemas)

·        User Defined data types

·        CLR (.NET code)

·        MAX (allows varchar and varbinary datatypes to exceed the traditional size limitations of 8064 bytes.

Zero Initialization: In SQL Server 2005, you no longer have to zero initialize the database.  In previous versions of SQL server you had to wait while the SQL server received the physical space from the NTFS driver and then zero all of the sectors to create a blank database.  Now, SQL 2005 can create databases without zeroing out all of the data sectors.  This makes database creation much faster, but potentially more risky if there is a need for data recovery.

Encryption:  With Native Encryption it will be potentially harder to recover data that has been corrupted or if the keys are lost or corrupted.

Scalability: With the size limitations effectively removed, expect database growth to be astronomical, challenging those that do data recovery to accommodate the new sizes and perform their data recoveries in a timely manner.

With these challenges comes a tremendous amount of opportunity.  Expect a huge growth in the need for SQL Server experts.  Not just those who know Transact-SQL, but those who understand database architecture partitioning, indexing, mirroring and clustering as well as those who can recover data in the unfortunate event something happens to a functioning SQL server.  Another expertise that will be required in the near future is that of a SQL backup expert – a person who understands the objective of the backup process and can formulate a plan to accomplish that objective.

While we are not able to help with all of the challenges that await you with SQL Server 2005, partnering with Ontrack^® enables you to help mitigate data loss when disasters occur. Ontrack realizes that time is of the essence when you experience data loss; Ontrack’s goal is to get the data back to you as fast as possible. For the past 20 years, Ontrack has been pioneering advanced recovery techniques and technology. Ontrack continues to invest in the future of technology and innovations in data storage. When you choose Ontrack, you are partnering with the recovery experts.  Ontrack has developed tools that allow for the recovery of SQL Server 2005 data in almost every disaster scenario. 

A few of the more common cases that we encounter and are able to recover from are:

Failed Hardware – We maintain clean rooms in all of our facilities and can assist with everything from a failed RAID array to a single failed disk.

File System Corruption – Even if the volume will not mount, we can usually recover the critical data from the volume and copy it into a new SQL Server 2005 database.

Database Corruption – In most cases we are able to recover data from even the most corrupt files.  With our ground up approach, we are able to recover data from files that are otherwise un-mountable and copy those rows into a fully functional SQL Server 2005 database.

Missing data – If the data has been deleted or is simply missing, we can help.  Dropped databases, tables or even rows are usually recoverable.

Failed Upgrade – Even if the upgrade fails, we can usually recover the data and copy it in to a new functional database.

For those critical data loss situations where downtime is not an option, using our Remote Data Recovery^™ technology gets the data back as fast as possible. As the fastest solution for SQL recoveries, Remote Data Recovery can copy the recovered data into a SQL server that is setup on you or your client’s site. They only need SQL server running. For best results we recommend that the storage device we are working on be connected to a SQL server, because all of the data transactions will be occurring on a single machine.

Ontrack Data Recovery^™ (www.ontrack.co.uk) is the largest, most experienced and technologically advanced provider of data recovery products and services worldwide. Ontrack^® is able to recover lost or corrupted data from virtually all operating systems and types of storage devices through its do-it-yourself, remote and in-lab capabilities, using its hundreds of proprietary tools and techniques.  Ontrack operates 18 worldwide locations featuring customer support in 11 languages.  With more than 150 engineers worldwide, Ontrack invests in technology and techniques to speed recovery times and enhance recovery capabilities.