SQL Server Magazine UPDATE, October 12, 2006--Application Server or Database Server?

In This Issue:
When you’re trying to avoid mixing the database and application tiers, you might run into two provocative questions. First, when is a database not a database? And second, where does SQL Server the database start and SQL Server the application server end?

Plus: Can I help you with that?
Why can’t Windows and Linux just get along?

New Instant Poll: Staying Ahead of the Curve
"Which of the following types of training resources do you consider MOST trustworthy?" Go to the SQL Server Magazine home page and submit your vote for:

Live training (e.g., conference, seminar, training center)
Online training (e.g., Web seminar, podcast)
Print magazines, newsletters, or books
Free online materials (e.g., articles, white papers, newsletters, documentation, ebooks)
Newsgroups and forums
Blogs

Be sure to add [email protected] to your list of allowed senders and contacts.

Our Sponsors, Who Help Support the Free Delivery Of This Newsletter:

October 12, 2006

1. Perspectives

Application Server or Database Server?

2. SQL Server Watch

TechX World: Handling Interoperabilty with Humor
This Month’s Theme: High Availability: Database Mirroring
Product Watch: Red Gate and Joe Celko

3. Hot Articles

Q&A: The Disappearance of SQL Server Express
SELECT TOP(X): SQL Server Everywhere
In a Nutshell: Opinions on Vista
Hot Threads: Replication and SSIS

4. Events and Resources

Solve Interoperability Problems in a “Windows Plus” Environment
Augment Your Vulnerability-Management Processes
2006 PASS Community Summit November 14-17
Expand Your Geographic Reach

5. Featured White Paper

Optimize Your IT Controls Environment for Compliance with Multiple Regulations

6. Reader Challenge

October Reader Challenge Solution: Enforcing Uniqueness Without the Unique Constraint
November Reader Challenge: Parse and Insert Data from Uploaded Image Files

7. Announcements

Invitation for VIP Access
Save $40 On SQL Server Magazine

8. Web Community

http://www.sqlmag.com

Sponsor: Double-Take Software
Enhance SQL Reporting and Increase Server Performance
Increase your SQL production server’s performance by offloading Reporting Services to a secondary server. Gain additional insight into your important business data quickly and effectively. Download the whitepaper today!

1. Perspectives

Application Server or Database Server?
by Brian Moran, [email protected]

In last week’s editorial, “SQLCLR: The Barbarian at the Gate”, I asked you to tell me why you are or aren’t using the SQLCLR and send me your opinions about why the SQLCLR isn’t used more in the SQL Server community. My request has generated some great reader feedback, and I’m looking forward to addressing many of the issues readers raised. But this week, I want to address one of the most common reasons that readers gave for not using the SQLCLR. These readers told me, basically, that they believe “Business logic belongs in the application tier, not in the database.’ I tend to agree with that sentiment, although I’ve learned to never say never (which, oddly enough, I think I just did).

When I thought about avoiding mixing the database and application tiers in the broader context of “When should we use SQLCLR,” I came up with two provocative questions. First, when is a database not a database? And second, where does SQL Server the database start and SQL Server the application server end?”

Several weeks ago an interesting thread on the Solid Quality Learning email lists mentioned that one of our customers was positioning SQL Server as an “application server” rather than a database server for political reasons. The customer explained he was battling the “evil forces of oppression” that were attempting to keep SQL Server 2005 out of the corporate mix because SQL Server 2005 isn’t an “official” database standard for the organization. Readers who work for sane, rational companies might be surprised to learn that some IT shops may be dogmatically opposed to allowing the “non-approved” SQL Server 2005 database server platform in production but will allow SQL Server 2005 to be positioned as an “application server” that will host SQL Server services such as Reporting Services, Analysis Services, and SQL Server Integration Services (SSIS). I wonder where the line really is between the database server and the application server. Clearly, a substantial amount of what comes “in the box” when you buy SQL Server is unarguably a database server and always will be. However, a growing number of pieces that come in the box aren’t really core components of a database server, are they? Don’t get me wrong—pieces such as Reporting Services, Analysis Services, and SSIS are valuable pieces to have in the box. But they certainly do blur the line between database server and application server.

