Search results matching tag 'sqlserver'

Bootstrapping SQL Server bloggers and blog readers with Twitter!

jamiet — Sun, 03 Mar 2013 23:20:00 GMT

On 17th December 2009 Aaron Nelson (you may know him as @sqlvariant) had a great idea – he invented the #SQLHelp hashtag; with a little kickstart from Brent Ozar the idea grew and #SQLHelp became a successful QnA channel in the SQL Server community and is today going from strength to strength.

I’m a great advocate of SQLHelp and not just because it builds bridges between those needing help with the people that are able to provide that help. It is also a great exemplar of the power of Twitter and, more specifically, the power of coalescing open data around a shared interest. As I thought more about this I figured there must be a way that the SQL Server community could further leverage what I think is a nascent opportunity around hashtags and as my mind wandered I thought about Steve Gillmor’s post from 5th May 2009 Rest in Peace, RSS in which he opined that RSS (the syndication technology that bootstrapped the blogging craze in the first decade of this century) should be replaced by Twitter feeds. Here’s a choice quote:

It’s time to get completely off RSS and switch to Twitter. RSS just doesn’t cut it anymore.

Steve isn’t averse to putting the cat among the pigeons with his blog posts and in this case I think he has a salient point. Whilst RSS isn’t a consumer technology (i.e. none of my none-techie friends have a clue what it is), Twitter most definitely is. One downside of RSS (in my opinion) is that most blog authors simply publish their outpourings then hope that it gets some Google juice and catches people’s attention. On the other hand, there are still a lot of people that use RSS readers and those people have a problem too – where do they find good bloggers and good blog material?

So, consider this:

Lots of people are blogging great stuff but don’t have a way of telling people about it
Lots of people want to learn from great bloggers but might not know where to go and find that material

Is there an opportunity to use Twitter to build bridges between bloggers and blog readers in a similar manner to how #SQLHelp has done between questioners and answerers? I think there is and that’s when I hit upon an idea – perhaps we as a community could (as https://ifttt.com/ expertly put it) put the internet to work for us.

Here is my suggestion. If you as a blog author tweet a link to a newly published SQL Server related blog post and use the hashtags

#sqlserver

#blogged

and also a hashtag to indicate the language then that tweet (and the all important link) will be available at https://twitter.com/search?q=sqlserver%20blogged. One can then use Twitter’s ability to make search results available as an RSS feed and subscribe to that RSS feed in one’s RSS reader of choice.

Is that a good idea? I think it is, but then again its my idea so I would, wouldn’t I? I hope a few people out there will get on board with this initiative (perhaps even blog and tweet about it) and hopefully if it can became a fraction as successful as SQLHelp.

Call to action for bloggers

If you as a blogger want to get involved with this initiative then its really very simple. Tweet a link to your SQL Server related blog posts along with a title and the following three hashtags

#sqlserver
#blogged
ISO 639-1 code indicating the language that the blog post is written in

*ISO 639-1 is a standard for 2-digit language codes. You can view the complete list on the International Standards Organisations (ISOs) website at http://www.loc.gov/standards/iso639-2/php/English_list.php although here are a few to get you on your way:

en – English

de – German

fr – French

es – SPanish

zh – Chinese

I would also encourage you to use other hashtags to more specifically define the subject matter as this might make for some interesting analysis later.

As an example, here is a tweet that I just tweeted for my blog post Obtaining rowcounts when using Composable DML [T-SQL]

https://twitter.com/jamiet/status/308351607924486144

Also, please blog about this yourselves (at the very least that gives you an opportunity to add your first tweet to the SQL Server twitter RSS stream).

Call to action for blog readers

If you are someone who enjoys reading SQL Server related blog posts wants to get involved in this initiative simply subscribe to the appropriate RSS feed in your RSS reader of choice and watch as (hopefully) great content flows into your RSS reader without you having to lift a finger. Here are a few such URLs:

