sqlexpress connection string

Hi all...

1)
connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\mydb.MDF;Integrated Security=True;Connect Timeout=60;User Instance=True"

2)
connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True"


what is the difference in these .. which one is best .. and when shall i use each one of them ?
what about deployment .. are they the same ?

thanks.

LVL 9
MisbahAsked:
Who is Participating?
 
kenpemCommented:
Unless you have a specific need to directly access the data via its file name as in

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=E:\mydb.MDF;Integrated Security=True;Connect Timeout=60;User Instance=True"

it is ALWAYS better to just refer to the database, as in

connectionString="Data Source=localhost\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True"

The database could move physical location. The file could have been renamed, etc. Directly referring to the file name could possibly allow you to run a few queries on an otherwise unattached database file, but I don't see how that could be useful - you are bypassing all sorts of controls that SQL puts in place. You would have to be running at an administrative level for this to work, and this is generally not a good idea.

Performance: there will be a time penalty on initial connection while the file is attached and checked, but otherwise there should be no appreciable difference.

Security: since you can only do this as an adminitrative-level user, you've bypassed most of the security features that SQL would otherwise assist with.

Deployment: will the application absolutely KNOW where the physical data file is going to be? And where it ALWAYS will be, even years in the future? If the answer is not an emphatic "YES", then you can't really consider using the direct-filename method - any deployed applications will need updating if the file is ever moved or renamed. If the connection is based simply on db name, there is no such problem.

In summary, I cannot imagine when I would ever prefer to use the direct-filename connection method.

Hope this helps!
0
 
MisbahAuthor Commented:
sorry .. assume :
Data Source=.\SQLEXPRESS

in both
0
 
imran_fastCommented:
The second one is the best because it is refering to the database name not the database file name.
It is not good to refer to the database Data file directly.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MisbahAuthor Commented:
ok.. thanks .. but i need more information about this..

what is the difference ?? there must be a situation where one is more appropriate than the other ?? right ??

for example .. in the first one.. will the database file be attached to the server every time the programs run .. and
deattached when the program is finished ??

I need some explanation of how these things work

0
 
imran_fastCommented:
Check this link.
==========

www.connectionstring.com 
0
 
MisbahAuthor Commented:
what is this !!

did u check it ur self ???

I couldn't find any information there ... it has only sponsored links !!! ???

and plz.. I am here to save my time and increase my productivity
0
 
imran_fastCommented:
Wow how come  this looks totally different now.

ok this is what you need to know.

which eliminates the need to have a DBA attach the database files to a server before they can be used. When the AttachDBFilename keyword is included in a connection string, the specified file is attached to the SQL Server instance and the client is connected to the newly attached database
The |DataDirectory| is a shortcut for the directory where the program opening the connection lives. To attach a file in a different directory, you must provide the full path to the file. The log file in this case is named Database1_log.ldf and is located in the same directory as the database file. If the database file is already attached to the SQL Server instance, the connection is opened to the existing database.

This is a great option because if you are an administrator, you can attach and connect to a database file by specifying the file name in the connection string in your application. Many developers run as administrators on their systems so AttachDBFilename works well for them. The problem is that Microsoft strongly recommends NOT running as an administrator because this reduces the amount of damage that can be inflicted by a virus. What we need in this case is a way to use AttachDBFilename without being a member of the Windows Administrators group. The solution is the User Instance feature.



But if you have the database already attached to your sql server than why using this option and this option is extended only in .netframework 2.0
0
 
imran_fastCommented:
Do you need further assitance  ?
0
 
MisbahAuthor Commented:
well ..
my friend u gave me links and huge material to read .. I just need a short answer from an expert who really understand this .. in ur first post u said that the second option is the best .. and in a next post u said the frst is great ... so I am confused now !!!!

my question is still not answered.. :

what is the difference in these (performance, security, deployment .....) .. which one is best .. and when shall i use each one of them ?
what about deployment .. are they the same ?
0
 
kenpemCommented:
imran gave you the answer in his very first comment.

The first form is bad. Do not use it.

The second form is good. Use it.

Is that short enough?
0
 
MisbahAuthor Commented:
ok .. thanks Kenpem ..

your answer is too short .. it does not  really cover my question above :)

what is the difference in these (performance, security, deployment .....) .. which one is best .. and when shall i use each one of them ?

u answered which one is the best ... but based on what !!!

I need a good explanation .. no problem how long or short it is .. but remeber I don't like reading 15 pages of MSDN to get an answer that can be in only 5 lines :)
0
 
imran_fastCommented:
hi kenpem,
can you post any comment in below link so that i can close that

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21898249.html
0
 
MisbahAuthor Commented:
ok thanks :)
0
 
kenpemCommented:
Dr-H, thanks for the points. However, imran_fast actually earned most of them - can you re-allocate them to him? Or at least split them?
0
 
MisbahAuthor Commented:
yes.. actually .. i was planing to split  the points .. but since i am new here .. i don't know how to do this ..
when I clicked 'accept answer' .. I didn't see  the spilt options !!

I will check now again .. to see how to do this .
0
 
MisbahAuthor Commented:
I could not find a way .. plz .. tell me how to do it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.