Visual Studio Magazine

Data Driverby David Ramel

Database Wars: Who Has the Edge?

I found all kinds of interesting stuff in Information Technology Intelligence Corp.'s recent 2010 Database Deployment Trends Survey, which predicts more companies will be upgrading their infrastructure this year, thus allowing database vendors a rare chance to persuade customers to switch their RDBMS allegiances.

Usually, the cost and hassle of switching database systems is prohibitive.

In a blog posted Thursday titled "Database Competition Heats Up," ITIC wrote:

A wholesale switch from one platform to another requires significant capital expenditure monies. Additionally, the business must also invest a lot of time and energy in converting to a new platform, testing new applications, rewriting scripts and re-training DBAs and getting them certified on the new environment. For CIOs, CTOs and IT departments this prospect has roughly the same appeal as having root canal without Novocain.

But with companies scrambling to remain competitive after two and half years of cutting costs amid the economic downturn, they will be upgrading their systems and possibly switching their vendors, ITIC said.

The research firm concluded: "What will distinguish the DBMS market this year is that the always intense and vociferous vendor rivalries will heat up even more over the next 12 months."

So I wondered which of the Big 4 database vendors have an advantage going forward, specifically related to the developer community. So I asked.

Here is the reply from ITIC principal Laura DiDio:

IBM and Microsoft are very well positioned at present and over the next 12 months. IBM's DB community is solid, stable and very loyal.

The fact that 72 percent of survey respondents said they hadn't switched DB platforms in the last three years and the fact that it's harder for very large enterprises in market segments like banking, financial and insurance (traditional IBM strongholds) to switch because of the legacy investment, bodes well for IBM. Many of these enterprises have mature DB environments that are stable for many years.

In Microsoft's case the improvements to the SQL Server 2008 platform which make it more enterprise ready and the fact that Microsoft has a very strong developer community, combined with a vibrant reseller channel, puts Microsoft in a good position to expand its presence into SME and enterprise organizations.

As for Oracle, the developer community -- particularly Open Source developers -- are watching and waiting to see if Oracle will remain true to its word and continue to support and develop for MySQL and integrate that platform into their other offerings as they've promised.

Oracle also faces other challenges. Their many acquisitions over the last three years could prove to be an unwelcome distraction and cause the company to not provide enough support to its developers. That said, on the plus side, Oracle's most recent second quarter financials were very strong. This was largely attributable to strong demand for new software license renewals and maintenance plans.

In the most recent quarter, Oracle reported revenue of $5.858 billion. Of that figure, $3.247 billion was software maintenance revenue and $1.653 billion came from new software licenses. Common sense dictates that if so many of Oracle's customers are upgrading their DB licenses, then they'll be upgrading their applications as well. So, while Oracle does have some specific challenges that IBM and Microsoft will undoubtedly attempt to exploit, it remains very strong.

Sybase has a strong following but it lacks the deep pockets of IBM or Microsoft to market on the same scale. And marketing and positioning will be key elements of DB success in 2010. It's going to be a real dogfight for market share and licensing renewals.

Are you planning an upgrade? Might you switch vendors? Who looks good to you? Comment here or send me an e-mail.

Posted by David Ramel on 02/22/2010 at 6:09 PM2 comments

SQL Saturdays: Free, Fun, Informative

To start up SQL Server Management Studio faster, you can turn off Online Help, error/usage reporting and certificate revocation checking. It also helps to exclude the SqlWB.exe and Ssms.exe executables from anti-virus and anti-spyware tools.

Those are all tips from SQL Server MVP Aaron Bertrand, who presented at the recent SQL Saturday #34 Boston 2010 event (actually held in Waltham, Mass.).

Those are all beginner tips, of course, but dozens of other presentations targeted intermediate and advanced users, covering diverse subjects such as career tips, storage design and SQL injection attacks.

SQL Saturdays are held across the country, and chances are one is coming to a city near you.

The SQL Saturday brand name and Web site is licensed free of charge to local groups that want to organize an event, courtesy of Andy Warren, Brian Knight and Steve Jones, who started them off in 2007. The all-day events are a great way to network with peers and learn from experts.

