Thoughts from Data Platform MVP and Microsoft RD – Dr Greg Low
Great to see that our friend Ola Hallengren has updated his maintenance utility scripts to deal with SQL Server 2008 R2.
These scripts are highly regarded, particularly given the price: free !
You'll find them here:
http://ola.hallengren.com/Versions.html
Ola noted that the main change from 2008 is that backup compression is now supported in Standard Edition of SQL Server. That in itself is good news.
In CTP3, I found setting up the StreamInsight Event Flow Debugger fairly easy. For RTM, a number of security changes were made.
First config: To be able to connect to the management interface, your user must be added to the Performance Log Users group. After you make this change, you must log off and log back on as the token is only added to your login token when you log on. I forgot this and spent ages trying to work out why I couldn't connect.
Second config: You need to reserve the URL that the management service will be exposed on. For current operating systems, this involves a command like:
netsh http add urlacl url=http://*:8090/StreamInsight/MSSQLSERVER user=GREGWIN7\Greg
This must be executed from a command prompt with elevated privileges (ie: right-click command prompt and Run As Administrator). This says that my user is allowed to use the URL reserved above. The user specified would need to be the user that is the identity your StreamInsight server would run as.
Third config: You need to open a WCF endpoint. I did this by declaring the following members:
private WSHttpBinding _binding = null;
private ServiceHost _host = null;
and then configuring and opening the host as:
_host = new ServiceHost(_server.CreateManagementService());
if (_binding == null)
{
_binding = new WSHttpBinding(SecurityMode.Message);
_binding.HostNameComparisonMode =
HostNameComparisonMode.WeakWildcard;
}
_host.AddServiceEndpoint
(typeof(IManagementService),
_binding,
@"http://localhost:8090/StreamInsight/MSSQLSERVER");
_host.Open();
Once this was working I could connect to the endpoint using Event Flow Debugger, using the address in the AddServiceEndpoint call.
Another thing that tripped me up at first was that my app was too UI-bound and the management service wasn't responding in a timely manner. I either saw timeouts on connection or I saw a message telling me that my server was too busy. Turns out that was an accurate message. I just hadn't believed it while I couldn't get it working!
For those that have worked with the earlier versions of the simulator that Bill Chesnut and I constructed for the Metro content (the Highway Simulator), changes are also required to how AdvanceTimeSettings are specified.
The AdapterAdvanceTimeSettings value is now generated by binding an AdvanceTimeGenerationSettings (that is based on your adapter configuration) with an AdvanceTimePolicy setting.
public class TollPointInputFactory :
ITypedInputAdapterFactory<TollPointInputConfig>,
ITypedDeclareAdvanceTimeProperties<TollPointInputConfig>
{
public InputAdapterBase Create<TollPointEvent>
(TollPointInputConfig configInfo,
EventShape eventShape)
{
return new TollPointInput<TollPointEvent>(configInfo);
}
public void Dispose()
{
}
public AdapterAdvanceTimeSettings DeclareAdvanceTimeProperties<TPayload>
(TollPointInputConfig configInfo,
EventShape eventShape)
{
var atgs = new AdvanceTimeGenerationSettings
(configInfo.CtiFrequency,
TimeSpan.FromSeconds(0),
true);
var ats = new AdapterAdvanceTimeSettings
(atgs,
AdvanceTimePolicy.Drop);
return ats;
}
}
In this case, I've specified the Drop policy. An alternate policy is Adjust. The documention suggested that Adjust causes the event to be moved back into the window that you're using. For the events we were using, we found that wasn't what happened. It turns out that that was because we were using Point events. What Adjust actually does is clip an event to lie wholly within the CTI period as long as the event overlaps the CTI. This would only then work with Interval or Edge events, not with Point events.
I'd briefly played around with user-defined aggregates in StreamInsight with CTP3 but when I started working with the new Count Windows, I found I had to have one working. I learned a few things along the way that I hope will help someone.
The first thing you have to do is define a class:
public class IntegerAverage: CepAggregate<int, int>
{
public override int GenerateOutput(IEnumerable<int> eventData)
{
if (eventData.Count() == 0)
{
return 0;
}
else
{
return eventData.Sum() / eventData.Count();
}
}
}
In this case, I've defined an IntegerAverage class that inherits from CepAggregate. CepAggregate is then declared as a generic type taking an int input parameter and providing an int output. The GenerateOutput method is overriden and called when it's time to process the input. In this case, I've just checked if there are any values, returned zero if not and returned an integer average if there are values.
This is fine but to make it work with Intellisense and make the compiler play nicely, etc. you need to do more. The following code helps:
public static class UDAExtensionMethods
{
[CepUserDefinedAggregate(typeof(IntegerAverage))]
public static int IntAvg<T>(this CepWindow<T> window,
Expression<Func<T, int>> map)
{
throw CepUtility.DoNotCall();
}
}
We define the name that we will use in code (in this case IntAvg). As this should never be called directly, we throw an exception if it is ever called.
The part that tricked me at first was this:
Expression<Func<T, int>> map
I had seen it used in the doco in Books Online but BOL didn't ever mention where it came from. To use this, you need to add a using statement in your code as:
using System.Linq.Expressions;
This is the line that maps the function signature. Without it, you'll get an error telling you that your aggregate has no overload taking one parameter. This allows me to then call it in code via:
select new { AverageMilliseconds = w.IntAvg(e =>
e.MillisecondsToPassSpeedCheckPoint) };
Passing The Event Instead
An alternative to this would be to build an aggregate that's specific to the the event type. You could then call it via:
select new { AverageMilliseconds = w.SomeAvg() };
but then your user-defined aggregate would have to be defined via:
public class SomeAverage: CepAggregate<TollPointEvent, int>
In this case, TollPointEvent was the name of the event type that the window is being created over. You'd need to then access members of the event class inside your aggregate code, like:
public override int GenerateOutput(IEnumerable<TollPointEvent> events)
{
if (events.Count() == 0)
{
return 0;
}
else
{
return events.Sum(e => e.MillisecondsToPassSpeedCheckPoint)
/ events.Count();
}
}
Finally, you'd need to modify your declaration similar to:
public static class UDAExtensionMethods
{
[CepUserDefinedAggregate(typeof(IntegerAverage))]
public static int SomeAvg<T>(this CepWindow<T> window)
{
throw CepUtility.DoNotCall();
}
}
Note that the Expression section would not be needed as the whole event was being passed.
While you can do this, I'd only look to do this where your aggregate is very specific to the event class and where it involved multiple fields from within the event.
Another interesting change in the RTM version of StreamInsight is the addition of a new window type. Count Windows aren't time based but are based on counting a number of events. The window type provided in this release is called CountByStartTimeWindow. Based on that name, you'd have to presume we might get other types of count windows in the future.
This new window type takes two parameters. The first is the number of events. The second is an output policy, similar to the policies now required for the previous window types. The CountWindowOutputPolicy currently only offers one policy type as PointAlignToWindowEnd.
Here's an example of the window in use:
var queryOutput = from w in input.CountByStartTimeWindow
(10,
CountWindowOutputPolicy.PointAlignToWindowEnd)
select new { AverageMilliseconds = w.IntAvg(e =>
e.MillisecondsToPassSpeedCheckPoint) };
There are a few important things to note here. The first is that the count window doesn't work with the built-in aggregates such as Sum, Min, Max, Avg, etc. This surprised me and I'm sure that will be an option some time in the future. At present, it works with user-defined aggregates. (I'll describe building this user-defined aggregate in another post).
The second note is that the count isn't strictly a count of events, it's a count of distinct event start times. So if you have only one event per start time, the number of events in the window will match your count parameter. But, if you have multiple events starting at the same time, you can have more events in your window than your parameter has requested.
One of the problems that existed in the CTP3 edition of StreamInsight was an error that occurred if you tried to access the grouping key from within your projection expression. That was a real issue as you always need access to the key. It's a bit like using a GROUP BY in TSQL and then not including the columns you're grouping by in the SELECT clause. You'd see the results but not be able to know which results are which. Look at the following code:
var laneSpeeds = from e in vehicleSpeeds
group e by e.Lane
into lanes
from eventWindow in lanes.TumblingWindow
(TimeSpan.FromSeconds(10),
HoppingWindowOutputPolicy.ClipToWindowEnd)
select new
{
Lane = lanes.Key.ToString(),
AverageSpeed = eventWindow.Avg(e => e.Speed),
MaxSpeed = eventWindow.Max(e => e.Speed)
};
Executing this code would return:
System.IndexOutOfRangeException: Index was outside the bounds of the array.
This was a known issue in CTP3 that I hoped would have been corrected at RTM. I spoke with the development team and they pointed out that the error only occurs if you're using an anonymous type. This means that if I declare the type using code like:
struct LaneGroup
{
public string Lane;
public double AverageSpeed;
public double MaxSpeed;
}
my code will then work as long as I modify it to specify the type in the select clause:
var laneSpeeds = from e in vehicleSpeeds
group e by e.Lane
into lanes
from eventWindow in lanes.TumblingWindow
(TimeSpan.FromSeconds(10),
HoppingWindowOutputPolicy.ClipToWindowEnd)
select new LaneGroup
{
Lane = lanes.Key.ToString(),
AverageSpeed = eventWindow.Avg(e => e.Speed),
MaxSpeed = eventWindow.Max(e => e.Speed)
};
This should be a suitable workaround in most cases. I gather the original problem with be fixed in a later refresh of StreamInsight.
We've been working on updating our demos and samples for the RTM changes of StreamInsight. I'll detail these as I come across them.
The first is that there is a change to the HoppingWindow. The first two parameters are the same in the constructor but the third parameter is now required. It is the HoppingWindowOutputPolicy. Currently, there is only a single option for this which is ClipToWindowEnd.
A similar change happened to the TumblingWindow. Curiously, it also takes a HoppingWindowOutputPolicy. I suppose that makes sense though as it is really just a special case of a HoppingWindow.
SnapshotWindow also now has a required parameter but in this case it's a SnapshotWindowOutputPolicy. The option for this policy is just called Clip.
Here are some examples of creating HoppingWindow, TumblingWindow and SnapshotWindow.
var queryOutput = from w in input.HoppingWindow
(TimeSpan.FromMinutes(1),
TimeSpan.FromSeconds(10),
HoppingWindowOutputPolicy.ClipToWindowEnd)
select new { VehiclesPerMinute = w.Count() };
var queryOutput = from w in input.TumblingWindow
(TimeSpan.FromSeconds(10),
HoppingWindowOutputPolicy.ClipToWindowEnd)
select new { VehiclesPer10Seconds = w.Count() };
var queryOutput = from w in input.SnapshotWindow
(SnapshotWindowOutputPolicy.Clip)
select new { VehiclesPerWhat = w.Count() };
A question came up today about whether it was a bug that the TRUSTWORTHY database setting isn't restored to its previous value when a database is restored.
TRUSTWORTHY is a very powerful setting for a database. By design, it's not restored when a database is. We actually documented this behavior when writing the Upgrade Technical Reference for 2008: http://www.microsoft.com/downloads/en/confirmation.aspx?familyId=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displayLang=en
The other settings that are not restored with a database (for similar reasons) are ENABLE_BROKER and HONOR_BROKER_PRIORITY. After a restore or upgrade of a database, you need to check these. (Note: HONOR_BROKER_PRIORITY was introduced in 2008 so it won't apply to upgrades from 2005 but ENABLE_BROKER does).
It's probably apparent that I've been travelling again a lot lately as the number of posts related to books has gone up.
One book that I picked up along the way and really enjoyed was Scott Berkun's Confessions of a Public Speaker. I could relate to so much of what Scott was talking about and there are quite a few solid nuggets of advice in the book.
It's very important when you are regularly giving technical presentations to spend time learning about the "presenting" part of the task, not just about the "technical" aspects. I found it quite insightful when Scott discussed how giving technical presentations has so much in common with stand-up comedy. It's not that you need to be a stand-up comedian but much can be learned by watching how good stand-up comedians ply their trade. They endlessly deliver the same material but need to make it sound fresh each and every time.
While the book is fairly solid from start to end, I did get quite a few laughs in one of the final chapters where he discusses things that other people have shared with him about their personal speaking disasters. It was also great to see our ex-RD buddy Scott Hanselman (now a star at Microsoft) quoted in the book.
Recommended!
I've met Klaus a number of times now and attended a few of his sessions at conferences. Klaus is doing a great job of evangelising Service Broker. I wish the SQL Server team would give it as much love.
Service Broker is a wonderful technology, let down by poor resourcing. Microsoft did an excellent job of building the plumbing for this product in SQL Server 2005 but then provided no management tools and no prescriptive guidance. Everyone then seemed surprized that the takeup of it was slow. I even heard noises questioning it's future a while back and I hope those noises have quietened now. The lack of serious tooling in 2008 was a case of seriously "dropping the ball" regarding the product. It also highlights the other real problem with SSMS in the lack of extensibility. If a supported extensibility model for SSMS was available, others would have stepped up to the plate and we'd have really good Service Broker tooling by now, even when Microsoft hadn't provided it.
Enterprise clients are finally getting their heads around what Service Broker does and are starting to use it, in spite of the lack of resources. I've lost count of the number of sites I've gone into that have a problem that Service Broker would address beautifully but when you suggest it, you get very blank stares back. What makes this worse is that most DBAs aren't very familiar with message-based architectures. Ironically, these sorts of architectures can give them much of the scalability they're trying to get from the product. I constantly go into sites where I see people building an unreliable "house of cards" with bunches of inter-connected systems, the failure of any one of which renders the whole system unusable. Asynchronous processing adds a real new dimension to SQL Server and is such a good solution to so many problems.
This book from Klaus is an update to the 2005 book and does a wonderful job of covering most of what you need to know to work with Service Broker. The book is well-written and builds a good story from beginning to end.
The book isn't perfect. In particular, I noticed a number of places where (I assume) a global replacement of the word 2005 with 2008 caused errors, such as statements about mirroring first appearing in 2008, etc. Also, I'd have to disagree with some of the advice that's outside the realm of Service Broker. For example, Klaus shows how to change the SQL Server service account by using the services snap-in for the MMC. The service account should always be changed from within SQL Server Configuration Manager as it updates ACL's, etc. as well as the logon account.
But these sorts of details are minor and Klaus has done a wonderful job of showing how to use Service Broker and explaining why you'd want to do so.
Recommended!