SQL Server White Paper Template - Download Center - Microsoft

cuttlefishblueΔιαχείριση Δεδομένων

16 Δεκ 2012 (πριν από 4 χρόνια και 6 μήνες)

182 εμφανίσεις






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 “n
o 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, whic
h 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 ma
n 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
understand
s 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 c
ustomers choose which
database is most suitable for their database applications.



SQL Server 2012 Comp
ared 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 Micr
osoft 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
w
hite
p
aper is for info
rmational 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 r
eferences, 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 intr
oduced 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, pa
tent 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
l
icense to these patents, trademarks, copyrights, or other intellectual property.


© 201
2

Microsoft Corporation. All rights reserved.


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


All other trademarks are property of their res
pective owners.



SQL Server 2012 Comp
ared With PostgreSQL 9

3


Contents

Executive Summary

................................
................................
................................
...................

4

Product Editions

................................
................................
................................
.........................

5

SQL Se
rver 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 Comp
ared With PostgreSQL 9

4


Executive Summary

T
his paper shows that
SQL Server 2012 has better features than PostgreSQL in the
categories

of engine, enterprise, security, tools, data warehous
e, business intelligence, big data, and
support.

Moreover, t
here 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,
co
mmercial 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 secu
rity 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
. Progra
m ma
nagement 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 com
munity 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

trac
k 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 o
f 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 po
sitive aspects of PostgreSQL 9
, this paper shows that SQL Server

2012
is the
clear choice for all aspects of business
es

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 m
ake better business decisions based on data
.

SQL Server 2012 Comp
ared 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, p
roviding premium corporate and self
-
service
BI



Standard

for basic database, reporting and analytics capabilities



Express Edition

sc
aled
-
down free edition that can be freely downloaded as well as freely
distributed by Independent Software Vendors (ISVs)


P
ostgreSQL 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.
O
n 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:

e
ngine
, e
nterprise
, s
ecurity
, t
ools
,
d
ata warehouse, business intelli
gence, big data
, and s
upport
.


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 Comp
ared 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 v
iews.

Multiple
Instances



Multiple instances refer
s

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 f
rom applications or within
other SQL statements.

Transactions



Abil
i
ty 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 d
efined events occur in a
database, such as the changing
of data in a table.

SQL Server 2012 Comp
ared 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
捯牲敳e潮d猠t漠扵獩湥獳
req畩r敭敮ts.

siews



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 sup
ports 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 sup
ports 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.

A
vailable 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 te
xt column so that it
SQL Server 2012 Comp
ared 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 dat
a 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 applicati
ons 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 Micro
soft 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
cl
early
excels in its enterprise features. The comparison of enterpr
ise features is shown in Table 2.

Feature

PostgreSQL 9

SQL Server 2012


Comments

64
-
Bit Support



Both
PostgreSQL & SQL Server
SQL Server 2012 Comp
ared 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.

Alway
sOn



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 replica
tion 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 handhel
d 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 Comp
ared 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
Mem
ory &
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 fo
r 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 secur
ity features is shown in Table 3.

Feat
ure

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 ca
n 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 Comp
ared With PostgreSQL 9

11


Feat
ure

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 ef
ficient 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 to
ols 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.

Graphi
cal
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 Comp
ared 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 refer
red
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
-
Bas
ed 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 si
ngle
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.
U
sers are often t
rying to spot trends in data. However,
SQL Server 2012 Comp
ared 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
Da
ta 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
multipl
e 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 Comp
ared 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 solutio
n 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.

SQL Server 2012 Comp
ared 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 tha
t
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://w
ww.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 wr
iting?

This feedback will help us improve the quality of white papers we release.

Send feedback
.