Search results matching tags 'Development' and 'Best Practices'

Preparation is key to a successful cloud deployment

BuckWoody — Tue, 01 May 2012 13:09:16 GMT

If you want to be wise, watch the actions and outcomes of others. Emulate the successful actions, and avoid the actions that cause failure. That’s true in life in general - and in technology projects in specific.

I’ve worked with several clients who have created or migrated an application to “the cloud” - meaning using Microsoft Windows Azure or another provider. Although the statement in the title of this post is trite, I cannot over-emphasize how accurate it is. In every case of those who had a great experience with a distributed computing environment (which is thankfully the vast majority of my projects),

What kind of preparation do you need to do? Here are some tips I’ve learned in the successful (and not-so-successful) deployments I’ve seen:

Follow standard recommendations for successful projects in general

You and your organization have probably done a few projects before - this one should have the same general attributes: a well-defined goal, a small, motivated team, a realistic timeline, and an adequate budget. I know, I know, you *never* seem to get those things - but if you don’t, you’ll fail. Simple as that.

Educate yourself

Computing technology started out on a single set of hardware for a single purpose - and realizing the limits of the hardware at hand, systems designers quickly realized that scale-out and virtualization was key. No, that’s not new - mainframes almost always worked on the concept of scale-out and virtual machines. But we switched in the 1980’s to single-user systems again, and we’ve been there ever since. By that I mean you install an OS on the things you work on. Now we move back to distributed system concepts, and there are some real differences. You’ll need to learn how those work, and do things a new way. Hey, we’re IT - we LOVE learning new things, right?

Get a partner if needed

There are a few of us white-haired Gandalf’s around that remember how to work in a distributed system, but if it’s new to you, that’s completely OK. You can save yourself a world of trouble by working with someone who’s done this before - a partner you hire, someone from Microsoft Consulting, whatever.

And don’t forget support - who will handle each issue, what is the escalation model, who are your contacts at Microsoft, and what is your “light’s out” strategy?

“A new broom sweeps clean”, the old adage goes, but the old brooms know where the dirt is.

Build a model

Take some time to do a Proof of Concept on your local system and using your Azure hours from your MSDN account if you have one. Going through this build - and being willing to throw it away and try it a different way - is invaluable.

Test your theories

Three statisticians are walking in a field. They see a rabbit - the first guy raises his gun, firing far in front of the rabbit. The second guy simultaneously raises his gun and fires far behind the rabbit. The third guy yells “We got him!”

Not every theory is correct - not every attempt is the right one. Build in your success tests while you’re building your model. Then check them - don’t leave this step out.

Rinse, lather, repeat

This is advice from a shampoo bottle - which I’ve never used (I don’t really have that much hair - especially now). But in a “Cloud” project, it’s important. It’s an evolving system, that gains new improvements at an amazing rate. As soon as you deploy and stabilize you need to start the process over again. If you created your system in a Services model, with contracts for the APIs and abstracted code, this is far easier.

It’s not hard to do a cloud project right. But it’s really simple to do it wrong. Follow these guidelines and you’ll learn from the successes - and mistakes - of others.

Cloud Computing Patterns: Using Data Transaction Commitment Models for Design

BuckWoody — Tue, 14 Feb 2012 20:45:47 GMT

There are multiple ways to store data in a cloud provider, specifically around Windows and SQL Azure. As part of a “Data First” architecture design, one decision vector – assuming you’ve already done a data classification of the elements you want to store – is to decide the transaction level you need for that datum. Once you’ve decided on what level of transactional commitment you need, you can make intelligent decisions about the storage engine, method of access and storage, speed and other requirements.

Although the list below is neither original nor exhaustive, these are the general considerations I use for a given data set. It’s important to note that in many on premises systems the engine choice at hand overrides these concerns. If you have a large Relational Database Management System (RDBMS) for instance, you might simply place all data there without further consideration. In a Platform as a Service (PaaS) like Windows and SQL Azure, however, selection of the proper engine for a particular dataset has implications ranging from cost to performance, and selecting the right engine is critical when you want to leverage the data across “Bid Data” analysis like Hadoop or other constructs.

Monolithic Consistent Transactional
The first selection is analogous to a local RDBMS system. The dataset is retrieved in a functionally single, monolithic transaction, i.e. kept together with ACID properties in mind. This is the most reliable type of data design for datasets that require a high degree of safety in the read/write pattern. As an example, a bank ATM transaction should be modeled in a monolithic way. If I make a transfer of funds from one account to another, I want the money to be subtracted from one account if and only if it is successfully added to the other. The bank, on the other hand, wants the money added to the second account if and only if it is subtracted from the first. This is a prime example of a monolithic (single atomic transaction), Consistent (if and only if) and Transactional (as a unit, with provision for roll-back and reporting if unsuccessful) data requirement.

