SQL Server 2012 and PostgreSQL 9

(1)

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.

(2)

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.

Microsoft & SQL Server are trademarks of the Microsoft group of companies.

All other trademarks are property of their respective owners.

(3)

3

 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.

(5)

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:

 Enterprisefor mission critical applications and large scale data warehousing

 Business Intelligence, a new product edition, providing premium corporate and self-service BI

 Standardfor basic database, reporting and analytics capabilities

 Express Editionscaled-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

(6)

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.

(7)

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

(8)

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.

64-Bit Support Both PostgreSQL & SQL Server

(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

(10)

10

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.

(11)

11

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.

(12)

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,

(13)

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

(14)

14

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 3^rd-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.

(15)

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.

SQL Server 2012 and PostgreSQL 9