http://search.twitter.com/search.rss?q=%23sqlserver%20%23blogged%20%23en%20-rt (SQL Server blog posts published in English)
http://search.twitter.com/search.rss?q=%23sqlserver%20%23blogged%20%23de%20-rt (SQL Server blog posts published in German)
http://search.twitter.com/search.rss?q=%23sqlserver%20%23blogged%20%23fr%20-rt (SQL Server blog posts published in French)
http://search.twitter.com/search.rss?q=%23sqlserver%20%23blogged%20%23es%20-rt (SQL Server blog posts published in Spanish)
http://search.twitter.com/search.rss?q=%23sqlserver%20%23blogged%20%23zh%20-rt (SQL Server blog posts published in Chinese)

Thanks to Dan English for pointing out in the comments that the search URL can be amended to remove retweets.

That’s all there is to it. Fingers crossed that this initiative catches on because there is a fantastic knowledge sharing opportunity here – let’s put the internet to work for us to make it happen.

I have one more thing to say, a line that I stole from my ex-colleague Howard van Rooijen, one which I am a great believer in and which I believe is very pertinent here:

Work smarter, not harder.

@Jamiet

A SQL Saturday in Cambridge – Buck Woody’s Ragtime Database Workshop

BuckWoody — Mon, 30 Jul 2012 20:49:00 GMT

The SQL Server community is really engaged. They are an active bunch on Twitter, Facebook and LinkedIn, they help each other on forums, they attend conferences. But that isn’t enough interaction – the community started a grass-roots effort to hold local conferences on a Saturday. Free conferences. Odds are there’s one near you… http://www.sqlsaturday.com/default.aspx … and if not, you can start one.

Sessions at SQL Saturdays are all over the map, and there’s something for (almost) everyone, from Business Intelligence to Database Administration and Development. Some of these events have “training days” associated with them – longer, more in-depth training that has a fee. I’ve taught quite a few of these, and of course I’ve done my share of other presentations at the events as well.

