But What about Postgres?

What About Postgres?

Since I wrote my post yesterday about Oracle and SQL Server, I’ve gotten a lot of positive feedback (except for one grouchy Oracle DBA) on my post. That said, I should probably stay clear of Redwood Shores anytime soon. However there was one interesting comment from Brent Ozar (b|t)

Screen Shot 2016-08-20 at 12.05.36 PM

While Postgres is a very robust database that is great for custom developed applications, this customer has built a pretty big solution on top of SQL Server, so that’s not really an option.

multiple-cords-in-one-outlet

However, let’s look at the features they are using in SQL Server and compare them to Postgres. Since this a real customer case, it’s easy to compare.

1. Columnstore indexes—Microsoft has done an excellent job on this feature, and in SQL Server 2016 new features like batch mode push-down drive really solid performance on large analytic queries. Postgres has a project for columnstore but it is not developed. There’s also this add-on feature https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/ which does not offer batch execution mode performance enhancements and frankly offers extremely mediocre performance.

You can compare this benchmark:

https://www.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout

to the SQL Server one:

SQL Server 2016 posts world record TPC-H 10 TB benchmark

2. Always On Availability Groups—In this system design we are using readable secondaries as a method to deliver more data to customers. It doesn’t work for all systems, but in this case it works really well. Postgres has a readable secondary option, but it is far less mature than the SQL Server feature. For example, you can’t create a temp table in a readable secondary.

3. Analysis Service Tabular—There is no comparison here. Postgres has some OLAP functions that are comparable to windowing functions in T-SQL. Not an in-memory calculation engine.

4. R Services—You can connect R to Postgres. However, SQL Server’s R Services leverages the SQL Server engine to process data, unlike Postgres which uses R’s traditional approach of needing the entire dataset in memory. Once again, this would require a 3rd party plug in to work in Postgres.

5. While Postgres has partitioning, it is not as seamless as in SQL Server, and requires some level of application changes to support.

https://www.postgresql.org/docs/9.1/static/ddl-partitioning.html

While I feel that SQL Server’s implementation of partitioning could be better, I don’t have to change any code to implement.

6. Postgres has nothing like the Query Store. There are data dictionary views that offer some level of insight, but the Query Store is a fantastic addition to SQL Server that helps developers and DBAs alike

7. Postgres has no native spatial feature. There is a plug-in that does it, but once again we are making an even bigger footprint of 3rd party add-ins to manage.

Postgres is a really good database engine, with a rich ecosystem of developers writing code for it. SQL Server on the other hand, is a mature product that has had a large push to support analytic performance and scale.

Additionally, this customer is leveraging the Azure ecosystem as part of their process, and that is only possible via SQL Server’s tight integration with the platform.

Please, Please Stop Complaining about SQL Server Licensing Costs and Complexity

Recently, I’ve seen a number of folks on twitter and in the blogosphere complaining about the complexity of SQL Server licensing. While it is a slightly complicated topic and nowhere near as simple as Azure SQL Database (need more perf? Spend more $€£), there are other products in our space like Oracle and SAP that make licensing SQL Server look like a piece of brioche.

 

german_sailing_grand_prix_2006_oracle-2

 

And while talking about vendors who spend your hard earned licensing dollars on racing sailboats and MIG fighter planes, through a recent project, I’ve had the opportunity to make a direct comparison between the licensing cost for SQL Server and Oracle. These numbers are not from quotes (list price), however this is a real customer of mine, and the features they use. This customer was an early adopter of SQL Server 2016, and uses MANY of the features in the product. Most of which are cost options in Oracle.

 

SQL Server Oracle
Core Engine (16 cores)  $109,980.00 Database Engine  $380,000.00
Compression Advanced Compression  $92,000.00
Columnstore Database In-Memory  $184,000.00
Analysis Tabular OLAP  $184,000.00
R Services Advanced Analytics  $184,000.00
Partitioning Partitioning  $92,000.00
Query Store Tuning Pack  $40,000.00
Spatial Spatial  $140,000.00
Availability Groups Active Data Guard  $92,000.00
Total  $109,980.00  $1,388,000.00

 

When I see those numbers in Microsoft marketing slides, I sometimes wonder if they can be real, but then I put these numbers together myself. Granted you would get some discounts, but the fact that all of these features are built into SQL Server, should convince you of the value SQL Server offers. Pricing discounts are generally similar between vendors, so that is not really a point of argument. If you are doing a really big Oracle deal you may see a larger upfront discount, but you will still be paying your 23% support fees on that very large list price. (Software Assurance from Microsoft will be around 20%, but from a much lower base) Additionally, several of these features ae available in SQL Server Standard Edition. None of these features are in Oracle’s Standard Edition.