For Adam Machanic, head of the local SQL Server user group and organizer of SQL Saturday #34 (also called New England Data Camp), the payoff for all of his hard work is the community participation.

"It was great to see so many new faces--people I've never seen come out to the user group--show up on a Saturday morning for a full-day, community-driven event," Machanic said of the data camp, which attracted some 350 attendees. "Some of these people even agreed to show up at 7 a.m. to assist with setup. For someone to do that without any prior interaction with me or the local SQL Server community is truly amazing and I think really illustrates the power and community nature of these events."

And community-driven certainly doesn’t mean quality-lacking. He pointed out that an entire track was dedicated to performance tuning and three SQL Server MVPs and two Microsoft employees were on the presenter roster.

To see for yourself, many of the session materials can be downloaded.

The next event is #33 in Charlotte on March 6 (yes, after #34; I forgot to ask why the events’ numbers have no correlation to the calendar schedule). Fifteen more are scheduled through August. Check them out.

Do you have any experience with SQL Saturdays? Or any other similar community-driven events? Comment here or send me an e-mail.

Posted by David Ramel on 02/11/2010 at 2:45 PM4 comments

Is Security Worth Risking Your Job?

It's 1:30 a.m., you're sweating bullets and your head is pounding. You can feel the stress wracking your body as you try to concentrate. The code-complete deadline for your part of the module is coming up, and the bean-counters are on everyone's back. Product has to ship on time -- period.

You've almost got that last, thorny problem licked, but testing all the different security scenarios with this new cloud-based database-conversion tool takes soooo much time. A potential "connection string pollution" vulnerability has popped up in the back of your mind, but you're unsure how dangerous it is -- or even if it's possible. It's a brand-new threat. It will take forever to investigate.

What the heck. No one will ever find it. You can't be the one holding things up -- again. There are thousands of talented coders out there who would jump at the chance to slide into your job at half your salary. You've got mouths to feed. Let QA worry about it.

Could this happen? Has it? Who's to blame for the sorry state of application security these days?

Here's a chilling quote:

"Security is not something app developers have prioritized in the past. Their focus has been getting a product that has a competitive edge in terms of features and functionality to market as quickly as possible. That's not a criticism, it's just a factor of commercial priority."

That just happens to have come from the world's biggest app dev shop, shipping more "product" than anyone else: Microsoft. David Ladd, principal security program manager at Microsoft, said it in a news release earlier this week announcing that the company is "helping the developer community by giving away elements of its Security Development Lifecycle process."

It couldn't come at a better time. "Today, in the middle of the worst economic downturn in thirty years, information security has an enormously important role to play," reads the 2010 Global State of Information Security Survey from PricewaterhouseCoopers.

"Not surprisingly, security spending is under pressure," the report states. "Most executives are eyeing strategies to cancel, defer or downsize security-related initiatives."

But the report is generally optimistic, finding that "Global leaders appear to be 'protecting' the information function from budget cuts -- but also placing it under intensive pressure to 'perform.' "

That makes sense to me. It would have to be the short-sighted executive indeed who would risk all the associated costs of a data breach just to meet a deadline. How bad can those costs be? Just ask TJX.

Then again, that executive has mouths to feed, and "commercial priority" and "intense pressure to perform" could cloud judgment.

What about your shop? Where do the priorities lie? Know any juicy stories about security breaches caused by economic pressures? I'd love to hear about it. Comment here or send me an e-mail. But for goodness' sake, don't put any sensitive information in it. There are bad guys out there.

Posted by David Ramel on 02/04/2010 at 10:56 AM0 comments

Hands-on PowerPivot: To the Cloud

I previously wrote about an interesting demonstration that involved a 44-milliion-record data set imported into PowerPivot, Microsoft's coming "self-service business intelligence" add-in for Excel 2010 and SharePoint 2010.

Connecting to a cloud data service, importing a feed into Excel and culling BI info from the data is remarkably easy. You can try it for free with the Microsoft Office 2010 Beta (requires registration) and PowerPivot for Excel 2010.

Here's how.

Click on the PowerPivot tab in the main Excel menu, then click on the PowerPivot window, Launch button. It will take a few moments to load, displaying the message: "Preparing PowerPivot environment, please wait."

