Updated 29th may 2012 for sql server 2012 (below)

Microsoft have changed the licence model for multiple passive machines, you can now only have one secondary passive failover per primary - see http://download.microsoft.com/download/7/3/C/73CAD4E0-D0B5-4BE5-AB49-D5B886A5AE00/SQL_Server_2012_Licensing_Guide_Apr2012.pdf

Personally I don't agree with this move, like previous editions you should be able to have multiple passive failovers.

-------------

If you were in any doubt at all that you need to license Standby / Passive Failover servers then the White Paper “Do Not Pay Too Much for Your Database Licensing” will settle those doubts.

I’ve had debate before people thinking you can only have a single instance as a standby machine, that’s just wrong; it would mean you could have a scenario where you had a 2 node active/passive cluster with database mirroring and log shipping (a total of 4 SQL Server instances) – in that set up you only need to buy one physical license so long as the standby nodes have the same or less physical processors (cores are irrelevant).

So next time your supplier suggests you need a license for your standby box tell them you don’t and educate them by pointing them to the white paper.

For clarity I’ve copied the extract below from the White Paper.

Extract from “Do Not Pay Too Much for Your Database Licensing”

Standby Server

Customers often implement standby server to make sure the application continues to function in case primary server fails. Standby server continuously receives updates from the primary server and will take over the role of primary server in case of failure in the primary server.

Following are comparisons of how each vendor supports standby server licensing.

SQL Server
Customers does not need to license standby (or passive) server provided that the number of processors in the standby server is equal or less than those in the active server.

Oracle DB
Oracle requires customer to fully license both active and standby servers even though the standby server is essentially idle most of the time.

IBM DB2
IBM licensing on standby server is quite complicated and is different for every editions of DB2. For Enterprise Edition, a minimum of 100 PVUs or 25 Authorized User is needed to license standby server.

The following graph compares prices based on a database application with two processors (dual-core) and 25 users with one standby server.

[chart snipped]

Note All prices are based on newest Intel Xeon Nehalem processor database pricing for purchases within the United States and are in United States dollars. Pricing is based on information available on vendor Web sites for Enterprise Edition.

Microsoft SQL Server Enterprise Edition
25 users (CALs) x $164 / CAL + $8,592 / Server = $12,692 (no need to license standby server)

Oracle Enterprise Edition (base license without options)
Named User Plus minimum (25 Named Users Plus per Core) = 25 x 2 = 50 Named Users Plus x $950 / Named Users Plus x 2 servers = $95,000

IBM DB2 Enterprise Edition (base license without feature pack)
Need to purchase 125 Authorized User (400 PVUs/100 PVUs = 4 X 25 = 100 Authorized User + 25 Authorized Users for standby server) = 125 Authorized Users x $1,040 / Authorized Users = $130,000

https://sqlblogcasts.com:443/blogs/tonyrogerson/archive/2011/10/21/you-do-not-need-a-separate-sql-server-licence-for-a-standby-or-passive-server-this-microsoft-white-paper-explains-all.aspx

When considering buying a laptop that’s going to cost me around lb5,000 I really need to justify the purchase from a business perspective; my Lenovo W700 has served me very well for the last 2 years, it’s an extremely good machine and as solid as a rock (and as heavy), alas though it is limited to the 8GB.

As SQL Server 2012 approaches and with my interest in working in the Business Intelligence space over the next year or two it is clear I need a powerful machine that I can run a full infrastructure though virtualised.

My requirements

For High Availability / Disaster Recovery research and demonstration

Machine for a domain controller
Four machines in a shared disk cluster (SQL Server Clustering active – active etc.)
Five machines in a file share cluster (SQL Server Availability Groups)

For Business Intelligence research and demonstration

Not entirely sure how many machine I want to run here, but it would be to cover the entire BI stack in an enterprise setting, sharepoint, sql server etc.

For Big Data Research

I have a fondness for the NoSQL approach to scalability and dealing with large volumes so I need a number of machines to research VoltDB, Hadoop etc.

As you can see the requirements for a SQL Server consultant to service their clients well is considerable; will 8GB suffice, alas no, it will no longer do. I’m a very strong believer that in order to do your job well you must expense it, short cuts only cost you time, waiting 5 minutes instead of an hour for something to run not only saves me time but my clients time, I can do things quicker and more importantly I can demonstrate concepts.

My W700 with the 8GB of RAM and SSD’s cost me around lb3.5K two years ago, to be honest I’ve not got the full use I wanted out of it but the machine has had the power when I’ve needed it, it’s served me and my clients well.