SQL Server 2016 Database Mail Not Working

One of the nice things about SQL Server 2016 is that .NET 3.5 is no longer required for installation. The .NET requirement wouldn’t have been problematic, but in order to add the .NET feature access to the Windows media was required (this was particularly painful in Azure VMs). Fortunately the product team eliminated the requirement as part of the release. Unfortunately, a few things broke–namely database mail, native log shipping, and distributed replay. You can identify this problem, by a couple of symptoms:

  • Your database mail log has no entries
  • You have messages with a status of “unsent’ in sysmail_allitems in MSDB

Screen Shot 2016-07-27 at 2.41.59 PM

  • If you try to execute the file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe you get a .NET 3.5 error

Cumulative Update 1 for SQL 2016 has a fix for this, but only for log shipping. The fix is simply a config file (or you can install .NET 3.5, but don’t do that). If you have a default installation, just run the below PowerShell code to copy the LogShipping file to a new file in the database mail directory.

copy-item C:\Program Files\Microsoft SQL Server\130\Tools\Binn\SQLLogship.exe.config C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe.config

That’s it–that file is just a config file that tells the executable to run on .NET 4.0. I’m sure this will be fixed in CU2, but this will get your going.

 

SQL Server 2016 Automated Installs and a SQL 2016 Webinar

I hate installing things through a GUI–I’ve talked about this before at PASS Summit, and when I worked at big cable, we build a private cloud and fully automated the SQL Server installation process. When SQL Server 2014 launched, my process had virtually no changes, however a number of things broke it in SQL Server 2016 (mostly for the better)

factory_automation_robotics_palettizing_bread

  • TempDB installs with the right number of files–they are still too small though
  • The behavior of trace flags 1117 and 1118 are built in to TempDB so you don’t need to turn them on
  • SSMS is a now a separate installation and download
  • I used batch and I wanted to rewrite into PowerShell

So since this is 2016, I decided to share it on Github. You can find it here: https://github.com/DC-AC/SQL2016_Scripted_Install. Feel free to fork and make edits in the code, or tell me how crappy my PowerShell logic is. Read the readme–there’s a few assumptions about your configuration. I’d like to parameterize those over time.

One other thing I wanted to mention is that Denny Cherry and I are doing a webinar this Friday July 29th at 11 PDT/2 EDT talking about managing SQL Server 2016 in production. We’ve have a customer live for a year now, and will share what we’ve learned and answer your questions. You can sign up here.

 

 

What Happens to Pages in the Buffer Pool when your Availablity Group Fails Over?

Recently at SQL Saturday Philadelphia, we started discussing failover  as it relates to mirroring and Always On Availability Groups. Specifically, we were wondering what would happen if you had a relatively busy readable secondary replica (which would have a lot of pages in the buffer pool on the secondary instance) and if those pages would be flushed from cache or anything like that. So I reached out to the product group and Kevin Farlee from Microsoft was extremely helpful:

Pages in the buffer pool are still valid, as they are updated by the redo thread.  By the time the secondary has transitioned to primary, all pending updates will have been applied, so pages in the bufferpool will all have correct contents. Note that the set of pages in bufferpool may be different between primary and secondary so you may have some cache warm up to do.”

So in a nutshell, if you have a busy readable secondary your cache won’t take much time to warmup, since the hot pages are already there.

PASS Summit 2016 I’m Speaking

pass_2016_websiteI have been honored to be selected to speak at this October’s PASS Global Summit in Seattle this October. I will be speaking on a topic near and dear to my heart, Security in Azure SQL Database. I worked last year to write a white paper with Stacia Varga and Microsoft discussing the best practices and security for Azure SQL Database.

In this session we’ll talk about all the encryption features, the security certifications that Azure has, and how audit is better in Azure SQL Database than it is in SQL Server. Additionally, you’ll learn about some of the other enhancements Microsoft has made to protect your data in Azure.

 

 

SQL Server 2016—The Licensing Info

SQL Server 2016 launched last week to great reviews and with a ton of great new features. I have been working with this version for well over a year now and extremely happy to see it hit RTM and be broadly adopted. So as DBAs it always sucks when you get excited about new features, only to find out the price changed, or vendor “O” made that feature a cost option. So what’s new with SQL Server 2016 licensing? (you won’t this as a session title at any upcoming SQL Server events).  Well first the good news—SQL Server 2016 is the same price and 2012 and 2014 (roughly $6800 core for Enterprise Edition). That’s definitely good news—Microsoft gave us a bunch of new functionality and didn’t raise the price. Additionally, if you see my below post on what is in Standard Edition, they added a lot of functionality there, too.

