Tuesday, March 16, 2010

Smoke and Mirrors in SQL Server 2008 R2

Microsoft is touting its new Release 2 of SQL Server 2008. If they weren't planning to charge for it they would be honest and call it a service pack, not a Release. When you consider that SQL Reporting Services arrived in a free service pack for SQL 2000, what R2 offers as a full release is a bit underwhelming.


R2 has its one flagship feature, self-service business intelligence in the PowerPivot for Excel feature, but that is not available in Standard Edition. I have not used the feature yet but I have my doubts that it will bring business intelligence to the masses as promised.

My opinion, based on my experience, is that the masses don’t want business intelligence. A two dimensional grid like a spreadsheet is about as far as many business users are willing to go.

There is only so much simplification that can be done to allow rank and file users to create and browse multi-dimensional data. (How many business users do you know who can even create and use an Excel pivot table?) Real BI may always be beyond the capacity of the casual business user.


For the Standard Edition user, there is even less that would drive you to upgrade. The backup compression feature of SQL Server 2008 Enterprise Edition has been added to the Standard Edition of R2. That’s about it.


R2 introduces two more SQL Server editions, Parallel Data Warehouse and Datacenter. They are dubbed Premium Editions because they come at a distinctly premium price. Either one will set you back $57,498 per processor (list price). However, Microsoft is the only database vendor who counts processor sockets rather than cores in its licensing scheme. So an 8 core processor will cost you the same as a single core. This makes the premium editions an attractive alternative to Oracle where you would need to pay for 8 licenses.

Oddly enough, these two new expensive powerhouse editions may be the most compelling reasons to upgrade, but only for those who need to deal with multi-terabyte databases and huge processing loads. These editions are designed to close the gap with Oracle at the high end of the database spectrum with attractive pricing. If you live on the upper edge of the spectrum you might want to look into what they offer.

Saturday, January 9, 2010

A Tool for Herding Cats

Managing developers is often like herding cats. Many development teams lack formal change control procedures and in many teams everyone has a sysadmin password. Sometimes in smaller companies there is not an adequate test environment and developers acquire the habit of making changes in production. Often they do not communicate those changes to the team lead or the other members of the team.

As we all know, this is a terribly dangerous practice, but we have all done it. Sometimes we have no choice.

if you are responsible for managing a situation like that and the bean-counters are not likely to approve the cost of a real change control platform, here is a script that will make your life a little easier. It will quickly show you the important things that have changed in a database since a point in time that you specify.

declare @cutoffdate datetime
, @days smallint;
set @days = -7; -- change this as necessary
set @cutoffdate = DATEADD(dd, @days,getdate());
select db_name() as DBName
, name
, type_desc
, case when create_date > @cutoffdate then 'New' else 'Changed' end as [Status]
, create_date
, modify_date
from sys.objects
where (modify_date > @cutoffdate or create_date > @cutoffdate)
and is_ms_shipped = 0 -- exclude system objects
and is_published = 0 -- exclude repl tables that will always show change
order by DB_NAME(), type_desc, name;

This is a handy tool for a quick look but it has a lot of limitations. It's redeeming feature is that it is simple and puts no load on the database.

It doesn't show you what change was made or who made it. It doesn't allow you to roll back changes. It doesn't include all the types of database objects (no indexes included for example). Worst of all, it doesn't tell you what has been dropped or deleted.

However, it does alert you to newly created objects and changes for object types that are most relevant to developers: stored procedures, triggers, tables, etc. This will at least allow you to ask around to find out why the change was made and who made it. Most importantly, if the wheels suddenly come off of your website, you can quickly see what, if anything, was changed in the database at the same time the problem started.