I opened the data management track at TechEd EMEA yesterday, to a full room and as ever if I am unsure of a question I research it and put it on this post the next day.

I was pretty sure of the differences in the way database mirroring in standard and enterprise edition of SQL Server 2008 which are:

Standard Edition you can only use mirroring in full safety mode i.e. synchronous mirroring optionally with a witness to enable automatic failover. btw the witness can be express, but the mirror must be standard as well. 

Enterprise Edition.

• Mirroring can be run in high performance (asynchronous mode) which can be useful in a geo-mirror configuration over a slow network, where synchronous mirroring would create unacceptable delays for the user.
• Torn page detection allows the principal to discover and automatically copy a damaged page from the mirror to the principal.
• log compression dramatically improves the performance of mirroring in several ways, the send queue is reduced the transaction per/sec transmitted to the mirror are up by 2.5x on a 10mbs network (lees on faster ones). There is some CPU overhead for this

There are no restrictions on how many mirrored databases can be setup on the mirror in standard or enterprise (which could come from different principal servers), however there are some things to remember:

• An instance can only have one endpoint which could be a bottleneck if there are lots of databases in the one instance.
• It is not recommended to have more than ten databases mirrored to one server, I have not seen that change for SQL Server 2008 but the safe limit should be higher for Enterprise edition given the compression feature.

If you are TechEd I have now got the shuttle working like this

… so that the database in the cluster is mirrored to another server which in turn is log shipped to another instance, so come and say hello on the SQL Server Ask the Experts stand.

Imagine you have a windows 2003/SQL Server 2005 cluster (active-active) and your mission is to both virtualise and maintain high availability using Hyper-V in Windows Server 2008/SQL Server 2008.  This may sound odd but I was asked the question at TechEd and I wanted to do some research on it. Fortunately I on Windows 7 training this weekend and have access to 2 of the key players Jeff Woolsey who ‘owns’ Hyper-V development and clustering product manager Dave Dion. 

So are your options? and more importantly how do they help?

I would start this by addressing what are you most worried about…

• Server outage
• Site Outage
• Network issues
• SAN issues and media loss

Also what are your goals e.g.

• Database availability
• User response time
• Supported by your solution providers (e.g. hardware vendors,  Microsoft etc.)
• Ease of Maintenance, automation diagnostics
• Predictability that when something goes wrong you know what will happen to mitigate it.

Finally other workloads are also on this setup which will also be virtualised.

The customer who asked me this wanted to cluster the cluster by..

• Creating a hyper-V cluster ClusterH on Node1 and Node2
• Creating  virtual machines (VM) vmA and vmB to run on that Cluster with a resource group for each VM such that vmA runs on Node1 and vmB runs on Node2 (i.e. active active). Both of these are using pass though disks i.e. they are talking directly to a LUN on a SAN and so will perform  very well.
• Clustering vmA and vmB to form ClusterV with SQL Server 2008 installed.

So how does this stack up against our criteria above?

Basically not very good..

Initially this solution looks quite good - if node 1 or node 2 dies then the surviving vm on the surviving node will take  ownership of the storage. But this will take time. Currently hyper-V VM’s only support iSCSI (fibre channel is on the way). The two VMs will have different routes to the same shared storage so if that is not accessible then the system will fail. However the killer is that Microsoft do not support SQL Server failover clustering of VMs as per this KB. BTW this limitation applies to all virtualisation technologies, not just Hyper-V.

Is there a better way? For example if you mirrored the critical databases between vmA and vmB then you now have a copy of the database on either shared or separate storage.  You could then script a manual failover to the mirror if the node running on the principal moved over to the other node as its storage would now be remote to the VM. This is supported, and mitigates a number of failures including:

• Hardware failure of a node.
• Media failure on one of the nodes or part of the SAN.
• Site failure if the nodes are remote to each other and have shared SAN
• An instance of SQL Server crashing

Of course this is at the database level, but the principal could be extended for more than one database and other workloads as well. 

However in my opinion the best way to consolidate here is to create a physical cluster and consolidate SQL Server workloads on into it.  Clustering is a lot easier in Windows Server 2008 and you n o longer need carefully matched hardware. though the surviving nodes in a cluster need to be powerful enough to handle all of the work of the cluster.

Let me know what you think and what your plans are in this space.

Among the interesting stuff discussed at last nights SQL community meeting at the Microsoft Campus,was a lively debate on whether to wait for SQL Server 2008 sp1.  The traditional view has been to wait until sp1 because that is the stable release.  One wit in the audience suggested that SQL Server 2008 was really SQL Server 2005 sp4 and therefore everyone should go right ahead.  While SQL Server 2008 is actually a lot more than a service pack, it is not the major rework that SQL Server 2005 was. 