I’m honored to be presenting at the Cambridge, UK SQL Saturday this year (http://www.sqlsaturday.com/162/eventhome.aspx) in September. For one thing, I used to live near there and plan to take the family with me to show them my old stomping grounds. For another, I’m excited about the sessions I get to present.

The Training Day

Friday I’ll be leading one of those training days – and I’ll be delivering a very important workshop, where I’ll cover SQL Server – all of it. OK, maybe not *all* of it, and maybe you won’t be a complete PhD after the class, but we’ll do a complete immersion in learning SQL Server as a product from the ground up. It’s a workshop format, so no sitting and listening to someone droning on and on for hours. You’ll be asked to bring a laptop, and do actual work on the product from the first few minutes of the 8-hour day to the last of the workshop. You’ll be jumping in from the very start, and in deep until the end of the day.

But wait – this is kind of a “beginner” thing, isn’t it? Shouldn’t I be doing something on the internals of the locking mechanism of the hashing system in memory on x64 architectures, with complete code diagrams? Shouldn’t you be able to snatch the T-SQL pebble from the master’s hand when you’re done, or be able to shoot the wings off of an XML fly when you’re done?

No.

We need this kind of session. For one thing, you can get that depth in other sessions. For another, we need to have a place for someone that wants to learn SQL Server but doesn’t have a lot of time to do that. We need something relatively inexpensive that a boss can send a developer, administrator or new employee to learn how to take over the SQL Server, or augment the DBA team. We need a place where good habits are formed, and where someone can branch out into a new part of technology, into one of the best places in tech to be – data.

After you’re done with this session, stick around for Saturday – now all those presentations will make more sense. And you’ll be able to network with a lot of folks that already do what you learn about on Friday, and who knows – find out where to look for work in this amazing career field.

What will you learn?

You’ll start with knowing that SQL Server is a database product by Microsoft. That’s all the pre-requisite you need, other than being the technology industry. From there we’ll cover:

The Data Professional Career
Installing, setting up and configuring the right Edition of SQL Server for the job (including SQL Azure)
Database engine fundamentals – How does the engine work, what are the components, what can you configure and tune
Transactions, Locking and Blocking
Creating and managing databases
Database options and their impact
Database Objects including Tables, Views, Stored Procedures, Functions and more
General maintenance including backups and recovery
Security fundamentals including users, roles, and object security
Performance tuning fundamentals including indexes and query research tools
Multiple resources to help you get to the next level

In 8-hours. Come ready to learn. You’ll need a laptop, and complete focus for a few hours. You’ll leave with the ability to manage and work with a SQL Server system – and you’ll learn what to do next.

Who should go?

If you’re new to Relational Database Management Systems (RDBMS’s) but not technology, and you’re looking to expand your technical reach, coming from another platform (to be sure, there will be some repeat info here), want to explore a new tech career area, want to learn more about developing against an RDBMS or know someone who does.

The registration for the training day is here: https://www.regonline.co.uk/?eventID=1120017&rTypeID=242030

What else are you doing whilst (I love saying whilst) there?

I’m also pleased to be providing the keynote on Saturday. (I can’t wait to see what I’m going to say), as well as two other sessions – more on those soon. My daughter wants to be a Zoologist, so while we’re there we’ll be visiting the Zoology museum at one of the colleges, I’ll probably eat too much and potentially go punting. I’ll also mingle with you, my SQL Family, and we’ll just generally have a good time.

If you’re not busy in September, and even if you are, make plans to come check all this out. It promises to be awesome. (Americans think everything is awesome)

See you there.

Big Data and the Cloud - More Hype or a Real Workload?

BuckWoody — Tue, 18 Oct 2011 13:57:36 GMT

Last week Microsoft announced several new offerings for “Big Data” - and since I’m a stickler for definitions, I wanted to make sure I understood what that really means. What is “Big Data”? What size hard drive is that? After all, my laptop has 1TB of storage - is my laptop “Big Data”?

There are actually a few definitions for this term, most notably those involving the “Four V’s” Volume, Velocity, Variety and Variability. Others disagree with this definition. I tend to try and get things into their simplest form, so I’m using this definition for myself:

Big data is defined as a large set of computationally expensive data that is worked on simultaneously.

Let me flesh that out a little. To be sure, “Big Data” has a larger size than say a few megabytes. The reason this is important is that it takes special hardware to be able to move large sets of data around, store it, process it and so on. (large set)

If you store a LOT of data, but only use a small portion of it at a time, that really isn’t super-hard to do. It’s mainly a storage issue at that point. But, if you do need to work with a large portion of the data at one time, then the memory, CPU and transfer components of the system have to adapt to be responsive - new ways to work with that data (game theory, knot-algorithms, map-reduce, etc.) need to be brought into play. (computationally expensive)

Once that data is loaded into the processing area (memory or whatever other mechanism is used) it must be worked on in parallel to come back in a reasonable time. You have two options here - you can scale the system up with more internal hardware (CPU’s, memory and so on) or you can scale it out to have multiple systems work on it at the same time using paradigms such as map/reduce and so on. Actually, when you lay this out in an architecture diagram, scale up or out doesn’t actually change the logical structure of the process - in scale out the network becomes the bus, and the nodes become more RAM and computing power. Of course, there are changes in code for how you stitch the workload back together. (worked on simultaneously)

So back to the original question. Is Big Data, as I have defined it here, a workload for Windows and SQL Azure? Absolutely! In fact, it’s probably one of the main workloads, and I believe it represents the latest, and perhaps also the earliest frontier of computing. Jim Gray, a former researcher here at Microsoft and a hero of mine, was working on this very topic. I believe as he did - all computing is simply an interface over data.

Microsoft has multiple offerings on the topic of Big Data. In posts that follow from myself and my co-workers, we’ll explore when and where you use each one. Whether you are a data professional or a developer, this is the new frontier - don’t wait to educate yourself on how to leverage Big Data for your organization.

Hadoop on Windows Azure and SQL Server - Microsoft’s partnership to include Hadoop workloads on Windows Azure and SQL Server/Parallel Data Warehouse (PDW)

LINQ to HPC - Microsoft’s High-Performance Computing SKU of HPC is now in Azure

Windows Azure Table Storage - A key/value pair type storage with full partitioning that is immediately consistent, able to handle huge loads of data and works with any REST-compatible language

Other offerings - Including the new Data Explorer, Project Daytona (with a Big Data Toolkit for Scientists and researchers), Power View and more.

The era of Big Data is here. And you can use Windows and SQL Azure to bring it to your organization.

Building a database installer with WiX, datadude and Visual Studio 2010

jamiet — Thu, 10 Feb 2011 21:45:17 GMT

Today I have been using Windows Installer XML (WiX) to build an installer (.msi file) that would install a SQL Server database on a server of my choosing; the source code for that database lives in datadude (a tool which you may know by one of quite a few other names). The basis for this work was a most excellent blog post by Duke Kamstra entitled Implementing a WIX installer that calls the GDR version of VSDBCMD.EXE which coves the delicate intricacies of doing this – particularly how to call Vsdbcmd.exe in a CustomAction. Unfortunately there are a couple of things wrong with Duke’s post:

Searching for “datadude wix” didn’t turn it up in the first page of search results and hence it took me a long time to find it. And I knew that it existed. If someone else were after a post on using WiX with datadude its likely that they would never have come across Duke’s post and that would be a great shame because its the definitive post on the matter.
It was written in October 2009 and had not been updated for Visual Studio 2010.

Well, this blog post is an attempt to solve those problems. Hopefully I’ve solved the first one just by following a few of my blogging SEO tips while writing this blog post, in the rest of it I will explain how I took Duke’s code and updated it to work in Visual Studio 2010.

If you need to build a database installer using WiX, datadude and Visual Studio 2010 then you still need to follow Duke’s blog post so go and do that now. Below are the amendments that I made that enabled the project to get built in Visual Studio 2010:

In VS2010 datadude’s output files have changed from being called Database.<suffix> to <ProjectName>_Database.<suffix>. Duke’s code was referencing the old file name formats.
Duke used $(var.SolutionDir) and relative paths to point to datadude artefacts I have replaced these with Votive Project References http://wix.sourceforge.net/manual-wix3/votive_project_references.htm
I commented out all references to MicrosoftSqlTypesDbschema in DatabaseArtifacts.wxi. I don't think this is produced in VS2010 (I may be wrong about that but it wasn't in the output from my project)
Similarly I commented out component MicrosoftSqlTypesDbschema in VsdbcmdArtifacts.wxi. It wasn't where Duke's code said it should have been so am assuming/hoping it isn't needed.
Duke's ?define block to work out appropriate SrcArchPath actually wasn't working for me (i.e. <?if $(var.Platform)=x64 ?> was evaluating to false) so I just took out the conditional stuff and declared the path explicitly to the “Program Files (x86)” path. The old code is still there though if you need to put it back.
None of the <RegistrySearch> stuff is needed for VS2010 - so I commented it all out!
Changed to use /manifest option rather than /model option on vsdbcmd.exe command-line. Personal preference is all!
Added a new component in order to bundle along the vsdbcmd.exe.config file
Made the install of the Custom Action dependent on the relevant feature being selected for install. This one is actually really important – deselecting the database feature for installation does not, by default, stop the CustomAction from executing and so would cause an error - so that scenario needs to be catered for

