Category Archives: Oracle

SQL Server, Pricing, Editions and Features

I Want Features Not Cost!

Recently Brent Ozar (blog|twitter) wrote a post SQL Server Standard Edition Sucks, And It’s All Your Fault. Needless to say, he isn’t happy with the price and what you don’t get in Standard Edition of SQL Server 2012. Brent seems to be mostly upset that since SQL Server 2008 Standard edition has been restricted to 64GB of ram. Adding additional insult, Always On is an Enterprise only feature with Database Mirroring on the green mile Microsoft will have to do something in the near future to fill this gap.

What about the rest of his list?

  • Database snapshots (a huge lifesaver when doing deployments)
  • Online reindexing, parallel index operations (wouldn’t you like to use more than one core?)
  • Transparent database encryption (because only enterprises store personally identifiable data or sell stuff online, right?)
  • Auditing (guess only enterprises need compliance)
  • Tons of BI features (because hey, your small business doesn’t have intelligence)

Yep, it sure does suck not to get any of those features and get stuck with a nice price increase.

But, Microsoft Says The Increase Isn’t That Bad(tm)

Imagine you are going from Enterprise per server to Enterprise per core on a 64 core system. At $7,000.00 a core that’s $448,000.00 bucks. If you were on SQL Server 2008 R2 Enterprise server licensing you paid just $13,969.00 and got it with 25 CALs. That is a 3100% increase in cost if you want to move to SQL Server 2012. Now that is something to bitch about. I can buy an HP DL580 with four 10-core processors and 128GB of ram for $24,259 retail, then pay $280,000.00 to license it. The days of hardware being a significant part of your IT deployments are gone. Hardware is getting cheaper and more powerful while software is getting more expensive. It’s almost an exact inverse equation.

So, What Are My Options

Well, few unfortunately. To license Oracle with the same features as SQL Server 2012 Enterprise you would be looking at 40% or more in licensing fees. On the open source front things aren’t much better. There just isn’t a single RDBMS with all the features of SQL Server or any other commercial offering for that matter.

Lets take a look at our options out of the box:

PostgreSQL

  • Can use maximum server memory – Yes
  • Database snapshots – Nope
  • Online reindexing, parallel index operations – Yes/Nope You can do an online operation but not parallel.
  • Transparent database encryption – Nope
  • Auditing – Nope
  • Tons of BI features – Nope

MySQL/MariaDB

  • Can use maximum server memory – Yes
  • Database snapshots – Nope
  • Online reindexing, parallel index operations – Nope
  • Transparent database encryption – Nope
  • Auditing – Nope
  • Tons of BI features – Nope

Now What?

Heck the only open source database that has a parallel query execution core is written in Java. On the plus side HyperSQL runs on Windows. All of these offerings also have things that SQL Server doesn’t. PostgreSQL is incredibly powerful for GIS and its unique among database engines for the large amount of indexing options available. MySQL/MariaDB has a nice feature that allows you to write your own storage engine and let MySQL act as the query processor.

If you don’t need all the feature add-on’s like Reporting Services, Integration Services or Analysis services for SQL Server then I recommend PostgreSQL every time. If you don’t mind doing some grunt work you can assemble a BI stack, reporting stack and all the other wiz-bang stuff you get out of the box with SQL Server to some degree.

If you want SQL Server’s set of tools AND its outstanding query engine you WILL have to pay for it. If you want to use SQL Sever and aren’t worried about new features, support or up to date tools you can always stick with SQL Server 2005 Standard Edition 64 bit, since it doesn’t cap memory usage and allows you to use all the memory the OS has available.

Well I’m Still Unhappy.

Yes the new licensing was a kick in the seat of the pants. Ask the Oracle guys how well they took it when Oracle did the same thing in late 2005. An article on CRN reads like it was written about SQL Server 2012 in the last month. Read it and replace “Oracle” with “SQL Server” its a hoot.

“Everyone is upset about it,” said Joe Vaught, COO of solution provider PCPC.

We sure are.

Oracle declined to comment for this story, beyond sending a short statement to CRN, which read in part: “As platforms change, and market opportunities arise, we continue to examine our pricing structure.”

Can Oracle sue Microsoft for stealing their statement? I don’t know but it sure does look familiar. I can also tell you that Microsoft used Oracle’s licensing changes to gobble up the lower end of the enterprise space and dominate the SMB space all together. Our problem is there isn’t another major up and coming software company gunning for the middle and not the top of the RDBMS business. PostgreSQL is making great strides and is my favorite front runner. They are also looking at the parallel query issue too! If you can hold out for another year or two you may be able to live without some of the bells and whistles and PostgreSQL or some other platform will be good enough to meet your needs.

