Search results matching tags 'Development' and 'programming'

Cloud Computing - just get started already!

BuckWoody — Tue, 30 Oct 2012 14:43:27 GMT

OK - you've been hearing about "cloud" (I really dislike that term, but whatever) for over two years. You've equated it with just throwing some VM's in some vendor's datacenter - which is certainly part of it, but not the whole story. There's a whole world of - wait for it - *coding* out there that you should be working on. If you're a developer, this is just a set of servers with operating systems and the runtime layer (like.NET, Java, PHP, etc.) that you can deploy code to and have it run. It can expand in a horizontal way, allowing massive - and I really, honestly mean massive, not just marketing talk kind of scale. We see this every day.

If you're not a developer, well, now's the time to learn. Explore a little. Try it.

We'll help you. There's a free conference you can attend in November, and you can sign up for it now. It's all on-line, and the tools you need to code are free.

Put down Facebook and Twitter for a minute - go sign up. Learn. Do. :)

See you there. http://www.windowsazureconf.net/

Please Vote: Windowing Enhancements in SQL Server

Mike C — Fri, 20 Nov 2009 03:45:00 GMT

Back in January 2007, SQL guru Itzik Ben-Gan posted a series of MS Connect enhancement requests concerning windowing function enhancements. Those who have used the ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE() functions on SQL 2005 and 2008, you already know how useful they are. They simplify code and can improve performance considerably over the alternatives, which usually include self-joins, temp tables and/or cursors in various combinations.

Well, the windowing functionality that you've seen in SQL 2005 and 2008 is just the tip of the iceberg. The ISO SQL standard actually defines several additional options for these functions that SQL Server doesn't yet support. These additional options allow you to do some pretty amazing calculations. The ROWS and RANGE window subclauses that the standard defines allows you to perform "sliding window" calculations; the ORDER BY clause for aggregate functions which simplifies complex running sum (and other) calculations.

As a SQL developer or DBA, these enhancements will simplify your life. But don't take my word for it - read Itzik's white paper at http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc. Then let Microsoft know you want these enhancements in SQL Server by voting on Itzik's enhancement requests at the following links:

Progressive Ordered Calculations: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254397

FIRST_VALUE, LAST_VALUE functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254395

DISTINCT clause for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254393

ROWS and RANGE window subclauses: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254392

Vector expressions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254391

TOP OVER: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254390

LAG and LEAD functions: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254388

ORDER BY for aggregates: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=254387

Read Itzik's article, vote on the Connect items, and spread the word!

"Cloning" Symmetric Keys

Mike C — Thu, 18 Jun 2009 01:40:00 GMT

It's well-known by now that SQL Server 2005 and 2008 include new encryption-related statements that allow you to create and administer encryption keys. You can use CREATE CERTIFICATE to create or import a certificate or DROP ASYMMETRIC KEY to remove an asymmetric key from the database, for instance. One of the interesting ommissions from the T-SQL encryption statements is the statements necessary to backup and restore a symmetric key. Why would you want to do this? I can think of a couple of reasons off the top of my head:

You need to backup symmetric keys (and all other encryption keys, in fact) as part of an overall disaster recovery (DR) program. If a server needs to be rebuilt you obviously need a way to restore all encryption keys.
You need to implement the same symmetric keys on multiple servers. There could be a couple of reasons for this -- you might encrypt data on one server and decrypt it on another, or you might be load-balancing across a server farm and need identical encryption keys on multiple servers simultaneously.

It seems like a bit of an oversight to not include BACKUP and RESTORE SYMMETRIC KEY options in T-SQL, but in practice you can effectively achieve the same end results with the standard CREATE SYMMETRIC KEY statement. Basically the CREATE SYMMETRIC KEY statement gives you an option to "clone" the exact same symmetric key on any SQL Server 2005 or 2008 instance, anywhere, at any time. To create a cloneable symmetric key you need to specify two special CREATE SYMMETRIC KEY options:

The IDENTITY_VALUE option, which SQL Server uses to generate a GUID (uniqueidentifier) for the key
The KEY_SOURCE option, which SQL Server uses as key material to generate the actual key

As long as you specify the same values for the IDENTITY_VALUE and KEY_SOURCE options (and the same ALGORITHM), your symmetric key will be exactly the same no matter where, when, or how many times you create it. To be honest, if I were creating a list, always use IDENTITY_VALUE and KEY_SOURCE would be listed in the top 10 list of SQL Server encryption best practices.