David Portas an MVP working for Conchango pointed out how stable it was and he should know he’s been using it for year.  He went on to pint out that maybe an sp1 would not be that well tested and would you really deploy that version the day it came out? 

My own take on this is that there probably won’t be a service pack for a long time if at all. SQL Server 2008 is very stable and don’t take my word for it  because if it wasn’t the stories would be all over the web (the Register, ZDNet, slashdot etc).  So your decision on when to upgrade should not be influenced by that and reasons to upgrade should instead depend on what you are doing with SQL Server:

• You want to move off of SQL Server 2000 perhaps because are concerned about support
• You have SQL Server 2000 and want to go 64 bit so you can address more memory
• You want to use one of the new data types in your application, like spatial or filestream.
• You want to better manage a large estate of SQL Servers and consolidate them.  

If your interested in SQL Server 2008, you might want to download the product from your TechNet subscription and at least install the client on your local machine so you can see how it looks.

I often say that a database is only as good as the system it is running on. For example SQL Server supports 64 cores (as opposed to CPUs) because that is the limit in Windows Server, even in Windows Server 2008.   However if you have been at PDC or TechEd in Barcelona (where I am writing this from) then you will have seen that Windows 2008 r2 will have support for 256 cores.  Surprise surprise the SQL server team at WinHEC (the Hardware Engineering Conference) have now issued a  statement that SQL Server can use all of that with the release Kilimajaro.

If you haven’t heard of Kilimanjaro it’s primarily aimed at providing end user business intelligence tools for SQL Server.

BTW SQL Server is licensed per CPU so this could be a really cheap solution if Intel release a 256 core version of the Xeon.

Another good thing that’s done at the SQL Server community evenings is a 5 minute slot where anyone can have a go at showing something interesting.

Tony Rogerson got me to open the batting to encourage others to have a go.  I am not sure who it was who came up next but it was a simple thing on renaming the infamous sa account…

ALTER LOGIN sa WITH NAME = SECRETSA.

Tony was too keen on leaving his laptop like that in case anything broke so just run the same command again..

ALTER LOGIN SECRETSA WITH NAME = SA.

It’s a useful security tip, especially if you are seeing lots of failed sa logins.

So here’s how it’s done..I rarely know more about SQL Server than my friend Beatrice so I was surprised when I mentioned plan guides and she hadn’t heard of this new feature. I have to say this is not a big mainstream thing and it takes me back query hints in SQL Server 2000.

Essentially you get the optimiser to generate a plan for you and then apply this to the query so that it always gets used every time that query is subsequently run.  It might be a good way of getting the best performance out of a third party application you can’t change.

So here’s how it’s done..

SET STATISTICS XML ON
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM
    Production.WorkOrder AS w
    INNER JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE
    p.ProductSubcategoryID > 4
ORDER BY
    p.Name, DueDate;
GO
SET STATISTICS XML OFF
GO

The important bit is the query plan (double click on the xml to see this)..

You can get the SQL handle for the plan like this …

SELECT
    *
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY
        sys.dm_exec_sql_text(qs.sql_handle) AS qt
    CROSS APPLY
        sys.dm_exec_text_query_plan(qs.plan_handle,
                                    qs.statement_start_offset,
                                    qs.statement_end_offset) AS qp
WHERE
    qt.text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO

and create a plan from it with this…

DECLARE @plan_handle varbinary(64);
DECLARE @offset int;

SELECT
    @plan_handle = qs.plan_handle,
    @offset = qs.statement_start_offset
FROM
    sys.dm_exec_query_stats AS qs
    CROSS APPLY
        sys.dm_exec_sql_text(qs.sql_handle) AS qt
    CROSS APPLY
        sys.dm_exec_text_query_plan(qs.plan_handle,
                                    qs.statement_start_offset,
                                    qs.statement_end_offset) AS qp
WHERE
    qt.text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle
    @name =  N'MyPlanGuide',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO

There are event classes to see if its being used or missed e.g. you might have changed the schema for example and these  are cunningly named as

• plan guide successful
• plan guide unsuccessful

You can check your plan guides using fn_validate_plan_guide(plan_guide_id), for example

USE AdventureWorks;
GO
SELECT plan_guide_id, msgnum, severity, state, message
FROM sys.plan_guides
CROSS APPLY fn_validate_plan_guide(plan_guide_id);
GO

You can see if a plan guide is in use if you run the query again and look at the properties of the query plan (press F4):

The plan guide will also show up under programmability in Management Studio:

and to get rid of it you’ll need to run 

EXEC sp_control_plan_guide @operation = N'DROP', @name = N'[MyPlanGuide]'

Finally the full detail is here in the SQL Server TechCenter

I have finally finished at TechEd after another round of interesting questions on the Microsoft SQL Server ask the experts stand. Virtualisation was the consistent hot topic  and the 2 FAQ’s are:

Is it supported?  Yes fully in a variety of situations the chapter on verse for Hyper-V is here. Apparently there are other virtualisation platforms out there and so there is also a Server Virtualisation Validation Program (SVVP) which also means that Microsoft will also support SQL Server (and the rest of the Microsoft server) on a variety of other Virtualisation platforms including selected products from:

• Cisco Systems, Inc.
• Citrix Systems, Inc.
• Novell, Inc.
• Oracle, USA Inc.
• Sun Microsystems
• Unisys Corp.
• Virtual Iron Software
• VMware, Inc.

Photo’s courtesy of Kevin Moran 

Activity Monitor is a simple and quick way to see what’s happening right now in SQL Server,

Not too much happening on my demo rig but you get the idea.

It took me a few minutes to open this because my local books on line has an error in it  on how to open it (it suggests expanding form the management tree) so my other tip is to use the books on line that is actually on-line.

I see a lot of people asking about how to make integration services highly available and there isn’t an easy answer, so let me explain why.

Integration services (SSIS) is designed to bulk move a set of data from a source to a target while performing some sort of translation on the way.  The most often causes of failure for SSIS packages I have seen are, in order:

1. The source is not available. This can because a process upstream of the SSIS package has not completed to create the target, a change in credentials or an actual issue with the source

2. The target is not available.  Credentials or networking issues are the main culprits

3. There has been some change to the structure of the source or the data in it , not anticipated in the design of the package.

No SSIS high availability solution is going to be able to mitigate these risks, so you end up protecting what you can by making the source and targets highly available and then try and figure out some way of doing the same for SSIS which doesn’t have any inherent high availability built in e.g. it is not cluster aware.