I have made my amended solution available for download at: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.zip It contains two projects: the WiX project and the datadude project that is the source to be deployed (for demo purposes it only contains one table).

I have also made the .msi available although in order that it gets through file blockers I changed the name from InstallMyDatabase.msi to InstallMyDatabase.ms_ – simply rename the file back once you have downloaded it from: http://cid-550f681dad532637.office.live.com/self.aspx/Public/BlogShare/20110210/InstallMyDatabase.ms%5E_ .You can try it out for yourself – the only thing it does is dump the files into %Program Files%\MyDatabase and uses them to install a database onto a server of your choosing with a name of your choosing - no damaging side-affects. I will caveat this by saying “it works on my machine” and, not having access to a plethora of different machines, I haven’t tested it anywhere else. One potential issue that I know of is that Vsdbcmd.exe has a dependency on SQL Server CE although if you have SQL Server tools or Visual Studio installed you should be fine. Unfortunately its not possible to bundle along the SQL Server CE installer in the .msi because Windows will not allow you to call one installer from inside another – the recommended way to get around this problem is to build a bootstrapper to bundle the whole lot together but doing that is outside the scope of this blog post. If you discover any other issues then please let me know.

Here are the screenshots from the installer:

And once installed….

Hope this is useful!

@jamiet

TechEd 2010 Day Two – No SQL Server in Sight

