SQLblog.com - The SQL Server blog spot on the web

Optimize Many-to-Many with SUMMARIZE and Other Techniques

Marco Russo (SQLBI) — Fri, 01 Jun 2012 12:12:00 GMT

We are still in the early days of DAX and even if I have been using it since 2 years ago, there is still a lot to learn on that.

One of the topics that historically interests me (and many of the readers here, probably) is the many-to-many relationships between dimensions in a dimensional data model. When I and Alberto wrote the The Many to Many Revolution 2.0 we discovered the SUMMARIZE based pattern very late in the whitepaper writing. It is very important for performance optimization and it should be always used. In the last month, Gerhard Brueckl also presented an approach based on cross table filtering behavior that simplify the syntax involved, even if it’s harder to explain how it works internally.

I published a short article titled Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering on SQLBI website just to provide a quick reference to the three patterns available. A further study is still required to compare performance between SUMMARIZE and Cross Table Filtering patterns. Up to now, I haven’t observed big differences between them, even if their execution plans might be not identical and this suggest me that depending on other conditions you might favor one over the other.

SSIS Training Comes to NYC 30 Jul-3 Aug!

andyleonard — Fri, 01 Jun 2012 11:00:00 GMT

Linchpin People is excited to announce the scheduling of From Zero To SSIS in New York City 30 Jul – 03 Aug 2012! Training Description From Zero to SSIS was developed by Andy Leonard to train technology professionals in the fine art of using SQL Server Integration Services (SSIS) to build data integration and Extract-Transform-Load (ETL) solutions. The training is focused around labs and emphasizes a hands-on approach. Most technologists learn by doing; this training is designed to maximize the time...(read more)

Learn SSIS in London 12-14 Sep 2012!

andyleonard — Thu, 31 May 2012 13:30:00 GMT

My friends at TechniTrain , the students, and I had a blast during the March 2012 London delivery of From Zero To SSIS ! We have decided to do it again in September 2012 with my new Learning SSIS 2012 3-day course ! Please find a course outline here . It is difficult to list everything I cover in the course, but the outline hits the high spots. This material grew out of my experiences serving as a consultant on short-term engagements and as a manager (and enterprise ETL architect) for a team of forty...(read more)

Smart defaults [SSDT]

jamiet — Thu, 31 May 2012 10:55:12 GMT

I’ve just discovered a new, somewhat hidden, feature in SSDT that I didn’t know about and figured it would be worth highlighting here because I’ll bet not many others know it either; the feature is called Smart Defaults. It gets around the problem of adding a NOT NULLable column to an existing table that has got data in it – previous to SSDT you would need to define a DEFAULT constraint however it does feel rather cumbersome to create an object purely for the purpose of pushing through a deployment – that’s the situation that Smart Defaults is meant to alleviate.

The Smart Defaults option exists in the advanced section of a Publish Profile file:

The description of the setting is “Automatically provides a default value when updating a table that contains data with a column that does not allow null values”, in other words checking that option will cause SSDT to insert an arbitrary default value into your newly created NON NULLable column. In case you’re wondering how it does it, here’s how:

SSDT creates a DEFAULT CONSTRAINT at the same time as the column is created and then immediately removes that constraint:

ALTER TABLE [dbo].[T1]
    ADD [C1] INT NOT NULL, 
        CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b] DEFAULT 0 FOR [C1];

ALTER TABLE [dbo].[T1] DROP CONSTRAINT [SD_T1_1df7a5f76cf44bb593506d05ff9a1e2b];

You can then update the value as appropriate in a Post-Deployment script. Pretty cool!

On the downside, you can only specify this option for the whole project, not for an individual table or even an individual column – I’m not sure that I’d want to turn this on for an entire project as it could hide problems that a failed deployment would highlight, in other words smart defaults could be seen to be “papering over the cracks”. If you think that should be improved go and vote (and leave a comment) at [SSDT] Allow us to specify Smart defaults per table or even per column.

@Jamiet

SQL vs. Oracle Live Debate (AKA Smackdown!)

Peter W. DeBetta — Wed, 30 May 2012 18:43:08 GMT