Alienware now do a model (the M18x) with 32GB of RAM; yes 32GB in a laptop! Dual drives so I can whack a couple of really good SSD’s in there, a quad core with hyper threading i7 and a decent speed.

I can reduce the cost of the memory by getting it from Crucial, so instead of lb1.5K for 32GB it will be around lb900, I can also cost save on the SSD as well. The beauty about the M18x is that it is USB3.0, SATA 3 and also really importantly has eSATA, running VM’s will never be easier, I can have a removeable SSD with my VM’s on it and can plug it into my home machine or laptop – an ideal world!

The initial outlay of lb5K is peanuts compared to the benefits I’ll give my clients, I will be able to present real enterprise concepts, I’ll also be able to give training on those real enterprise concepts and with real, albeit virtualised machines.

https://sqlblogcasts.com:443/blogs/tonyrogerson/archive/2011/10/21/cost-justification-for-buying-a-32gb-superfast-alienware-m18x-with-a-price-tag-of-around-163-5k-10k.aspx

https://sqlblogcasts.com:443/blogs/tonyrogerson/archive/2011/08/16/first-annual-sqlrelay-annouced-for-3rd-6th-october-visit-one-of-over-13-regional-uk-based-sql-user-groups.aspx

CPU Saturation – an over performing IO subsystem

It’s not often that you see the IO subsystem able to out drive the cores in the box. To demonstrate this behaviour the TestGuid_HeapInsert table (see http://sqlblogcasts.com/blogs/tonyrogerson/archive/2011/07/19/use-guid-s-uniqueidentifier-for-keys-when-storing-data-on-ssd-s-part-2-initial-database-schema.aspx for set up scripts), a 50 million row table (a heap) 54GBytes in size will be used, processor affinity will be used to lock SQL Server to a specific number of available cores.

Note: the equipment for this test is described here: http://sqlblogcasts.com/blogs/tonyrogerson/archive/2011/07/22/use-guid-s-uniqueidentifier-for-keys-when-storing-data-on-ssd-s-part-3-initial-base-line-with-iometer-and-first-test-group.aspx. In summary, Windows 2008 R2 x64, SQL 2008 R2 x64, 16GB of RAM but SQL Server is limited to 1GB, processor is a AMD Phenom II X6 Six Core 1090T Black Edition.

Interestingly this behaviour will not show up when using the COUNT(*) function without any WHERE clause, performance is consistent regardless of the number of cores used, however, once you have a Compute Scalar in the plan then the core scalability issue described kicks in.

select COUNT(*) from TestGuid_HeapINSERT with ( index = 0 )

Average Disk Bytes/read 404,396
Average Disk Sec/read 0.001
Average Disk Reads/sec 2,806
Average Read Bytes/sec 1,134,827,393

The SQL below has been used; the index hint used to simplify the experiment and make sure a table scan is being performed:

select MAX( cast( expandrow as bigint ) ) from TestGuid_HeapINSERT with ( index = 0 )

Reviewing the figures above Average Disk Bytes per read, Average Disk Sec/Read, Logical Reads, Read-ahead reads and CPU Time (ms) remain fairly constant, however Average Disk Reads/Sec and Average Read Bytes/Sec are stepped and Elapsed (ms) reduces dramatically with a second core then savings diminish as cores are added.

It should be noted, this problem isn’t actually specific to the IO subsystem, and the problem occurs if you have enough of your table in the buffer pool too, essentially the core cannot get the data through quick enough.

Relating to the real world

The figures above show that given a query that has no parallelisation and therefore runs on a single core the duration will be 512% of the capability of the box should all cores be used and will use only 19% of the availability Read Bytes/sec maximum.

In past versions of SQL Server we have been used to turning parallelism off because it more often than not extended the duration of the query, which was fixed in SQL Server 2008 where the parallelism does now work well. However, legacy systems and code and a perpetual myth that needs breaking is that MAXDOP should be removed and just let SQL Server get on with it.

It is true that on a system with a number of concurrent connections that this problem will balance itself out, however, what about the sequential overnight batch jobs that so many of us have?

No direct advice here (yet), may be that will come when I finally do my conclusions around these SSD benchmarks, but you need to be aware that this problem is here and as IO subsystems perform better (which mine does) then these issues will need addressing.

https://sqlblogcasts.com:443/blogs/tonyrogerson/archive/2011/07/23/discovery-when-your-io-subsystem-out-performs-processor-cores-how-parallelisation-really-is-our-friend.aspx