Redistributing SQL Server 2008r2 runtime as part of a VS 2008 project?

[Amesville]

Hi Folks

Forgive me if I'm asking this in the wrong place.

I've just completed development on a VB.NET 2008 application that uses a SQL Server 2008 r2 (might be express, not sure) database. So I'm looking to create an MSI Install file for my project. What do I need to include in the installer package to copy my database so that it can be used by my clients? This will be a stand-alone installation so no communication over the web to a remote server.

I'm concerned because I started looking for a Prerequisite install package to include in the install file, but there only seems to be one for SQL Server 2005. After a couple of hours of searching there doesn't seem to be one available from Microsoft which I find alarming. I've spent months learning to use Sequel Server 2008, changing all my queries to stored procedures, and the prospect that I might not be able to automatically set up my database with the program for my clients is making my blood run cold.

What options do I have?

 

[gmmastros]

You can do this.

The only version of SQL Server you can legally distribute is SQL Server Express. What you should do is find the setup application for SQL Server Express and bundle this with your application's setup. Most likely, you'll want to use a configuration file for your setup. The configuration file will allow you to specify the setting you want for the setup, which in turn, allows you to run the setup for SQL Server without displaying an interface.

After running the setup for SQL Server, you'll want to use SQLCMD (included in the setup) to attach your database to the instance of SQL Server you just installed. SQLCMD is a command line tool that allows you to connect to a running instance of SQL Server to issue commands, like adding logins, attaching databases, etc....

I strongly advise you to set up the configuration file to install a named instance of SQL Server. This will likely prevent problems where there may already be an instance of SQL Server installed. By default, SQL Express installs as a named instance. From the computer you installed SQL Server on, you can connect to the server named [red](local)\SQLExpress[/red]. If another application that uses SQLExpress is already installed on the computer, and they did not change the instance name, your setup will fail if you try to install another instance of SQL Server using the same instance name. Industry standard best practices is to install an instance with your company name. For example, if your company is named Amesville, then you would install a named instance of Amesville. In this situation, the server that you connect to would be [red](local)\Amesville[/red]. This connection would only work from the computer you installed the SQL Server engine on. From other computers, it would appear as [red]ComputerName\Amesville[/red].

If you want the database to be accessible from other workstations, and you are installing SQL Express, please be aware that the default settings will not work for you because SQL Express defaults to no network connectivity. You can change this using the configuration file used during the setup of SQL Server.

Please refer to this article to help get started:

http://msdn.microsoft.com/en-us/library/dd239405(v=sql.105).aspx

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

Adding to what George said.

In my experience, those companies that already have a SQL Server machine do not like to have a express edition installed - it is advisable that your install allows for the use of an existing SQL Server installation

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

Good point Frederico. This is exactly what my installer does (prompting to install the DB engine or not). I planned on mentioning this and forgot.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Amesville]

Thank you Gentlemen, I appreciate the advice very much.

Yes, I will be using SQL Server Express 2005 (mostly because there is a microsoft-provided bootstrap install for it that can be included in the Application install. I'm trying to figure out how to do this as we speak).

This is for a standalone Winforms application written in VB.NET 2008, I don't expect it to be accessible over a network. Frankly I wouldn't even begin to know how to do that.

Industry standard best practices is to install an instance with your company name. For example, if your company is named Amesville, then you would install a named instance of Amesville. In this situation, the server that you connect to would be (local)\Amesville. This connection would only work from the computer you installed the SQL Server engine on. From other computers, it would appear as ComputerName\Amesville.

Yes this is exactly what I would like to do. I will investigate putting together a config file for this.