A few years ago I was speaking at a conference in Raleigh, NC where Ted Neward and I found a fun way to promote a Java vs. .NET debate that was planned one evening. We stood in the middle of a crowd during one of the breaks and starting “arguing” about Java vs. .NET with one another. Our voice levels quickly raised and we ended it by slapping each other across the face with a glove to request a challenge. It was a great way to segue to our announcing of the actual debate planned later that evening....(read more)

Redistribution of sqlpackage.exe [SSDT]

jamiet — Wed, 30 May 2012 14:15:51 GMT

This is a short note for anyone that may be interested in redistributing sqlpackage.exe. If this isn’t you then no need to keep reading. Ostensibly this is here for anyone that bingles for this information.

sqlpackage.exe is a command-line that ships with SQL Server Development Tools (SSDT) in SQL Server 2012 and its main purpose (amongst other things) is to deploy .dacpac files from the command-line. Its quite conceivable that one might want to install only sqlpackage.exe rather than the full SSDT suite (for example on a production server) and I myself have recently had that need. I enquired to the SSDT product team about the possibility of doing this. I said:

Back in VS DB Proj days it was possible to use VSDBCMD.exe on a machine that did not have the full VS shell install by shipping lots of pre-requisites along for the ride (details at How to: Prepare a Database for Deployment From a Command Prompt by Using VSDBCMD.EXE).

Is there a similar mechanism for using VSDBMCD.exe’s replacement, sqlpackage.exe?

here was the reply from Barclay Hill who heads up the development team:

Yes, SQLPackage.exe is the analogy of VSDBCMD.exe. You can acquire separately, in a stand-alone package, by installing DACFX.

You can get it from:

Feature pack is here: http://www.microsoft.com/en-us/download/details.aspx?id=29065

Web Platform Installer here: http://www.microsoft.com/web/gallery/install.aspx?appid=DACFX

You will notice it has dependencies on SQLDOM and SQLCLRTYPES. WebPI will install these for you, but it is al carte on the feature pack.

So, now you know. I didn’t enquire about licensing of DACFX but given SSDT is free I am going to assume that the same applies to DACFX too.

@Jamiet

Ghost Records, Backups, and Database Compression…With a Pinch of Security Considerations

Argenis — Wed, 30 May 2012 04:12:00 GMT

Today Jeffrey Langdon (@jlangdon) posed on #SQLHelp the following questions:

So I set to answer his question, and I said to myself: “Hey, I haven’t blogged in a while, how about I blog about this particular topic?”. Thus, this post was born.

(If you have never heard of Ghost Records and/or the Ghost Cleanup Task, go see this blog post by Paul Randal)

1) Do ghost records get copied over in a backup?

If you guessed yes, you guessed right. The backup process in SQL Server takes all data as it is on disk – it doesn’t crack the pages open to selectively pick which slots have actual data and which ones do not. The whole page is backed up, regardless of its contents.

Even if ghost cleanup has run and processed the ghost records, the slots are not overwritten immediately, but rather until another DML operation comes along and uses them.

As a matter of fact, all of the allocated space for a database will be included in a full backup.

So, this poses a bit of a security/compliance problem for some of you DBA folk: if you want to take a full backup of a database after you’ve purged sensitive data, you should rebuild all of your indexes (with FILLFACTOR set to 100%). But the empty space on your data file(s) might still contain sensitive data! A SHRINKFILE might help get rid of that (not so) empty space, but that might not be the end of your troubles. You might _STILL_ have (not so) empty space on your files!

One approach that you can follow is to export all of the data on your database to another SQL Server instance that does NOT have Instant File Initialization enabled. This can be a tedious and time-consuming process, though. So you have to weigh in your options and see what makes sense for you. Snapshot Replication is another idea that comes to mind.

2) Does Compression get rid of ghost records (2008)?

The answer to this is no. The Ghost Records/Ghost Cleanup Task mechanism is alive and well on compressed tables and indexes. You can prove this running a simple script:

CREATE DATABASE GhostRecordsTest
GO