The primary engine used for this type of data is often SQL Azure – an RDMBS in the same datacenters as Windows Azure. Placing both the calling application, whether that is a Data Access Layer-based code widget or a direct call from a Web or Worker Role, means that data is retrieved quickly and in a monolithic way. The costs for this method is based on overall database size. A consideration is how much data you can store this way. Database sizes have limits, although there are ways of overcoming size issues using technologies such as Sharding or SQL Azure Federations. There is also the consideration of performance. In an RDBMs that conforms to ACID properties, locking and other overhead for safety is at conflict with the highest possible read performance. But in some cases the ACID properties are worth the cost, as in the banking example.

You are not limited to SQL Azure in this model. Windows Azure Table storage, while similar to NoSQL offerings is different in that it is immediately consistent across all three replicated copies of data, offering a higher degree of safety. And while Table storage does not offer built-in support for transactions, there are ways to achieve certain transaction levels.

Monolithic Realtime
If consistency can be relaxed – meaning that a guaranteed read/write patter is not essential – then more options arise in Windows and SQL Azure. You can still use SQL Azure for this type of storage, with either automatic or programmatic hints allowing for “dirty reads”. Windows Azure Table storage is still consistent, but the selection of the method for querying the data such as separate copies of read and write data can be employed. Because of the relaxed transaction nature, higher speeds are possible by querying cached or separate datasets.

An example here is that same transaction from the bank, but a statement inquiry. Just after the money is deposited, the user wishes to query the current balance. The current balance – minus the transaction that just occurred – is retrieved and shown to the user, perhaps even combining the amount with the latest transaction, perhaps saved as a local cached object, with a caveat to the user.

Distributed Realtime
At some point, the data becomes too large to fit inside a single processing session, and parallelism is used. In this case, either separate databases in SQL Azure or Windows Azure Tables, local data storage on the Web or Worker Role, or a combination of all with Caches is the right approach for the data design.

The biggest implication in this type of system is speed – a higher degree of data separation is essential, and so the dataset selection must fit the pattern. It is unacceptable to force an ACID-properties type workload into this environment. Typical examples here are the actual data asset payload for streaming video or music, read-only documents and so on. This pattern is often separated from the meta-data, which is kept in more of a transactional model.

As an example, assume you log on to a website to watch a movie or listen to music. The provider needs to verify your identity and account balance, which are transactional data loads. After that process is complete, the workload shifts to a copy – perhaps one of several – of the asset to stream to your location.

In this case, Windows Azure Blob storage, along with the Content Delivery Network (CDN – a series of servers closer to the user) is employed along with the transactional realtime requirements for the metadata.

Distributed Eventual
At the furthest end of the data scale are large datasets that need deeper analysis, but not necessarily in realtime. Examples here are terrabytes of data requiring a Business Intelligence view, but with a tolerance of a few seconds to minutes or hours. In this case, Storage, Processing and Query methods, such as the Hadoop offering in Windows Azure, or perhaps the High Performance Computing (HPC) Windows Server in Windows Azure fit well. Here, the design of the data is often dictated by the source, and more emphasis is placed on the algorithms around processing and re-assembling the data.

There are, of course, other patterns. In many cases a single dataset may have needs in one or more of these categories – in fact, sitting at 30,000 feet typing this entry, I’m having that very design discussion with a gentleman sitting next to me. The key is to design data-first, and fit the technology to the requirement for each datum. Allow each function and engine to handle the data in the most efficient, effective way for cost, performance and utility.

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

Rip and Replace or Extend and Embrace?

BuckWoody — Tue, 13 Sep 2011 11:20:05 GMT

As most of you know, I don’t like the term “cloud” very
much. It isn’t defined, which means it can be anything. I prefer “distributed
computing”, which is more technically accurate and describes what you’re doing
in more concrete terms.

So when you think about Windows and SQL Azure, you don’t
have to think about an entire product – you can use parts of the system
together or independently to accomplish what you need to do. You can use the
computing functions, storage, and more and more I see folks leverage the
Service Bus to enable current applications to expose things to the web.