If this is something you do want to do then I would run SSIS on two separate servers and put tests and logging in the packages such that if a package runs on server B it checks to see it has not already been run on server A.  In this scenario it’s important to ensure each package can be rerun from the beginning without corrupting any data (something which is good practice anyway).  It is also possible to set up load balancing for SSIS(http://technet.microsoft.com/en-us/library/ms345184.aspx), and store logging information centrally (which could be on a cluster).

Another thing to consider if this flow of data is mission critical and not too large is to use a completely different approach like BizTalk.

It’s a simple matter to restore SQL Server backups to later versions, in fact you don’t need to do anything different but what about the other way around?

I first got caught out with this  back in the days of SQL Server 7 and 2000, and the basic answer is the same today for SQL Server 2008 as it was then – you can’t restore a backup from a newer version of SQL Server to an older one.  Attach and detach will also fail.

The simplest approach I can think of is to transfer database objects in Integration Services. And don’t forget to bring over any logins as well to avoid orphaned users for example:

• to get them out of the current system use SSIS or this BCP command

Bcp master..syslogins out \\sqlserverdemo\data\syslogons.dat -N -S . -T

• To import them

EXEC sp_resolve_logins @dest_db= ‘personnel’

@dest_path= ‘\\sqlserverdemo\data\’

@filename= ‘syslogins.dat’

The only reason I can think of that you would need to do this would be to back out of a migration to a later version of SQL Server, and my advice is to do everything possible to avoid the need for this to happen by carefully planning the migration.

I rarely go off topic on my blog, but I was wondering whether I am wasting my time here.  Eileen rhas some research predicts that blogging is becoming redundant and the new thing to do is microblog on Twitter (I am DeepFat on Twitter) , Yammer et al, as this is the best way to share things instantly. My research indicates that my stuff is being read more and more and I get a lot of positive feedback when I get to meet some of you.  I do use microblogging to keep in touch with what I am doing, but not how to do stuff or why.

Just to be clear this blog is not about level 400 deep dives in to the dark corners of SQL Server. It’s about:

• (hopefully) useful stuff related to data management and BI, 
• making sense of how Microsoft’s solutions fit together,
• when to use what.

I don’t see the point in competing with the excellent technical output from MVPs and the wider UK SQL community, or with all of the essential stuff in TechNet.  When I call out important KB’s, Hand on Labs, WebCasts and events, it’s because these are in my opinion the good ones.

Another point is that I find blogs generally easy to search and I use mine as my personal books on line.  Some of the stuff on here requires considerable research,  as I haven’t always got the answer, so if I am wasting my time here I can free up about 20% of my day for other things.   

My final point is that blogs are quite permanent which is both good and bad.  Good because when SQL Server 2008 is in extended support some of this will be useful to those businesses that haven’t upgraded yet.  Bad because if I make a mistake it’s there until it’s discovered (so far only the odd typo).

OK Rant over, let me know what you think while I get back to SQL Server.

I am still getting loads of questions on virtualising SQL Server so rather than blogging away here, I am getting involved with Windows ITPro magazine along with a number of cross industry virtualisation experts to run a virtual (what else would it be!) event “Virtualization: Get The Facts” from 11:00am (GMT) tomorrow.  

I am on the SQL server slot at 15:00 so if I haven’t answered your specific questions you can virtually chat to me from the comfort of your desk, and we can both save the cost and time of travelling. 

If you need any other incentive you can win an ipod nano (??) for joining in!

During a quiet moment at TechEd I was talking to the Search Server Express product team about our respective technologies (SQL Server and SharePoint) and how significant they were.  I have a simple demo to prove how widespread SQL Serve is to back me up in these situations which you might like to use if you are feeling unloved and unwanted at work…

1. Logon to your favourite on-line IT Job site and note the number of jobs on line. In my test it was 20,496.
2. Filter  on “SQL Server”  and note divided by answer 1. In my case the answer was 2,571 to give 12.5% of all jobs advertised are for SQL Server.
3. Repeat for the technical specialism of the person you are talking to. For SharePoint the answer was 756 jobs to give a 3.7% ratio
4. Smile politely.

As I have remarked before Business isn’t run by the IT Department it is run by the people who hire and fire the IT department i.e. the Finance Director (FD).  Given the current economic climate now would be a perfect time to see what you can do to help, so I have spent the morning listening to my FD (.. of Microsoft UK), Toby Wilson at the Institute of Chartered Accountants of England & Wales (ICAEW).  He joined a round table on Business Intelligence hosted by Andrew Sawers the Editor of Financial Director. I thought some of the discussions although often repeated would be worth repeating because these are straight from the men with the cheque books, and their number one priority is:

One Version of the Truth. Often repeated mantra perhaps because it is so rare in many organisations.  Toby deals with this in a two key ways:

• Challenge any other report that cannot be reconciled with his own data.  The main UK scorecard and financial data at his disposal has been rigorously tested and so is the accepted truth.
• Data provided by third parties e.g. PC sales from the Dixons Group needs to be accurate and timely and this can be relied because that reporting mechanism is how third parties get their incentives and discounts.

One really obvious point is the need to snapshot your data at point in time so all reports from it are based on the same set of data.  Several technical approaches can help to achieve this

• database snapshots, although this is quick it is fragile as snapshot are dependent on a database and if the version of this database is lost the snapshot are useless and cannot be easily recreated.
• Type 2 slowly dimensions (see this post on type 2 dimensions for more info), can have extra attributes to marked when a row is in force or not, as can the fact table.  This can be important to rule in or rule out financial adjustments reversals etc. applicable to a particular scenario.
• A special scenario dimension which allow fact to be included in certain scenarios and not in others. This allows for multiple budgets and forecasts to be sotred alongside the actuals. 

My other top tip is to ruthlessly ensure that every reports contains  a header with the all the details of how filters are set the date it was published and by whom etc.

Data Visualisation is one of my many interests and Marc Holmes showed us a bunch of wordles like the one above in yesterdays team meeting, and we had to guess the blogger. Mike Taulty won despite not recognising his own blog!. You can see why Marc didn’t use my wordle, SQL Server sort of stands out from the rest. 

So what you see is what you get!

The other hot topic at last Friday’s ICAEW round table on Business Intelligence was KPI’s.

Toby Wilson (Finance Director of Microsoft UK) explained that Microsoft uses 30 KPIs despite the diversity of its diverse business (XBox, Mobile, Live services, Software, Consulting etc.).  These are set top down and although they are a constraint they are fair because everyone knows the rules of the game.  Once you get green on so many of these (including revenue of course) then an FD has flexibility to innovate to be more successful. 

One of the other finance directors on the panel countered this by stating that there was only one KPI needed by a business – cash.  His assertion was that many a profitable business has gone to the wall because it actually had no money, and in the current credit crisis this is even more likely. 

However the sorts of KPI’s used at Microsoft and many other companies I have worked for are lead indicators and monitoring these will lead to sustainable long term growth.  This is where the balance in balanced scorecard comes in. In this case balance between success today and success tomorrow.

A typical balanced scorecard might look like this with KPI’s under each heading

• Financial health including cash, contribution margin, cost base are showing you how you are doing today. 
• Growing the business. Improving market share, by retaining existing customers and attracting new ones.
• Innovation.  Developing and delivering new ideas to maintain your edge of the competition.
• People.  Retaining and attracting the right talent to your organisation.

The other key facet of the balanced scorecard and its associated KPI’s is to derive departmental, team and individual scorecards from the top level scorecard for the company and to link individual bonuses and performance to these.

This approach was worked in numerous companies from the global Microsoft sized organisation down to small businesses with under 50 employees, since the theory was first published by Robert S. Kaplan and David P. Norton in 1992. To conclude I am sure that the focus it provides to all staff in an organisation is even more important today than it was then, but it is also important to never forget about financila health whether your own or the company you work for.