USE GhostRecordsTest
GO

CREATE TABLE myTable (myPrimaryKey int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
myWideColumn varchar(1000) NOT NULL DEFAULT 'Default string value')

ALTER TABLE myTable REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
GO

INSERT INTO myTable DEFAULT VALUES
GO 10

DELETE myTable WHERE myPrimaryKey % 2 = 0

DBCC TRACEON(2514)

DBCC CHECKTABLE(myTable)

TraceFlag 2514 will make DBCC CHECKTABLE give you an extra tidbit of information on its output. For the above script: “Ghost Record count = 5”

Until next time,

-Argenis

Windows Azure End to End Examples

BuckWoody — Tue, 29 May 2012 13:45:59 GMT

I’m fascinated by the way people learn. I’m told there are several methods people use to understand new information, from reading to watching, from experiencing to exploring.

Personally, I use multiple methods of learning when I encounter a new topic, usually starting with reading a bit about the concepts. I quickly want to put those into practice, however, especially in the technical realm. I immediately look for examples where I can start trying out the concepts. But I often want a “real” example – not just something that represents the concept, but something that is real-world, showing some feature I could actually use.

And it’s no different with the Windows Azure platform – I like finding things I can do now, and actually use. So when I started learning Windows Azure, I of course began with the Windows Azure Training Kit – which has lots of examples and labs, presentations and so on. But from there, I wanted more examples I could learn from, and eventually teach others with. I was asked if I would write a few of those up, so here are the ones I use.

CodePlex

CodePlex is Microsoft’s version of an “Open Source” repository. Anyone can start a project, add code, documentation and more to it and make it available to the world, free of charge, using various licenses as they wish. Microsoft also uses this location for most of the examples we publish, and sample databases for SQL Server.

If you search in CodePlex for “Azure”, you’ll come back with a list of projects that folks have posted, including those of us at Microsoft. The source code and documentation are there, so you can learn using actual examples of code that will do what you need. There’s everything from a simple table query to a full project that is sort of a “Corporate Dropbox” that uses Windows Azure Storage.

The advantage is that this code is immediately usable. It’s searchable, and you can often find a complete solution to meet your needs. The disadvantage is that the code is pretty specific – it may not cover a huge project like you’re looking for. Also, depending on the author(s), you might not find the documentation level you want.

Link: http://azureexamples.codeplex.com/site/search?query=Azure&ac=8

Tailspin

Microsoft Patterns and Practices is a group here that does an amazing job at sharing standard ways of doing IT – from operations to coding. If you’re not familiar with this resource, make sure you read up on it. Long before I joined Microsoft I used their work in my daily job – saved a ton of time. It has resources not only for Windows Azure but other Microsoft software as well.

The Patterns and Practices group also publishes full books – you can buy these, but many are also online for free. There’s an end-to-end example for Windows Azure using a company called “Tailspin”, and the work covers not only the code but the design of the full solution. If you really want to understand the thought that goes into a Platform-as-a-Service solution, this is an excellent resource.

The advantages are that this is a book, it’s complete, and it includes a discussion of design decisions. The disadvantage is that it’s a little over a year old – and in “Cloud” years that’s a lot. So many things have changed, improved, and have been added that you need to treat this as a resource, but not the only one. Still, highly recommended.

Link: http://msdn.microsoft.com/en-us/library/ff728592.aspx

Azure Stock Trader

Sometimes you need a mix of a CodePlex-style application, and a little more detail on how it was put together. And it would be great if you could actually play with the completed application, to see how it really functions on the actual platform.

That’s the Azure Stock Trader application. There’s a place where you can read about the application, and then it’s been published to Windows Azure – the production platform – and you can use it, explore, and see how it performs.

I use this application all the time to demonstrate Windows Azure, or a particular part of Windows Azure.

The advantage is that this is an end-to-end application, and online as well. The disadvantage is that it takes a bit of self-learning to work through.

Links: Learn it: http://msdn.microsoft.com/en-us/netframework/bb499684 Use it: https://azurestocktrader.cloudapp.net/

Wordpress Installation (on IIS and SQL Server)