BuckWoody — Tue, 08 Jun 2010 10:12:07 GMT

Today I worked the booth at TechEd 2010, manning the new “Surface” computer, which is just the coolest object on the planet. After that I didn’t attend a single SQL Server session – instead I’ve been frequenting SharePoint, Microsoft Office, and even the High-Performance Computing sessions. The reason is that I get really high quality SQL Server presentations at PASS, SQL Saturdays, and online from Microsoft and other vendors. While there are SQL Server sessions here (after all, I’m giving one of them!) I tend to try and see things that I don’t normally get to learn about. And the cross-pollination between those technologies and mine is fantastic.

I’ve even managed to go to an Entity Framework presentation for the developers. I actually have (a little) more respect for that technology – and I’ve modified my presentation to encompass more of that information.

So whenever you have the chance, take a walk outside your comfort zone. Even at PASS and SQL Saturdays (and certainly online) you can investigate technologies other than the ones you know best.

It will happen on a spring day in May...

ArnieRowland — Mon, 22 Feb 2010 16:20:00 GMT

"The world's favorite season is the spring.
All things seem possible in May."
- Edwin Way Teale

This spring, the Portland, Oregon, development community is coming together in a way never experienced before.

Using the University of Portland campus, SQL Saturday and Portland CodeCamp are combining and coordinating efforts to bring 700-800 regional technology professionals together for the opportunity to immerse themselves in seminars, presentations, group exploration, and networking -using both the 'professional conference' and the 'unconference' models. Participants will be able to engage in their preferred technology, as well as to 'sample' other options

Mark your calendar! Portland SQL Saturday and Code Camp 2010 is scheduled for May 22, 2010, at the University of Portland campus. The event is scheduled from 8:00 AM until 10:00 PM. The UP facility offers a great venue –including larger, fully equipped (and air conditioned) meeting rooms, excellent parking, access to public transportation, and easy bike commuting. Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleages know about the event.

Portland SQLSaturday is encouraging presentations related to interoperability of any of the SQL platforms, including T-SQL (SQL Server), PostgreSQL, MySQL, and PL-SQL. Abstracts for Platform specific sessions are also encouraged. Most sessions are one hour fifteen minutes (1:15), and a few 'deep dive' sessions will be longer (2.5 hour) in duration. The 'deep dive' sessions will be scheduled for 7:00 PM (after the snacks and refreshments.) Also, through out the day, there may be opportunities for 30 minute sessions. (Be sure to indicate your preferred session length.) Remember, in addition to folks working with a specific SQL platform, there will be a large number of developers seeking information about using one or more SQL variants to create solutions to their problems, as well as needing to work with multiple SQL platforms.

Consider adding your presentation abstract here.