And that brings up the point of this post. Once you decide
that a distributed architecture works to solve a problem, you’re faced with a
decision: should you completely re-write your architecture to take advantage of
the current systems or should you just fold in new code that makes the data or
function available to the web?

Of course, the answer is always “it depends” on the situation
– and it does. But unless you’re fixing a problem with current code, I usually
advocate a migration approach. That means at the very least retaining the
business logic (again, unless it’s not currently working) and as much of the
code as you can. In fact, if you follow this paradigm, you’re on your way to
making a Service Bus out of the functions you currently have. You can expose
the results of a system rather than opening the system up. Let’s take an
example.

Assume for a moment that you have an order-taking system
on-premise. That system performs many functions, one of which might creating a
Purchase Order. Your system might be enclosed, meaning that it has an
application that talks to a middle-tier, and then from there to a database
system. A query is generated from a screen, and passed along to eventually
compute, store and return a Purchase Order Number, along with other
information. Imagine now that you wire up the code not only to return the PO
number to the client, but to make that number available on an endpoint –
actually really not that hard to do.

Now you can make that PO number available to the web using
Azure. You could restrict who can make that call to the system, or open it up
to a broader audience. Or instead of the PO Number, you could make a product
list available. And you can go further than that – EBay, for instance, uses the
OData protocol (which is very cool in and of itself) which you can query from
the web. You could compare your company’s product catalog to what is on EBay,
and list the items you have there if there are no competitors in that space.
And on and on it goes.

So the point is this – where you can, retain what works.
Fold in systems like Azure where they make sense. Extend and Embrace.

Plan for Diagnostics in Cloud Computing From the Git-Go

BuckWoody — Tue, 06 Sep 2011 13:11:22 GMT

“Git-Go” is something we say in the South that means “right at the start”. I’ve seen several applications for on-premise systems that don’t have much in the way of diagnostics - the developers rely on a debugger, the event logs on the server and client workstation, and most of all, the ability to watch the system from end-to-end.

This approach is a mistake for an on-premise system, and it’s definitely a problem for a distributed architecture. You simply do not own all of the components from end to end in a cloud environment, nor are you always able to attach a debugger or other remote monitoring tools to the various areas within the code path. So you need to make sure that from the very outset of your design that you build in diagnostics. My personal preference is to build a system such that a control file turns on deeper information gathering from the system, up to a minimal level.

When I do that, I set a high level of logging, a medium level, and a moderate level. I normally use the deepest level of information during the testing and acceptance phase of the deployment, then switch to moderate and then the least level of information gathering. Also in my design I often set an error condition to begin gathering the deeper information along with the exception, where possible.

There are decisions you need to make as to where to store the diagnostics (many operations in the cloud cost money), how often you collect them, and so on. You can get a quick overview on using the diagnostics that come with Windows Azure here: http://www.azuresupport.com/2010/03/getting-started-with-windows-azure-diagnostics-and-monitoring/ This is where you should start first. More detail on that: http://msdn.microsoft.com/en-us/library/gg433048.aspx

My friend Dave Pallman has a great tool he’s released for free: http://davidpallmann.blogspot.com/2009/03/azure-application-monitor-now-on.html

If the issue is in storage apps: http://social.msdn.microsoft.com/Forums/en-US/windowsazuredata/thread/d84ba34b-b0e0-4961-a167-bbe7618beb83

If you have System Center, this is the quickest and easiest way to implement the monitoring – really handy: http://pinpoint.microsoft.com/en-us/applications/windows-azure-application-monitoring-management-pack-release-candidate-12884907699

Cloud Computing and the Importance of Code Diagrams

BuckWoody — Tue, 03 May 2011 13:59:20 GMT

Most mature development shops use various code diagrams to give a symbolic representation of high-level and database code structures. Standards such as Business Process Model Notation (BPMN), Entity Relationship Diagrams (ERD) and the Unified Modeling Language (UML) are a few I use all the time.

In the Distributed Computing (Cloud Computing) paradigm, these three diagrams (or their equivalent) become essential. In the past, I’ve been able to rely on a single architecture where my code will run. I understand the servers, the networking and the path the code takes between the client and the components within that architecture.

With Distributed Computing (DC), the architecture changes. In fact, the reason I use the term “Distributed Computing” instead of “Cloud Computing” most often (except in the title of this post, as you can see) is that I feel it’s more technically accurate about how we write code. I don’t view DC coding as an “all or nothing” exercise – I view it as just another option to solve a computing problem. A “hybrid” approach, where I mix in the strengths of a cloud provider is often a great way to leverage the best cost, performance and other advantages of each part of your solution. It can also help keep data secure, provide options for High Availability and Disaster Recovery, and more.