Davide Mauri — Sun, 27 May 2012 14:14:59 GMT

To proceed with the installation of Wordpress on SQL Server and IIS, first of all, you need to do the following steps

Create a database on SQL Server that will be used by Wordpress
Create login that can access to the just created database and put the user into ddladmin, db_datareader, db_datawriter roles
Download and unpack Wordpress 3.3.2 (latest version as of 27 May 2012) zip file into a directory of your choice
Download the wp-db-abstraction 1.1.4 (latest version as of 27 May 2012) plugin from wordpress.org website

Now that the basic action has been done, you can start to setup and configure your Wordpress installation.

Unpack and follow the instructions in the README.TXT file to install the Database Abstraction Layer. Mainly you have to:

Upload wp-db-abstraction.php and the wp-db-abstraction directory to wp-content/mu-plugins. This should be parallel to your regular plugins directory. If the mu-plugins directory does not exist, you must create it.
Put the db.php file from inside the wp-db-abstraction.php directory to wp-content/db.php

Now you can create an application pool in IIS like the following one

Create a website, using the above Application Pool, that points to the folder where you unpacked Wordpress files.

Be sure to give the “Write” permission to the IIS account, as pointed out in this (old, but still quite valid) installation manual:

http://wordpress.visitmix.com/development/installing-wordpress-on-sql-server#iis

Now you’re ready to go. Point your browser to the configured website and the Wordpress installation screen will be there for you.

When you’re requested to enter information to connect to MySQL database, simply skip that page, leaving the default values. If you have installed the Database Abstraction Layer, another database installation screen will appear after the one used by MySQL, and here you can enter the configuration information needed to connect to SQL Server.

After having finished the installation steps, you should be able to access and navigate your wordpress site. A final touch, and it’s done: just add the needed rewrite rules

http://wordpress.visitmix.com/development/installing-wordpress-on-sql-server#urlrewrite

and that’s it!

Well. Not really. Unfortunately the current (as of 27 May 2012) version of the Database Abstraction Layer (1.1.4) has some bugs. Luckily they can be quickly fixed:

Backslash Fix
http://wordpress.org/support/topic/plugin-wp-db-abstraction-fix-problems-with-backslash-usage

Select Top 0 Fix
Make the change to the file “.\wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php” suggested by “debettap” http://sourceforge.net/tracker/?func=detail&aid=3485384&group_id=315685&atid=1328061

And now you have a 100% working Wordpress installation on SQL Server!

Since I also wanted to take advantage of SQL Server Full Text Search, I’ve created a very simple wordpress plugin to setup full-text search and to use it as website search engine:

http://wpfts.codeplex.com/

Enjoy!

Executing SSIS 2012 Packages Video is Available!

andyleonard — Sun, 27 May 2012 01:56:00 GMT

