Our buddy Buck Woody made an interesting post about a common question: “How do I back up a single table in SQL Server?”
That got me thinking about what a backup of a table really is. BCP is often used to get the data but you want the schema as well.
For reasonable-sized tables, the easiest way to do this now is to create a script using SQL Server Management Studio. To do this, you:
2010-06-07
A question came up on the SQL Down Under mailing list today about how to add an included column to an index using SMO. A quick search of the documentation didn’t seem to reveal any clues but a little investigation turned up what’s needed: the IndexedColumn class has an IsIncluded property.
Index i = new Index();
IndexedColumn ic = new IndexedColumn(i, “somecolumn”);
ic.IsIncluded = true;
2010-05-28
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.
2010-05-17
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.
2010-05-08
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:
2010-05-08
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.
2010-05-08
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.
2010-05-08
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.
2010-05-08
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.
2010-05-07
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.
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).
2010-04-15