To gain these advantages, we have to think more about the components of the application rather than a monolithic stack of components in a single architecture. And that brings us to the title of this post…

For us to correctly identify code components, database objects, security paths and other elements, we have to be able to conceptualize them. And that’s where those diagrams come into play. Starting with some sort of business or organizational need, we can use BPMN or UML Actor diagrams to explain what the program needs to do. That helps segregate the security and location requirements. For instance, if the BPMN shows a data access to Private Information, we can evaluate the need for an on-premise system that is federated to a DC provider. If the business users need global access, we can decide whether to set up a VPN to allow access to an on-premise system or whether a login component can be used on the web.

After determining the flow of the program, move on to the data the system will store. In the case of Windows and SQL Azure, there are several options for storing data. In the past, I’ve often selected a single storage type, such as an RDBMS, and stored program data there. Now we can store in multiple formats, in multiple locations and more. The ERD is pivotal, because it defines data types, which can help decisions around where things go. Another important aspect to the data decision which is not covered in an ERD (but perhaps should be) is the estimated size and growth of a datum, since that can also drive the decision on where to put a data component.

From there, the UML document helps me understand where each computing element can live. There are strengths for each type of computing, and using the UML diagram I can place each code component in the best environment for speed, security and other considerations.

So in the new Distributed Computing world, these graphical documents do much more than just help design the application – they can help define the architecture as well.

SQL Server Best Practices: Protect CmdExec

BuckWoody — Thu, 03 Dec 2009 15:49:34 GMT

In SQL Server, there are times that you need to do things in the operating system, and to allow that there is a feature called CmdExec. This is not always a good thing –whenever you leave the confines of SQL Server and go out to the operating system, you can cause issues, not the least of which are security-related.

This best practice is primarily aimed at SQL Server 2000 – in SQL Server 2005 and higher, you’ll have these as job step types in SQL Server Agent (or ActiveX). What you should to do is ensure that only the sysadmins role can run CmdExec job steps.

In SQL Server 2005 and higher, you should use other methods to work with the operating system, such as SQL CLR or PowerShell to handle that with better safety and security.

What is a physical database?

drsql — Thu, 11 Jun 2009 22:26:36 GMT

A bit of terminology that gets beaten to death is that of the “physical” database. I would think most every DBA uses this term (I do), but…to mean what? I think there are two common utilizations:

The layer of tables, constraints, indexes, etc used to store data
The actual on-disk structures.

Frankly, until 3 years ago, I used the first interpretation. However, I was beaten up pretty badly by a few people whom I don’t really remember (I think Anith Sen was one of them.) The problem is, I was scolded, “physical” already had a meaning, given it by the “founder” himself, EF Codd.

So, checking his 12 Rules, Codd stated the following two things:

Rule 8: Physical data independence:

Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.

Rule 9: Logical data independence:

Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.

And actually, the implementation layer really is the logical model if you follow his terminology since his rules were pertaining to the relational model and not the entire design process. This article says it better than I can in a long blog, but I am not sure about that URL (mac.com?):

http://homepage.mac.com/s_lott/iblog/architecture/C465799452/E20080301143528/

The physical layer of a relational database occurs down at the file system level. Codd's "Rule 8" (Physical Data Independence) says that the things we're designing in ERwin (and similar tools) are the things our application depends on. These are not physical in nature, but are the relational implementation.

So the thing I am trying to say is that physical means that a little 5 volt charge is sitting there representing a bit of data in the physical world. I like the term logical to mean implementation platform non-specific.. The thing in the middle is the SQL Server/Relational implementation specific model. It may take liberties to optimize for SQL Server, but it is not physical. That is were partitioning. indexing, filegroups, etc come in. Changes to this layer ought never be noticable by the application.

I guess in the comments, I ought to expect a good number of replies that might start to answer the question. Does it matter? Is it only semantics? Hey if you don’t think semantics matter, I hope that when you find yourself drowning that the person who has the choice of tossing you a life preserver or a sack of door knobs interprets the meaning of your cry for help in the way you intended. You would hate to find yourself at the bottom of a lake thinking “hmm, I wonder why they did that? Did they hate me, of just mis-interpret the meaning of my sentence?"

disallow results from triggers

drsql — Sun, 07 Jun 2009 02:38:06 GMT