The video for the microTraining session on Executing SSIS 2012 Packages is now available here ! :{>...(read more)

T-SQL User-Defined Functions: the good, the bad, and the ugly (part 2)

Hugo Kornelis — Sat, 26 May 2012 14:56:00 GMT

In a previous blog post , I demonstrated just how much you can hurt your performance by encapsulating expressions and computations in a user-defined function (UDF). I focused on scalar functions that didn’t include any data access. In this post, I will complete the discussion on scalar UDFs by covering the effect of data access in a scalar UDF. Note that, like the previous post, this all applies to T-SQL user-defined functions only. SQL Server also supports CLR user-defined functions (written in...(read more)

List columns where collation doesn't match database collation

TiborKaraszi — Wed, 23 May 2012 16:58:00 GMT

Below script lists all database/table/column where the column collation doesn't match the database collation. I just wrote it for a migration project and thought I'd share it. I'm sure lots of tings can be improved, but below worked just fine for me for a one-time execution on a number of servers. IF OBJECT_ID ( 'tempdb..#res' ) IS NOT NULL DROP TABLE #res GO DECLARE @db sysname , @sql nvarchar ( 2000 ) CREATE TABLE #res ( server_name sysname , db_name sysname , db_collation sysname , table_name...(read more)

Windows Azure Myths

BuckWoody — Tue, 22 May 2012 13:56:00 GMT

Windows Azure is part of the Microsoft "stack" - the suite of software and services we offer. Because we have so many products in almost every part of technology, it's hard to know everything about all parts of what we do - even for those of us who work here. So it's no surprise that some folks are not as familiar with Windows and SQL Azure as they are, say Windows Server or XBox.

As I chat with folks about a solution for a business or organization need, I put Windows Azure into the mix. I always start off with "What do you already know about Windows Azure?" so that I don't bore folks with information they already have. I some cases they've checked out the product ahead of time and have specific questions, in others they aren't as familiar, and in still others there is a fair amount of mis-information. Sometimes that's because of a marketing failure, sometimes it's hearsay, and somtetimes it's active misinformation.

I thought I might lay out a few of these misconceptions. As always - do your fact-checking! Never take anyone's word alone (including mine) as gospel. Make sure you educate yourself on your options. Your company or your clients depend on you to have the right information on IT, so make sure you live up to that.

Myth 1: Nobody uses Windows Azure

It's true that we don't give out numbers on the amount of clients on Windows and SQL Azure. But lots of folks are here - companies you may have heard of like Boeing, NASA, Fujitsu, The City of London, Nuedesic, and many others. I deal with firms small and large that use Windows Azure for mission-critical applications, sometimes totally on Windows and/or SQL Azure, sometimes in conjunction with an on-premises system, sometimes for only a specific component in Windows Azure like storage.

The interesting thing is that many sites you visit have a Windows Azure component, or are running on Windows Azure. They just don't announce it. Just like the other cloud providers, the companies have asked to be completely branded themselves - they don't want you to be aware or care that they are on Windows Azure. Sometimes that's for security, other times it's for different reasons. It's just like the web sites you visit. For the most part, they don't advertise which OS or Web Server they use. It really just shouldn't matter. The point is that they just use what works to solve a given problem.

Check out a few public case studies here: https://www.windowsazure.com/en-us/home/case-studies/

Myth 2: It's only for Microsoft stuff - can't use Open Source

This is the one I face the most, and am the most dismayed by. We work just fine with many open source products, including Java, NodeJS, PHP, Ruby, Python, Hadoop, and many other languages and applications. You can quickly deploy a Wordpress, Umbraco and other "kits". We have software development kits (SDK's) for iPhones, iPads, Android, Windows phones and more. We have an SDK to work with FaceBook and other social networks.

In short, we play well with others.

More on the languages and runtimes we support here: https://www.windowsazure.com/en-us/develop/overview/

More on the SDK's here: http://www.wadewegner.com/2011/05/windows-azure-toolkit-for-ios/, http://www.wadewegner.com/2011/08/windows-azure-toolkits-for-devices-now-with-android/, http://azuretoolkit.codeplex.com/

Myth 3: Microsoft expects me to switch everything to "the cloud"

No, we don't. That would be disasterous, unless the only things you run in your company uses works perfectly in Azure. Use Windows Azure - or any cloud for that matter - where it works.

Whenever I talk to companies, I focus on two things:

Something that is broken and needs to be re-architected
Something you want to do that is new

If something is broken, and you need new tools to scale, extend, add capacity dynamically and so on, then you can consider using Windows or SQL Azure. It can help solve problems that you have, or it may include a component you don't want to write or architect yourself.

Sometimes you want to do something new, like extend your company's offerings to mobile phones, to the web, or to a social network.

More info on where it works here: http://blogs.msdn.com/b/buckwoody/archive/2011/01/18/windows-azure-and-sql-azure-use-cases.aspx

Myth 4: I have to write code to use Windows and SQL Azure

If Windows Azure is a PaaS - a Platform as a Service - then don't you have to write code to use it?

Nope.

Windows and SQL Azure are made up of various components. Some of those components allow you to write and deploy code (like Compute) and others don't. We have lots of customers using Windows Azure storage as a backup, to securely share files instead of using DropBox, to distribute videos or code or firmware, and more. Others use our High Performance Computing (HPC) offering to rent a supercomputer when they need one. You can even throw workloads at that using Excel!

In addition there are lots of other components in Windows Azure you can use, from the Windows Azure Media Services to others. More here: https://www.windowsazure.com/en-us/home/scenarios/saas/

Myth 5: Windows Azure is just another form of "vendor lock-in"

Windows Azure uses .NET, OSS languages and standard interfaces for the code. Sure, you're not going to take the code line-for-line and run it on a mainframe, but it's standard code that you write, and can port to something else.

And the data is yours - you can bring it back whever you want. It's either in text or binary form, that you have complete control over.

There are no licenses - you can "pay as you go", and when you're done, you can leave the service and take all your code, data and IP with you.

So go out there, read up, try it. Use it where it works. And don't believe everything you hear - sometimes the Internet doesn't get it all correct. :)

SSIS Reporting Pack v0.4 – Execution Report updated

jamiet — Tue, 22 May 2012 08:22:38 GMT

SSIS Reporting Pack is a suite of reports that I maintain at http://ssisreportingpack.codeplex.com/ that provide visualisation over the SSIS Catalog in SQL Server 2012 and attempt to add value over the reports that ship in the box. Work on the reports has stalled (my last SSIS Reporting Pack blog post was on 4th September 2011) as I’ve had rather more important things going on my life of late however I have recently checked-in a fix that couldn’t really be delayed.

I discovered a problem with the Execution report that was causing the report to effectively hang, it was caused by this bit of SQL hidden away in the report definition:

[generated_executables] AS (
   SELECT  [new_executable].[execution_path],[new_executable].[parent_execution_path]
   FROM    (
           SELECT  [execution_path] = SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_close_square] + 1)
           ,       [parent_execution_path] = SUBSTRING([loop_iteration].[execution_path] ,1, [loop_iteration].length_exec_path - [loop_iteration].[char_index_open_square])
           FROM    (
                   SELECT  [execution_path]
                   ,       [char_index_open_square] = CHARINDEX('[',REVERSE([execution_path]),1)
                   ,       [char_index_close_square] = CHARINDEX(']',REVERSE([execution_path]),1)
                   ,       [length_exec_path] = LEN([execution_path])
                   FROM    [exec_stats] es
                   WHERE   execution_path LIKE '%\[%]%'  ESCAPE '\'
                   )AS [loop_iteration]
           ) AS [new_executable]
   GROUP   BY [new_executable].[execution_path],[new_executable].[parent_execution_path]
)

