assign a table to a path and filename?

Hi group
I'm a newbee, so please bear with me if this is a stupid question.
I would like to have a one to one relationship on the server with
regard to tables created in a database and the file(s) the data ist
stored. From what I understand, when I create a database on the
(sql2005) server, I have ONE file per database by default. I'm aware
that the server administrator can modify this but that's not what I'm
after.
Due to reasons beond what fit's into this post here, I would like to
have the clients (using ODBC) define not only a new table (obviousely
useing CREATE TABLE) but also in which file on the server the table
data should be stored. Is this possible and if so how?
TIA
MarkusMarkus Zingg wrote:
> Hi group
> I'm a newbee, so please bear with me if this is a stupid question.
> I would like to have a one to one relationship on the server with
> regard to tables created in a database and the file(s) the data ist
> stored. From what I understand, when I create a database on the
> (sql2005) server, I have ONE file per database by default. I'm aware
> that the server administrator can modify this but that's not what I'm
> after.
> Due to reasons beond what fit's into this post here, I would like to
> have the clients (using ODBC) define not only a new table (obviousely
> useing CREATE TABLE) but also in which file on the server the table
> data should be stored. Is this possible and if so how?
> TIA
> Markus
What do you hope to achieve by creating separate files per table?
Doesn't make much sense the way you've described it.
Tables map to filegroups. A filegroup can consist of a single a file or
multiple files. There are two options for putting tables into files.
Either you can use the ON clause in the CREATE TABLE statement to
specify a filegroup directly or you can specify a partition scheme
which maps the table onto filegroups.
In either case the filegroups and their file(s) must be created first.
Distributing that responsibility between many different users on a
production server is surely a recipe for chaos and disaster. But that's
a management issue rather than a technical one. Possible? Yes.
Desirable? Almost certainly not.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
Thanks a lot for your reply.
[snip]
>Tables map to filegroups. A filegroup can consist of a single a file or
>multiple files. There are two options for putting tables into files.
>Either you can use the ON clause in the CREATE TABLE statement to
>specify a filegroup directly or you can specify a partition scheme
>which maps the table onto filegroups.
I'm very glad to hear that there is a way to do this. The reasoning
behind this is that I'm about to write a piece of middleware that
interfces an existing huge long existing application so far not
supporting ODBC to SQL-Server. While it would be feasable to actually
have SQL-Server handle filesets etc. by default, being able to
controll this by the application (and NOT by the users) introduces
fewer changes to how things work. There is an absolut clear and well
defined schema used by the application already and no chaos can or
will be the result of this.

