Microsoft's SQL Server is the database management system (DBMS) for enterprises requiring a robust feature set at a lower price point than its competitors. SQL Server DBMS has been steadily increasing in popularity each year since its release. By 2001 it ranked as the number one DBMS for the Windows platform, and by 2004 it was the clear market leader for OLAP installations across all platforms. The fact that its popularity continues...
to grow is no coincidence.
With the SQL Server 2005 release, Microsoft further strengthened the product as a platform upon which database administrators could build for the future, and it won the prestigious Jolt Award in the process. Microsoft is also the only major DBMS vendor to show significant growth, according to Gartner.
An enterprise-ready solution
Enterprise-level, mission-critical applications must be built on top of highly reliable database software -- certainly if management wants to sleep at night! SQL Server provides a vast array of features to ensure data is available when needed, well protected from damage and easily recoverable in the event of a disaster. These features include data replication, server clustering, transaction logging and data page verification. Enterprises trying to reach five or even six "9s" can count on SQL Server to deliver when it comes to being there for customer requests.
To allow the product to scale easily to meet the needs of demanding enterprise applications, SQL Server DBMS offers built-in support for advanced replication. SQL Server includes a traditional replication approach, such as transactional and merge replication for applications that require extreme data integrity. It also uses newer approaches, such as peer-to-peer replication, which provide higher performance for scale-out scenarios.
For high availability, SQL Server supports Windows clustering, allowing up to eight nodes for complete failover protection. SQL Server 2005 also supports an advanced form of instantaneous data replication called Database Mirroring, which ups the bar with features including automatic application failover and the ability to fail over to remote servers.
SQL Server's database engine involves a robust transaction logging model that guarantees data integrity in case of hardware failure by fully enforcing the ACID properties required by the SQL standard. This means that data involved in a transaction in SQL Server is logged and only made available to other users when the transaction has successfully completed. This ensures that data consistency will be maintained even in the event that a hardware failure occurs in the middle of a transaction.
Transaction logging is not the only form of data integrity protection offered by SQL Server DBMS. SQL Server 2005 adds a new form of checksum page verification to further protect data from disk or controller failures that may otherwise cause data corruption to occur. In addition to the protection offered by the database engine itself, SQL Server supports a variety of constraints that administrators can define to help ensure relational data integrity.
Easy to use and administer
SQL Server database management system is known as a DBMS that can be quickly picked up by just about anyone, making it a great choice for applications ranging from small workgroup-based systems up to large enterprise installations. Since version 7.0, SQL Server has shipped with an integrated suite of graphical tools to help administrators ease into working with the product. The result is better productivity and faster results from SQL Server DBAs -- and that translates into better ROI for companies that adopt SQL Server.
SQL Server 2005 ships with a brand-new version of the graphical management suite now called SQL Server Management Studio (SSMS). SSMS is based on the wildly popular Visual Studio interface, which was designed from the ground up with productivity and ease of use in mind. The result, for SQL Server DBAs, is a seamless solution for both administration and development across the entire database platform.
Via the same interface, database developers program complex scripts while administrators create jobs and monitor server metrics. The look and feel remains similar for a variety of tasks, including working with relational data, data in the OLAP engine, data integration and other areas of the system. This allows developers to learn only a single interface in order to be effective in working with any area of the product. Less training and less ramp-up time required for SQL Server DBMS professionals translates into immediate ROI.
To help make administrators more effective in their roles, Dynamic Management Views -- a toolset in SQL Server 2005 -- gains admins visibility into the inner workings of the server. A large collection of Windows performance counters exposes aggregate data about the server's operations and the SQL Server Profiler tool so they can easily monitor the real-time status of the server. These tools provide administrators with a means of quickly finding and fixing problems early on -- or even before they occur.
SQL Server DBMS: Flexible and functional out of the box
SQL Server 2005 is not merely a relational database platform; it also includes a robust OLAP engine (Analysis Services), a highly customizable reporting engine (Reporting Services), a high-performance data integration tool (Integration Services) and a subscription applications tool (Notification Services).
Each of these features comes "in the box." There is no need to purchase separate licenses in many cases (as long as the features are running on the same server) and, as mentioned earlier, the learning curve is not especially steep because of the similar look and feel of the interfaces shared by each feature.
In addition to its vast array of services, SQL Server 2005's database engine includes specialized features not found in many other database engines: Service Broker, a robust, transactional message-queuing solution; and support for XML as a first-class data type in the database. Such features make it easy to create extensible, loosely coupled database applications important in today's agile enterprise environments.
Inexpensive compared to the competition
Evident in the Transaction Processing Performance Council's benchmarks, SQL Server tops the price versus performance benchmarks for both OLTP and decision-support performance. SQL Server DBMS can deploy on systems ranging from pocket PCs to 64-bit servers with hundreds of gigabytes of RAM and tens of processors. Priced on a per-socket basis, SQL Server becomes quite inexpensive when installed on systems running today's newer multi-core processors. As a result, the DBMS can scale both up or down in terms of price to accommodate the needs of the enterprise.
The price difference between SQL Server DBMS and other database management systems can be astonishing. SQL Server 2005 Standard Edition retails at $5,999 U.S. per processor. The price includes management tools as well as all reporting, notification, integration and business intelligence features that ship with the product. Compare that to Oracle Database 10g Standard Edition, which has a retail price of $15,000 U.S. per processor for the core database engine, and you must pay an additional $6,000 per processor for management tools plus even more fees for add-on features.
The pricing story with open source solutions is not much better. For instance, MySQL Network Silver, at only $1,995 per server, may seem like a bargain. However, MySQL is licensed yearly, meaning that these costs will continue to add up over the lifetime of the application. In addition, like Oracle, MySQL does not ship with a comprehensive suite of management tools or additional features outside of those found in the core database engine. Given this lack of functionality, SQL Server is still quite competitively priced for any application that requires more than just a database back-end.
Another consideration from a price perspective is how long your investment will last. You can use SQL Server in applications that will be around for a while. It is a fact that upgrading to new versions can be expensive and time consuming -- an issue that Microsoft is well aware of. Indeed, versions as old as SQL Server 7.0 are still in use; they are also officially supported by Microsoft now and for at least five more years.
The final price issue is staffing. Most companies already have in-house Windows servers and staff to administer them. This pre-existing infrastructure can be leveraged with SQL Server, which runs on Windows and is administrated using tools that are intimately familiar to anyone who manages Windows servers for a living. Companies can use their existing resources better by choosing SQL Server over other database platforms.
Easy interoperability and migration path
SQL Server can interoperate with a vast array of third-party software, thanks to its support for the Open Database Connectivity (ODBC) standard. Any programming language with a library to support the standard can easily connect to SQL Server.
Along with this ease of interoperability is the addition of native HTTP endpoints. Now, any platform that supports standard Web services can communicate with SQL Server 2005, opening many new opportunities for interoperation between the DBMS and other software without needing to open potentially dangerous ports on the firewall.
Those migrating to SQL Server DBMS from other database platforms will find the transition simple, thanks to the SQL Server Migration Assistant, a tool that helps transition databases and code from other platforms into SQL Server.
SQL Server 2005 is an enterprise-ready database platform that provides high performance, scalability and availability, plus a vast array of easy-to-use features, all at a low price point. It is the high-end choice in the database market for enterprises that require a robust database for their application development needs.
About the author: Adam Machanic is a database-focused software engineer, writer and speaker based in Boston, Mass. He has implemented SQL Server for a variety of high-availability OLTP and large-scale data warehouse applications, and also specializes in .NET data access layer performance optimization. He is a Microsoft Most Valuable Professional (MVP) for SQL Server and a Microsoft Certified Professional. Machanic is co-author of Pro SQL Server 2005, published by Apress.