It was there because SSIS does not currently treat a loop iteration as an executable yet I figured there was still value in being able to view it as such – this SQL essentially “invents” new executables for those loop iterations; its what enabled the following visualisation:

where each of the three iterations of a For Each Loop called “FEL Loop over top performing regions” appear in the report. Unfortunately, as I alluded, this could under certain circumstances (most likely when there were many loop iterations) cause the report to hang as it waited for the results to be constructed and returned.

The change that I have made eradicates this generation of “fake” executables and thus produces this visualisation instead:

Notice that the three “children” of the For Each Loop are no longer the three iterations but actually the task (“EPT Call Data Export Package”) contained within that For Each Loop. The problem here is of course that there is no longer a visual distinction between those three iterations; I have instead made the full execution path viewable via a tooltip:

If you preferred the “old” way of presenting this information and are happy to put up with the performance degradation then I have kept the old version of the report hanging around in the reporting pack as “execution loop with iterations”

however none of the other reports link to it so you will have to browse to it manually if you want to use it. Please let me know if you ARE using it – I would be very interested to hear about your experiences.

The last change to make you aware of in the execution report is that by default I no longer show OnPreValidate or OnPostValidate messages as I consider them to be superfluous and only serve to clutter up the results.

If you want to put them back, well, its open source so go right ahead!

The latest release of SSIS Reporting Pack that contains all of these changes is v0.4 and can be downloaded from http://ssisreportingpack.codeplex.com/releases/view/88178

Feedback on all of the above changes would be very much appreciated.

@Jamiet

Cumulative Updates available for SQL Server 2008 SP2 & SP3

