Deployment of SQL Server: installing a second instance?

Question

Simple problem. I'm working on a Delphi 2007/WIN32 application which now uses MS Access as simple data store. I have to modify it to support SQL Server Express, which is easy. These modifications are working so the application can be deployed using either SQL Server or MS Access. (Whatever the user prefers.) I did consider deploying the whole application together with the SQL Compact but this is not practicak. Using SQL Server Express 2008 instead of 2005 is an option, but also has a few nasty side-effects which we don't want to resolve for now.

The problem is deploying the whole project. The installation with SQL Server would need a quiet installation so the user won't notice it. SQL Server is mentioned in the documentation so they know it's there. We just don't want to bother them with technical issues. In most cases, such an installation will go just fine.

But what if the user already has an SQL Server (2005) installation which is used for something else? Personally, I would prefer to just install a second instance of SQL Server on their system so it won't conflict with the other installation. (Thus, if they uninstall the other app, the SQL instance will just stay installed.)

While SQL Server 2005 and 2008 can be installed on the same system simply by using two different names for the instance, I wonder if it's also possible to install SQL Server 2005 twice on a single system to get two instances. And if possible, how?

Answer 1

To answer your question: yes SQL2005/SQL2008 and SQLExpress2005/2008 can all live happily side by side. The default instance name for the SQLExpress install is [machine name]\SQLEXPRESS. But having said that, you should consider giving your customer the option to use the sql instance they already have, and only install a new instance if they choose to.

I don't know if SQLExpress can be installed silently (most likely it can as long as you specify the right properties on the command line when you install it). But we have rolled it out to lots of customers, and they have very few issues installing it normally.

Edit: I have added this as an edit because a comment doesn't allow enough.

I understand your reluctance to both having the user install SQL manually, and to sharing another instance. To address these points:

• uninstalling a product should never automatically uninstall the SQL instance, even if that SQL instance was put there when installing that product. By all means the database can be blown away, but uninstalling a SQL instance should be a manual process, as it is a server product that may be used by many other products
• you can make your task a lot easier by using a decent installer product. For instance, we use InstallShield. It has a sql browse dialog built in (its a baked in feature of InstallShield) that the user can use to select which sql instance and database they want to use for our product. The details the user enters are then inserted in to the web.config file using an XML file change task (also functionality baked into InstallShield). By using dialogs like this you eliminate a lot of potential user errors.
• if there is already an existing sql instance, use it. The only dependence your database should have on the instance is that it is the right version (i.e. SQL2005, and 2008 is fine for a 2005 database). The only time you should demand your own instance is if you are processing/storing enough data that you require your own server, or if you are depending upon undocumented features. If the existing instance is already under heavy load, then insisting on a new instance on a different server is fine, but then you have also avoided that whole side-by-side situation. Alternatively you could just install in to the existing instance and get the customer to commit to upgrading the hardware.

I hope this helps somewhat - i'm just trying to persuade you that there are limited reasons for needing a separate instance and that 99% of the time you will be fine installing in to an existing instance. It's nice to have your own instance but in reality it brings you few real benefits, especially if you are using a robust installer.

Answer 2

You can install SQL Server Express in silent mode using the /Q command line switch or use the /QS command line switch to see setup progress without user input. You can install a named instance on a system that already has SQL Server installed.

http://msdn.microsoft.com/en-us/library/ms144259.aspx

Answer 3

There are a number of reasons why it is very useful to have your own instance of an SQL Server.

• You can decide for yourself what type of authentication you use (SQL authentication or Windows authentication). Although Windows authentication is recommended, scenario's exist where this is simply not an option. And enabling SQL authentication for an instance where other products use the same instance is a security risk.
• You can safely assume that your product is the only user of the installed instance. So with installing and uninstalling the instance you know the version and databases in use by that instance. No extra detection needed, as long as different versions of your product use the same SQL configuration and version.

Isolation of your installments (files, registry keys, dll's and other products) is a very good practice!

Also, uninstalling an SQL Server instance doesn't lead to data loss, because the data files of the databases will not be removed. After reinstalling, you can attach the data files again if needed.

that being said, SQL Server express can be installed in three different interaction modes:

• Full UI, including SQL license agreement acceptance
• Silent, but with detailed progress UI
• Silent, without any progress UI (and suppressed errors!)

Detailed instructions for installation can be found at http://msdn.microsoft.com/en-us/library/ms144259(SQL.90).aspx