>In either case the filegroups and their file(s) must be created first.
>Distributing that responsibility between many different users on a
>production server is surely a recipe for chaos and disaster. But that's
>a management issue rather than a technical one. Possible? Yes.
What ODBC (or better said SQL') statement would one use to create the
propper filesets first? Having my middleware create the filesets
before the tables are created would otherwise not be a problem.

>Desirable? Almost certainly not.
It depends. The application manages up to several hundred databases of
the same structure (one database per firm is what I think of). Data
should be backed up and restored based on firms so as others are not
affected etc. I'm sure there are probably other ways to achive the
same thing, but a gain, the application already manages all details
along this paradigm and I would apreciate to keep things this way if
possible.
Markus|||I noticed you said
"Data should be backed up and restored based on firms so as others are
not affected etc"
are you attempting to write some backup and restore software? if so you
should be aware that a diskcopy of the database files will probably not
even work, and will certainly not mean that you can safely restore just
by copying back over them|||On 7 Apr 2006 04:46:23 -0700, "Will" <william_pegg@.yahoo.co.uk> wrote:

>I noticed you said
>"Data should be backed up and restored based on firms so as others are
>not affected etc"
>are you attempting to write some backup and restore software? if so you
>should be aware that a diskcopy of the database files will probably not
>even work, and will certainly not mean that you can safely restore just
>by copying back over them
Hi Will
I'm aware of these limitations and no I'm not trying to write
backup/restore software. As Dave said it's more an organisational
thing, and to be honest I'm now also simply curious...
Apart from this I think though that it's eventually easier / more
straight foreward to backup/restore firms using the tools provided if
the data per firm resides in it's own directory and filesets than if
all data would be within one single database - could be wrong here or
it could not matter. I just don't see an obvious divantage of not
also doing it the way it is made with the existing database system.
Especially because the application ensures that no chaos is created.
Markus|||One obvious advantage of keeping all tables in the same filegroup is
that the files only need one portion of unsused space.
SQL server will pad out the files such that it can write new data into
them without needing to fragment the file, the way this is done can be
specified in database options. If you were to split out every table
into its own file group then each filegroup would have to manage this
free space separately, so whereas with one filegroup any table that
grew could fill up the same free space, with multiple filegroups they
are only able to fill into the free space that their group contains.
The result of this would either be an overall increase in the database
size in order to provide enough space such that you're sure no table
would fragment, or you would regularly see filegroups having to resize
themselves, and fragmenting as a result of this.
Not to mention the advantages in terms of disk access if all the tables
are in one filegroup (which is not fragmented). Mainly not to mention
it because I can't quantify it though
Cheers
Will|||Markus Zingg wrote:
> Apart from this I think though that it's eventually easier / more
> straight foreward to backup/restore firms using the tools provided if
> the data per firm resides in it's own directory and filesets than if
> all data would be within one single database
Not so. The file location is pretty irrelevant as far as backups are
concerned because databases are specified by name, not by file
location. From a performance point of view it is far better to put
files on separate drives rather than in one directory. Also, the more
files you have the bigger task it is for someone to manage storage
allocation for each file. Unless your data is entirely static you need
a sensible plan for allocating storage. Incrementally expanding the
files is a very bad idea. So nothing good is achieved by your scheme.
Instead it will likely generate a lot more fragmentation at the drive
level and performance will be adversely affected.

> - could be wrong here or
> it could not matter. I just don't see an obvious divantage of not
> also doing it the way it is made with the existing database system.
> Especially because the application ensures that no chaos is created.
But this is NOT their existing system - it is SQL Server. You need to
adapt if you are to get the most out of the product you have chosen to
use.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> Data
> should be backed up and restored based on firms so as others are not
> affected etc.
Does this mean that you try to backup part of a database, i.e., the set of t
ables (and as per your
desire, files) that constitutes a firm, for instance? If so, this will not b
e a good thing. Backup
is done at the database level in SQL Server. Sure, there is something called
filegroup backup, but
you won't be able to restore only one filegroup. Filegroup backup is much mo
re complex than database
backup and I strongly encourage you to read up on the subject before plannin
g further (if this is
your intention).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Markus Zingg" <m.zingg@.nct.ch> wrote in message news:t5fc32det43em6p9cr12eofoc14ela22ib@.
4a
x.com...
> Hi David,
> Thanks a lot for your reply.
> [snip]
> I'm very glad to hear that there is a way to do this. The reasoning
> behind this is that I'm about to write a piece of middleware that
> interfces an existing huge long existing application so far not
> supporting ODBC to SQL-Server. While it would be feasable to actually
> have SQL-Server handle filesets etc. by default, being able to
> controll this by the application (and NOT by the users) introduces
> fewer changes to how things work. There is an absolut clear and well
> defined schema used by the application already and no chaos can or
> will be the result of this.
>
> What ODBC (or better said SQL') statement would one use to create the
> propper filesets first? Having my middleware create the filesets
> before the tables are created would otherwise not be a problem.
>
> It depends. The application manages up to several hundred databases of
> the same structure (one database per firm is what I think of). Data
> should be backed up and restored based on firms so as others are not
> affected etc. I'm sure there are probably other ways to achive the
> same thing, but a gain, the application already manages all details
> along this paradigm and I would apreciate to keep things this way if
> possible.
> Markus
>|||Hi David,

>Not so. The file location is pretty irrelevant as far as backups are
>concerned because databases are specified by name, not by file
>location. From a performance point of view it is far better to put
>files on separate drives rather than in one directory. Also, the more
>files you have the bigger task it is for someone to manage storage
>allocation for each file. Unless your data is entirely static you need
>a sensible plan for allocating storage. Incrementally expanding the
>files is a very bad idea. So nothing good is achieved by your scheme.
>Instead it will likely generate a lot more fragmentation at the drive
>level and performance will be adversely affected.
[snip]

>But this is NOT their existing system - it is SQL Server. You need to
>adapt if you are to get the most out of the product you have chosen to
>use.
Thanks for pointing out the divantages this aproach is having with
SQL server. I highly apreciate your feedback, and I'm here to find a
good way to implement this. I'm ready to listen :-)
I try to give a better picture of what I need then.
The system is a system for acountant firms. In the market we operate,
such acountant firms tend to have hundreds of coustomers. Every such
customer forms/is a firm and because of legal reasons it's obvious
that firm data should be strictly separeted. Since every firm is
treated with the same application it's asured that the data structure
of all firms is absolutely identical.
The acountants usually support up to 20 customers (or firms). Since
they usually don't work on those firms concurently, we also can say
that the vast majority of firm databases just sit idle because the
individual acountant will decide to work with the vouchers of a given
firm, then move on to the next one and so on.
Another fact is that in our market only relatively small firms
completey outsource their acounting. If we therefore create a database
per firm, such a database should not be huge in size, but there will
be many of them each of which is holding somewhere around 260
different tables.
Considering what you said I therefore think that I should at least
create a database per firm then. If I want to assign a database to a
given fileset, the question still stands how an ODBC client can create
a fileset? I like to know this, cause the acountant simply open a new
firm using the application and then the database creation etc. is
handled by the existing application which now also should operate with
SQL server. Needing a symin to create filesets manually is
definately not possible.
Markus|||I get the feeling you've come from an access background, could you
explain more clearly the legal reasons that every customer needs their
own database. you say they must be separated, but there are different
interpretations of separated, just having a unique primary key, or
having a foregn key to group together a firms records could be
considered separation.