As a software professional i would attend some technical interviews with some reputed companies for their open positions. Most of them asked me What is the differenced between SQL 2000 and 2005.
Since i have worked on all SQLversions from SQL Server 7.0/2000 to 2008 r2, I felt just like our human race is evolving, SQL Server is also evolving. Each release will meet with exact requirement of the industry it was born.
When SQL Server has evoled from SQL Server 2000 to SQL Server 2005, this was the major difference we felt, in a quick summary in tabular format.
| Feature | SQL Server 2000 | SQL Server 2005 |
| Security | Owner = Schema, hard to remove old users at times | Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates. |
| Encryption | No options built in, expensive third party options with proprietary skills required to implement properly. | Encryption and key management build in. |
| High Availability | Clustering or Log Shipping require Enterprise Edition. Expensive hardware. | Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware. |
| Scalability | Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support. | 4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation. |
That’s for a quick view, apart from that there was few Edition releases.
SQL Server Version History
(Courtesy Wikipedia)
| Version | Year | Release Name | Codename |
|---|---|---|---|
| 1.0(OS/2) | 1989 | SQL Server 1.0(16bit) | – |
| 1.1(OS/2) | 1991 | SQL Server 1.1(16bit) | – |
| 4.21(WinNT) | 1993 | SQL Server 4.21 | SQLNT |
| 6.0 | 1995 | SQL Server 6.0 | SQL95 |
| 6.5 | 1996 | SQL Server 6.5 | Hydra |
| 7.0 | 1998 | SQL Server 7.0 | Sphinx |
| – | 1999 | SQL Server 7.0OLAP Tools | Plato |
| 8.0 | 2000 | SQL Server 2000 | Shiloh |
| 8.0 | 2003 | SQL Server 200064-bit Edition | Liberty |
| 9.0 | 2005 | SQL Server 2005 | Yukon |
| 10.0 | 2008 | SQL Server 2008 | Katmai |
| 10.5 | 2010 | SQL Server 2008 R2 | Kilimanjaro (aka KJ) |
SQL Server Base
The code base for MS SQL Server (prior to version 7.0) originated in Sybase SQL Server, and was Microsoft’s entry to the enterprise-level database market, competing against Oracle, IBM, and, later, Sybase. Microsoft, Sybase and Ashton-Tate originally teamed up to create and market the first version named SQL Server 1.0 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 3.0 on Unix, VMS, etc. Microsoft SQL Server 4.2 was shipped around 1992 (available bundled with Microsoft OS/2 version 1.3). Later Microsoft SQL Server 4.21 for Windows NT was released at the same time as Windows NT 3.1.
Microsoft SQL Server v6.0 was the first version designed for NT, and did not include any direction from Sybase.
About the time Windows NT was released, Sybase and Microsoft parted ways and each pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server.
Since parting ways, several revisions have been done independently.
SQL Server 7.0 was a rewrite from the legacy Sybase code. It was succeeded by SQL Server 2000, which was the first edition to be launched in a variant for the IA-64 architecture.
SQL Server 2000
Microsoft SQL Server 2000 is a full-featured relational database management system (RDBMS) that offers a variety of administrative tools to ease the burdens of database development, maintenance and administration. In this article, we’ll cover six of the more frequently used tools: Enterprise Manager, Query Analyzer, SQL Profiler, Service Manager, Data Transformation Services and Books Online.
Query Analyzer offers a quick and dirty method for performing queries against any of your SQL Server databases. It’s a great way to quickly pull information out of a database in response to a user request, test queries before implementing them in other applications, create/modify stored procedures and execute administrative tasks.
SQL Profiler provides a window into the inner workings of your database. You can monitor many different event types and observe database performance in real time. SQL Profiler allows you to capture and replay system “traces” that log various activities. It’s a great tool for optimizing databases with performance issues or troubleshooting particular problems.
Service Manager is used to control the MSSQLServer (the main SQL Server process), MSDTC (Microsoft Distributed Transaction Coordinator) and SQLServerAgent processes. An icon for this service normally resides in the system tray of machines running SQL Server. You can use Service Manager to start, stop or pause any one of these services.
Data Transformation Services (DTS) provide an extremely flexible method for importing and exporting data between a Microsoft SQL Server installation and a large variety of other formats. The most commonly used DTS application is the “Import and Export Data” wizard found in the SQL Server program group.
Books Online is an often overlooked resource provided with SQL Server that contains answers to a variety of administrative, development and installation issues. It’s a great resource to consult before turning to the Internet or technical support.
Hopefully, this article has provided you with a brief introduction to the various tools available to Microsoft SQL Server users
SQL Server 2005
SQL Server 2005 (codenamed Yukon), released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data. For this purpose, it defined an xml data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using XQuery; CLR Integration was the main features with this edition where one could write SQL code as Managed Code these are those code which are being executed by CLR(Common Language Runtime). SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL. In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using TDS packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML.
For relational data, T-SQL has been augmented with error handling features (try/catch) and support for recursive queries (Common Table Expressions). SQL Server 2005 has also been enhanced with new indexing algorithms and better error recovery systems. Data pages are checksummed for better error resiliency, and optimistic concurrency support has been added for better performance. Permissions and access control have been made more granular and the query processor handles concurrent execution of queries in a more efficient way. Partitions on tables and indexes are supported natively, so scaling out a database onto a cluster is easier.
SQL CLR was introduced with SQL Server 2005 to let it integrate with the .NET Framework.
SQL Server 2005 introduced “MARS” (Multiple Active Results Sets), a method of allowing usage of database connections for multiple purposes.
SQL Server 2005 Express Edition a Slim down version of SQL Server 2005 free to use and deploy, in which each Database can handle up to 4GB max file size, and another much more advanced version SQL Server 2005 Express Edition with Advanced Services ( which has a slim down version for SQL Server 2005 Reporting Services). But, if your database is large and need much more load balancing and performance features, you have to go for Production Version of SQL Server 2005 , which are called Enterprise, Standard, Workgroup & Developer. Each edition is for a sole purpose, based on the usage. And there is a Compact Edition for mobile/embedded application database.
SQL Server 2005 really was a major out break in SQL Server, people started loving SQL Server 2005, because of the features it supports and stability it provides and again developer love it because of Integrated Support for .NET Framework CLR and the new shiny SQL Server Management Studio, which is actually evolved from SQL Server 2000-Enterprise Manager. The new SSIS(SQL Server Integration Services), SSRS(SQL Server Reporting Services) , SSAS(SQL Server Analysis Services) has made revolution is scalable application development with Reporting and Data Warehousing concepts.
SQL Server 2008
Then in the year 2008, another sensational evolution was happened for SQL Server, the birth of the new boy SQL Server 2008, with shiney new features.
SQL Server 2008 is an advanced version as compared to SQL Server 2005, which has the support for handling Spatial and Geographic data, which was major turning point for SQL Server. Until SQL Server 2008, it was difficult to maintain geographic information in SQL Server databases, the introduction of new geographic and spatial data types has the rocked the world of Application Development and Data handling.
This latest release of the SQL Server Database Engine introduces new features and enhancements that increase the power and productivity of architects, developers, and administrators who design, develop, and maintain data storage systems.
These are the areas in which the Database Engine has been enhanced.
| Topic | Description |
|---|---|
| Availability Enhancements | The availability of Microsoft SQL Server 2008 databases is improved by enhancements to database mirroring. Database mirroring enables the creation of hot standby servers that provide rapid failover support with no loss of data from committed transactions. |
| Manageability Enhancements | Manageability of the SQL Server 2008 Database Engine is simplified by enhancements to tools and monitoring features. |
| Programmability Enhancements | Programmability enhancements in the Database Engine include new data storage features, new data types, new full-text search architecture, and numerous improvements and additions to Transact-SQL. |
| Scalability and Performance Enhancements | Scalability and performance enhancements in the Database Engine include filtered indexes and statistics, new table and query hints, and new query performance and query processing features. |
| Security Enhancements | Security enhancements in the Database Engine include new encryption functions, the transparent data encryption and extensible key management features, and a clarification of DES algorithms. |
SQL Server 2008, was released (RTM) on August 6, 2008 and aims to make data management self-tuning, self organizing, and self maintaining with the development of SQL Server Always On technologies, to provide near-zero downtime. SQL Server 2008 also includes support for structured and semi-structured data, including digital media formats for pictures, audio, video and other multimedia data. In current versions, such multimedia data can be stored as BLOBs (binary large objects), but they are generic bitstreams. Intrinsic awareness of multimedia data will allow specialized functions to be performed on them.
Other new data types include specialized date and time types and a Spatial data type for location-dependent data. Better support for unstructured and semi-structured data is provided using the new FILE STREAM data type, which can be used to reference any file stored on the file system.
The Full-Text Search functionality has been integrated with the database engine, which simplifies management and improves performance.
Spatial data will be stored in two types. A “Flat Earth” (GEOMETRY or planar) data type represents geospatial data which has been projected from its native, spherical, coordinate system into a plane. A “Round Earth” data type (GEOGRAPHY) uses an ellipsoidal model in which the Earth is defined as a single continuous entity which does not suffer from the singularities such as the international dateline, poles, or map projection zone “edges”. Approximately 70 methods are available to represent spatial operations for the Open Geospatial Consortium Simple Features for SQL, Version 1.1.
SQL Server includes better compression features, which also helps in improving scalability. It also includes Resource Governor that allows reserving resources for certain users or workflows. It also includes capabilities for transparent encryption of data as well as compression of backups. SQL Server 2008 supports the ADO.NET Entity Framework and the reporting tools, replication, and data definition will be built around the Entity Data Model. SQL Server Reporting Services will gain charting capabilities from the integration of the data visualization products from Dundas Data Visualization Inc., which was acquired by Microsoft. On the management side, SQL Server 2008 includes the Declarative Management Framework which allows configuring policies and constraints, on the entire database or certain tables, declaratively.
The version of SQL Server Management Studio included with SQL Server 2008 supports IntelliSense for SQL queries against a SQL Server 2008 Database Engine. SQL Server 2008 also makes the databases available via Windows PowerShell providers and management functionality available as Cmdlets, so that the server and all the running instances can be managed from Windows PowerShell
So that was SQL Server 2008. And in some time at the end of year 2008 or beginning of year 2009, we started hearing about SQL Azure. An SQL Server version for Cloud, which will be a slim down version of SQL Server 2005
SQL Azure
SQL Azure is simply a Microsoft branding change. SQL Services and SQL Data Services are now known as Microsoft SQL Azure and SQL Azure Database. There are a few changes, but fundamentally Microsoft’s plans to extend SQL server capabilities in cloud as web-based services remain intact. SQL Azure will continue to deliver an integrated set of services for relational databases. The reporting, analytics and data synchronization with end-users and partners also remains unchanged. This makes it most appealing to current users of SQL Server.
The SQL Azure database service offers a scalable and distributed database hosted in the cloud, and therefore highly available. As HA, backup and recovery, geo-distribution and disaster recovery are built-in, developers do not need to manage any software, but in the case of a dedicated hosted database, they will still be responsible for database software, i.e. for the installation and tasks related to OS and database software.
SQL Azure Development
SQL Azure’s relational database service supports the T-SQL (Transact-SQL) over TDS (Tabular Data Stream) protocol. The relational data model in the cloud can therefore be used together with current T-SQL developments. The new distributed functionality of the SQL Azure Database in the cloud should provide development cost-savings, as existing applications, tools and expertise can be incorporated. The ability to use the traditional RDBMS data model in the cloud implies that developers should be able to use current interfaces to build new applications, and previous investments in development, training and tools should hold their value.
SQL Azure vs. SQL Server
SQL Server database technologies were used to build SQL Azure. Specifically, the technologies used in critical enterprise and web applications are included. The extensive data platform of SQL Server is capable of handling all data types and the SQL Azure platform introduces many associated capabilities. New relational functionalities are included and extended as services in the cloud .
Availability, self-management and ease-of-use are the highlights of the new service. While only the core RDBMS capabilities of the full SQL Server data platform are presently included, more services are expected to be introduced in the future. Reporting, analytics and ETL will no doubt be available over time. As SQL Server and SQL Azure share the same technologies, bilateral innovation can also be anticipated.
SQL Server 2008 R2
April 2010, Microsoft has announced the RTM of their latest version of SQL Server, called SQL Server 2008 R2 (R2 Stands for Release 2), which comes with another set of breaking changes.
Built on SQL Server 2008, SQL Server 2008 R2 delivers higher mission-critical scale, more efficient IT, and expanded reporting and analytics through self-service business intelligence.
The Express Edition maximum database file size for each database has been increased to 10GB from the previous 4GB in older versions. Which is going to be a relief for people who are developing applications using EXPRESS EDITION databases, since if their database grows beyond 4GB, they would be able to handle with 10GB limit, which is really a blessing by Microsoft for independent developers and organizations who can’t afford the cost of buying Production Version of SQL Server Enterprise or Standard editions.
Database Engine wise the SQL Server 2008 R2 is built on SQL Server 2008, means it’s just same old SQL Server 2008 with some new set of Tools for delivering higher mission-critical scalable database solutions.
Database Engine has not much changes at all, not introduced any new data types. It is actually fine tuned for more efficiency and scalability and managability.
The SQL Server Management Studio 2008 R2, now supports the SQL Azure database connectivity through the management studio. It’s a cool feature, you can sit in office / home and manage your SQL Azure database in cloud using the shiney new Management Studio 2008 R2.
Key SQL Server 2008 R2 enhancements include
• Master Data Services ( a centralized console to manage multiple SQL Server instances)
• Self-managed business intelligence (BI) for analysis and reporting (Project Gemini)
• Application and multiserver management
• Support for more than 64 logical processors
SQL Server 2008 R2 boasts a number of new services , including PowerPivot for Excel and SharePoint, Master Data Services, StreamInsight, ReportBuilder 3.0, Reporting Services Add-in for SharePoint, a Data-tier function in Visual Studio that enables packaging of tiered databases as part of an application, and a SQL Server Utility that manages multiple SQL Servers.
In Edition wise there is an introduction of two new Editions specially for Data Warehousing.
So that’s all about it for now. I hope this post was useful for some body in some way. Wish you all a well improvement in your career enhancement. good luck..
References ::
Wikipedia
MSDN
One thought on “Evolution of SQL Server from SQL 2000 to SQL Server 2008 R2”