Here's a quick sample demonstrating the CREATE SYMMETRIC KEY statement with IDENTITY_VALUE and KEY_SOURCE specified:

CREATE SYMMETRIC KEY test_aes128_key
WITH KEY_SOURCE = 'I am the very model of a modern major general',
IDENTITY_VALUE = 'E pluribus unum',
ALGORITHM = AES_128
ENCRYPTION BY PASSWORD = 'p@$$w0rd';

This CREATE SYMMETRIC KEY statement will create the same symmetric encryption key on any SQL Server 2005 or 2008 instance on which you run it. This brings up another point, about security. If the same KEY_SOURCE and IDENTITY_VALUE options can create the exact same encryption key on any of your servers, they will create the exact same encryption key on any of my servers, or any server owned by any hacker anywhere in the world. So once you've run your CREATE SYMMETRIC KEY statement, the IDENTITY_VALUE and KEY_SOURCE need to be handled like any other secure information. Don't leave them lying around where just anyone can access them. Store them with your certificates, key backups, and other confidential materials in a secure off-site location.

So what happens when you don't specify the IDENTITY_VALUE and KEY_SOURCE options? Well, basically SQL Server generates an unpredictable GUID to identify the symmetric key and the encryption key source material is randomly generated. Basically you'll never regenerate the exact same key again. Ever. There could be situations where this would be handy. The concept of "session keys" comes to mind. A session key is basically a "temporary" key that's only required to encrypt data for a user during a single session. Since it only exists for the life of the session, a totally randomly-generated key is just fine.

For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default. If you didn't want to specify both of these options there should be another option/indicator specifically to say that you want these options generated randomly. At any rate, it's a good idea to get into the habit of treating these options as if they are mandatory unless they have a very specific special-purpose requirement (e.g., "session keys").

Calvinball Projects

Mike C — Wed, 03 Jun 2009 02:20:00 GMT

I used to be a huge Calvin and Hobbes fan, and when a friend today reminded me of the old game of "Calvinball" I couldn't help drawing similarities to database and programming projects.

Here are the rules to Calvinball as they apply to database development projects:

1.1. All players must wear a Calvinball mask (See Calvinball Equipment - 2.1). No one questions the masks.

Anonymity is key to any development effort. Never put your name on any code you allegedly generated. Even the simple act of documenting code can give away too much information about you and your mental state.

1.2. Any player may declare a new rule at any point in the game. The player may do this audibly or silently depending on what zone (Refer to Rule 1.5) the player is in.

Managers in particular are allowed--no encouraged--to create new rules and add new features that are way out of scope. Particularly during the implementation phase of the project. The later the better, I always say. You may wish to tell others about the new rule or the slew of new requirements. Or not. Your choice.

1.3. A player may use the Calvinball (See Calvinball Equipment - 2.2) in any way the player see fits, from causal injury to self-reward.

Feel free to substitute a mouse, iPod, or old-style 21" CRT monitor for the Calvinball here. I highly recommend you use some else's though.

1.4. Any penalty legislation may be in the form of pain, embarassment, or any other abasement the rulee deems fit to impose on his opponent.

If a developer makes a mistake managers and coworkers are encouraged to apply the penalty legislation as described in 1.4.

1.5. The Calvinball Field (See Calvinball Equipment - 2.3) should consist of areas, or zones, which are governed by a set of rules declared spontaneously and inconsistently by players. Zones may be appear and disappear as often and wherever the player decides. Zones are often named for their effect. For example, a corollary zone would enable a player to make a corollary (sub-rule) to any rule that has benn, will be, or might be declared. A pernicious poem place would require the intruder to do what the name implies. Or an opposite zone would enable a player to declare reverse playibility on the others. (Remember, the player would declare this zone oppositely by not declaring it.)

Developers should be moved about the office, or on client sites, spontaneously and inconsistently to random seats by managers, coworkers, or janitorial staff as necessary.

1.6. Flags (Calvinball Equipment 2.3) shall be named by players whom shall also assign the power and rules which shall govern that flag for particular moment in that particular game.

Any employee or manager should adopt transient superpowers at random moments throughout the day. It's cooler and more effective if you have a symbolic flag, magic staff, cape or conch shell to reinforce your symbolic (though transient) authority.

1.7. Songs are an integral part of Calvinball and verses must be sung spontaneously through the game when randomly assigned events occur. These random events will be named and pointed out after the player causes the event.

Spontaneously singing in the office, particularly if your singing voice isn't very good, will help deter both friends and foes from bothering you.

