SQL Server 2012 Compared With PostgreSQL 9 White Paper Apr2012

SQL Server 2012 and PostgreSQL 9 A Detailed Comparison of Approaches and Features SQL Server White Paper Published: April 2012 Applies to: SQL Server 2012 Introduction: The question whether to implement commercial vs. open source software is a debate that has been going on for years. Open source software such as PostgreSQL is typically very low cost or no cost at all. However, even those who think that implementing open source software saves money agree that “free” does not mean “no cost”. While the upfront cost of the software and licenses might be low or no cost, the overall cost to implement and maintain the software can be quite substantial. Moreover, support and consulting fees can be higher than most organizations expect, which raises the overall Total Cost of Ownership (TCO). On the other side, commercial software such as Microsoft SQL Server 2012 achieves an overall superior performance because the development teams who work on the products invest hundreds of thousands of man hours of time and millions of dollars in research and development costs to ensure their products are able to support organizations’ business needs and run their most mission-critical applications. Microsoft also understands the need of organizations very well and satisfies those requirements by building features into multiple SQL Server releases. This paper compares the major features of both the latest version of SQL Server 2012 and PostgreSQL 9 in each product in six separate categories. It will help customers choose which database is most suitable for their database applications. SQL Server 2012 Compared With PostgreSQL 9 2 Copyright The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. This document is provided "as-is." Information and views expressed in this document, including URL and other Internet Web site references, may change without notice. You bear the risk of using it. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. © 2012 Microsoft Corporation. All rights reserved. Microsoft & SQL Server are trademarks of the Microsoft group of companies. All other trademarks are property of their respective owners. SQL Server 2012 Compared With PostgreSQL 9 3 Contents Executive Summary ................................................................................................................... 4 Product Editions ......................................................................................................................... 5 SQL Server 2012 .................................................................................................................... 5 PostgreSQL 9 ......................................................................................................................... 5 Feature Comparison .................................................................................................................. 5 Engine .................................................................................................................................... 6 Enterprise Features ................................................................................................................ 8 Security .................................................................................................................................10 Tools .....................................................................................................................................11 Data Warehouse, Business Intelligence, and Big Data ..........................................................12 Support ..................................................................................................................................14 Conclusion ................................................................................................................................15 References: ..............................................................................................................................15 SQL Server 2012 Compared With PostgreSQL 9 4 Executive Summary This paper shows that SQL Server 2012 has better features than PostgreSQL in the categories of engine, enterprise, security, tools, data warehouse, business intelligence, big data, and support. Moreover, there are well-known inherent issues that come with open source software development as it is typically done by members of the open source community, which is made up of volunteers, including (but not limited to):  Introduction of bugs into software. For PostgreSQL, many community developers contribute to the source code. Bugs can easily be introduced into the product if the skill level of developers is not known or cannot be guaranteed. However, commercial software companies like Microsoft ensure that developers have the appropriate skill level to contribute to their products.  Response to security threats. Microsoft has teams of engineers who are responsible for doing nothing but responding to security threats and patch is made available almost immediately should a vulnerability be exposed. As the result, SQL Server has the least amount of security vulnerability among database products in the last 10 years according to NIST. For open source software, it is difficult or impossible to know who to contact for any security patches or to know when they will become available.  Inadequate program management and project management. Program management in Microsoft bridges the gap of software developers and customers. Project management manages software project requirements that include the scheduling of resources. Microsoft employs full-time program managers and project managers to focus on specific areas of the products. On the other hand, members of the community typically have “day jobs” and impossible to achieve the same level of commitment, so they contribute when they have time. This makes it very difficult to keep software projects on track and according to requirements.  Inadequate testing due to lack of hardware availability. Testing of any software product requires many different hardware configurations. Because open source software is developed as part of a community effort and no corporate backing, there is often no specific lab environment with which to adequately test the hardware.  Lack of hardware vendor support. Microsoft work closely with hardware manufacturers such as HP, Dell, and Intel to ensure compatibility and support of SQL Server based on specifications. On the other hand, there has been very limited hardware support to ensure compatibility for open source databases, such as PostgreSQL. While there are positive aspects of PostgreSQL 9, this paper shows that SQL Server 2012 is the clear choice for all aspects of businesses and all types of applications, including those that are the most mission-critical that requires highest performance, scale, high availability, security, manageability, support, as well as ability to make better business decisions based on data. SQL Server 2012 Compared With PostgreSQL 9 5 Product Editions SQL Server 2012 Microsoft SQL Server 2012 is a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization and quickly build solutions to extend data across on-premises and public cloud, backed by mission critical confidence. Small, mid-sized, and enterprise customers can all benefit from SQL Server 2012. Regardless of budget or business requirements, there is an appropriate edition of SQL Server 2012. One of the major advantages of having multiple editions is that they are all produced using the same database engine:  Enterprise for mission critical applications and large scale data warehousing  Business Intelligence, a new product edition, providing premium corporate and self-service BI  Standard for basic database, reporting and analytics capabilities  Express Edition scaled-down free edition that can be freely downloaded as well as freely distributed by Independent Software Vendors (ISVs) PostgreSQL 9 PostgreSQL 9 (including 9.1) is available in only one edition which contains all the core features described later in this paper. Other commercial companies charge licensing/support for packaged PostgreSQL, added functionalities, and support. On the high-end, PostgreSQL does not contain many of the enterprise-level features required to run highly available, mission-critical applications, such as AlwaysOn, clustering, and online operations. The major features supported in PostgreSQL are outlined in the next section. Feature Comparison Because of the large number of features in both SQL Server 2012 and PostgreSQL 9, the comparison of each is broken into the following categories: engine, enterprise, security, tools, data warehouse, business intelligence, big data, and support. For each of the features presented in this paper, the following visual keys are used to indicate the extent to which each feature is supported by SQL Server 2012 and PostgreSQL 9:  - Feature is fully supported  - Feature is partially supported  - Feature is not supported SQL Server 2012 Compared With PostgreSQL 9 6 Engine The engine in a database system is the core technology that processes data. The engine handles all inserts, updates, deletes, and selects from the database, as well as optimization, indexing, and all other base-level processing supported by a database technology. The comparison of engine features is shown in Table 1. Feature PostgreSQL 9 SQL Server 2012 Comments Automatic Tuning Automatic tuning optimizes the database for best performance. This eliminates administrative burden on DBAs. Cascading Referential Integrity Automatically updates or deletes data in nested tables that participate in foreign key relationships. Indexed Views Indexed views provide optimal performance of database views. Multiple Instances Multiple instances refers to the ability for a single server to host more than one instance of the database server. Multiple Languages Ability to display messages in languages other than English. Also controls the sorting of dates, times, and currency, and strings. Rules Enables you to control the valid values that can be stored in a column in a table. Stored Procedures Enables you to encapsulate complex SQL statements into a single procedure that can be called from applications or within other SQL statements. Transactions Ability to treat a set of SQL statements as a single unit of work, thereby allowing exactly all or none of the statements to complete. Triggers Automatically notifies, or fires, when defined events occur in a database, such as the changing of data in a table. SQL Server 2012 Compared With PostgreSQL 9 7 Feature PostgreSQL 9 SQL Server 2012 Comments User-defined Functions Method of providing programmatic means to complex functionality in line with SQL statements. User-defined Data Types Ability to create a data type that is not available “out of the box,” that corresponds to business requirements. Views Method of abstracting and filtering specific columns or rows in an underlying table, but still looks like a table to SQL queries. UPSERT logic SQL Server supports UPSERT operation (combination of update and insert into a single clause) using MERGE statement. Parallel Query SQL Server provides parallel queries to optimize query execution and index operations. Distributed Federated Query SQL Server supports distributed queries access data from multiple heterogeneous data sources. Native XML Support and XML processing SQL Server stores XML data as a native type, which allows indexing and querying using industry- standard technologies, like XQuery. Available at no cost The Express Edition of SQL Server is available at no cost. Cross- platform support PostgreSQL 9 runs on Unix and Linux, as well as Windows. SQL Server runs only on Windows. Multiple row data storage strategy Data is stored in a way that ensures good performance in high-transaction environments. Service Broker SQL Server Service Broker is used for Asynchronous communications with other applications. Full-Text Searching Ability to catalog the data that comprises a text column so that it SQL Server 2012 Compared With PostgreSQL 9 8 Feature PostgreSQL 9 SQL Server 2012 Comments can be easily queried. Index Organized Table SQL Server uses clustered indexes (index-organized tables) on the primary key per default. Filtered Indexes Filtered Indexes that provide high-performance lookups of subsets of data in SQL Server. Persisted Computed Column Computed column in SQL Server is computed from an expression that can use other columns in the same table. FileTable Brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. Hierarchical Data Type Makes it easier to store and query hierarchical data in SQL Server XEvents General event-handling system for SQL Server and Windows Server. CLR Integration CLR hosted in Microsoft SQL Server (called CLR integration) allows authoring of stored procedures, triggers, user-defined functions, user-defined types, and user-defined aggregates in managed code. Integrated Database Mail Allowing sending of bulk emails from SQL Server databases. Table 1: Comparison of Engine Features. Enterprise Features Enterprise Features refers to more advanced capabilities that are needed in a medium or large enterprise. These features are not typically used in a small business. SQL Server 2012 clearly excels in its enterprise features. The comparison of enterprise features is shown in Table 2. Feature PostgreSQL 9 SQL Server 2012 Comments 64-Bit Support Both PostgreSQL & SQL Server SQL Server 2012 Compared With PostgreSQL 9 9 Feature PostgreSQL 9 SQL Server 2012 Comments support 64-bit. Distributed Partitioned Views Method to efficiently access federated databases. Failover Clustering PostgreSQL does not integrate with the operating system to provide failover clustering, but some level of high-availability can be obtained by using clustering on some UNIX platforms. AlwaysOn SQL Server supports synchronous and asynchronous multiple active secondary servers. Log Shipping Applies the transactions in the transaction log to another server for high availability. Replication PostgreSQL supports Master/Slave replication on a limited basis. SQL Server supports snapshot, merge, and transactional replication. Multi-device support SQL Server can run on Mobile devices. PostgreSQL has a relatively large footprint and is not specifically ported to work on small handheld devices. Advanced Compression Help compress the data inside a database to help reduce the size of the database and improve performance of I/O intensive workloads in SQL Server. Online Backups Backups can be performed without taking the database offline. SAN Support While a SAN can be used with both products, SQL Server has built-in support for a Storage Area Network. Complex Event Processing SQL Server StreamInsight provides complex event processing capabilities. Data Quality SQL Server Data Qualities SQL Server 2012 Compared With PostgreSQL 9 10 Feature PostgreSQL 9 SQL Server 2012 Comments Services enables correction, enrichment, standardization, and de-duplication of data using knowledge-driven method. Master Data Management SQL Server Master Data Services provides solution for master data management. Hot Add Memory & CPU SQL Server allows adding CPU and Memory while it is running. Online Schema Change SQL Server allows changing database schema while it is running. Resource Governor SQL Server provides lower and upper limits of resource management for performance consistencies Table 2: Comparison of Enterprise Features. Security Security is at the forefront of everyone’s mind. SQL Server 2012 has better built-in advanced security features. The comparison of security features is shown in Table 3. Feature PostgreSQL 9 SQL Server 2012 Comments Active Directory Support Kerberos authentication is supported with both products for Active Directory support. Role-Based Users can be grouped into roles they assume in the database. Then, permissions can be assigned to those roles. SSL Encryption Encryption of TCP/IP connections to the database for added security. Stored Procedure Security Ability to apply security permissions to stored procedures. Table Security Ability to apply security permissions to tables. SQL Server 2012 Compared With PostgreSQL 9 11 Feature PostgreSQL 9 SQL Server 2012 Comments View Security Ability to apply security permissions to views. Auditing SQL Server has built in comprehensive auditing capabilities Transparent Data Encryption SQL Server supports real-time I/O encryption and decryption of the data and log files. Centralized Key Management SQL Server allows storing encryption keys in hardware security modules devices. Table 3: Comparison of Security Features. Tools Tools in a database system allow it to be managed in an easy and efficient manner. SQL Server has much more built-in database tools and excels in its ability to manage one or more SQL Servers. The comparison of tools features is shown in Table 4. Feature PostgreSQL 9 SQL Server 2012 Comments Centralized Administration Administration of one or more servers is performed in single, centralized place. Integrated SQL Debugger Debugger is integrated into the development environment. Profiler Tool for measuring and monitoring performance and server health. Graphical Query Tool Graphical (GUI) tools for querying the database. Database Tuning Advisor SQL Server uses current workloads to determine how to best tune a database. Distributed Replay Tool to replay captured trace and simulate mission-critical workload against an upgraded test environment to help assess the impact of hardware and operating system upgrades. SQL Server 2012 Compared With PostgreSQL 9 12 Feature PostgreSQL 9 SQL Server 2012 Comments Graphical Wizards SQL Server has many wizards to guide through administrative tasks. PostgreSQL has a few dialog boxes, which are referred to as Wizards. Graphical Activity Monitor PostgreSQL does not have native GUI monitoring tool for performance. Central Management Servers PostgreSQL does not have the capability to execute SQL statements at the same time against server groups SQL Server Utility Manage SQL Server environment as a whole with summary and detailed data in terms of underutilization and overutilization policies for a variety of key parameters such as CPU, file spaces. Manage servers using Policy Policy-Based Management allows managing one or more instances of SQL Server based on extensive policies. Maintenance Plan Wizard SQL Server maintenance plan wizard create a workflow of the tasks required to make sure database is optimized, regularly backed up, and free of consistencies. Dedicated Administrator Connection SQL Server provides ways to let administrator troubleshoot very busy databases. Data-tier Application SQL Server can package database applications (schema, objects, and data) into a single unit of deployment Table 4: Comparison of Tools Features. Data Warehouse, Business Intelligence, and Big Data Data warehouse, Business Intelligence, and Big Data is simply the ability to make better business decisions based on data. Users are often trying to spot trends in data. However, SQL Server 2012 Compared With PostgreSQL 9 13 trends might exist that the business analyst doesn’t know to look for. The ability to load data from multiple, disparate data sources, process that data, and analyze it is an enormous strength of SQL Server. Microsoft has made very large investments in R&D in this area and provides many more of these features in SQL Server 2012 than does PostgreSQL 9. The comparison of is shown in Table 5. Feature PostgreSQL 9 SQL Server 2012 Comments xVelocity in- memory column store index Delivers 10x-100x faster performance for Data Warehousing queries. Change Data Capture SQL Server provides way to capture insert, update, and delete activities applied to tables. Data Warehouse Alliance SQL Server has an alliance of Data Warehousing vendors. Data Warehouse Reference Architectures SQL Server has multiple data warehouse reference architectures from multiple hardware vendors. Data Warehouse Appliances SQL Server has multiple data warehouse appliances from multiple hardware vendors. Integration Services, also known as Extract, Transform, and Load (ETL). While PostgreSQL does not have any ETL tools, it does allow for text file importing. SQL Server has extensive and robust ETL capabilities. High Speed Drivers SQL Server supports high performance Oracle and Teradata destinations Analysis Services (OLAP) Ability to build OLAP, ROLAP, and MOLAP database cubes. Data Mining SQL Server supports algorithms for mining structured data. Text Mining SQL Server supports algorithms for mining unstructured text data. English Query SQL Server English query is the SQL Server 2012 Compared With PostgreSQL 9 14 Feature PostgreSQL 9 SQL Server 2012 Comments ability to query the database using English syntax instead of SQL syntax. Reporting Services SQL Server has a graphical reporting solution for different types of users. PowerPivot SQL Server provides deep Integration into Microsoft Excel to query and view data in OLAP and OLTP databases. Big Data Support SQL Server is shipping with Hadoop adapters for Big Data processing Table 5: Comparison of Data Warehouse, Business Intelligence, and Big Data Features. Support Reliable support is one of the biggest reasons to use a commercial software package over an open source application. Open source applications can be difficult to architect and implement without support. Very few companies can provide the level of support that a company like Microsoft can. The comparison of support features is shown in Table 6. Feature PostgreSQL 9 SQL Server 2012 Comments 3rd-Party periodicals A wide variety of periodicals are available for SQL Server. Limited periodicals are available for PostgreSQL. Classroom training Classroom training is widely available for SQL Server through certified training centers. Limited classroom training is available for PostgreSQL. Company- Funded R&D and Development PostgreSQL asks for and hopes that individuals will make financial contributions. Email Support Support via email from customers. ISP Support Support is available from variety of hosters. SQL Server 2012 Compared With PostgreSQL 9 15 Feature PostgreSQL 9 SQL Server 2012 Comments Newsgroup Support Support is available through newsgroups. Phone Support SQL Server has telephone support available. Hardware Vendor Support SQL Server has support from hardware vendors and it certified to run Windows and SQL Server. Mission Critical Support Microsoft provides Mission Critical Premier Support for SQL Server. Table 6: Comparison of Support Features. Conclusion SQL Server 2012 excels in numerous ways compares to PostgreSQL 9. SQL Server 2012 contain many more features than PostgreSQL 9 in the categories of engine, enterprise, security, tools, data warehouse, business intelligence, big data, and support. Therefore, SQL Server is the clear choice for all aspects of businesses and all types of applications, including those that are the most mission-critical. While open source software such as PostgreSQL 9 can be downloaded and installed for “free”, inherent issues with open source software might raise its total cost of ownership (TCO). References: SQL Server Web Site http://www.microsoft.com/sqlserver/en/us/default.aspx PostgreSQL Web Site http://www.postgresql.org/ Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example: Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason? Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing? This feedback will help us improve the quality of white papers we release. Send feedback.