If you are in the Pacific Northwest in May, don't miss this event!

It doesn't matter, it's just data...

ArnieRowland — Tue, 15 Dec 2009 01:03:00 GMT

Database Design. Sounds simple, and at the same time, demanding.

I mean, what's to design. You just need a few tables to hold the data for the application you are creating. You just can't really take the time to design -the entire project is agile and under continuous evolution.

Or, you are trying to support a database that someone else allegedly 'designed'. Yes, you know it is bad, held together with more of a wish than a demand. Yes, you would just love to take the time and 'do it right' -but let's get real, it's impossible. Too many moving parts, too many applications, too many folks depend on what is there right now. No way can you make 'design' changes.

Help is now available in the form of a new Forum on the MSDN and TechNet Forums sites. The Database Design Forum is moderated by a group of MVPs from around the world. They have a lot of experience with database designs -good and not so good. Heck, even some of them have written books on the subject.

Sure there are other forums and newsgroups where you can get help, where you can ask questions, where you can seek 'work-a-rounds'. At the MSDN/TechNet Database Design Forum, you can ask the questions no one wants to hear, you can challenge the 'conventional wisdom' of database designers, and you can propose your design and ask folks to help you see if there are any flaws. At the MSDN/TechNet Database Design Forum you will not be berated, belittled, demeaned, or in any way dismissed just because you are diligently working to do something different, or support someone else's mistake.

At the same time though, expect to be asked difficult questions, expect to explain and educate about your design decisions, expect to have your thoughts challenged. For it is through rigorous examination that your designs and your skills will improve. And everyone participating will learn from the process.

MSDN or TechNet -your choice.

Give it a try and let me know what you think...

You Bet Your Life

ArnieRowland — Thu, 22 Oct 2009 15:16:00 GMT

Yesterday, Jonathan Kehayias wrote in his blog post "Have you got air in your spare.." about dealing with a flat time. He went on to compare spare tires to HA/DR planning. An excellent article and recommended reading. I wish to expand on Jonathan's discourse a bit. It strikes me that he opened the door for a fertile analogy.

Consider that having a spare tire is like having a database back-up. Knowing that there is a spare tire in the car is similar to knowing that there is a backup 'plan' -perhaps even regularly scheduled database backups. But when was the last time you checked your spare tire? When was the last time you checked to see if you could easily change the flat tire? My wife's vehicle recently had a very low tire, and it took 15-20 minutes just to locate the the jack points and safely elevate the vehicle. It was a new vehicle, neither of us had changed a tire on it previously, so we had to learn a new process for new equipment.

In Jonathan's situation, it was discovered that the spare tire was also 'flat'. We constant talk about regularly testing backups for reliability and validity. -But when was the last time your organization fully restored a backup and 'bet your life on it'. That is what you are doing when you start driving on a spare tire -betting your life, and/or the lives of your family. Of course restoring a backup isn't betting 'your life', but it could actually be someone else's -or your career. Have you ever restored a database and subjected it to a rigorous test regimien?

Imagine if your family was in the car, and had a flat tire. Could your wife (the junior DBA) properly and safely restore and get moving quickly? She's taking your child to a needed medical appointment -how long before the 'business' is running again? Does she have a fall-back support contact, like in Jonathan's situation, the AAA membership? (or a PSS account.) Even then, there will most certainly be additional waiting -will the appointment be missed?

You could, of course, invest additional resources into a "High Availability' solution -for cars, buy 'run-flat' tires.How would you deal with knowing that your family was on the side of the road somewhere, perhaps in the dark, maybe even in a questionable area, waiting, and waiting, and waiting... PSS may be good, but they may also require some time to get back to you. You may wish to protect your family with redundancy -at a cost. Run-flat tires will cost about double the regular tire cost. Think clustered servers... No latency, no downtime, continue moving, repair as soon as possible without negatively impacting the schedule.. Who are you protecting by not investing in the proper solution?