But we know finance and marketing employees have jobs to do as well, and there is no way they were letting a major version release happen without some changes. So let’s take a look at the one’s Denny Cherry (b|t) and I could glean out of the licensing guide. Please download and read for yourself.

Licensing Changes

Additional licenses are required when:
 A single hardware thread is supporting multiple virtual cores. (A core license is required for each v-core.)
 Multiple hardware threads are supporting a single virtual core simultaneously. (A core license allows a
single v-core to be supported by a single hardware thread.)

What does this mean? It means you can’t over provision CPUs on your VMs (which you shouldn’t be doing for DB servers anyway). There may be something that applies to hyperthreading here, but if you are licensing individual VMs, you probably shouldn’t be using hyperthreads.

“Beginning with SQL Server 2016, deploying and running SQL Server PDW is done through SQL Enterprise
Edition Per Core licensing with SA coverage. The number of SQL Server Enterprise Edition core licenses for an
APS appliance will depend on the total number of physical cores in the SQL Server PDW compute servers
configured within the appliance.”

What does this mean? It means you can actually know what an APS costs, at least from a licensing perspective. Hardware costs will need to be gathered from a reseller. This is a good change as it makes SQL Server’s pricing consistent across platforms (pro-tip: use SQLDW)

“For each server licensed with SQL Server 2016 and covered by active SA, customers can run up to the same
number of passive failover instances in a separate, OSE to support failover events. A passive SQL Server
instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The
passive failover instances can run on a separate server. These may only be used to synchronize with the primary
server and otherwise maintain the passive database instance in a warm standby state in order to minimize
downtime due to hardware or software failure.

 The secondary server used for failover support does not need to be separately licensed for SQL Server as
long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as
reports to clients running active SQL Server workloads, or performing any “work”, such as additional
backups being made from secondary servers, then it must be licensed for SQL Server.”

What does this mean? I had to consult Microsoft on this one, as it was a change in my understanding of the “free” secondary licensing benefit. Basically, if you are going to dedicated hardware (that you own or lease) your secondary license (if you have SA) is still included in your primary license. However, if you go to Azure from on-prem for your HA model, you will need to license the secondary. This does get murky because if both of those workloads are in Azure, you only license the primary.

“All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as
needed; however, they can only be reassigned to another server in another server farm, or to a non-private
cloud, once every 90 days.
A server farm may consist of up to two data centers located in time zones that are within four hours of
one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
A given data center may only be part of one server farm.”

What Does this Mean? Basically you can’t cross an ocean for HA or DR and not pay for it.

SQL Server Developer Edition
SQL Server 2016 Developer Edition is a fully featured version of SQL Server software—including all of the
features and capabilities of Enterprise Edition—licensed for development, test and demonstration purposes
only. SQL Server Developer Edition may not be used in a production environment or with product data. Any
test data that was used for design, development or test purposes must be removed prior to deploying the
software for production use.
Customers may install and run the SQL Server Developer Edition software on any number of devices. This is
significant, because it allows customers to run the software on multiple devices (for testing purposes, for
example) without having to license each non-production server system.”

What does this mean? It basically means only your production environments need to be licensed as long as you are following Microsoft’s rules for not using production data. This is a huge benefit, note that you can’t just restore prod to dev, you need to create some testing data, which is best practice anyway.

“Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016
edition.”

What does this mean? You need to have 2012 licenses (at least) to upgrade to SQL 2016. Basically Microsoft wants to make sure you went through the core conversion in 2012.

Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016
edition.”

What does this mean? If you had BI edition, your licenses will get converted to Enterprise Edition. If you read further into page 29, you will see that BI edition customers will be treated quite favorably. Any time there is a change like this, it’s usually a good time to negotiate with your Microsoft sales professional about getting a better deal.

Summary

There are no earth shattering changes in SQL Server 2016 licensing. The developer edition changes are probably the most significant, and can reduce your overall costs a great deal.

Did You Know?? R Services is in Standard Edition of SQL Server 2016

While my last post extolled the virtues of SQL Server Standard Edition, this week while doing some client testing  with Microsoft, I learned about another key standard edition feature. The new SQL Server R services is supported in standard edition of SQL Server 2016. While you won’t get access to some of the cool functions from Revolution Analytics and in-line parallelism (there is a @parallel=1 in sp_execute_external_script that automatically parallelizes your operations), you can still use all of the Open Source R functions, and build out your own parallelism (just multiple calls of your procedure). 

 

This is a huge benefit for those of you are who are doing statistical analysis of data and want to integrate with SQL Server. Stay tuned here for more detail for how DBAs can use R to analyze performance.

SQL Server 2016—Standard Edition Doesn’t Suck!