SSIS and Oracle All Your Non-options

Why do some things have to be so hard?

I have been asking myself that question for the better part of two weeks as I wrestle with SSIS 2008 R2 and getting data out of Oracle and into SQL Server.

It’s like the shell game, only with drivers.

Like, go native man!

Like anyone else working in SSIS and dealing with Oracle I started with the default drivers that ship with SSIS. Technically, they work. There are a couple of glaring caveats. They only work in 32 bit mode. Huge non starter on our 64 bit system. They are slow. I know that’s like saying the sky is blue, especially if you don’t have any context. Well I do have some context. Migrating packages from DTS to SSIS I’ve got historical run times and also did some test runs before actually converting the packages. The native 32 bit drivers were slower or just equal to the equivalent on the SQL Server 2000 box running the Oracle 8 drivers. They don’t return the proper metadata column data types. Everything comes back as a wide string a.k.a. st_wstr or varchar for you table creating types. Decimal(18,2)? Thats a varchar(50) for you. varchar(10)? You guessed it, varchar(50) should do it! This beyond anything else was probably the biggest problem.

No, only use what Oracle provides!

I decided to install the Oracle drivers. Let the pain begin!
First, you have to create an account on the Oracle Developer Network site. Really? I just need some drivers. I guess it could have been worse, like a sharp stick to the eye. You need to download the 32 bit and the 64 bit driver packs. Each one weighs in at 700MB compressed. They do include a ton of tools that I have no clue how to use, thats a bonus. Here is your sharp stick to the eye as you get to use of one of the worst installers in the history of installers. After about a dozen tries I finally found out the magic combination to get only the drivers cutting out about 1.3GB from being piled onto my server. Oh, and you get to do it twice. Next, as you look at where it put the drivers at you realize that each install is named client, client_1 and so on. No clue at all which is the 32 bit or 64 bit install bits. Get that figured out, you can go fix your borked path. The installer will gladly stick its path right at the beginning giving your hours of fun trying to figure out exactly what is broken. That is if your path isn’t already too long and it just skips this bit for you. And finally, you get to manually add a system variable pointing to your tnsnames.ora file usually stored in /app/<nt login>/11.2/client/network/tnsnames.ora

After all your hard work you are rewarded with Ole Db, ADO and ADO.Net drivers HUZZAH!
First thing I found out is the Ole Db drivers work as well as the native drivers as far as metadata is concerned. Performance was better. The ADO and ADO.Net do take it up a notch. You do get some additional metadata goodness from these drivers. I did get decimal and float types back but pretty much every string came back as st_wstr again. It would size correctly them that was nice. It did mean I had to add conversion from wide string to string. On a table with 86 columns and 64 million records this also was a bad combination. You do get to run in both 32 bit and 64 bit.

Use what random people on twitter recommend!

Well, not quite that bad. I posted a 14o character version of this post to the mighty #sqlhelp hashtag and lo’ my friend Merrill Aldrich (twitter|blog) and he simply said “Can you use the Attunity connector? #sqlhelp” Huh? When I did a search for just Attunity it brought me to their website http://www.attunity.com/ I didn’t see exaclty what Merrill was talking about. Doing a search for Attunity Connector brought me to the gold I’d been looking for http://www.attunity.com/products/attunity-connect/ssis-connectors-for-oracle-and-teradata. Apparently, Attunity makes connectors for Oracle and Teradata and releases them for F R E E. There are two versions currently 1.2 for SSIS 2008 R2 and 2.0 for SSIS 2012. The installer is rough but not Oracle rough. One of the nice things is installing the 64 bit drivers also installs the 32 bit drivers. Unless you have a problem with the installer and the 64 bit installer only installs the 32 bit drivers. After a few searches and a few more failed install attempts I found that you need to install the Visual C++ 2008 SP1 redistributable package. I only needed to do this on my server since I had Visual Studio 2010 already installed on my laptop. Once that was done the installer worked just fine. Except it really didn’t “install” everything. You still have to manually add them to your toolbox sidebar for data flows before you will see the source and destination connectors for Oracle. It’s totally worth it.

First off with a bang, all the metadata returned was 100% spot on. No more fussing with conversion steps or guessing what the data type should be. They are faster. Not by a small margin ether. On 50~ packages they were around 25% or more faster than the Oracle provided drivers. You still need to have a TNS names file you can’t use the machine name, port and service name directly. (that I know of)

And there was much joy to be had.

I do think it is sad that neither Microsoft or Oracle has a good solution to this issue. I’m glad Microsoft is supporting Attunity I wished they would ship them by default. As for Oracle, I know why Oracle developers and DBA’s get paid so much. If getting drivers installed was this hard I can’t imagine getting the whole database setup and going, ugh.