AaronBertrand — Mon, 21 May 2012 19:35:00 GMT

Today Microsoft has released two new cumulative updates for SQL Server 2008. Cumulative Update #10 for SQL Server 2008 Service Pack 2 Knowledge Base Article: KB #2696625 At the time of writing, there is one fix listed The build number is 10.00.4332 Cumulative Update #5 for SQL Server 2008 Service Pack 3 Knowledge Base Article: KB #2696626 At the time of writing, there are four fixes listed The build number is 10.00.5785 As usual, I'll post my standard disclaimer here: these updates are NOT for SQL...(read more)

Extracting GPS Data from JPG files

Peter W. DeBetta — Mon, 21 May 2012 19:14:27 GMT

I have been very remiss in posting lately. Unfortunately, much of what I do now involves client work that I cannot post. Fortunately, someone asked me how he could get a formatted list (e.g. tab-delimited) of files with GPS data from those files. He also added the constraint that this could not be a new piece of software (company security) and had to be scriptable. I did some searching around, and found some techniques for extracting GPS data, but was unable to find a complete solution. So, I did...(read more)

T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)

Hugo Kornelis — Sun, 20 May 2012 14:08:00 GMT

So you thought that encapsulating code in user-defined functions for easy reuse is a good idea? Think again! SQL Server supports three types of user-defined functions. Only one of them qualifies as good. The other two – well, the title says it all, doesn’t it? The bad: scalar functions A scalar user-defined function (UDF) is very much like a stored procedure, except that it always returns a single value of a predefined data type – and because of that property, it isn’t invoked with an EXECUTE statement,...(read more)

SSAS DMVs: useful links

Davide Mauri — Fri, 18 May 2012 14:47:42 GMT

From time to time happens that I need to extract metadata informations from Analysis Services DMVS in order to quickly get an overview of the entire situation and/or drill down to detail level. As a memo I post the link I use most when need to get documentation on SSAS Objects Data DMVs:

SSAS: Using DMV Queries to get Cube Metadata
http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

SSAS DMV (Dynamic Management View)
http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

Use Dynamic Management Views (DMVs) to Monitor Analysis Services
http://msdn.microsoft.com/en-us/library/hh230820.aspx

Azure Trust Services

KKline — Fri, 18 May 2012 14:35:00 GMT

Microsoft recently launched Trust Services for Windows Azure, an application-level encryption framework that can be used to protect sensitive data stored on the Windows Azure Platform. Read more......(read more)

'Backup Completed' is NOT an Error Message

KKline — Thu, 17 May 2012 13:40:00 GMT

I recommend that you set your SQL Server to use the startup traceflag DBCC TRACEON (3226) to disable backup success message......(read more)

Flexibility When Waiting on Locks

KKline — Thu, 17 May 2012 13:29:00 GMT

An attendee at a recent performance tuning session wanted ideas to more flexibly react to locks on blocks than using the WITH (NOLOCK) hint....(read more)

MicroTraining: Executing SSIS 2012 Packages 22 May 10:00 AM EDT (Free!)

andyleonard — Wed, 16 May 2012 12:00:00 GMT

