MySQL versus SQL Server

One if the great things about doing Fasttrack reviews is getting feedback from ISV’s. We were recently in a review where the ISV was using both SQL Server and MySQL. Of course working for Microsoft my first question was to ask why they had chosen to use MySQL. As expected the reply was because they required a lot of databases and they believed that the freely distributable version of SQL Server was still limited in the number of concurrent users it could support.

We explained that when we used to ship SQL Server 2000 MSDE there was indeed a 5 concurrent user throttle built into the product, but that was removed when we introduced SQL Server 2005 Express (Express). The only limitations that now exist are that Express addresses a maximum of 1 Gb of memory and a maximum single database size of 4Gb. Of course you can have multiple 4Gb database on a single machine if you wish.

What did surprise me to learn from the conversation is that ISV’s, who don’t distribute their application as open source, require a commercial license for MySQL. The ISV we were talking to had negotiated some form of unlimited distribution rights but it was version specific. They were several major versions out of date, probably unable to get support and lacking key functionality such as stored procedures. So for ISV’s whose database requirements are satisfied by SQL Express it’s clearly a more practical option. If you do need a more scalable version of SQL Server then because it ‘s the same code base you should be able to move your application across to Workgroup, Standard or Enterprise editions of SQL Server with no effort at all.

We then went onto talk about how ISV’s could deploy Express databases within their application. Essentially you need to be aware of two different requirements. The first is how you install Express; the second is how you install your database onto a client’s machine.

There is an excellent article on MSDN here that outlines all the options available to you for installing SQL Server Express. The options available to you range from using a command line install , to creating a wrapper around the installation of your application and Express, to creating a ClickOnce deployment.

This article here discusses using Xcopy Deployment for the actual database itself. In other words installation can be as simple as copying the database file to an expected directory and then starting the application.

So the entire process is very flexible and you can choose a deployment method that suits you and your client’s requirements.

What we then went onto discuss is how Express comes in several different downloads. So for example if you wish to have the minimum footprint you can download and install just the relational database. However if you want additional tools you can download the SQL Server Express Edition with Advanced Services. The latter also includes SQL Server Management Studio Express, Reporting Services and Full Text indexing. As the figure below illustrates Management Studio Express provides quite a lot of the functionality that Management Studio from the Standard and Enterprise Editions contains.

Management Studio Express with the online database backup dialog displayed.

Just to complicate things a little there is yet another download you might want to look at which is the Microsoft SQL Server Express Toolkit which provides some of the connectivity components and Business Intelligence Development Studio for authoring reports as well as a few other assorted components. (see below).

While I’m on the topic of reporting services its worth mentioning the report controls that exist in Visual Studio. These allow developers to embed reports within rich client applications. The reports can be sourced from a Report Server or from files on the local disk. This is ideal for mobile apps where the user will be disconnected from the network but requires an offline database and reporting capability. One concern is that if the reports are held on the local machine then they can be altered by the user. However we provide the capability to encrypt the reports to prevent this.

Installing SQL Server 2005 Express Edition Toolkit

So SQL Express really is a fully featured version of SQL Server with very few restrictions. That means you get all the database features you should be able to take for granted such full locking and transaction support, all the T-SQL syntax including support for functions, stored procedures as well as full support for DBA functionality such as online backup.

I’ve had a few conversations with ISV’s where they have deployed database on machines that are subject to being switched off by users. With desktop databases their experience is often that they have frequent corruptions. This adds to support costs when the user needs assistance “un-corrupting” the database. Because SQL Server Express has the same transaction log and recoverability as the other members of the SQL Server family it’s far more robust in these environments. When the machine reboots it will simply rollback any uncompleted transaction and then carry on as if nothing happened.

Up to now I’ve only mentioned the 2005 edition of Express. So what can you expect in 2008? Well that a topic and a half in its own right. However I’ll briefly mention two new features that I believe will be worth considering. The first is spatial. Anyone who has look at SQL Server 2008 will be aware that we are adding spatial capabilities to this release. What this allows me to do is store coordinates in a data type that represent various geometrical features such as a point a line or a polygon and then do set based operations on them. So a very simple example might be “show me all the locations within a region that stretches 30 miles around the edge of London. The locations would be stores as records containing a field with the point coordinates and London would be a record with a field containing the coordinates of a polygon. We would then join them together and apply a function to the London coordinates that created a 30km buffer around the London shape. It sounds far more complex than the actual SQL query which is quite simple to construct. The great thing is all the spatial features are in the 2008 version of Express. This provides ISV’s with some great opportunities for new apps. The second new feature is Policy Management. This allows an ISV to create policies which define “best practice” for managing the database and enforce these on the user. I think this has the potential to reduce ISV support cost significantly. Again this feature is available in all 2008 editions including Express.

So by the end of the session the ISV left convinced that SQL Express provided more functionality over what they had with their existing solution, it would be easier to integrate into their application that their existing offering and it really was “freely distributable”.

(There is of course SQL Server Compact, but that's for another discussion . . .)

Source: blogs.msdn.com
Posted By: IndoSourceCode