Now in the PowerPivot window, click on From Data Feeds under the Home menu item. You are presented with two options: From Reporting Services and From Other Feeds. Click on the latter. This brings up the Table Import Wizard (Fig. 1), where you input the data feed URL.

Figure 1. Table Import Wizard. (Click image to view larger version.)

You can find online data feeds with a Web search. I used the Open Government Data Initiative, specifically the Crime Incidents data set under the District of Columbia "Container" (Fig. 2).

Figure 2. Crime Incidents data is found in the District of Columbia container. (Click image to view larger version.)

Interestingly, clicking on Advanced brings up a window with "Provider: Microsoft Data Feed Providers for Gemini" as the only option. This is one of several leftover references to the former code-name of the project, Gemini. The Advanced button brings up settings for Context, Initialization, Security and Source that adjust the connection string, along with a Test Connection button.

If you're not into Advanced settings, just paste in the source data set URL, click the Test Connection button, and a window comes up with "Test connection successful" (in a window titled "Gemini Client"; see Fig. 3).

Figure 3. Testing the connection with the cloud data. (Click image to view larger version.)

Click on Next. The Table Import Wizard shows a window where you can "Select Tables and Views" if you just want to download parts of the feed (Fig. 4). There is also a Preview & Filter button that lets you see the data as it will appear and deselect different columns to tailor what you want to download.

Figure 4. Selecting Tables and Views you want to work with. (Click image to view larger version.)

Click Finish. It takes a few seconds to import, then displays "Success"; under Status it shows you how many rows transferred (368 in this case).

Click close, and a PowerPivot window comes up with all the data in tabular format (Fig. 5).

Figure 5. Time for some PowerPivot magic. (Click image to view larger version.)

