Help with connection string & setup of database and maybe application 4

[assimang]

Hello everybody,

I have created an application in vb 2005 which interacts with a sql database (mdf) created on microsoft sql server management studio express 2005. Well, I want to deploy my application, it's my first time and I am not familiar with, maybe I ll need your help.
In my project my connection string is as follows:
PCServer = My.Computer.Name
ConnectionString = "Data Source=" & PCServer & "\SQLEXPRESS;Initial Catalog=typing;Integrated Security=True"

Well, I am just wandering, if I want to install the application on another machine, the other machine maybe will not have sql server (SQLEXPRESS) which means that will give error as expected and my application will not work.
Well what can I do to avoid this? Must I have to change the connection string? And how? What can I do generally? Any help will be much appreciated.

Thanks anyone
in advanced.

 

[earthandfire]

From

http://www.connectionstrings.com/?carrier=sqlserver2005

=====================
Attach a database file on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;

=====================
Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Driver={SQL Native Client};Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf; Database=dbname;Trusted_Connection=Yes;




One of those should do what you need (but don't forget to set up the separate account)


Hiope this helps.

 

[gmmastros]

From a high level overview....

I recommend you install SQL Express as a named instance. This will benefit you in the long run, trust me. By default, SQL Express already installs as a named instance ([!]SQLEXPRESS[/!]). Instead of that name, I recommend you choose something that makes sense, like your company name or your product name.

If you choose to use AttachDbFilename, you may run in to problems. I've never used that functionality, but if I recall correctly, using that in your connection string will cause the database to attach every time you use it, which is likely to add a lot of overhead to your app (think slow performance). There are certain (rare) instances where it is appropriate to do this, but it's not recommended for long-term use. Another problem with this method is that a database can only be attached to a single instance of SQL Server. If you attempt to open multiple connections simultaneously, your second connection will fail.

So... here's what I recommend.

1. Include (as part of your installation) a silent install of SQL Server express. Your installation app should allow you to pick Client (your vb.net app), Server (SQL Server Express and your database), or Both (for installing locally on a desktop).

2. When you first run your app, you should check your configuration file for Server and Database. If those configuration entries are blank, then you should present the user with a configuration window where they can specify the server and database. Once the user has successfully connected to your database, you should store the configuration information so that it is available the next time the user runs your app.

3. You should build in to your app functionality to change the configuration. It happens to me all the time. I have a commercial app that is sold to large organizations (think 1000's of PC's, with approximately 5 of them connecting to my db with my app). Every couple months I get a phone call from the IT director informing me that they are switching to another server, and they want to know what they need to do to get the database moved. Understand that it's not the same organization every couple months, but based on my customer counts and their requirements, it happens frequently enough to become a problem. Anyway... make it easy for them to move the database to another computer. You'll be glad you did. With my app.... I tell them to pop in the installation CD and install the server component. Then I tell them to detach the database from the old computer, copy it to the new one, and reattach it. Then, to re-configure the app, they simply click a button on my login dialog and run through a wizard.

The mistake I made (years ago) was to NOT install SQL Server as a named instance. You see, on these large networks, it's not uncommon to have dozens of SQL instances running. With a named instance, it's a lot easier to identify which instance belongs to your app. Additionally, with a named instance, IT directors are more willing to allow you enough privileges to do what you need to do. Ya know. Every IT director that knows anything about SQL Server will want to lock down your privileges (no xp_cmdshell, or sp_oa functions). However, with a named instance, they will probably allow you to have SA privileges which allows you to create new database, and generally just administer your database (like making backups).

Now, while we are at it....

I recommend you install your named instance with mixed mode authentication. When you log in to a database, there are 2 methods for authenticating your login. You can use windows authentication or SQL Server authentication. To use windows authentication you need to be logged in to a domain and the group that your (windows) login is associated with must be configured on the SQL instance. This requires a bit of configuration that you may not want to get in to for a simple app. However, if you install a named instance that supports mixed mode authentication (Windows and SQL authentication), AND you have a hard coded system administrator account built in, then you can programmatically add logins and user to the database.

As you can see... this is a bit complicated, and is likely going to require some fiddling to get it right. I encourage you to download Microsoft Virtual PC. (It's free) This will allow you to test your install program multiple times without needing another computer that you are constantly re-formatting.

I wish you luck.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[assimang]

Thanks all of you.
E&f I tried to create a new account but unfortunately I am getting error 26.

George,
You recomended me instead of sqlexpress, choose something like my company name or my product name. Does this means that I have to uninstall sql server and reinstall it, changing the instance name? And if I don't do this will it get me into trouble? Initially I want to install my application in personal computers. I don't know later, but why not in large networks?
About the first step: how can I include as a part of my installation a silent install of SQL Server express?
About the second step: What do you mean a configuration file? Is that a file that must I have to create? And if yes, what type of file will it be? And how can I access it's information? Also what is a domain?
Althouth, I just found another way, I don't know if it's a good idea I want you opinion, both of you, which includes these steps: 1)Enabling Protocols
2)Configuring a Fixed Port
3)Opening Ports in the Firewall
4)Connecting to the Database Engine from Another Computer
5)Connecting Using the SQL Server Browser Service
from Configuration Surface Area.

 

[gmmastros]

This is really a lot of information to throw at you. Sorry.

Install SQL Server Express from the command line. You will see options for installing with a different instance name.

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

The config file I mentioned is just that. It's a file that you create. There's gotta be a million examples for creating, accessing and updating an ASCII file from within vb.net. Google is your friend.

A domain is a special kind of server that authenticates logins. You don't usually run in to these issues on a stand alone PC, but most networks will have a domain controller. This allows you to set permissions for each user. (Ex: Janitor is not allowed access to payroll information).

1)Enabling Protocols
Usually not an issue. You should be aware of it because in rare cases, it's going to be the solution to why your users cannot connect to the database.

2)Configuring a Fixed Port
Also usually not an issue. In a larger network, they usually have DNS setup and installed. This prevents you from needing to know the IP Address and port number for the server (where your database is located).

3)Opening Ports in the Firewall
This one, unfortunately, is common. If you have your database on a server, and a firewall is blocking access to the port SQL Server is using, then you won't be able to connect to the database.

4)Connecting to the Database Engine from Another Computer
This is pretty much handled with 1, 2, & 3.

5)Connecting Using the SQL Server Browser Service
from Configuration Surface Area.
Not really an issue unless you are doing things with the database that you really shouldn't be doing (like xp_cmdshell, and the sp_oa functions).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[assimang]

Thanks everybody again

George it sounds very complecated and I don't know if I finally decide to follow this.
E&F I managed to attach the file through my settings pane and my connection string is: Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\AngelTypingDbFldr\typing.mdf";Integrated Security=True;Connect Timeout=30;User Instance=False
I created a folder AngelTypingDbFldr in deployment project I set both mdf and ldf files to permanent and transitive to true and it works fine.