Some newer cars have builtin spare tire reporting -the car's monitoring system will alert you to low spare tire pressure. Do you have a plan/process where you regularly check your spare tire? How do you actively monitor the integrity of your backups? Do you just trust knowing that it is there? Do you actually test it on occasion? When was the last time you tested it?

Do you bet your career (or someone's life) on the Disaster Recovery Plan?

Spare Tire = Database Backup
Checking Spare Tire = Testing Backup
Practice Jacking Up Car = Practice Restores
AAA Membership = On Call Support

Spare Tire + Checking Spare Tire + Practice Jacking Up Car + AAA = Disaster Recovery Plan

Run-flat tires = High Availability

What is your family worth?

Is it time for SQL Server to have Isolated Transactions?

ArnieRowland — Tue, 10 Mar 2009 00:09:00 GMT

SQL Server allows nesting of Transactions –in a sense. In truth, there is only one Transaction, with each ‘nested transaction’ serving to increase (and decrease) the @@TRANCOUNT indicator. The transaction can be committed or rolled back at any point that the @@TRANCOUNT value is equal to one. Error conditions may or may not cause the transaction to rollback –depending upon the specific nature of the error or the XACT_ABORT setting. (In this discussion, I am not going to address the situations where the @@TRANCOUNT is not properly decremented and also when commit or rollback is not accomplished –leaving a hung transaction.)

And for many usages, the implementation works just fine. All of the work, or none of the work –at least that is what we hope for. Sometimes it is necessary to jump through seemingly unnecessary ‘hoops’ in order to get it working as we wish.

In the case of Logging, I need to log the activities and be assured that the log entries persist even if the parent transaction fails. With the current implementation, the activities of a ‘nested transaction’ are rolled back if the parent transaction rolls back, even when calls are made to stored procedures –all is lost if the parent transaction rolls back. Yes, there is a work-around. It is common to create a Table Variable prior to starting the parent transaction, populate it with values during the transaction scope, and then after the transaction is completed (either committed or rolled back) to then insert the rows of the Table Variable into the logging table. –BUT, if server power were to fail just before the inserts, any records of the transaction activity would be forever lost. And it makes it impossible to create a self contained logging procedure that can be executed with only one line of code in the transaction.

I suggest that there is a need for a completely ‘Isolated Transaction’. That is, a transaction that can be started in the midst of a ‘regular’ transaction (parent), and that will be durable regardless of the outcome of the parent transaction. Using this ‘Isolated Transaction’, once a commit occurs, even a rollback of the parent transaction will not undo the saved work. The activity log is properly persisted and durable.

In some situations, it may be necessary to force the parent transaction to fail and rollback if entries to the log cannot be accomplished. (I’m thinking of a security implementation where any activity, even unsuccessful activity must be logged.) That can be accomplished by the parent transaction examining the error that caused code execution to fall into the CATCH block and taking appropriate action, or perhaps, proper specification of the XACT_ABORT option.

Consider the following:

BEGIN TRANSACTION

TRY

{do some work}

BEGIN ISOLATED TRANSACTION

{do some work}

{Create Log Entries}

END ISOLATED TRANSACTION

{do more work}

END TRY

CATCH

ROLLBACK

RETURN

END CATCH

COMMIT

END TRANSACTION

I suggest that in the above scenario, the ISOLATED TRANSACTION should automatically commit (and persist) upon successful completion, or ABORT and exit upon any failure, reporting an error condition to the parent transaction. The parent transaction could (depending upon the XACT_ABORT setting) determine whether to continue or abort itself. IF the isolated transaction successfully completes, it is committed and durable regardless of the action taken by the parent transaction.

Now this scenario seems to satisfy my issues of being able to reliably log activities in a transaction, and have those log entries persist if the transaction is rolled back.

But additional questions arise.

Stored Procedures

What should be done IF the isolated transaction exists within a stored procedure that is called from the parent transaction code? How should the procedure handle an error that causes the isolated transaction to rollback? The RAISERROR statement in SQL Server has various quirks and should not be relied upon to communicate with the parent transaction –RAISERROR may itself cause the parent transaction to rollback and not allow the parent transaction to evaluate the error condition in its CATCH block.

Should the procedure be totally autonomous and not attempt to communicate with the parent transaction? How would the procedure best communicate an error condition, either in the isolated transaction or not? Would it be enough if failure in the Isolated Transaction caused an XACT_ABORT error to be passed to the parent transaction?

Dynamic SQL

It is possible to make a compelling argument for an Isolated Transaction in dynamic SQL. How would/should the dynamic SQL communicate to the parent transaction if the Isolate Transaction were to fail and rollback? Should the parent transaction even care?

Service Broker

Having an isolated transaction could solve some issues related to Service Broker implementations –such as starting a batch process, guaranteed messages, and/or mail delivery. Is this a use case scenario that makes sense for you?

Additional Considerations

Some have posited that the isolated transaction ‘should NOT’ automatically rollback on error, but instead require a specific ROLLBACK statement. Something like this:

BEGIN ISOLATED TRANSACTION

{do some work}

IF {condition}

ROLLBACK

{do some other work}

END ISOLATED TRANSACTION

But this raises all sorts of questions and issues. What happens after the ROLLBACK if there is an attempt to INSERT/UPDATE/DELETE? Is a new isolated transaction started? Is it the same transaction? Is it an error condition? What happens if the ROLLBACK is omitted and an error condition occurs?

Personally, I’m in favor of a simple approach to the isolated transaction –not at all bound by any previous SQL Server transaction implementation. Automatic COMMIT on successful completion, automatic ROLLBACK on error, and appropriate error messaging down the call stack. What is your opinion?

Your help is needed!

In order to best prepare and lobby for such a suggested ‘improvement’, it is important to have a clear understanding of the use case scenarios that we are attempting to solve with the isolated transactions.

So I ask you, if you see benefit from having an ISOLATED TRANSACTION in SQL Server, what is your use case? How would you use it? What are the pitfalls?

You HAVE to Trust, you MUST Verify -and that may still not be enough!

ArnieRowland — Fri, 20 Feb 2009 21:22:00 GMT

In Brian Kelly's recent blog post, he makes an excellent case outlining why there are few options but to 'Trust' SQL Server Administrators. And then he goes into excellent detail explaining that it may be impossible to completely 'prohibit' disruptive behavior, and that one should establish a robust auditing of security events.

And it is not just the SQL Server Administrators, or the network administrators that require ‘trust’. It is anyone that has access to the ‘wire’.

A while back I was working on a project that had to meet a HS/FIPS standards that mandated that all data in transit be encrypted. I recall sitting in a meeting where, in response to my request for the establishment of encryption, (possibly IPSec) between the web farm and the data cluster, the director of the infrastructure teams bluntly stated that it would not happen because 'we trust our people'. There was continued resistance to finding any alternatives to meet the encryption requirement. The network administrators were firmly opposed to having packets on ‘their wires’ that they could not ‘look into’. There were attempts to find some manner of ‘waiver’ from the standards. My arguments about the difficulty involved in discovering passive sniffers, or that anyone with access inside the firewall could easily install an ‘unknown sniffer’ were summarily dismissed as ‘overly concerned’. My team continued moving ahead in preparation to the time when encryption deadline became inescapable.

A few months later, all IT infrastructure staff were required to undergo new background security checks. I was not surprised that some of the 'trusted people' abruptly resigned or were terminated. (I've noticed that about 15% of IT staff seem to either refuse to submit to, or fail security checks. Sometime termination is for issues that would not have prevented the initial hire, but became mandated since the issues were not disclosed on the application. Sometimes just 'youthful indiscretions'...

And the data in transit was finally encrypted.