How will MS SQL Server "find" my DBs? 1

[OrthoDocSoft]

Folks,

My app had 2 MS Access DB's that I used to move around to whatever folder I wanted, while keeping track of the file path, so my app could find it. Easy breasy.

As you know, I have upgraded to MS SQL Server, and I understand that MS SQL SERVER needs to run as a service in the background "supporting" my DB's on the same server (more or less).

But if MS SQL Server is running on a server, and I move my DB's to that server, without putting the DB's in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA, how do I make MS SQL Server "see" and deal with my newly added DB's?

In other words, how do I "tell" SQL Server about the new DB's that just got put on the server with it, so it can support it?

The new MS SQL Server connect string does NOT appear to need a precise file path, but rather; only wants to know where the Data Source (ie, MS SQL Server) is. For my home computer, this was the "name" of my computer (something like "JKKUSL73")

In fact, here is the new connect string (See? no file path

strConnectString = "Provider=SQLOLEDB.1;" _
& "Integrated Security=SSPI;" _
& "Persist Security Info=False;Initial Catalog=frozens;" _
& "Data Source=JKKUSL73"

Thanks,

Ortho

"you cain't fix 'stupid'...

 

[TLowder]

Why don't you want to put them in the path already used?

I may be wrong but I believe all the databases need to be in the same predefined path. Most my knowledge is in MySQL but I believe they're fairly similar. You can change the server settings to see the data in the new location or any path for that matter but then data in the original path will not be seen. While I'm sure it's possible, the path is not something you should set at runtime in vb, it's a database specific setting. If you were to change the path at runtime it would be by changing a settings file, not a database call while establishing a connection, then you would probably need to restart or refresh the service. Basically the path is predefined and shouldn't be changed on the fly. I would just go with the flow of how database services differ from Access mdb files. Maybe explian why you want to do this and there is a better way to accomplish the same task.


Tom

 

[OrthoDocSoft]

Tom,

Thanks! You may be right, and, of course, I will default to precisely the practice you describe, i.e.; putting my DB's in that folder. But I'm hoping for something a bit more elegant....

Ortho

"you cain't fix 'stupid'...

 

[JoeAtWork]

I don't see any reason to worry about where your database files are. As long as they are someplace secure and with adequate space is all that matters.

Most of my databases have been created in the default location and stayed there for years. Why would I move them around? It's just extra work for no reason. Even with MDB files I would keep them in the same place unless there was some rational reason to move them. Predictability is very helpful for maintenance.

To answer your original question, you don't need to specify the path because SQL Server already knows where its files are. Remember that, unlike Access (where all processing occurs on the client), it is the server that is doing all the work. You are just passing along commands to it. SQL Server is what maintains the files, updates them and decides when they need to grow - so it needs to know at all times where the files are.

 

[fredericofonseca]

one of the reasons to have your databases on another location is to split the logs, the main databasefile, and the system databases over different hard disks - or volumes. that way IO is spread over diff hardware.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

SQL Server "knows" where your database is (on the hard drive) by you telling it. You can only use a database after you attach it to the sql server instance. Do a quick google search on sp_attach_db and sp_attach_single_file_db. When you use these commands, you are effectively telling SQL Server where the database files are located.



-George

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

 

[OrthoDocSoft]

gmmastros,

Star for you, for understading what I meant. I only move the DB at the installation. When developing, my default DB location was on the client getting installed for simplicity and for "demo mode," but, of course; in a multi-node extension of the application, that won't work. So I have to "move" the DB to a server common to all nodes. One move; that is all. Thanks to all for their very helpful tips.

Ortho

"you cain't fix 'stupid'...