Sql – Deployment of SQL Server: installing a second instance

delphidelphi-2007sql-server-2005winapi

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?

Best Answer

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.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql-server – Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql-server – Check if table exists in SQL Server

Related Topic