Click on PivotTable, and you are presented with various options for the number/type of tables to display, with or without charts, vertical or horizontal, and so on. I chose two vertical charts. You are asked whether you want to "Insert Pivot into New Worksheet or Existing Worksheet." Placeholders for charts come up, with a "Gemini Task Pane" (there's that code-name again) that lets you choose which fields to add to your charts, along with options for vertical and horizontal slicers, report filter, legend fields, axis fields and values.

I wanted to see which parts of D.C. are the most dangerous and when, so I chose Offense, Shift and Ward fields from the Crime Incidents data feed (Fig. 6).

Figure 6. Selecting Offense, Shift and Ward fields in the Crime Incidents table.

I then set up tables and charts to show crimes by Ward and by Shift, and with a simple formula constructed a pie chart displaying what percentage of crimes were committed during each shift. My resulting "BI" informs me that most crimes (38 percent) are committed during the Evening shift and most crimes occur in Ward 2 (Fig. 7).

Figure 7. Crimes by Ward and Shift, with a handy pie chart . (Click image to view larger version.)

There you go: practical, hands-on advice from the cloud, presented here free for your personal safety. Don't mess around in D.C.'s Ward 2 during the Evening police shift.

What danger areas are you avoiding these days? Comment below or e-mail me.

Posted by David Ramel on 02/01/2010 at 1:44 PM0 comments

PowerPivot: 44 Million Records in a 5MB File

At the front of a crowded room, Carmen Taglienti stood over his laptop, hovered the cursor over the "Sort" button and clicked. Instantly, some 44 million records in his Excel app were sorted, newest to oldest, top to bottom. That's right: 44 million records. Welcome to the world of "self-service BI."

Taglienti, a Microsoft technology architect, was at the monthly meeting of the New England SQL Server User Group to show off the PowerPivot for Excel 2010 add-in (formerly code-named "Gemini"). He said Microsoft developed PowerPivot to rein in the growing enterprise problem of "spreadmarts," basically tech-savvy users developing their own sophisticated spreadsheets to cull business intelligence out of various data sets and shipping the resulting -- sometimes gigantic -- files all over the company via e-mail, with no organization, security or management.

With PowerPivot, he said, users can create much more useful BI and share it throughout the company via SharePoint 2010, for example (PowerPivot is also a SharePoint add-in).

His presentation elicited some "oohs" and "aahs"from the 40-plus developers at the meeting, with the 44-million-record example generating special interest. Previously, Excel users were limited to using 1 million records.

"It's amazing," said Taglienti, as several people inquired about the demonstration. It did seem hard to believe that 44 million records could be instantly sorted on a laptop.

Granted, he was using a 64-bit machine with 4GB of RAM, but he said the remarkable speed of his demonstration was primarily attributable to compression technology that shrinks huge data sets so that they can be manipulated entirely in-memory. In fact, Taglienti said, the 44 million records were squeezed into an XLSX file of less than 5MB. That also got everybody's attention: 44 million records in a 5MB file. PowerPivot's data engine technology (called VertiPaq) has achieved up to about 20-to-1 compression, he said.

Surprisingly, it turns out that 44 million records is nothing special. There has actually been a 100-million-record PowerPivot experiment. Taglienti also demonstrated how PowerPivot can access data from the cloud. He showed how easy it was to connect, in just a few clicks, to an Open Government Data Initiative data feed and download it into PowerPivot. The OGDI data is hosted -- of course -- in Windows Azure, Microsoft's fledgling cloud platform.

Other usable data sources include several different kinds of databases, files and Microsoft Reporting Services feeds. The strategy behind PowerPivot is "to empower the end user" to do their own data analysis and intuitively build their own custom BI solutions, Taglienti said. This lets IT concentrate more on infrastructure and data management instead of numerous custom BI projects.

He stressed that it's all beta technology at the moment, and Microsoft isn't entirely certain how it will be leveraged in real-world, day-to-day use. He posited the question of whether-if a company has some 10,000 PowerPivot cubes floating around in SharePoint, for example-PowerPivot should have its own server. While questions such as that get sorted out, Microsoft is out in the front lines trying to convince users that, as Taglienti says, "it's a game-changer."

The mood at the meeting seemed to be wait-and-see.

Teresa DeLuca, a Web developer at International Data Group, which is headquartered in Boston, said she didn't anticipate using PowerPivot. But she said, "I do work with pivot tables a lot," so there might be some benefit to checking the product out.

Dean Serrentino, a software developer/consultant at Paradigm Information Systems in Wilmington, Mass., said before the meeting, "I'm totally unfamiliar with it" and that he was at the meeting to learn more about it. "I usually don't go bleeding edge," he said. After the meeting, he said in an e-mail, "I don't anticipate using it for two reasons. First, I think it is too complicated for use by the audience that would most benefit from it. Second, it is an SQL Server Enterprise version feature. Most of my clients are small to medium sized businesses and don't need the features in SS Enterprise, or have the resources to afford it."

Gary Chin, an independent developer in Newton, Mass., said, "I don't see using it immediately, but it's going to come in handy for BI and SQL Server applications ... the whole idea of storing the live data from SQL Server and downloading it down to financial analysts or other users that need to manipulate the data to see what happens within the data."

Some people are more skeptical. Readthis entertaining, semi-contentious exchangebetween one of PowerPivot's founding engineers and a user arguing about what capabilities PowerPivot provides that plain old Excel doesn't.

What do you think? Marketing hype or must-have technology? Have you tried it? Are you going to? Comment here or send me an e-mail.

Posted by David Ramel on 01/22/2010 at 4:49 PM2 comments

On Jobs, Salaries, Trends, Clouds and More

Some interesting salary data shows that there's still a strong need for software and Web developers stateside.

Jobs: Software engineer rated No. 2 on CareerCast.com's recent listing of the top 200 jobs for 2010, ranked on factors such as physical demands, work environment, income, etc.

Web developer was No. 15, and computer programmer was No. 34. (On a sad personal note, the jobs I have held rank no. 65 (publication editor); 79 (public relations executive, though I was a mere "PR specialist" -- didn't even make the list); 184 (newspaper reporter); and 199 (lumberjack -- only a "roustabout" was ranked lower).)

CareerCast.com listed these salaries for the above positions:

Software engineer: $85,139
Web developer: $60,090
Computer programmer: $70,178

That got me wondering about the IT job market, especially related to developers. It wasn't that long ago that everybody was bemoaning the impending death of the profession-at least in the U.S.-because the jobs were going to offshore outsourcers who could pay coders much less than what they were making here.

So I poked around the Web to answer some questions. Just what is the consensus on developer salaries (especially database-related, of course)? What are the hot technologies? Where is the action? What issues are database coders dealing with on the front lines?

Salaries: The Redmond 2009 Salary Survey, in a survey of Microsoft IT compensation, reported salaries of:

Programming project lead (Non-Supervisory): $100,635
Database administrator/developer: $80,894
Programmer/analyst: $78,818
Average base salary of respondents: $83,113

The 2009 IOUG Salary Survey (International Oracle User Group) reported an average base salary of about $96,000 per year for "Oracle technology professionals."

Dice.com reports current average salaries of:

Software engineer: $90,031
Database administrator: $89,742
Database developer: $84,176

CareerBuilder's salary site reports current average salaries of:

Software engineer: $90,530
Computer programmer: $72,661
Database developer: $95,951

(On a sad personal note, it says "features editors," of which I am one now, earn an average of $49,686.)

Clouds in the news: It appears I'm not the only one infatuated with the cloud lately (see blogs below). A quick scan of some major IT publications' home pages last week revealed the term "cloud" was quite popular:

Visual Studio: 14 instances
Redmond Developer News: 12 instances
iWeek: 28 instances
InfoWorld: 13 instances
Virtualization Review: 16 instances

Insights & Trends: Google Insights reveals how many searches have been done for some common database technologies, relative to the total number of U.S. searches in the "Computers & Electronics" category worldwide in 2009:

SQL Server: 90
MySQL: 78
DB2: 18
Oracle 10g+Oracle 11g: 10

Oracle/Sun acquisition thing.

Issues: So what are database developers struggling with? Here are the top forum topics in terms of views on various DBForums.com categories in 2009:

Microsoft SQL Server

DB2

MySQL

Oracle

Let's get some more info from the front lines. What are you struggling with? What do you like about your job, or dislike? What are the pros and cons of the different technologies and products? Where is the industry heading? Is the cloud where it's at, or just a passing fad? How much do you think features editors should make? Comment below or send me an e-mail.

P.S.: Don't ever call a logger a "lumberjack," at least in Montana. Nobody uses that term.

Posted by David Ramel on 01/15/2010 at 4:33 PM0 comments

Mining the Cloud

Imagine the world's information at your fingertips. Imagine being able to slice and dice it as you choose. Imagine you're a sales exec who, with just a few clicks, can pull up reports on consumer spending and demographics, mapped to your sales areas -- or your competitors' sales areas -- just about anywhere.

And the best part is that you don't have to invest in your own data warehouse, with all the accompanying costs and hassles. You can just pull the data out of the cloud.

This will soon be a reality as Microsoft's Windows Azure platform goes commercial, and vast repositories of public and private data are made available via subscription through Microsoft's Dallas project. These data feeds will consist of everything from AP news headlines to business analysis services to topographic maps to global U.N. statistics on just about anything you can think of.

I've been fascinated with Dallas since it was announced at the recent PDC 09 with much fanfare. I keep thinking of all that information out there and what people will be able to do with it. Maybe the yottabytes of government statistics we've been paying for will actually be put to good use.

If you haven't checked out Dallas, you should. It's still free through the end of the month.

Just to show how easy it is, I signed up, subscribed to some feeds and displayed them in a Windows Forms app. I then went further and fed U.S. crime data into a live ASP.NET site that lets me massage and cull information not readily apparent, such as cities with the highest crime rate in each state. (By the way, I'm using MaximumASP's service to host the site; it offers free beta accounts for developers to play around with ASP.NET 4 and Visual Studio 2010.)

I even analyzed the data with Power Pivot and Excel. BI for the masses is here.

Here's how to get started. First, check out the Dallas Quick Start. From there go to the Dallas Developer Portal to sign in with a Live ID, create an account and request an invitation token.

After you get the token you log in and you're in business. At the home page, you will see tabs for the catalog of available subscriptions, your subscriptions, your account keys and an access report that shows you how many times your subscriptions have been tapped.

Clicking on Catalog brings up dozens of feeds to which you can subscribe. Most of them are still in the "coming soon" stage, but available feeds include infoUSA business stats, U.S crime statistics, AP headlines, NASA Mars images and U.N. data. Here's a sampling of some coming feeds:

[Click image to view larger version.]

I subscribed to "2006 and 2007 Crime in the United States" from DATA.gov. Clicking on that link from your subscription page brings you to a page where you can preview the data and invoke the service, selecting state, city and year, with your choice of a table display or Atom 1.0 and Raw XML formats. Here is how the crime statistics look for Alabama cities for the year 2007, with the preview function:

Preview of Alabama cities crime statistics 2007

[Click image to view larger version.]

Of special interest to developers are the capability to copy the URL for the service to your clipboard, the request header used to call the service, and downloadable C# "service classes" that you can plug into your Visual Studio projects. You can use the request header to invoke the service with REST APIs in just about any programming environment. For .NET developers, all you need to do is plug your account number and a unique user ID (GUID) into the downloaded proxy class code and it will take care of all the nuts and bolts of accessing the service.

I built an ASP.NET Web page and used the crime stats proxy class to display the feed data in a GridView. It looks much like the preview of the data on the portal, with options provided through ListBoxes to display data by state, city and year:

Alabama crime data in a Gridview, built with ASP.NET

[Click image to view larger version.]

To demonstrate rudimentary data analysis, I added a "Find Most Violent Cities" button to display the cities in a particular state that had the highest crime rate (using a standard formula) and highest number of violent crimes in 2006 or 2007. Here's the result:

[Click image to view larger version.]

As mentioned, I published the project so you can check it out yourself to get an idea of what can be done with minimal effort.

For more serious data analysis and business intelligence, you might want to use Microsoft's Power Pivot. To test it, I downloaded the Office 2010 beta and PowerPivot for Excel 2010 (it's also available for SharePoint 2010).

I'm not a numbers guy, so I was lost from there on, but by clicking on the "Analyze" button on my Dallas crime statistics page, I downloaded the feed's data directly into PowerPivot/Excel:

[Click image to view larger version.]

I only managed to put some data into a useless, cheesy looking chart, but you get the idea of what could be done.

From here on, the possibilities are endless, especially when all the promised feeds come online. I wish my free trial subscriptions could go on forever, but Microsoft will soon start commercializing Dallas and the other Azure offerings. I haven't found any subscription pricing info.

In the meantime, I would love to see other ways that developers are using Dallas. As Microsoft's Dave Campbell said at PDC 09, Dallas could be the "killer app" for the cloud. There must be some cool experimentation going on out there.

Please share your project via the comments section here or shoot me an e-mail.

Posted by David Ramel on 01/06/2010 at 7:02 PM0 comments

Acing Azure and Dabbling in Dallas

Got Azure? I do!

Here's a ringing endorsement for the simplicity of the Windows Azure platform: I was able to migrate a database into a SQL Azure project and display its data in an ASP.NET Web page. What's more, I actually developed a Windows Forms application that displayed some of the vast store of public data accessible via Microsoft's Dallas project.

And believe me, if I can do it, anybody who knows what a connection string is can do it. It's wicked easy, as they say here in the Boston area.

I've long been fascinated by the cloud. In fact, almost exactly three years ago I commissioned an article on the nascent technology, having identified it as the future of... well, just about everything. For me, a coding dilettante, there's just something cool about the novelty of being in the cloud. For IT pros, it must be exciting having no worries about hardware, the nitty gritty of administration minutiae, and so on.

So if you haven't yet, you should check out the cloud. Microsoft's Windows Azure platform services are free until Feb. 1.

Here's how I did it. I used a laptop running Windows 7, with Visual Studio 2010 Beta 2, SQL Server Management Studio, SQL Server Express, the Windows Azure SDK, the Windows Azure Toolkit for Visual Studio 2010, and the Windows Azure Platform Training Kit.

To start out, I asked our friends in Redmond for a SQL Azure October CTP invitation through Microsoft Connect. This just involved signing in with a Windows Live ID and answering a few questions. The invitation code e-mail arrived in a few days. I went to the SQL Azure management portal, entered my invitation code, provided an administrator user name and password, created an AppFabric, a new Service Namespace and validated the name, created an account and started my first project.