There is safety in following the crowd. At least, that's what my mama liked to tell me when she was raising me. Unfortunately for her I don't care much for safety, or for that matter crowds. I like to think for myself and make decisions that are best for me not the crowd.
That being said, let's take five minutes or so to pause and reflect over the topic of databases and Web development; primarily Web development with PHP and Microsoft® SQL Server® 2008.
The Internet Has Lowered the Costs of Application Development
The Internet has made building Web applications so inexpensive that you can literally…
…take an idea
… turn it into a Web application
…only to find out there isn't a market for it
…and discard it
You can do all this without spending more than (at maximum) a few hundred dollars. Of course the idea can also take flight and become an international sensation, requiring the need to grow your application in ways you did not originally anticipate.
If you develop Web sites for yourself or others, you most likely want to limit costs and risks while building a solid application. You might also want to keep an eye on how you architect the application to ensure you can add features and/or scale it without re-writing it or descending into a refactoring abyss (I can assure this exists).
By adopting open-source technologies like PHP and MySQL you certainly limit costs and utilize proven, stable technologies. According to some crowds, open source is the only way to go. But is it? What if you like PHP but want more out of your database than MySQL can offer? Does migrating to a proprietary database solution like SQL Server make sense? (See Sidebar 1: Common SQL Server and MySQL Myths)
Open-source purists will tell you not to do it. It costs too much and MySQL does everything SQL Server does. But is this crowd correct? Is there wisdom in following their lead? Let's pause and reflect over some data I have concerning SQL Server and why it might be the best database for your PHP Web development efforts.
MySQL and SQL Server: Apples and Oranges?
At a basic level, MySQL and SQL Server both do what you need a database to do. You can create tables, relate tables to each other, create specialized views, add indexes to make your queries super-fast, read, write and delete data, add advanced logic via stored procedures, and much more. These features are expected. They are commodities that every database in the market must or it won't even qualify to be called a database. Commodity features are not the reason to choose a database.
So what are the reasons for choosing a database? Glad you asked. It is best to choose a database that has the features you need and provides the value you require. Notice how I didn't refer to cost? That's because cost is only one component of value. The rest of this section discusses a few features that provide maximum impact and value (including cost). (See Sidebar 2: Comparing Databases is Difficult)
Advanced Database Engine Features
As you move up the SQL Server 2008 food chain to its more advanced editions, you'll find many points of differentiation between SQL Server and MySQL. In this section, however, I want to cover a few out-of-the box SQL Server features that are available with every edition, including the free SQL Server Express, which help separate SQL Server 2008 from MySQL.
Indexed Views in SLQ Server 2008
Indexed views appear to be just like a normal view but they have tremendous performance enhancements. With a typical view, databases persist the metadata describing a view within the system tables. Only when you call the view does the database retrieve this metadata, materialize the view, compile, and execute its joins, functions, and calculations. It works well but the overhead involved can result in less-than-optimal performance. An Indexed view residing in SQL Server is stored like a table and is materialized when you create it.
Online Database Backup
Protecting the data in your databases is a fairly simple process. Using SQL Server Management Studio, you can backup your data without affecting the users connected to the database. Furthermore you can schedule backups to occur regularly. Also, you have the ability to use SQL Server’s advanced replication features to distribute your data between locations; this allows you to keep your data close to your users. Although these are features targeted toward DBAs, they are still valuable to a developers from a maintenance perspective as these activities can both be managed using T-SQL.
Business Intelligence Tools
The SQL Express with Advanced Services download includes integrated Reporting Services, including a Report Designer in the default installation. There is no need to utilize third-party tools and spend the effort required to integrate them in your application. These features combine to offer Business Intelligence features out-of-the box. Instead, you can create reports and include them in your applications using the Report Viewer Windows Forms Control and SQL Server’s report rendering capabilities. SQL Server security allows you to define report access based on groups and/or individual users, reducing code you might need to write to handle that in your web application.
Development and Management Tools
I am not a fan of using Notepad as my code editor. I'll admit I prefer fully featured editors that enhance my development experience and make life easier. Again, I'll focus on the tools provided with SQL Server Express since it is the free version and is suitable to show what you can expect with the entry-level version of SQL Server. The following tools are available at no charge. (See Sidebar 3: on Expression Web)
SQL Server Management Studio Express (SSMSE)
This is the administration tool that provides features for managing databases, tables, views, stored procedures, security, and more. It runs locally on your desktop and allows you to connect to SQL Server databases on your computer, network servers, and on hosted environments. SSMSE is the version of Management Studio available with the Express edition.
My favorite feature from Microsoft Visual Studio®, Intellisense, is now included in the T-SQL IDE. This new feature to SQL Server Management Studio greatly reduces the effort required to type T-SQL commands. It also jogs my memory as to T-SQL commands and syntax, reducing the need to refer to my reference library.
One last comment here is that SSME works with all editions of SQL Server, enabling you to connect to databases hosted on servers running SQL Server Standard edition, Enterprise edition and so on.
Visual Studio® Express Edition
Microsoft provides a free version of the popular Visual Studio development environment. While it's free and doesn't include all the features of the full-blown editions of Visual Studio, the Express version provides a great feature set for developing with SQL Server. For example, you can create a Web project, define a SQL Server connection, and drag a table to your Web page. Visual Studio automatically creates the objects required to initialize the connection, execute a SQL command, and display the returned data on the Web page. The Visual Studio Express tools are very powerful and might surprise you.
* This article was commissioned by and prepared for Microsoft Corporation. This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