I am pleased to announce the latest (free!) Linchpin People microtraining event will be held Tuesday 22 May 2012 at 10:00 AM EDT. The topic will be Executing SSIS 2012 Packages. In this presentation, I will be demonstrating several ways to execute SSIS 2012 packages. Register here ! Interested in learning about more microtraining from Linchpin People – before anyone else? Sign up for our newsletter ! :{>...(read more)

Book Review (Book 11) - Applied Architecture Patterns on the Microsoft Platform

BuckWoody — Tue, 15 May 2012 16:50:34 GMT

This is a continuation of the books I challenged myself to read to help my career - one a month, for year. You can read my first book review here, and the entire list is here. The book I chose for April 2012 was: Applied Architecture Patterns on the Microsoft Platform. I was traveling at the end of last month so I’m a bit late posting this review here.

Why I chose this book:

I actually know a few of the authors on this book, so when they told me about it I wanted to check it out. The premise of the book is exactly as it states in the title - to learn how to solve a problem using products from Microsoft.

What I learned:

I liked the book - a lot. They've arranged the content in a "Solution Decision Framework", that presents a few elements to help you identify a need and then propose alternate solutions to solve them, and then the rationale for the choice. But the payoff is that the authors then walk through the solution they implement and what they ran into doing it.

I really liked this approach. It's not a huge book, but one I've referred to again since I've read it. It's fairly comprehensive, and includes server-oriented products, not things like Microsoft Office or other client-side tools. In fact, I would LOVE to have a work like this for Open Source and other vendors as well - would make for a great library for a Systems Architect. This one is unashamedly aimed at the Microsoft products, and even if I didn't work here, I'd be fine with that. As I said, it would be interesting to see some books on other platforms like this, but I haven't run across something that presents other systems in quite this way.

And that brings up an interesting point - This book is aimed at folks who create solutions within an organization. It's not aimed at Administrators, DBA's, Developers or the like, although I think all of those audiences could benefit from reading it. The solutions are made up, and not to a huge level of depth - nor should they be. It's a great exercise in thinking these kinds of things through in a structured way.

The information is a bit dated, especially for Windows and SQL Azure. While the general concepts hold, the cloud platform from Microsoft is evolving so quickly that any printed book finds it hard to keep up with the improvements.

I do have one quibble with the text - the chapters are a bit uneven. This is always a danger with multiple authors, but it shows up in a couple of chapters. I winced at one of the chapters that tried to take a more conversational, humorous style. This kind of academic work doesn't lend itself to that style.

I recommend you get the book - and use it. I hope they keep it updated - I'll be a frequent customer. :)

Messages do not always appear in [catalog].[event_messages] in the order that they occur [SSIS]

jamiet — Tue, 15 May 2012 15:07:00 GMT

This is a simple heads up for anyone doing SQL Server Integration Services (SSIS) development using SSIS 2012. Be aware that messages do not always appear in [catalog].[event_messages] in the order that they occur, observe…

In the following query I am looking at a subset of messages in [catalog].[event_messages] and ordering them by [event_message_id]:

SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM   [catalog].[event_messages] em
WHERE  [event_message_id] BETWEEN 290972 AND 290982
ORDER  BY [event_message_id] ASC
--ORDER	BY [message_time] ASC

Take a look at the two rows that I have highlighted, note how the OnPostExecute event for “Utility GetTargetLoadDatesPerETLIfcName” appears after the OnPreExecute event for “FELC Loop over TargetLoadDates”, I happen to know that this is incorrect because “Utility GetTargetLoadDatesPerETLIfcName” is a package that gets executed by an Execute Package Task prior to the For Each Loop “FELC Loop over TargetLoadDates”:

If we order instead by [message_time] then we see something that makes more sense:

SELECT [event_message_id],[event_name],[message_time],[message_source_name]
FROM   [catalog].[event_messages] em
WHERE  [event_message_id] BETWEEN 290972 AND 290982
--ORDER	BY [event_message_id] ASC
ORDER  BY [message_time] ASC

We can see that the OnPostExecute for “Utility GetTargetLoadDatesPerETLIfcName” did indeed occur before the OnPreExecute event for “FELC Loop over TargetLoadDates”, they just did not get assigned an [event_message_id] in chronological order. We can speculate as to why that might be (I suspect the explanation is something to do with the two executables appearing in different packages) but the reason is not the important thing here, just be aware that you should be ordering by [message_time] rather than [event_message_id] if you want to get 100% accurate insights into your executions.

@Jamiet

How SQL Saturday could be better

AaronBertrand — Tue, 15 May 2012 12:58:00 GMT

I've been to a lot of SQL Saturdays. They are great events to attend - from a community standpoint, from a learning standpoint, and from a speaker growth standpoint. Who could ask for more, right? Great sessions, from passionate speakers willing to both teach and learn, fantastic networking opportunities and lunch. All for free, or at a very low cost - some events need to recover costs and charge $10 for lunch. Still a phenomenal bargain IMHO. But we all know that these events aren't perfect... there...(read more)