A setting that I noticed a while back when looking at sys.configurations was disallow results from triggers. Triggers are one of my favorite subjects, and you will find a lot of good uses of them in my book (triggers are also well named, as poor usage of them will allow you to shoot your foot off in a heartbeat as I discussed in my earlier post: http://sqlblog.com/blogs/louis_davidson/archive/2008/07/13/triggers-evil.aspx).

One of the main ways you can mess up your system is to have triggers that return results, often by accident, that the client doesn’t know about. There are three kinds of return values that are interesting:

Result sets
Raiserror messages
Rowcount messages

Also note that this is an overall system configuration. So it will disallow results from triggers in all of your databases. It is never a good idea to return data from a trigger, but it is actually pretty useful for testing. In the following code, I will test this setting and show how it will affect returning data to the client from the aforementioned methods.

What will the setting do? It will raise an error if you try to do a result set. It will automatically suppress rowcount messages (without the need for SET NOCOUNT ON), but will allow error messages (I won’t cover it here, but error messages can cause issues with transactions in triggers, particularly when coupled with TRY…CATCH blocks).

To demonstrate the setting, I will use tempdb. Checking the system setting for your server, use:

SELECT value
FROM sys.configurations
WHERE name = 'disallow results from triggers'

This will probably return, unless you have discovered the setting before I did, in which case you probably aren’t reading any longer, so there.

---------
0

To show you the effect of this setting , let's build the following scenario. The trigger will return 3 types of values

the contents of the table named triggerResult
the rowcount of rows affected from an insert statement like you would normally have in a database
the rowcount of the rows from the insert statement into a temp table

To start, we create 2 tables, one as the “main” table, and another that will hold the results of a side effect causing query:

--primary test table
create table triggerResult
(
    triggerResultId int primary key
)
--holds our side effect to prove the trigger executed
create table triggerResultSideEffect
(
    triggerResultId int
)

Then we will create the trigger that gives us several different types of output

create trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    --returns a result set
    select triggerResultId
    from   triggerResult

    --side effect like you might expect in a trigger
    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end
Now we will test out the trigger by inserting one row into the triggerResult table:

insert into triggerResult
values (1)

This returns (the final rows affected is from the original statement):

triggerResultId
---------------
1

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Now try a multi-row operation, to see the difference

insert into triggerResult
values (2),(3),(4)

This returns the following (which you can clearly now see the different number of rows that were affected by each statement, 4 from the one that returns all of the rows in the table, 3 from the side effect query that inserts the rows from the inserted table.

triggerResultId
---------------
1
2
3
4

(4 row(s) affected)

(3 row(s) affected)

(1 row(s) affected)

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(3 row(s) affected)

Now, change the setting to disallow trigger results (you may need to do allow advanced options)

exec sp_configure 'show advanced options',1
RECONFIGURE
exec sp_configure 'disallow results from triggers',1
RECONFIGURE

Now, just to make sure of where our data is, we know that there are 4 rows in the triggerResult table. Let’s check the triggerResultSideEffect table contents:

SELECT *
FROM triggerResultSideEffect
ORDER BY triggerResultId

This returns, showing all of the values we have inserted:

triggerResultId
---------------
1
2
3
4

Now, trying to run the statement with the same trigger:

insert into triggerResult
values (5)

This will cause the following error message:

Msg 524, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 6
A trigger returned a resultset and the server option 'disallow results from triggers' is true.

Altering the trigger, just remove the statement that returns a result set:

alter trigger triggerResult$insertTrigger
on triggerResult
after insert
as
begin
    ----returns a result set
    --select triggerResultId
    --from   triggerResult
    --side effect like you might expect in a trigger

    insert into triggerResultSideEffect (triggerResultId)
    select triggerResultId
    from   inserted

    --just to get a fixed rowcount output
    declare @test table (value char(1))
    insert into @test
    values (1)

    --and a couple of errors
    raiserror ('Low',10,1)
    raiserror ('Normal',16,1)

end

Now, re-executing the statement with no results being returned:

insert into triggerResult
values (5)

This simply returns the error message that are returned, and the rows affected message from the insert statement:

Low
Msg 50000, Level 16, State 1, Procedure triggerResult$insertTrigger, Line 22
Normal

(1 row(s) affected)

Just to make sure that the rows were created:

select *
from triggerResult

select *
from triggerResultSideEffect

This returns:

triggerResultId
---------------
1
2
3
4
5

(5 row(s) affected)

triggerResultId
---------------
1
4
3
2
5

(5 row(s) affected)

Which shows that the data was inserted..

Note that books online states: This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you set this value to 1.

This means that the ability to return result sets from a trigger will go away in the next version of SQL Server. Returning results from a trigger is certainly not something that has ever been a good practice, so if this does in fact occur, it is a good setting to set in your development servers and begin to use.

Data correctness during the development process

drsql — Tue, 10 Oct 2006 00:02:09 GMT

During all phases of development, particularly when building a completely new system, it is very common to have a need for data to test with. As important as this is for a UI system, the need is doubled is when you are working on a reporting system. While Everyone knows that the process of getting test data can be a fickle thing during the development process. All programmers, relational and functional alike, when we test our code, we tend to create some meaningless data, usually only a row or two, to validate whatever table constraints, triggers, procedures, form, screen, batch process, etc that we are building, but that data, generally speaking of course, likely bears little resemblance to "real" data. But it is fine for the development process.

To write queries, everyone (including myself, if I can be honest with you) want some reasonable data in a table to write queries against. Consider that the overarching mantra of the forum/newsgroup question answerer is: "provide me a table and some test data..." so we can test/write queries against it. Without some data, answering a question for a person can be like target shooting with little idea of where the target is actually located.

The important question is, what does "some" mean, as in "I need some data". I have worked on this mentally for years, as I struggled to put a label on the data correctness factor, because it it such a hard process balancing the needs of the programmers building the database and code that uses it with the programmers building reporting. The process is a bit like having builders building the foundation of a house while another group of folks setting up for a full dinner party on the floor above. Never going to happen in most any industry other than ours.

Lately, on a large reporting project I have been working on, this frustration has reached dizzying heights. We have data conversions from an existing system going on all of the time, working to get the final data right for the final cutover. We have gone over and over throughout the process of the entire project with each "new" set of data it was questioned how "right" the data was. Each of these sets of data was good enough to use in some capacity, but rarely if you need fine, verifiable detail going back to the original system, generally to match up to some existing report. The whole process can be really frustrating, though somewhat necessary.

So in order to label this data, I will attempt to coin a few names for some of the ways our data ends up in the system during the process, if for no other reason than I can begin to label data in our systems in this manner for developers who are stuck using data in various forms, even if it does drive them a bit nutty. The terms I am tossing out to define test data quality are: Scribble, Reasonable, Representative and Live/Real. I will define these as:

Scribble - Just data that is entered with no concern for other users. probably very fake looking, including values like 'ahdflka', or 'aafdfiduah' or even 'alsjdflaj;' (that's kind of fun...) No real value other than being there for you to mess around with to try updates, deletes, etc. Reporting value is almost nil, even for the person who thinks very abstractly.

Reasonable - Valid data based on business rules, but likely may not resemble reality. The purpose of this type of data is often to test one area of functionality, like to see some data on the screen. It might just be a set of rows that have been hand manufactured to look like possible data, even if it is not a combination of values that has ever, or will ever exist. This kind of data is built relatively easy, either using the user interface, or just by creating data using random number generation (like for keys based on numeric surrogates). You must think abstractly when using this data, since nothing is likely to be correct. Random numbers are certainly no great shakes when it comes to correctness, and a person typing data in for testing is very often going to get very lazy and start entering gibberish (like 'aslkfal', or 'askjakjcb' (that never loses it's fun factor))

Representative Subset - A set of data that is incomplete, but resembles, or is a subset of, reality. This is the type of data that we all want as developers. It looks good, feels good, and other than magnitude, it is real data. It is also far harder to create. For existing systems, it might be converted data, or for completely new systems, it is probably hand entered, likely for the QA team.

Real - Real live data that, if you weren't in a development area would in fact be usable for business. It is likely a snapshot up to some point in time, but as of a given point in time, this is what could be in production. This data is more likely in a system that you are building as an upgrade to another system. If this is a wholly new system, there is some chance that your system will start out empty. At best clerks might be hired to copy data that is currently held in file folders (paper, remember it?) into the online system, but they might not do that until after the "go live" date.

Maybe by labeling data as "reasonable" it would be clear to folks that there is data to browse, try out, etc, but there is no way possible that they can validate this data against anything other than the business rules. This is especially an issue with a reporting source like a data warehouse. It is expected in the minds of all users that data warehouse data will be based on a verifiable source, though when in the development phase, this clearly might not be the case.

Have better ideas? Better names? Want to show me up and look cool to your nerdy friends? Please leave comments :)

Crossposted to http://drsql.spaces.live.com