I’m sure people who say “Don’t put application code in the database” would accept the fact that you don’t need to physically distribute all the tiers in an application. So, is using the SQLCLR really putting application code in the database or is it simply putting a piece of the application tier on the same physical box as the data tier? Does it matter that both of the pieces might be running inside of the same physical process? Maybe. I’m not sure. If you’re arguing that yes, it does matter, are we simply being a bit dogmatic? All fun questions to argue about with your fellow database comrades. I do know that as SQL Server matures, the lines of where the database starts and stops get increasingly blurry.

A few years ago, when the availability of a database-like file system for Windows seemed more fact than fiction I joked that the next version of Windows should be codenamed “AS-400,” hearkening back to the days when it really was hard to see where the application and database stopped and started. Maybe I was wrong and SQL Server, not Windows, should be referred to as AS-400. We can still define where the database starts and ends in SQL Server today. But what about when Katmai ships? What about the release after that? In the future, will we simply have “Database Engine Services for SQL Server” on the same plane as the other services that make up the application server commonly called SQL Server?

Sponsor: Neverfail
SQL Server Guide to Clustering Alternatives
Want the convenience of a server cluster without the expense? Learn about server cluster alternatives that provide high availability, preventative maintenance, and failover capabilities at pricing that fits your budget.

2. SQL Server Watch

TechX World: Handling Interoperabilty with Humor
“Can’t Windows and Linux just get along?” If you’re fed up with such platitudes, you’ll appreciate the video “Can I Help You With That?” now running on YouTube. But you also probably want real solutions to your interoperability problems. TechX World brings you real-world experts, third-party providers, and industry technology leaders from Microsoft, IBM, and Centeris who will lay out practical solutions to problems with OS interoperability, directory and security integration, data interoperability, and virtualization. This inexpensive one-day event strips away rhetoric and digs into the issues you’re working to solve every day in your heterogeneous environment. TechX World is coming to Washington DC, Chicago, Dallas, and San Francisco between October 24 and November 2. Check out how to register today here.

This Month's Theme:
High Availability: Database Mirroring
by Michael Otey, [email protected]

Now that Microsoft has added database mirroring to SQL Server 2005 SP1, check out how this important high-availability feature works to automate switching between databases.

Product Watch
by Blake Eno, [email protected]

Get Code Profiling for Improved Performance
Red Gate Software announced ANTS Profiler 2.7, a tool for code and memory profiling applications written in all languages supported by the .NET Framework. ANTS Profiler identifies performance bottlenecks for both .NET desktop applications and ASP.NET Web Applications. When profiling on Windows 2003 Server, ANTS Profiler lets you profile a particular ASP.NET Web application, but still keep all other Web applications running as normal. ANTS Profiler is now more accurate, easier to use, and allows profiling to run on beta copies of Windows Vista. For more information, contact Red Gate Software at 866-733-4283.

New Joe Celko Puzzle Challenges
Morgan Kaufmann Publishers released Joe Celko's “SQL Puzzles and Answers,” Second Edition, which features a new collection of SQL puzzles with a series of solutions and explanations by Celko to help you solve them. He demonstrates his thought processes for attacking a problem from a SQL perspective to not only solve the problem at hand but also develop the mindset needed so solve SQL puzzles every day. The book also has new chapters about temporal query puzzles and common misconceptions about SQL and relational database management systems (RDBMSs) that lead to problems. “SQL Puzzles and Answers,” Second Edition costs $44.95. For more information, contact Morgan Kaufmann Publishers at 888-864-7547.

Sponsor: Innovartis
Control Your SQL Server Code with DB Ghost

With DB Ghost Change Management Components:

Scripter: Scripts all schema objects AND static data.
Builder: Builds DBs from object scripts handling ALL dependencies. You build the rest of your code regularly, why not your database?
Schema Compare & Data Compare: Compare and synch schema and data. Builds a delta script that is guaranteed to work with no manual intervention!
New Product: Packager & Packager Plus

Download our 14 day Free Trial!

3. Hot Articles

Q&A: The Disappearance of SQL Server Express
by Microsoft’s SQL Server Development Team, [email protected]

Q: I had SQL Server 2005 Express Edition installed along with Visual Studio 2005. Today, I installed SQL Server 2005 Developer Edition, but installed it as the default instance instead of as a named instance. Probably as a result of that installation, I’ve lost the Express Edition. Is it OK to install SQL Server Express on the same machine as the SQL Server Developer Edition?

Read the answer to this question today.

SELECT TOP(X): SQL Server Everywhere
Just when you thought Microsoft was finished with SQL Server 2005 releases, it announces SQL Server 2005 Everywhere Edition Community Technology Preview (CTP). It's a small-scale database designed to provide relational database capability to mobile and single-user desktop applications. In the October SELECT TOP(X) column "SQL Server Everywhere," Michael Otey answers some FAQs about the newest SQL Server database. Read this article today and post your comments.

In a Nutshell: Opinions on Vista
Are you wondering why we’re being forced to the new versions of Visual Studio and SQL Server for the Vista platform? So is Kevin Kline. Read his blog and send your opinions today.

Hot Threads:

4. Events and Resources

As an IT pro today, chances are that you work in a "Windows Plus" environment. Learn from and meet industry experts Gil Kirkpatrick, Mike Otey, Dustin Puryear, and Randy Dyess, in this full day of training on managing Windows, Linux, UNIX, Apache, MySQL, and more. Join TechX World—coming to Washington, DC, on October 24, Chicago on October 26, Dallas on October 31, and San Francisco on November 2.

How do you manage vulnerabilities? If you depend on vulnerability assessments to determine the state of your IT security systems, you can't miss this Web seminar. Special research from Gartner indicates that deeper penetration is needed to augment your vulnerability management processes. Learn more today!

Can you distinguish between the facts and fiction of Linux? Get the straight answers about Linux UNIX, and Windows—together with head-to-head comparisons. Read articles and download free resources today! You can also test your Linux skills and enter to win a $150 MSN Music gift card!

2006 PASS Community Summit November 14-17
Join the Professional Association for SQL Server (PASS) as it hosts the premier event of 2006 dedicated exclusively to Microsoft SQL Server education and networking! Come experience first-hand more than 100 technical sessions presented by more than 80 user-experts, MVPs, and members of the Microsoft development team.

Whether you're an outsourced IT provider, a member of an in-house IT service staff, or simply provide remote support, this can't-miss Web seminar will help you discover how the right technologies can expand your services. You'll learn how to tap into a $30 billion market for IT services and expand your geographic reach. Live Web seminar: Tuesday, October 17

See the complete Windows IT Pro Network guide to Web and live events.

5. Featured White Paper

One common set of controls can help you manage compliance across multiple regulations and standards. Download this free IDC white paper and find out how to map these controls and save time and money in demonstrating compliance.

6. Reader Challenge

October Reader Challenge Solution: Enforcing Uniqueness Without the Unique Constraint
by Umachandar Jayachandran, [email protected]

Congratulations to Chad Boyd, who won first prize of $100 for the best solution to the October Reader Challenge, "Enforcing Uniqueness Without the Unique Constraint." Chad graciously donated his prize to the Make-a-Wish Foundation. You can read a recap of the problem and the solution to the October Reader Challenge here.

November Reader Challenge: Parse and Insert Data from Uploaded Image Files
Now, test your SQL Server savvy in the November Reader Challenge, "Parse and Insert Data from Uploaded Image Files" (below). Submit your solution in an email message to [email protected] by October 19. Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.

