Distributing database with app

Distributing database with app

• Krey

  Do you have SQL Express installed anywhere

  You must have a SQL Server running in order to access your data. There are two basic models to do this:

  • Local Data Access - SQL Express is installed on the same computer with the application. Each user has thier own copy of the database installed on their computer and data is not shared. This is the configuration for a single user application.
  • Server Data Access - SQL Express is installed on a central computer. The database is also located on that centeral computer and all copies of the application connect through the network to that central database. This is a multi-user application because all users share the same database.

  Jen's comments related to TCP and remote connection apply to the second case, where data access is going to the central computer. You need to properly configure SQL Express to accept connections from a VB6 application by enabling TCP in SQL Server and ensuring that the Firewall on the central computer is not blocking access to SQL Express. There is a KB article that describes how to do this at http://support.microsoft.com/default.aspx scid=kb;en-us;914277.

  Regards,

  Mike Wachal
  SQL Express team

  
  
  

• John Hind

  I'm not using a remote connection. It's a local copy of the database. What simply does the target deployment machine require or in order for the VB app to connect to the db
  
  
  

• MotoJames

  The deployment machine needs a Data access components like MDAC or SQL Native Client, both the app machine and the database machine must be able to negotiate to a common protocol like TCP/IP. If that is used you have to allow remote connections on the database server and if you are using another port than 1433 either specify that in your connecting string like MachineName\SQLExpress,Portnumber or start SQL Browser on the database server which will automatically redirect the request to the appropiate port.
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• Dinesh73

  If you don’t uise the user instance keyword you use SQL Server Exprtess just as a normal database. If you want to attach the database to a SQL Server instance you don’t need to use User instance availbility. Just attach the db to a SQL Servert (Express) and connect via the "normal* connection string to it (without specifying something like user instance)
  
  

  HTH, Jens SUessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---

  
  

• Dan Danz

  If you are connecting via TCP/IP you have to enable these protocols. You also have to ensure to enable remote connections.
  
  HTH, Jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---
  

• Francis Tracey

  Mike ... I was speaking of Local access (and think I used the term "local" a few times). All I've been asking is -- must SqlExpress be installed a client machine, be it local or server Apparently, the answer is yes, which complicates distribution and installation by end-users who won't have a clue. I was under the mistaken impression it was not unlike distributing an Access mdb file. I'm clear on this point now. Thanks.
  
  Now I'm trying to understand more on User Instance usage:
  
  If not specified in the connection string, what is the default, yes or no
  For shared multiuser usage located on a server, should it be enabled or not
  
  I'm just unclear on when/when not to use it.
  
  TIA ... Rick
  
  
  
  
  
  
  
  
  

• Abaddon

  Hi Rick,

  Regarding User Instances:

  • If you don't specify it in the connection string, User Instances are not used.
  • User Instances should not, and in fact can not, be used for multi-user, remote access applications. This special type of instance can only be used for local access because it only supports the Shared Memory protocal, which does not work across a network.

  The recomendation is to use User Instances for:

  • Single user applications
  • Local database access only
  • You want to achive XCopy type deployment of the database file

  The advantage/purpose of User Instances is that they give the end-users the required SQL permissions to accomplish things such as attaching a database, which is a requirment for XCopy type deployment. (e.g. where you just copy the database file onto the computer and it is automatically attached at run time. Normally a user has to be an Admin to do this, User Instances allows for non-admin users to do this.)

  You can read the details about User Instances in the white paper located at http://msdn.microsoft.com/sql/express/default.aspx pull=/library/en-us/dnsse/html/sqlexpuserinst.asp if you want more information.

  As a point of interest about deploying Access databases...

  Access files actually have the same requirement, namely that they need a database engine in order to read the data in the file. What makes deploying an MDB file so easy is that the Jet database engine, which is what is used to read an Access database, is installed as part of Windows, so it's already on the computer. No need to deploy it specially.

  If you have an installer for your VB6 application, it's likely you can incorporate the SQL Express installation directly into it. This is typically called chaining, where you call two or more installers in sequence. SQL Express supports command line installation, so you can actually call it directly from your existing installation in quiet mode so that your end-users don't even have to mess with it.

  I recognize that you're using VB6, so this is only of use to you if you decide to migrate your application to VB.NET 2005 some day, but I'll mention it for your information. VS 2005 includes some built in deployment capabilities that allow you to automatically include SQL Express into your setup. This works for normal Windows Installer packages and for the new ClickOnce deployment. If you haven't looked at VS 2005, it's got some great stuff in it that you might like. Even better, you can get a version totally free, it's called Visual Basic .NET 2005 Express. You can check out more information on http://msdn.microsoft.com/vstudio/express if you interested.

  Regards,

  Mike

  
  
  

• aaronc76

  Thanks Jens. I don't quite understand what you're saying with remote connections. There is no database machine. This is all starting to sound like every target machine for my application requires SqlExpress be installed on that machine, or a server that machine is connected to. Is this correct or not
  
  
  
  
• Distributing database with app