1.8. Score may be kept or disregarded. In the event that score is kept, it shall have no bearing on the game nor shall it have any logical consistency to it. (Legal scores include 'Q to 12', 'BW-109 to YU-34, and 'Nosebleed to Trousers'.)

"Consistency is the hobgoblin of small minds." -- Walt Whitman

1.9. Any rule above that is carried out during the course of the game may never be used again in the event that it causes the same result as a previous game. Calvinball games may never be played the same way twice (Figure 1.9)

Any type of repetition throughout a project indicates a pattern, and patterns make for a boring and predictable life. As Vince the SlapChop guy said, "you're gonna have an exciting life."

Calvinball Equipment

2.1. Mask - All participants are required to wear a mask

Remember, anonymity is your friend.

2.2. Calvinball - A Calvinball may be a soccerball, volleyball, or any other reasonable or unreasonable, spherical or non-spherical object.

Non-spherical objects include monitors, computers, printers, paperclips, books, chairs, and just about anything else you can find in the office, on the subway, or on the sidewalk.

2.3. Calvinball Field - The Calvinball Field should be any well-sized field, preferably with trees, rocks, grass, creeks, and other natural hindrances to health.

Offices that have bathrooms with leaky toilets are a good substitute.

2.4. Miscellaneous - Other optional equipment include flags, wickets (especially of the time-fracture variety), and anything else the players wish to include.

Pens, paper, pocket protectors, iPod cables, and cases of Red Bull are recommended.

Next time you're on a project that resembles a game of Calvinball, just think how much worse it could be :)

For full Calvinball rules, check out: http://www.bartel.org/calvinball/

Practical SQL Server XML: Part 2

Mike C — Wed, 04 Mar 2009 02:28:00 GMT

In response to a challenge from Pinal Dave, a fellow MVP and a winner of an award for his blog at the MVP Summit 2009, I am writing a couple of articles to demonstrate practical uses for SQL Server’s XML capabilities. (Dave’s great blog is at http://blog.sqlauthority.com/).

Dave posted the first post in this series over at http://blog.sqlauthority.com/2009/03/17/sql-server-practical-sql-server-xml-part-one-query-plan-cache-and-cost-of-operations-in-the-cache/, describing how to shred the XML query plans in the SQL Server query plan cache. In this post I’ll talk about sending multiple parameters, and even parameter sets, to SQL Server via XML. SQL Server MVP Jacob Sebastian is finishing up the series with a post on his blog at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html.

One of the most common requests in the SQL Server newsgroups is “how do I send a delimited list of values to my stored procedures?” There are many creative answers, including SQL MVP Erland Sommarskog’s popular article “Arrays and Lists in SQL Server” (http://www.sommarskog.se/arrays-in-sql-2005.html). Erland touches on the method of XML shredding that I’ll cover here.

For this example I’ll use the AdventureWorks 2008 database. This sample database is available for download from CodePlex (http://sqlserversamples.codeplex.com/) for SQL Server 2008. For this example I’ll first create a procedure that inserts a single record that must be put in two separate tables:

CREATE PROCEDURE Person.AddPerson

@PersonType nchar(2) = NULL,

@NameStyle dbo.NameStyle = NULL,

@Title nvarchar(8) = NULL,

@FirstName dbo.Name = NULL,

@MiddleName dbo.Name = NULL,

@LastName dbo.Name = NULL,

@Suffix nvarchar(10) = NULL,

@EmailPromotion int = NULL,

@EmailAddress nvarchar(50) = NULL

BEGIN

-- Get a new BusinessEntityID

DECLARE @BusinessEntityID int;

INSERT INTO Person.BusinessEntity(rowguid)

VALUES (NEWID());

SET @BusinessEntityID = SCOPE_IDENTITY();

-- Insert Person

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

VALUES

(

@BusinessEntityID,

@PersonType,

@NameStyle,

@Title,

@FirstName,

@MiddleName,

@LastName,

@Suffix,

@EmailPromotion

);

-- Insert Email Address

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

VALUES

(

@BusinessEntityID,

@EmailAddress

);

END

The procedure simply accepts several parameters that reflect a single Person record. The first step is to insert a row into the Person.BusinessEntity table to get a business entity ID. Then the remaining parameter values are inserted into the Person.Person and Person.EmailAddress tables. Simple enough. But what if you want to insert 2 Person records? You can round-trip to the server twice with two calls to this procedure, or you can double up the number of parameters and add the extra code in the procedure to deal with it.

But then again, what if you want to add 100 Person records? How about a thousand or a million? That’s a lot of round trips to the server or a lot of extra parameters to add (SQL Server actually maxes out at around 2,100 parameters). If you have SQL Server 2008 and you’re using a .NET client you can use table-valued parameters. But what if you’re not using a .NET client or SQL 2005 is your target database? In that case you can use XML to accomplish the same thing.

Using XML to add data to a single table is normally a pretty straightforward shredding operation, but we’re actually touching three different tables in this case. And one of the tables generates an identity value that the other two tables need. This means the code is not so simple as a straight single-table insert, especially if you decide not to use cursors. Here’s my XML version of adding a Person:

CREATE PROCEDURE Person.AddPersonXML

@People xml = NULL

BEGIN

-- Generate and store all new business entity IDs

DECLARE @TemporaryIDs TABLE

(

BusinessEntityID int NOT NULL

);

-- Number the XML nodes and generate business entity IDs

WITH NodeNumbers

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType', 'nchar(2)')) AS NodeNumber

FROM @People.nodes('//Person') T(C)

)

INSERT INTO Person.BusinessEntity

(

rowguid

)

OUTPUT

inserted.BusinessEntityID

INTO @TemporaryIDs

(

BusinessEntityID

)

SELECT NEWID()

FROM NodeNumbers nn;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/PersonType)[1]', N'nchar(2)') AS PersonType,

pn.PersonData.value(N'(Person/NameStyle)[1]', N'dbo.NameStyle') AS NameStyle,

pn.PersonData.value(N'(Person/Title)[1]', N'nvarchar(8)') AS Title,

pn.PersonData.value(N'(Person/FirstName)[1]', N'dbo.Name') AS FirstName,

pn.PersonData.value(N'(Person/MiddleName)[1]', N'dbo.Name') AS MiddleName,

pn.PersonData.value(N'(Person/LastName)[1]', N'dbo.Name') AS LastName,

pn.PersonData.value(N'(Person/Suffix)[1]', N'nvarchar(10)') AS Suffix,

pn.PersonData.value(N'(Person/EmailPromotion)[1]', N'int') AS EmailPromotion

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.Person

(

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

)

SELECT

BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion

FROM GetPersonData;

WITH PersonNodes

(

SELECT

ROW_NUMBER() OVER (ORDER BY C.value('PersonType[1]', 'nchar(2)')) AS JoinID,

C.query('.') AS PersonData

FROM @People.nodes('//Person') T(C)

BusinessEntityIDs

(

SELECT

ROW_NUMBER() OVER(ORDER BY BusinessEntityID) AS JoinID,

BusinessEntityID

FROM @TemporaryIDs

GetPersonData

(

SELECT

bei.BusinessEntityID,

pn.PersonData.value(N'(Person/EmailAddress)[1]', N'nvarchar(50)') AS EmailAddress

FROM PersonNodes pn

INNER JOIN BusinessEntityIDs bei

ON pn.JoinID = bei.JoinID

)

INSERT INTO Person.EmailAddress

(

BusinessEntityID,

EmailAddress

)

SELECT

BusinessEntityID,

EmailAddress

FROM GetPersonData;

END

You notice in this example that we first have to get business entity IDs for each person. I use a table variable and the DML OUTPUT clause to grab the business entity IDs ahead of time. Then I shred the XML in two steps, once to put relevant data in the Person.Person table and another to put the email addresses in Person.EmailAddress. The procedure call looks like this:

EXEC Person.AddPersonXML N'<Person>

<FirstName>George</FirstName>

<LastName>Jetson</LastName>

<EmailAddress>George.Jetson@spacelysprockets.com</EmailAddress>

</Person>

<LastName>Flintstone</LastName>

<EmailAddress>Fred.Flintstone@sladegravelpit.com</EmailAddress>

</Person>';

As you can see, we can easily add as many people as you want with a single XML parameter. That is, so long as you respect the 2.1 GB size limit on SQL Server LOB data types like XML. Also since you are using XML you can use any client application in any language that can generate XML character data; you’re not limited to just any .NET-specific functionality.

You can verify that the people above were added with a few simple queries:

SELECT *
FROM Person.Person
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.EmailAddress
WHERE BusinessEntityID > 20777;

SELECT *
FROM Person.BusinessEntity
WHERE BusinessEntityID > 20777;

The results look like the following:

In the third article of this series at http://blog.beyondrelational.com/2009/03/practical-sql-server-xml-part-three.html, MVP and author Jacob Sebastian will show you how to add XML Schema validations to your xml data type parameter sets.