Here's the challenge:
Denny is a database developer who needs to develop a Web portal for intranet use. This Web portal will use a SQL Server 2000 database as the data store. The portal has functionality that lets users upload binary data, such as image files and Microsoft Office Outlook messages, to the database. The uploaded files are stored in a table called ItemChunks with the following schema:

CREATE TABLE ItemChunks (
 ItemChunkId INT NOT NULL PRIMARY KEY,
 ItemChunk image NOT NULL
)
GO

The Web portal page that lets users upload binary data has the following characteristics:

The page sends the binary data as a single BLOB to SQL Server.
One or more binary data files can be uploaded from the page in one transaction.
The file sizes can range from 1K to 20K.

Denny is designing a stored procedure that can insert the data from the BLOB into the ItemChunks table. The skeleton stored procedure looks like the following code. For the sake of simplicity, assume that the data offsets and length of each binary data file contained in the parameter value are known.

CREATE PROCEDURE InsertItemChunks (@data image)
AS
BEGIN
    DECLARE @data_offsets TABLE(item INT NOT NULL, 
       offset INT NOT NULL, length INT NOT NULL)
    INSERT INTO @data_offsets (item, offset, length) 
       values(1, 1, 200)
    INSERT INTO @data_offsets (item, offset, length) 
       values(2, 201, 10000)
    INSERT INTO @data_offsets (item, offset, length) 
       values(3, 10201, 30)
END

Help Denny write the stored procedure logic to parse and insert the data from the image value into the ItemChunks table. Denny can use the item column in the @data_offsets table as the primary key value for the ItemChunks table.

Denny can test the stored procedure by using the following code. (The batch generates a binary data value of 10,230 bytes with three chunks each containing 200 bytes, 10,000 bytes, and 30 bytes of data respectively.)

DECLARE @chunk1 VARCHAR(400)
DECLARE @chunk2_1 VARCHAR(8000)
DECLARE @chunk2_2 VARCHAR(8000)
DECLARE @chunk2_3 VARCHAR(4000)
DECLARE @chunk3 VARCHAR(60)
SET @chunk1 = replicate('af', 200)
SET @chunk2_1 = replicate('be', 4000)
SET @chunk2_2 = replicate('be', 4000)
SET @chunk2_3 = replicate('cd', 2000)
SET @chunk3 = replicate('dc', 30)

EXEC('EXEC InsertItemChunks 0x' + @chunk1 + @chunk2_1 
   + @chunk2_2 + @chunk2_3 + @chunk3)

SELECT *, datalength(ItemChunk)
FROM ItemChunks

Hot Spot: PolyServe
IT Consolidation: Maximizing the Potential of Your Windows Environment
Optimize your existing Windows Server infrastructure with the addition of server and storage consolidation software and techniques, and get tips and guidelines to evaluate your current infrastructure and determine what segments of your environment are suitable for consolidation.

6. Announcements

Invitation for VIP Access
Become a VIP Monthly Pass subscriber and get instant online access to every article published in our network. You'll get full Web access to Windows IT Pro, SQL Server Magazine, and the Exchange and Outlook Administrator, Windows Scripting Solutions, and Windows IT Security newsletters—that’s more than 26,000 articles at your fingertips. Sign up now for only $29.95 per month.

Save $40 On SQL Server Magazine
Subscribe to SQL Server Magazine today and SAVE up to $40! Along with your 12 issues, you'll get FREE access to the entire SQL Server Magazine online article archive, which houses more than 2,300 helpful SQL Server articles. This is a limited-time offer, so order now.

7. Web Community

About the newsletter—[email protected]
About SQL Server Perspectives—[email protected]
About technical questions—http://www.sqlmag.com/forums
About product news—[email protected]
About your subscription—[email protected]
About sponsoring SQL Server Magazine UPDATE—Richard Resnick, [email protected]

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today!

SQL Server Magazine is a division of Penton Media, Inc.
221 East 29th Street, Loveland, CO 80538
Attention: Customer Service Department

Comments

Plain text