Yesterday Microsoft announced SQL Server 2016 was launching on June 1st. I can say this is the most production ready version of SQL Server I have ever worked on. I have had a customer on production since last August. We have been very happy and stable, and performance has been fantastic, we are using columnstore, availability groups, and lately R integration. All of these features are tested first in Azure, then deployed to the on-premises product which allows you to have a fully tested enterprise class RDBMS on day 1 of GA.

But What About Standard Edition

In my circles, there are number of people who are complaining about the lack of features in standard edition. While I do agree that Always Encrypted should be in every version, as lack of strong data encryption is a problem that continues to confound IT. Putting Always Encrypted in all editions would be a good start to having wide ISV adoption of the Always Encrypted feature.

However, even without Always Encrypted, Microsoft added a LOT of new features to Standard Edition. Let’s list them (no specific order here):

  • Temporal Tables
  • Query Store
  • Basic Availability Groups
  • Row Level Security
  • Dynamic Data Masking
  • Basic R Integration
  • Tabular Mode of Analysis Services
  • JSON Support
    I saw a complaint about the Tabular support only being 16 GB, which equates to (with typical compression) 100-150 GB which a very reasonable size model. I’ve also seen complaints about Standard Edition only addressing 128 GB of RAM. Microsoft is not a charity, their end goal is to make their shareholders money. There are a bunch of smart finance people, who make these calculations. If Microsoft increased the memory limit to standard edition to say 512 GB, this might mean 25% fewer customers (note—I made these numbers up) buy Enterprise Edition. Here is the Microsoft description of the aim of standard edition.

Standard
SQL Server Standard provides core data management and business intelligence capabilities for non-critical workloads with minimal IT resources.

If you are running mission critical workloads, and need high levels of uptime, and the tools that come with Enterprise  Edition, you need to pay the big bucks (and it’s still way the hell cheaper than Oracle, no matter how your calculate it) or there’s another option.

The Other Option—Azure SQL DB

You may have noticed Microsoft is making a push towards cloud computing (sarcasm). Cloud computing is going to be the defining trend of the next decade and beyond. Microsoft has been careful to avoid feature limitations in Azure SQL Database (there are a couple, columnstore and in-memory are only available in Premium, but that’s just because of limited hardware resources). Want features like partitioning, data compression, and online index rebuilds? Use SQL DB and all of those features are available to you. Always Encrypted is available at all service tiers, and has been since last summer. You can even have scale out readable secondaries now, even in Basic edition. Microsoft, through these actions, has incentivized moving your databases into Azure (and with elastic pools, it’s easier than ever).  While there while there will always be some on-premises systems,the cloud is changing the way products are developed and sold, and the way customers deploy. Don’t get left behind!

Letting Go—SQL Server 2005 Deprecation

SQL Server 2005 is deprecated this week, specifically tomorrow. That means if you have a problem, or a new bug is discovered, Microsoft support will require you to upgrade in order for support to help you with your problem. This is particularly a big deal when a new security bug is found, and it is not patched for the release of SQL Server that your organization is running

But I Work in Healthcare, Banking, Government

If you work in government, I don’t really have any advice for you—I”m not familiar with how to get through the government machinations to get off of really old software. However, if you work in health care, or banking you want to make sure your boss, and your quality assurance people know that you are now running an unsupported version of the software. When I worked in pharmaceuticals and device this worked pretty well—what really helped was getting our RBDMS classified as part of infrastructure so it didn’t have to be qualified like application software. If you work in banking or finance, the better option to follow up is the security angle—if there is a new zero day attack, your servers won’t be patched until your upgrade.

SQL Server 2005—A Pretty Awesome Release

As much as I’ve been working on SQL Server 2016 lately (and it really is awesome), but SQL Server 2005 was a groundbreaking release that really gave Microsoft market share in the big enterprise database space. A few of the features that were introduced in SQL 2005 include:

  • Database Mirroring
  • Dynamic Management Views (DMVs)
  • Service Broker
  • SSIS
  • Modern Consistency Checks
  • SQL Server Management Studio

SQL Server 2005 was an excellent release, however if you are using it, you are running effectively 11 year old software to host your applications. That’s a bad idea—remember what cell phones were like in 2005?

image

That’s what your database looks like now. Just remember—if you are doing an upgrade skip 2008, 2008 R2 and 2012, and just go straight to SQL Server 2014 (or 2016 if you are reading this after RTM), there’s no reason to upgrade to software that’s going to be desupported in two years. Also, install it on the latest version of Windows, so you don’t have to do this again when Windows 2008R2 is deprecated.

Follow

Get every new post delivered to your Inbox.

Join 2,826 other followers

%d bloggers like this: