|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
assign a table to a path and filename?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 Markus Zingg wrote:
Show quote > Hi group What do you hope to achieve by creating separate files per table?> > 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 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 I'm very glad to hear that there is a way to do this. The reasoning>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. 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. What ODBC (or better said SQL??) statement would one use to create the>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. 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 ofthe 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_p***@yahoo.co.uk> wrote: Hi Will>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 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 disadvantage 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:
> Not so. The file location is pretty irrelevant as far as backups are> 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 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 But this is NOT their existing system - it is SQL Server. You need to> it could not matter. I just don't see an obvious disadvantage 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. 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 -- Hi David,
>Not so. The file location is pretty irrelevant as far as backups are [snip]>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. >But this is NOT their existing system - it is SQL Server. You need to Thanks for pointing out the disadvantages this aproach is having with>adapt if you are to get the most out of the product you have chosen to >use. 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 sysadmin 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. On 7 Apr 2006 07:01:53 -0700, "Will" <william_p***@yahoo.co.uk> wrote: No, not access. It's a proprietary database management system.>I get the feeling you've come from an access background, Actually the reason why there is a demand for this task is that we have a relatively small group of coustomers who - due to policy reasons - want SQL server. There is a bigger group of coustomers who don't care about what database system is used but want to use Office applications to access our data. Therefore the complete integration will be two folded. We are a) going to support SQL server as the engine but b) also will create ODBC drivers for the existing system. >could you I.e. it's possible that a customer decides to run our application on>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. his own and then want's to take his data with him in an easy painless fasion. Having the firms seperated by key actually would mean a 100% rewrite of the existing application which is no option. So from a table point of view, firm data will be seperated anyways. I'm also quite sure that the acountant firms will like the idea that firm data is seperated on a file level. I have no problems with all tables of a firm residing in one fileset, but having all firms within the same fileset is doable, but I don't think desireable. Markus ok - apologies about the access comment, it's just you appeared to be
considering databases and files as the same thing. you could have all the data stored in one table, but then provide access to it in different databases through the use of views. this would provide you with the flexibility required for your application, but would allow the SQL server to operate in the way it was intended. As for firms liking the data being separated on a file level, why not go to even more extreme and separate them on a disk level, or have a separate server per customer - they'd be happier with that! >As for firms liking the data being separated on a file level, why not Oh, I would love to sell the acountant firm a new server per customer>go to even more extreme and separate them on a disk level, or have a >separate server per customer - they'd be happier with that! they have, but then they probably would first have to rent/build an new building for all those servers to be stored in :-) Cheers Markus Markus Zingg wrote:
> I personally doubt that there are legal reasons to have separate> 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. databases per customer. Of course I'm not a lawyer but I'd be interested to know where you get that information from if that's what you mean. A more likely reason is that it will be easier to "sell" the solution to customers if you can say the databases are separate. That may be a "good" reason to do it from a customer relations perspective, but it isn't a good *technical* reason to do it. What I mean is that security is not scoped only to the database level so separate databases are not fundamentally more secure than one database. Anyway, the answer is to use the FILE options on the CREATE DATABASE and ALTER DATABASE commands. Take a look at those topics in Books Online. -- 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 -- AFAIK data belonging to different entities (legal subjects) must be
sufficiently *isolated*. Physical separation is IMHO *not mandatory*. I don't know of any such US statutes or laws, but in the EU there are no such inhibitions. A few illustrations: The privacy of individuals (customers) is protected by law - Company A cannot access Customer data that belongs to Company B without proper prior consent from each individual customer. The property of individual commercial subjects (companies) is protected by law - Company A cannot see Items in Company B's stock, unless Company B has made those records public or has explicitly shared the data with Company A. The misinterpretation of these norms leads into such silly implementations as is the one in this thread. Or are CA certificates for each company stored each in its own database? How insane would that be? ML --- http://milambda.blogspot.com/ >I personally doubt that there are legal reasons to have separate legal in the strict sense surely stresses it. Btw. we operate all over>databases per customer. Of course I'm not a lawyer but I'd be >interested to know where you get that information from if that's what >you mean. the globe, but mainly in europ and there not only in EU countries. What I'm trying to say is that not only US laws / etics / ways to do things apply here. >A more likely reason is that it will be easier to "sell" the This kind of reasons is much more what I meant.>solution to customers if you can say the databases are separate. >That I have no doubt that this is true. But explain this to an acountant or>may be a "good" reason to do it from a customer relations perspective, >but it isn't a good *technical* reason to do it. What I mean is that >security is not scoped only to the database level so separate databases >are not fundamentally more secure than one database. boss of such a firm. Besides, considering the relatively small size of each resulting database I'm quite sure perfomance will be ok. >Anyway, the answer is to use the FILE options on the CREATE DATABASE Thank you very much for this information. Will do so.>and ALTER DATABASE commands. Take a look at those topics in Books >Online. Markus > Data Does this mean that you try to backup part of a database, i.e., the set of tables (and as per your > should be backed up and restored based on firms so as others are not > affected etc. desire, files) that constitutes a firm, for instance? If so, this will not be 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 more complex than database backup and I strongly encourage you to read up on the subject before planning further (if this is your intention). -- Show quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ "Markus Zingg" <m.zi***@nct.ch> wrote in message news:t5fc32det43em6p9cr12eofoc14ela22ib@4ax.com... > 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 >  |
|||||||||||||||||||||||
Other interesting topics