New Features in SQL Server Express Edition with Advanced Services CTP
      by Michael Otey, mikeo@teca.com

Just when you thought you knew what SQL Server 2005 Express was all about, Microsoft releases a new version called SQL Server 2005 Express Edition with Advanced Services Community Technology Preview (CTP), which includes the new features in SQL Server 2005 Express Edition Service Pack 1 (SP1) CTP. No, Microsoft didn’t release SQL Server Express with Advanced Services just to keep you guessing. The CTP includes features that were supposed to be in the original SQL Server Express release but were missing because the features weren't finished in time for the November 2005 release date.

For starters, the new SQL Server Express with Advanced Services CTP includes SQL Server Management Studio Express (SSMSE)--you don’t have to download this application separately. Additionally, SQL Server Express with Advanced Services provides support for full-text search and the ability to view reports by using Reporting Services. Full-text search operates on words and phrases based on rules of a particular language to enable fast, flexible indexing for keyword-based queries of text data stored in a SQL Server database. The report-viewing feature lets you view Reporting Services reports by using SQL Server Express with Advanced Services instead of other SQL Server 2005 products. This feature can render reports in Microsoft Excel, Adobe PDF, image, print, and Dynamic HTML (DHTML) formats.

Even though SQL Server Express with Advanced Services lets you view Reporting Services reports, the CTP still lacks something that many SQL Server Express users want: the ability to create Reporting Services reports. That’s where the SQL Server Express Edition Toolkit fits in. The Toolkit brings the Business Intelligence Development Studio and its report-design capabilities to SQL Server Express. You need SQL Server Express with Advanced Services to render reports you create in SQL Server Express Toolkit. Using the CTP and the toolkit to generate reports makes the reports completely compatible with the other members of the SQL Server 2005 product family.

You can download SQL Server Express Edition with Advanced Services CTP along with the SQL Server Express Edition SP1 CTP and associated toolkits at
      http://www.microsoft.com/downloads/details.aspx?familyid=57856cdd-da9b-4ad0-9a8a-f193ae8410ad&displaylang=en

Although the standard SQL Server Express Edition download is 54MB, the new SQL Server Express Edition with Advanced Services is much larger--a little more than 234MB--and SQL Server Express Edition Toolkit is another 213MB. And remember, Microsoft uses the moniker CTP to indicate a beta product, so the company warns you against installing CTPs in a production environment. CTPs are released so that you can try new features before they're released. The final releases for SQL Server Express Edition with Advanced Services, SQL Server Express Edition Toolkit, SQL Server Express Edition SP1, and SSMSE are expected sometime this month. Like the original SQL Server Express and SSMSE, you can download SQL Server Express Edition with Advanced Services and SQL Server Express Toolkit free of charge.


From the Community

In this edition, I thought I'd share some users' thoughts and concerns about using SQL Server 2005 Express Edition in a multiuser environment. The Jump Start column will be back in the next edition of this newsletter.

No SQL Express Web Hosts

I tried to use Microsoft ASP.NET 2.0, Visual C#, and SQL Server Express on a Web-hosting service, but none of the Web hosts I contacted would host sites that were created in SQL Server Express. The hosting services told me that the main reason for not supporting these Microsoft applications on their hosting site was that the SQL Server Express applications created a separate process for each instance, which affected their server usage. I was beginning to use C# and SQL Server Express to rewrite my new Web site, but when I couldn't find a Web host, I went back to the Hypertext Preprocessor (PHP) version of my Web site and am continuing to use PHP open-source programming language to develop in MySQL. So I guess SQL Server Express isn't an option for a small business to use to create a Web site, correct?
-- Susan Shemin

Great question, Susan. SQL Server Express uses the same core relational database engine as the other members of the SQL Server product family. After you enable network connections, SQL Server Express can serve a large number of users. In "Is SQL Server Express Multiuser-Capable?" (http://www.sqlmag.com/Article/ArticleID/49736/sql_server_49736.html), I talked about running your own Web server and using SQL Server Express as the back-end database. The information I provided described a somewhat different case than the way you’re attempting to build your Web site. In your case, you’re referring to using a third-party Web-hosting provider. Unless you buy a dedicated hosting plan (which is often too expensive for a small business) most Web-hosting sites won't let you install your own database server. You must typically use the same applications the Web host uses to develop your Web site. Because many Web hosts use the Linux platform, it’s no surprise that MySQL is the database they offer most. Web-hosting sites that offer SQL Server Express do exist (e.g., http://www.aspdotnet.sk) but most Web hosts that offer SQL Server support use the SQL Server 2005 or 2000 Standard or Enterprise Editions and share the installation with multiple clients. The problem of separate processes for each instance is a result of SQL Server Express’s User Instance support. This feature is actually a deployment feature that's turned off by default. In the off state, SQL Server Express doesn't create separate database instances for each user.
-- Michael Otey

A Source of Multiuser Confusion

Thanks for clearing up that misconception about SQL Server Express being unsuitable for multiuser applications. Do you know why so many people are confused about multiuser capability? Because Microsoft Developer Network (MSDN) indicates that you can't use SQL Server Express in a small workgroup environment. It's actually posted on the MSDN Web site. After reading that information, I worried about the multiuser thing for a long time. One MSDN article in particular that people keep referencing (and this is just one example that I can find quickly) is "Using SQL Express from Visual Basic 6" at
      http://msdn.microsoft.com/vbrun/vbfusion/usingsqlexpress/

The article says, "A final consideration for SQL \[Server\] Express is security. SQL \[Server\] Express is designed to be an application database, not a server database. The typical usage is to provide storage of application data on the desktop or laptop of the user. For this reason, by default, you can't make a remote connection to SQL \[Server\] Express. This, by itself, makes SQL \[Server\] Express safe from a whole host of attacks, such as SQL Slammer types of viruses."

You can see how this information would confuse people. The article tells everyone that the reason you can't access the SQL Server Express database across the network by default is because it's not meant to be used like this.
-- Matt Ferguson

Matt, I certainly agree that this information contributes to the mistaken notion that SQL Server Express is not multiuser capable. I think the author of this quote was attempting to stress the security aspects of SQL Server Express by describing why network connections are off by default. SQL Server Express is designed to be a secure, out-of-the-box product with multiuser support that's fully capable of supporting the same multiuser connection and security models that the other members of the SQL Server 2005 family support.
-- Michael Otey


SQL Server Agent Replacements
      by Michael Otey

One MSDE feature that SQL Server Express lacks is support for SQL Server Agent, a job-scheduling service that lets you schedule automated and recurring jobs such as database backups. However, with SQL Server Express, you can use Microsoft Windows 2000 Task Scheduler to automatically kick off jobs. Additionally, some third-party products can fill this void. You might want to check out Vale Software's Express Agent at
      http://www.sqlexpresstools.com/

Let me know what solutions you’re using at mikeo@windowsitpro.com.


Simplify Database Development
      by Blake Eno

Upscene Productions announced Database Workbench 2.8.5, a database development IDE that supports SQL Server Express. Database Workbench provides a single UI that performs all aspects of database development. The product provides code and object templates and lets you debug stored procedures and create and alter schema objects. In addition, you can convert existing schemas, generate test data, import comma-separated value (CSV) data, and run and analyze stored procedures. Database Workbench also features a point-and-click Grant Manager solution so you can easily grant rights to your users. For more information, contact Upscene Productions at
      http://www.upscene.com