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

SQL Server Data Tools does support required variables

jamiet — Sun, 12 Feb 2012 11:58:24 GMT

Over the past few years of using datadude (aka DBPro aka Visual Studio Database Projects) I have fallen prey to a peculiar little nuance – if you forget to supply a value for a sqlcmd variable then it will simply use a default and often that is not the desired behaviour. Hence why yesterday I submitted the following suggestion to http://connect.microsoft.com/sqlserver/feedback :

Specify sqlcmdvars properties as "required to be overridden”

In my current place of work I am responsible for maintaining our datadude projects and we have another team that is in charge of deployments. Hence, when we place new properties into the sqlcmdvars file I need to tell the deployment team what values to supply for that property per environment (dev, systest, uat, prod).
Unfortunately lack of communication/human error occasionally creeps in and, for whatever reason, no value gets supplied for some property at deployment time. If this is the case the default value as specified in the sqlcmdvars file gets used instead - invariably this will be the wrong value. I would like a mechanism within SSDT of preventing this from ever happening.

One simple way to prevent this would be to specify that a sqlcmdvars property is *required* to be overridden during the deployment. In other words, never use the default. if an override is not supplied at deployment time then the deployment should fail.
Note that this stipulation should only be in place when deployment occurs from the command-line - if deploying from Visual Studio the default should be allowed (simply because Visual Studio doesn't provide a way to specify anything other than the default value supplied in the sqlcmdvars file).

https://connect.microsoft.com/sqlserver/feedback/details/724366/ssdt-specify-sqlcmdvars-properties-as-required-to-be-overridden#details

It transpires that this requested feature is already available in the forthcoming SQL Server Data Tools (SSDT) as I shall now demonstrate. This screenshot shows the project properties of a SSDT project where we define the sqlcmd variables, I have defined a variable called $(Id_value):

In SSDT the nomenclature for deploying a project is “Publish”, a function that can be found by right-clicking on a project in Solution Explorer:

Selecting that brings up the Publish dialog.

Notice how the “Publish” button is greyed out – that it because I have not supplied a value for $(Id_value); supplying a value enables the Publish button and I can go ahead and publish my project. In other words, SSDT insists that I supply a value for that variable – exactly as I requested in my Connect submission.

The same is true if I use the command-line publishing tool sqlpackage.exe. The following command does essentially the same thing as the above depicted Publish dialog:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac"

Executing that gives me an error:

Publishing to database 'MyDB' on server '.\RC0'.
*** Missing values for the following SqlCmd variables:
'Id_value'.

Here’s a screenshot showing the same:

In order to supply a value for the variable from the command-line you need to use the /v: switch like so:

>sqlpackage.exe /TargetDatabaseName:MyDB /TargetServerName:".\RC0" /Action:Publish /SourceFile:"TestRequiredSqlCmdVars.dacpac" /v:Id_value=1

As you can see, publish was successful!

So there you go, using SSDT you’ll no longer be able to fall prey to the problem I highlighted at the top of this blog post. In a later blog post I’ll show how you CAN supply a default value if you want to and also how you can override for your local environment.

@jamiet

Clustering every SQL Server instance

Linchi Shea — Sat, 11 Feb 2012 03:32:00 GMT

You may disagree, but I believe it is a good practice to cluster all the SQL Server instances. That is, even when you plan to run a SQL Server instance on a single machine, you should install it in a single node cluster. The primary advantage is that you only need a single standard SQL Server build instead of one for the stand alone and one for the clustered. This results in simplified configurations such as when you configure network aliases, Kerberos, and multiple instances. If you need to add...(read more)

3 Big Changes in Analysis Services 2012 Enabling Flexible Design

smisner — Sat, 11 Feb 2012 01:32:00 GMT

The upcoming release of SQL Server 2012 has a lot of new features for business intelligence developers to love. The free preview of Introducing Microsoft SQL Server 2012 (Microsoft Press, 2012) does not include the chapter on Analysis Services, but you’ll be able to read the details when the final version of the ebook is released for download in March.

Overall, there are a lot of changes in Analysis Services 2012, and it’s easy to get overwhelmed by the details. So, just as I did for Integration Services 2012 last month, I thought about the key aspects of this release that I would single out as important:

Business Intelligence Semantic Model
Tabular mode
Installation experience of PowerPivot for SharePoint

Business Intelligence Semantic Model (BISM)

Back in Analysis Services 2005, the Unified Dimensional Model (UDM) made its debut. The UDM was supposed to blur the lines between relational and multidimensional modeling. How well it accomplished that goal could be argued, but I’m not going to take sides on that issue because now it no longer matters. Why? Because BISM replaces UDM and very definitely addresses two styles of modeling: multidimensional and tabular (about which I explain more below). To get the background, see Microsoft's vision and roadmap statement describing the benefits at a high level.

Just as many people didn’t really understand that their development in Analysis Services (versions 2005, 2008, or 2008 R2) produced a UDM, they also don’t need to understand that their development in Analysis Services 2012 produces a BISM. Cubes from prior version will upgrade automatically when migrated to Analysis Services 2012. It still stays multidimensional, and there is no magic button to convert it to tabular.

Lack of a magic button is not that big of a deal. I can’t think of a good reason to convert from multidimensional to tabular in most cases. However, I have heard through the grapevine of some scenarios where queries performed significantly faster against a tabular model as compared to a comparable design in a multidimensional model. It’s probably too early to say whether these performance improvements resulted from an anomaly or a genuine benefit of the VertiPaq engine that the tabular model uses. For now, I would say the only way to know is to test it yourself with your own data. To do this, you will have to build the tabular model from scratch (unless some clever developer comes up with a tool to do the conversion some day).

Tabular Mode

When you install Analysis Sevices, you must choose from one of three server modes: Multidimensional mode (the one we’ve had since Analysis Services 2005), Tabular mode, and PowerPivot for SharePoint mode. They differ in the way they store data, how they use memory, and the engine they use to retrieve data for queries. Tabular mode uses the same VertiPaq Engine as PowerPivot for SharePoint mode, but as a separate instance and without the same dependency on SharePoint.

You can only store tabular models on a server running a tabular mode instance. If you have ever built a PowerPivot model, you will find that building tabular models is strikingly similar. The difference is that you use SQL Server Data Tools (SSDT), the SQL Server 2012 replacement for Business Intelligence Development Studio that runs as a Visual Studio 2010 shell.

With tabular models, you can go beyond using relational sources to populate the model, using any of the sources that PowerPivot supports: relational databases, Analysis Services cubes (any mode), Reporting Services reports (as a data feed), Azure DataMarket datasets, ATOM data feeds, Excel files, or text files. This flexibility can significantly speed up development time, and enables you to rationalize development of one-off or limited life-span tabular models, which might not happen if you were limited to multidimensional mode.

Some shortcomings of the tabular model that originated in PowerPivot have been overcome in this release. The following design features are now available in both PowerPivot and tabular models:

Hierarchical structures, including parent-child hierarchies
Perspectives
Key performance indicators
Diagram interface for working with tables and relationships

(UPDATE: Removed aggregations from the list. Strikethrough doesn't seem to work for me here!)

One advantage that tabular models have over PowerPivot models is the ability to partition the data. You can then manage reprocessing of individual partitions manually within SSDT or using scripts that you execute on the tabular mode server.

Another advantage with tabular models is the ability to use role-based security to control what people can see and do. At minimum, you set one role with permissions to allow read access and another role to administer the database. You can optionally create a role to allow users to both query and process the database, or restrict users only to processing. Another aspect of security that you might implement is row-level filtering. For example, you might set up a role that can view only Bikes as a category, but you can also completely block a role from viewing any rows from a particular table.

A third distinctive feature of tabular mode is its DirectQuery mode. You use this mode when you need to retrieve current data (rather than use the cache that tabular mode creates by default) from the source or when you want to query data volumes that are too large to hold in memory. You can use DirectQuery only when using SQL Server 2005 (or later) data. There are some additional limitations with formulas, security, and client tool support that you should understand before choosing this option.

Use the following links to download tabular samples and follow a tutorial:

Use these links to follow blogs that discuss BISM and tabular topics:

Chris Webb (@technitrain)

Marco Russo (@marcorus)

PowerPivot for SharePoint Installation Experience

Installation and configuration of SQL Server 2008 R2 PowerPivot for SharePoint and SharePoint Server 2010 can be a bit challenging. Curiously, my post on performing these steps on a Windows 7 machine is one of the most popular posts on my blog. It was challenging due to the dependencies on the SharePoint farm, but the new release provides a configuration wizard that greatly simplifies and automates the process. You are not required to use it, but it’s a terrific option if you’re not well-versed in SharePoint administration. If you prefer to use PowerShell, there are SharePoint and PowerPivot PowerShell cmdlets that you can use instead.

Virtual Event Coming Soon!

And if possible, make some time to learn more at the SQL Server 2012 Virtual Launch on March 7th.

What features are you really looking forward to trying out?

SQL Saturday 108 Redmond PowerShell Session Material

merrillaldrich — Fri, 10 Feb 2012 21:43:24 GMT

I am honored to be presenting my first SQL Saturday session in Redmond on the 25th of this month. The session will be a PowerShell basics class, emphasis on helping people who might be starting out with PowerShell, or feel intimidated by PowerShell’s syntax or object-orientation. I have demos and material focused on PowerShell syntax in general, for any task, and then a few demos with a SQL Server slant. The main idea is to get an understanding of how and why PowerShell syntax works, which should...(read more)

Rick Review: Day 3 Keynote PASS Summit 2011 - Big Data-What's the Big Deal?

RickHeiges — Fri, 10 Feb 2012 13:40:00 GMT

I recently did a Rick Review of the Day 3 Keynote from the 2008 PASS Community Summit that featured Dr. DeWitt speaking on new technology that ended up in the Parallel Data Warehouse (PDW) edition of SQL Server 2008 R2. I decided to also listen again to the Day 3 Keynote from the 2001 PASS Community Summit. While I listened intently to the keynote on Day 3 of this past summit, something seemed to be somewhat familiar. Had I heard some of these concepts before? The keynotes from the most recent PASS...(read more)

Rick Review: Day 3 Keynote of the 2008 PASS Community Summit

RickHeiges — Thu, 09 Feb 2012 19:04:00 GMT

Am I stuck in the past? Not really, but I like to blog about the recordings that I have recently watched. There are a lot of recordings out there on the PASS website and other places. Recently I have had the good fortune of exploring some of the neat technology behind the Parallel Data Warehouse (PDW) in SQL Server 2008 R2. In order to get myself grounded in some of the basics around what PDW was all about, I decided to review the Dr. DeWitt keynote (Day 3) from the 2008 PASS Community Summit. The...(read more)

Speaking at Lisbon NetPonto UG on 02/13/2012

AlbertoFerrari — Thu, 09 Feb 2012 15:48:12 GMT

As you might (or not) know, I travel a lot for work and, when possible, I always try to speak at local user groups. It is fun and I always learn a lot by speaking with other professionals like me. Being in Lisbon next week, I’ll deliver a speech at Microsoft Portugal with the NetPonto user group for a couple of hours of DAX, Vertipaq and many-to-many relationships. Here’s the link of the event, a free registration is required: http://netponto-lisboa-fevereiro-2012-powerpivot.eventbrite.com/...(read more)

PBM for SSIS? What policies would you want?

jamiet — Thu, 09 Feb 2012 13:50:00 GMT

My mind was wandering today after reading Andy Leonard's excellent post Name Those Connections and it occurred to me that due to SQL Server 2012 including an SSIS Server there may be an opportunity to leverage Policy-Based Management (PBM) for ensuring adherance to an organisation's best practises around SSIS development. I have absolutely no idea whether such plans are afoot or not (I suspect not) but nonetheless it may be fun to come up with a desired list of policies. Here are some brainstormed ideas:

All task and package names have to adhere to some naming convention (here are some suggestions)
All executions need to use an environment reference
All Connection Managers must be project-scoped
All connection strings must use an Application Name that is not a GUID (this is where this train of thought started after reading Andy's post)
Insist on a given LOGGING_LEVEL

Any more ideas? Pop 'em in the comments below!

@Jamiet

SSIS Snack: Name Those Connections!

andyleonard — Thu, 09 Feb 2012 12:00:00 GMT

When creating Connection Managers in SSIS, take a moment to click the “All” page and set a value in the Application Name property. In SSIS 2008+, a default value is applied to this property. It’s useful but it contains a GUID. Yuck. I prefer to enter a more concise identifier like the one shown here: “Why should I enter this information, Andy?” I’m glad you asked! When troubleshooting performance or locking / blocking issues, DBAs start with a query to ascertain what is currently executing...(read more)

Avoiding nested transactions might not improve performance.

Alexander Kuznetsov — Wed, 08 Feb 2012 18:09:00 GMT

Beginning a transaction only when @@TRANCOUNT=0 might not improve performance at all. At least, I did not notice any difference whatsoever. No matter if I use this pattern: BEGIN TRAN ; -- (snip) COMMIT ; or a more complex one: DECLARE @trancount INT ; SET @trancount = @@TRANCOUNT ; IF @trancount = 0 BEGIN ; BEGIN TRAN ; END ; --(snip) IF @trancount = 0 BEGIN ; COMMIT ; END ; the performance stays the same: I was not able to notice any difference. Here are my benchmarks. Prerequisites All we need...(read more)

[Shameless Plug] Can We Have Your Vote?

Argenis — Wed, 08 Feb 2012 17:34:00 GMT

Robert Davis (a.k.a. @SQLSoldier) and yours truly have submitted a Pre-Conference for SQL Rally 2012 in Dallas, TX called “Demystifying Database Administration Best Practices”. We think it’s going to be an epic session with just loads and loads of tips and best practices on multiple aspects of Database Administration, from Windows/AD setup to Daily Operations to Code Reviews.

While our session wasn’t chosen by the committee in the first round, it made it to the community voting round – so this post is to kindly ask for your vote. We promise we’ll deliver great content! Robert and I have combined over 25+ years of experience. Robert is an MCM, and I’m halfway there (I actually will take a shot at the MCM lab tomorrow!)

Please vote here:

http://www.sqlpass.org/sqlrally/2012/dallas/UserLogin.aspx?returnurl=/sqlrally/2012/dallas/CommunityChoice.aspx

If you haven’t become a member of PASS yet (You haven’t? What are you waiting for?) It’s free, go sign up now here:

http://www.sqlpass.org/RegisterforSQLPASS.aspx

And even if you do not feel that our session would be the best choice, please vote and make your voice heard. Do your part in keeping our community strong.

-Argenis

SSIS Package Configurations MicroTraining Recording Available!

andyleonard — Wed, 08 Feb 2012 12:00:00 GMT

Although there are some audio issues, the recording and code for the Linchpin People microTraining session on SSIS Package Configurations is now live! :{>...(read more)

SQLBits now publishing all SQLBits agendas as an iCalendar

jamiet — Wed, 08 Feb 2012 08:36:00 GMT

Three weeks ago I published a blog post Get the SQLBits agenda in your phone's calendar where I said:

If you want to get the SQLBits calendar onto your smartphone then the easiest way to do it is add my calendar [containing all SQLBits sessions] to whichever calendar service (i.e. Hotmail or Google) you have got synced to your phone and let technology do its thing.
I will keep the calendar updated with any changes to the agenda so, assuming you have subscribed, changes will just propogate to you without you having to do anything.

This is not the first time I have published a subscribable calendar (I did it for the 2010 World Cup for example, I also curate a calendar for my home town) nor the first time I have opined about them. The reason I bang on about subscribable calendars (aka iCalendars) all the time is that I truly believe that they are a transformative technology. In my humble opinion the world would be a better place if it ran on iCalendar and I'm not the only one who thinks so, Scott Adams (yes, THAT Scott Adams) says the same (but in a much more coherent way) in his blog post Calendar as Filter.

My super-smart former colleague Howard van Rooijen is fond of saying work smarter, not harder and, to me, subscribable calendars are the epitome of that mantra. Why should many people do the same work of downloading .ics files and importing them to their own calendar service when the content owner can simply make that information available to anyone? That is my motivation for publishing these subscribable calendars - I want to motivate the content owners to publish this information for themselves.

With that in mind I am delighted to tell you that the SQLBits organising committee have taken this on board and published the SQLBits agenda as an iCalendar. One benefit of that is I don't have to go through the rigmarole of keeping my own calendar up to date but more importantly any changes to the SQLBits agenda (e.g. a room change) will automatically flow to your own calendar service and if you have that calendar service (e.g. Hotmail Calendar, Google Calendar) synced to your phone then the changes will automatically show up there too. Very cool!

That new SQLBits subscribable calendar lives at http://sqlbits.com/information/SQLBitsCalendar.ashx; note how it is not specific to a particular conference - subscribe to (don't import) that calendar and the agenda for future SQLBits conferences will automatically flow to you too. Want to subscribe to that calendar yourself? Click one of the following links:

If you use Hotmail, click here
If you use GMail/Google Calendar, click here

N.B. I am assuming that Hotmail & Google Calendar covers the majority of folks that are going to be reading this. If you use a different service (e.g. Yahoo) then perhaps you could find out what the appropriate link should be and it as a comment below.

You may also want to unsubscribe from my calendar because I am no longer going to keep it updated.

My thanks go to the SQLBits committee for doing this; more accurately the thanks should go to Simon Sabin because it was he that made this happen.

@jamiet

P.S. If you agree with me that iCalendar is a transformative technology and would like to get involved then take a look at Jon Udell's Elmcity project (FAQ) to curate your own calendar for your home town.

UPDATE: Some folks are saying that the link to Google Calendar produces the message: "You do not have access to <the calendar>". The exact same problem was reported with the calendar that I produced three weeks ago so I am assuming that the problem is at Google's end. The workaround is to subscribe to URL http://sqlbits.com/information/SQLBitsCalendar.ashx in Google Calendar by clicking Other calendars->Add by URL.

Application Lifecycle Management Overview for Windows Azure

BuckWoody — Tue, 07 Feb 2012 14:58:39 GMT

Developing in Windows Azure is at once not that much different from what you’re familiar with in on-premises systems, and different in significant ways. Because of these differences, developers often ask about the specific process to develop and deploy a Windows Azure application - more formally called an Application Lifecycle Management, or ALM.

There are specific resources you can use to learn more about various parts of ALM - I’ve referenced those at the end of this post. But ALM has multiple definitions, from the governance of code injection, domain upgrade, testing, process flow and more. Many developers are interested in the finer-grained information, like how do I develop and deploy an application? What tools do I need, and how do I get the code running somewhere that I can test?

I’ll cover the very high-level process here, and refer you to specifics at the end of each section, so that you can take it all in at one viewing, and then bookmark for more detail when you need more information. I won’t be covering processes like Continuous Integration or Agile and other methodologies in this post - I’ll blog those later.

Initial Development

You start with writing code. You have three ways to do this. You can use Visual Studio (even the Express Edition Works), Eclipse, or by leveraging the REST API format. You can do this in a standalone (non-connected) environment like your laptop.

Using Visual Studio is one of the simplest methods to create an Azure application, allowing you to combine the Azure components you want to leverage (Storage, Compute, SQL Azure, the Service Bus, etc.) along with the on-premises code you have now or are creating. Once you’ve installed and patched Visual Studio, just download and install the Windows Azure Software Development Kit (SDK) and you’ll have not only all the API’s you need to talk to Azure, but a fully functioning local environment to run and test your code before you deploy it. You’ll also get a robust set of samples. You can download what you need for all of that (free) here: http://www.windowsazure.com/en-us/develop/downloads/ . There’s a step-by-step process here: http://msdn.microsoft.com/en-us/magazine/ee336122.aspx

You can also use Eclipse to develop for Windows Azure. You won’t get the full runtime environment in just that kit alone, but you can use this successfully on a Linux system. I have several folks using this method. The downloads and documentation for that is here: http://www.windowsazure4e.org/

You can use REST API’s to hit Azure Assets and control them. Not my preferred method, but possible. There are REST API’s for various sections of Azure. You can find the main reference for that here: http://msdn.microsoft.com/en-us/library/windowsazure/ff800682.aspx

Note: We recently demonstrated using a Cloud-based Integrated Development Environment (IDE) for Node.js deployment to Windows Azure. More on that here: http://www.readwriteweb.com/cloud/2012/01/cloud9-ide-to-enable-nodejs-ap.php

Deploying to a Test Instance

After you write the code, you’ll need to test it somewhere. The Azure Emulator on your development laptop is for a single user on that laptop, and it also has some subtle differences from the production fabric as you might imagine. Normally you’ll set up a small subscription to run and test the application, just like you would have a set of test servers. Each subscription has its own management keys and certificates, so this assists in keeping the testing environment separate for billing and control.

More on that general information here: http://msdn.microsoft.com/en-us/library/ff803362.aspx

Deploying to Production

Once you have developed the code and tested it, you need to move it to a location where users can access it. In reality, there is no physical difference in the type of machines, fabric or any other component in “Production” Windows Azure accounts and the “Test” accounts, but you’ll most often pick smaller systems to deploy on in testing, and you’ll probably keep the URL in the plain format.

In the Production Windows Azure account, the team normally limits the access to the account for deployment to a separate set of developers. This ensures code flow and control. A DNS name is normally mapped to the longer, Microsoft-generated URL so that your users access the application or data the way you want them to.

More on setting up an account here: http://techinch.com/2010/06/14/setup-your-windows-azure-account/

Managing Code Change

With the application deployed, there are two broad tasks you need to consider. One is managing changes through the application, and the other involves management, monitoring and performance tuning for an application.

To make a code change, the standard ALM process is followed, just as above. You can use command-line tools to automate the process as you would with an on-premises system. A vide on that shows you how: http://www.microsoftpdc.com/2009/SVC25. Normally this is used with an “In-Place” upgrade into Production Account, since your testing is completed in a separate account. More on that process here: http://msdn.microsoft.com/en-us/library/windowsazure/ee517255.aspx

One difference is the “VIP Swap” process you can use for the final push to Production. In essence, this allows you to have two copies of the application running on the Production account, with a quick way to cut over and back when you’re ready. The process for that is detailed here: http://msdn.microsoft.com/en-us/library/windowsazure/ee517253.aspx

For monitoring, you have several options. You should enable the Windows Azure Diagnostics in your code - more on that here: http://archive.msdn.microsoft.com/WADiagnostics.

You can observe uptime and other information on the Windows Azure Service Dashboard, where you can also consume the uptime as an RSS feed: http://www.windowsazure.com/en-us/support/service-dashboard/

From there, you can also use System Center to monitor not only Windows Azure deployments but internal applications as well. The Management Pack and documentation for that is here: http://www.microsoft.com/download/en/details.aspx?id=11324.

There are also 3rd-party tools to manage Windows Azure. More on that here: http://www.bing.com/search?q=monitor+Windows+Azure&form=OSDSRC

Other References:

There is a lot more detail in this official reference: https://www.windowsazure.com/en-us/develop/net/fundamentals/deploying-applications/

Bryan Group explains the ramifications of the Secure Development Lifecycle (SDL) with lots of collateral you can review: http://blogs.msdn.com/b/bryang/archive/2011/04/26/applying-the-sdl-to-windows-azure.aspx

Memory Settings in #Tabular #ssas (and Prepare for #msteched!)

Marco Russo (SQLBI) — Mon, 06 Feb 2012 21:57:07 GMT

We are going very close to the release of SQL Server 2012 and after using Analysis Services 2012 for almost one year in a relatively small number of scenarios, I’m really curios to see the questions and the issues that will emerge when its adoption will go mainstream. I expect that many will consider a Tabular instance of Analysis Services similar to a Multidimensional one, also from a system engineer perspective, whereas there are many important differences. For example, the hardware required to...(read more)

Upgrading to SQL Server 2012 with Lock Pages in Memory

AaronBertrand — Mon, 06 Feb 2012 20:36:00 GMT

During SQL Server 2012 RC0 setup (specifically when upgrading), you may have noticed upgrade rules regarding Lock Pages in Memory (LPIM): However, for most folks, these rules always pass, whether or not they are actually using LPIM. I wanted to run a few tests to demonstrate why this is - or at least in which situations the rule checks will fail. So I created two virtual machines running SQL Server 2008 R2 SP1 CU3 - one running Windows Server 2008 SP2 (x86), the other running Windows Server 2008...(read more)

To Snark or Not to Snark…

andyleonard — Mon, 06 Feb 2012 12:00:00 GMT

Introduction This post is the fifty-first part of a ramble-rant about the software business. The current posts in this series can be found on the series landing page . This post is about communication. On Being Clever I enjoy listening to a good comedian and reading the works of humorous writers. Life is too short to waste on misery and a hearty laugh is good for the soul. Some humor is educational, thought-provoking and surprising. Some humor, though, can be hurtful. Some jokes and comments build...(read more)

SQL Server 2012 Early Adoption Cook Book <- starting to be available

Greg Low — Wed, 01 Feb 2012 22:28:00 GMT

I've been working on a team with Roger Doherty building parts of what's now become the SQL Server 2012 Early Adoption Cook Book . So, if you work on the bleeding edge of SQL Server and are keen to get your head around what's coming, this is a seriously good resource. Time to go and get it. The material is constructed as a large number of bite-sized pieces. Each presentation is about 15 minutes in length, and each demo is about 5 minutes. And there are lots of them. Look for recordings of these by...(read more)

Using XACT_ABORT ON may be faster than using TRY...CATCH

Alexander Kuznetsov — Wed, 01 Feb 2012 22:25:00 GMT

To ensure atomicity of transactions, we can use XACT_ABORT ON or wrap the transaction in TRY block and rollback in CATCH block. In some cases, the XACT_ABORT ON approach uses noticeably less CPU. I am posting repro scripts. Please run them, tweak them, and post your findings. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled. Test data We are using the same test data as in my previous post: CREATE TABLE dbo.Toggle1 ( id INT...(read more)

Wrapping related changes in a transaction may use less CPU.

Alexander Kuznetsov — Wed, 01 Feb 2012 22:02:00 GMT

Wrapping related changes in a transaction is a good way to ensure data integrity. Besides, in some cases it just runs noticeably faster, using less CPU. As usual, I am posting repro scripts, which you can run, tweak, and see for yourself. Environment I've run my scripts on 2008 R2 Dev Edition. Snapshot isolation is enabled, READ_COMMITTED_SNAPSHOT is not enabled. Test data After applying Occum's razor, all we need is two tables with one row in each: CREATE TABLE dbo.Toggle1 ( id INT NOT NULL PRIMARY...(read more)

DTLoggedExec 1.1.2008.4 SP2 released!

Davide Mauri — Wed, 01 Feb 2012 17:15:44 GMT

I’ve released couple of hours ago the SP2 of my DTLoggedExec tool.

For those who don’t know it, it’s a DTEXEC replacement, useful to execute SSIS and having logging provided right from the engine and not from the package itself.

More info can be found here:

http://dtloggedexec.codeplex.com/

This SP2 release add an important feature to the CSV Log Provider. It's now possible to store a personalized label into each log, in order to make it easy to identify or group logs.

Let's say, for example, that you have 10 packages in your ETL solution, and each time you have to load your data, you need to execute all those 10 packages. In other words , you have a batch made of 10 packages. It would be nice if all logs - one for each package - can be identified as a whole and grouped together, in order to quickly identify all the log of a articular batch.

This will make things easier when you want to know the overal time consumed by each batch execution.

The new "ExecutionLabel" attribute will help to achieve this. A useful ExecutionLabel can be obtained using SQL Server Agents Token. For example:

LABEL=$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))

ChangeLog

Updated the CSV Log Provider in order to write the status of the log file in the header.
- An OPEN status means that the log file is being written.
- A CLOSED status means that the log files has been written correctly.
- A file can be loaded into the database log only if is in the CLOSED state.
CSV Log files have 2 additional rows in the header: one for the FileStatus and one for the ExecutionLabel values.
- File format has been update to 4 from 3. (Only the header section of the file has been changed).
CSV Log Provider will now display EndTime value to the Console
Updated the import-log-data.sql to correctly load file with format 3 (the old one) and 4 (the new one).
Updated database schema to version 19 in order to store the new ExecutionLabel value
Update samples in order to show how to use the new ExecuteLabel option

As usual the download is available for free here:

http://dtloggedexec.codeplex.com/releases/81526/download/336490

SSIS MicroTraining: SSIS Package Configurations

andyleonard — Wed, 01 Feb 2012 12:00:00 GMT

I will be delivering another free, online MicroTraining on SSIS Package Configurations Tuesday, 7 Feb 2012, at 10:00 AM EST. Join Andy Leonard as he demonstrates uses of SSIS Package Configurations for externalizing SSIS package variable values. Register here ! :{>...(read more)

Performance impact: the cost of NUMA remote memory access

Linchi Shea — Mon, 30 Jan 2012 23:17:20 GMT

These days if you get a new server-class machine to run SQL Server, you can almost be 100% sure that it’ll be running on NUMA hardware. The recent AMD Opteron and Intel Nehalem-based processors are all built on some form of NUMA architecture. The current consensus is that as the number of processors grows, their shared memory bus can easily get congested and becomes a major impediment to scalability. NUMA hardware solves this scalability challenge by dividing the processors into groups, with each...(read more)

Book Review (Book 8) - The Elements of Persuasion

BuckWoody — Mon, 30 Jan 2012 14:57:23 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. The book I chose for January 2012 was: The Elements of Persuasion by Richard Maxwell and Robert Dickman.

Why I chose this Book:

As I mentioned in a previous review, I think good storytelling is an essential part of any career. Communication is basic in not only our professional but personal lives, and everyone I’ve met responds well to stories, from children to executive audiences. Not only that, learning to tell a story helps you formulate concepts about the topic, which is yet another way of learning.

I heard about this book from a couple of folks, and it landed within search of “storytelling” and “business”. Whenever I just search for “Storytelling” I either end up with lists of stories (which is fine) or lists of children’s books on storytelling (which is also fine) but neither of these are quite what I’m looking for.

A quick search on Amazon and I located the book, and then a quick check of my various e-library offerings and I downloaded it to my laptop for reading.

What I learned:

This is a “selling” book, but not like you might think. It’s not a book of a quick sale like at a car-lot or a “quick-sale” environment. It’s more along the lines at the executive level and longer-term sales - those involve stories as well.

Sadly, this is another “business book” - the kind I normally don’t like much. There are typical case-study layouts with lots of examples, but in my mind not enough didactic information to actually help you develop a good story-telling mantra.

Even so, I learned some interesting things about the process these authors use. Some of the case studies are interesting, and I did pull out that a story should work towards a single, defining sentence. This isn’t unique to this book, but it is a reinforcement of what I’ve learned elsewhere. Although nothing to do with storytelling, I did like the reference to Lockheed’s “14 Rules”, which I hadn’t read before. They also break down the storytelling process into five elements, which is actually covered better (in my mind) in a book called “20 Master Plots” , which may actually be the storytelling book I’ve been searching for.

Or perhaps I should just write the one I’m looking for.

At any rate, not sure I would recommend this book to others - perhaps as a check-out, but not a purchase, at least if this is for the same reason I looked it up.

Raw Notes:

As I read, I take notes - it’s called “reading with a pencil”. These are the notes I made to myself, in no particular order and with no context other than the book itself:

Stories are interesting to us all.
Describes five elements in a story, but in fact this is for only one type of story. Other books describes more story types.
Very standard business book, but there are good tips in some of the chapters.
Explained how to connect with the audience, good points Spends a lot of time referring to other books The book of five rings Work towards a single, memorable sentence.
Changes partway through into stories about stories. This is better.
A mix of storytelling and sales, although this was touted for sales, feels much more like selling than storytelling, advertisements.
Interesting story about memory championships, where contestants memorize cards. They use unusual stories.
Look up Lockheed and the 14 rules

OT: A Good Reason to Attend #SQLBits

AlbertoFerrari — Mon, 30 Jan 2012 08:41:46 GMT

My 4yo son keeps asking me why I should leave home so often to attend SQL conferences around the world and today, after a couple of days of snow, he discovered a good reason, along with the best place for the SQLBits scarf....(read more)