store sql server - multiple databases in the same server - vw 7.7.1
Locked
store sql server - multiple databases in the same server - vw 7.7.1
 
|
Hi,
I have a need to create a second and third store database in our SQL Server. IIRC this wasn't supported in the past (7.4 and before). How can I do this with VW 7.7.1? Samuel, any sage words of wisdom here? Thanks much, -Steve _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
Since there's not been a flood of responses here :), I thought I'd
share what I've found. It appears as though the table owner could be switched out from BURN to something like BURN2 for instance which would create a separate set of tables in the same db. That appears to work find from the limited testing I've done so far. Thanks, -Steve On 7/25/2011 3:18 PM, Steve Whitson wrote: > Hi, > > I have a need to create a second and third store database in our SQL > Server. IIRC this wasn't supported in the past (7.4 and before). > > How can I do this with VW 7.7.1? Samuel, any sage words of wisdom here? > > Thanks much, > -Steve _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
Dear Steve,
I'm not understanding what the requirement is here. Is there a reason you do not just create a second and third database in SQLServer? (When you use store to login to each for the first time, you will be prompted to run the store DB creation in each. I assume this is how you created your first store DB.) For a given DB installation, I typically create several databases for various purposes. I will have several databases running on my Oracle installation, several more under my PostgreSQL installation, more under SQLite, etc. In PostgreSQL, I login to e.g. localhost:5432_postgresStoreDB for ordinary local saving, or localhost:5432_testReplicateTarget for replication testing, etc. I've never changed the table owner from BERN to another name. Yours faithfully Niall Ross >Since there's not been a flood of responses here :), I thought I'd >share what I've found. > >It appears as though the table owner could be switched out from BURN to >something like BURN2 for instance which would create a separate set of >tables in the same db. That appears to work find from the limited >testing I've done so far. > >Thanks, > -Steve > >On 7/25/2011 3:18 PM, Steve Whitson wrote: > > >>Hi, >> >>I have a need to create a second and third store database in our SQL >>Server. IIRC this wasn't supported in the past (7.4 and before). >> >>How can I do this with VW 7.7.1? Samuel, any sage words of wisdom here? >> >>Thanks much, >> -Steve >> >> > >_______________________________________________ >vwnc mailing list >[hidden email] >http://lists.cs.uiuc.edu/mailman/listinfo/vwnc > > > > _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
Hi Naill,
Your statement makes perfect sense when we are talking about something like SQLite or PostgreSQL, and from what you say Oracle too. We too do that all the time (in PostgreSQL and SQLite). I need the same capability in SQL Server. Currently the way it works is that there are two databases named NEWBURN1 and NEWBURN2 created when setting up a SQL Server database (when you installDatabaseTables), and that's just what they are. In 7.7 or 7.7.1 at least you can name them something else in the Store Settings... but this is a bit illogically for multiple users to share multiple databases (switching out this odd setting with each new login, or to support replication and such doesn't seem doable). But, by changing out the table owner name in the profile, that's the best I've found thus far (credit goes to a vw support person for suggesting that it might work). Thanks, -Steve On 7/27/2011 4:19 PM, Niall Ross wrote: > Dear Steve, > I'm not understanding what the requirement is here. Is there a > reason you do not just create a second and third database in > SQLServer? (When you use store to login to each for the first time, > you will be prompted to run the store DB creation in each. I assume > this is how you created your first store DB.) > > For a given DB installation, I typically create several databases for > various purposes. I will have several databases running on my Oracle > installation, several more under my PostgreSQL installation, more > under SQLite, etc. In PostgreSQL, I login to e.g. > localhost:5432_postgresStoreDB > for ordinary local saving, or > localhost:5432_testReplicateTarget > for replication testing, etc. > > I've never changed the table owner from BERN to another name. > > Yours faithfully > Niall Ross > >> Since there's not been a flood of responses here :), I thought I'd >> share what I've found. >> >> It appears as though the table owner could be switched out from BURN >> to something like BURN2 for instance which would create a separate >> set of tables in the same db. That appears to work find from the >> limited testing I've done so far. >> >> Thanks, >> -Steve >> >> On 7/25/2011 3:18 PM, Steve Whitson wrote: >> >> >>> Hi, >>> >>> I have a need to create a second and third store database in our SQL >>> Server. IIRC this wasn't supported in the past (7.4 and before). >>> >>> How can I do this with VW 7.7.1? Samuel, any sage words of wisdom >>> here? >>> >>> Thanks much, >>> -Steve >>> >> >> _______________________________________________ >> vwnc mailing list >> [hidden email] >> http://lists.cs.uiuc.edu/mailman/listinfo/vwnc >> >> >> >> > > _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
In reply to this post by Steve Whitson
Steve:
> That appears to work find from the limited > testing I've done so far. Well, it may not work in the future since we have changed how we test for table existence. We use INFORMATION_SCHEMA now, and we don't test for the table owner = TABLE_SCHEMA. We might have to, but for now, we don't. Somewhat better is to use the table spaces as Niall mentioned.... However those are global and not per connection/login based, and would require being changed for each login... Something else not optimal but without a solution for now. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
Hi Samuel,
When you say "We use INFORMATION_SCHEM now" do you mean in 7.8 by chance, but prior to 7.8 you didn't? Thanks much, -Steve On 7/27/2011 5:04 PM, Samuel S. Shuster wrote: > Steve: > >> That appears to work find from the limited >> testing I've done so far. > Well, it may not work in the future since we have changed how we test for table existence. We use INFORMATION_SCHEMA now, and we don't test for the table owner = TABLE_SCHEMA. We might have to, but for now, we don't. > > Somewhat better is to use the table spaces as Niall mentioned.... However those are global and not per connection/login based, and would require being changed for each login... Something else not optimal but without a solution for now. > > And So It Goes > Sames > ______________________________________________________________________ > > Samuel S. Shuster [|] > VisualWorks Engineering, Store Project > Smalltalk Enables Success -- What Are YOU Using? > > > > _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
In reply to this post by Samuel S. Shuster-4
What I do not understand is why Store for SQL Server is different from
support for every other database. This difference causes a lot of heartburn for our customers' IT departments. They do not want to dedicate a machine (server) per database; they do not want to launch several instances of SQL Server on a machine. What they want is to create multiple databases within the same instance of SQL Server -- a practice that is not just common but universal in the world of databases. When I asked why SQL Server support was the way it was -- almost 2 years ago now -- I was told no one knows and the author is no longer with the company. Mea culpa -- I should have taken that as a warning and advised my customers to use anything but Store for SQL Server. -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Samuel S. Shuster Sent: Wednesday, July 27, 2011 5:04 PM To: Steve Whitson Cc: [hidden email] Subject: Re: [vwnc] store sql server - multiple databases in the same server - vw 7.7.1 Steve: > That appears to work find from the limited testing I've done so far. Well, it may not work in the future since we have changed how we test for table existence. We use INFORMATION_SCHEMA now, and we don't test for the table owner = TABLE_SCHEMA. We might have to, but for now, we don't. Somewhat better is to use the table spaces as Niall mentioned.... However those are global and not per connection/login based, and would require being changed for each login... Something else not optimal but without a solution for now. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the sameserver - vw 7.7.1
|
|
Charles,
I'd love to see SQL Server support named databases for Store, the current situation is just plain silly and can't be that hard to address, can it? (I'm afraid of the answer a bit). We are a SQL Server shop for our main products and it would make total sense to run Store off the same servers instead of needing to install and maintain PostgreSQL (which is a fine database on its own and I've nothing against it per se, it served us well over the years). -Boris -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Adams, Charles Sent: Thursday, July 28, 2011 12:56 PM To: Samuel S. Shuster; Steve Whitson Cc: [hidden email] Subject: Re: [vwnc] store sql server - multiple databases in the sameserver - vw 7.7.1 What I do not understand is why Store for SQL Server is different from support for every other database. This difference causes a lot of heartburn for our customers' IT departments. They do not want to dedicate a machine (server) per database; they do not want to launch several instances of SQL Server on a machine. What they want is to create multiple databases within the same instance of SQL Server -- a practice that is not just common but universal in the world of databases. When I asked why SQL Server support was the way it was -- almost 2 years ago now -- I was told no one knows and the author is no longer with the company. Mea culpa -- I should have taken that as a warning and advised my customers to use anything but Store for SQL Server. -----Original Message----- From: [hidden email] [mailto:[hidden email]] On Behalf Of Samuel S. Shuster Sent: Wednesday, July 27, 2011 5:04 PM To: Steve Whitson Cc: [hidden email] Subject: Re: [vwnc] store sql server - multiple databases in the same server - vw 7.7.1 Steve: > That appears to work find from the limited testing I've done so far. Well, it may not work in the future since we have changed how we test for table existence. We use INFORMATION_SCHEMA now, and we don't test for the table owner = TABLE_SCHEMA. We might have to, but for now, we don't. Somewhat better is to use the table spaces as Niall mentioned.... However those are global and not per connection/login based, and would require being changed for each login... Something else not optimal but without a solution for now. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the sameserver - vw 7.7.1
|
|
Well, I would generalize the advice to customers as "use anything
but SQL Server", but that's just my personal opinion :-) Things
like the requirement to use identity columns and the length of time
it took them to get around to supporting multiple simultaneous open
cursors I find really annoying.
The reason that this is a problem on SQL Server isn't all that mysterious. I'm doing this without checking my facts, and I'm probably getting the terms wrong, but databases have different ideas about what constitutes a database, a table owner, schemas, tablespaces, and such things. SQL Server makes it harder to keep those things separate than others. In addition, the original implementation of SQL Server carefully uses two different "owners" (I think that's the right term for SQL Server) which it doesn't do on most database. This is probably the thing whose reasoning is lost in the mists of time and the person responsible is no longer around (and hasn't been for a very long time), but I think it's a pretty good guess that at one point it was a performance optimization. And no, it shouldn't be that hard to fix. The biggest issue is compatibility with existing databases. But I would think we could do something like - don't use two owners when creating a new SQL Server database - if connecting to an existing database, look for an owner that corresponds to the owner field in the login dialog - if the owner is blank, or perhaps if that owner doesn't exist, look for the old hard-coded name I say that stroking my pointy hair and leaving the details to others to implement, but I think it ought to be doable, it's just a small matter of programming.
_______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the same server - vw 7.7.1
|
|
In reply to this post by Steve Whitson
Steve:
> When you say "We use INFORMATION_SCHEMA now" do you mean in 7.8 by > chance, but prior to 7.8 you didn't? Actually I had to look that up. Yes, we first started to use it in 7.8 to determine if database: 1) A Store schema is installed and 2) If specific Store tables are installed (in order to determine which DescriptorSystem we must use). We moved from a "Try To Select Count From Blah And See If There Is An Error" to this, since in general, it is supported across MANY Database platforms, and is not dependent on catching specific errors. In SqlServer though, this table (actually a view) is One Per TableSpace vs others where it is one per Database. Really, the whole Schema, TableSpace, User thing is as bad across database platforms, or worse than the UI/GUI Platform thing... Just my luck I've had to work in BOTH areas. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the sameserver - vw 7.7.1
|
|
In reply to this post by Boris Popov, DeepCove Labs (SNN)
Boris:
> I'd love to see SQL Server support named databases for Store, the > current situation is just plain silly and can't be that hard to address, > can it? (I'm afraid of the answer a bit) Be afraid, be VERY afraid! As I stated/implied in another response to in this thread, there are many hurdles to overcome in the major database platforms by way of their different takes on various concepts. At this time we officially support DB2, PostGreSQL, SqlServer and Oracle for Store. We are working on SQLLite which may make it into the next release (it pretty much already works in 7.8, but not "officially supported" yet). A simple example of the issues at play are: Database Schemas, and their relationship to users, rolls/groups and Table Spaces. In PostGreSQL, they are explicitly named and exist in a "database". The relationship between user and schema doesn't exist except for the granting of rights to a roll (not users). TableSpaces to the extent that they exist, do not play in this at all. The INFORMATION_SCHEMA lives in a Catalog at the Database level. In Oracle, a "schema" is in effect associated with the user, TableSpaces exist but do not play in this at all. There is no INFORMATION_SCHMA in stead we have the global all_tables view. In SqlServer a "schema" is associated with a user and lives in a TableSpace. Unlike in Oracle (and PGSQL) you may not define a "relationship" between objects across TableSpaces. Each TableSpace has it's own local (user based) INFORMATION_SCHEMA I don't have my DB2 at hand to look it up, but it too is slightly different. Fortunately we now have Glorp which allows us to (mostly) have it deal with the differences. None the less, our Login system doesn't have per login TableSpaces, which is really kind of needed for SqlServer but none of the others (but would be useful but only in installing and removing a Store database in Oracle), and unfortunately even Glorp doesn't currently have all the mechanisms to deal with this added dimension. This is sort of like dealing with a four dimensional array Library[x][x][x][x]. In one system it is Library[Floor][Room][Shelf][Book] and in the next it is Library[Room][Floor][Shelf][Book] and in the next its Library[Building][Room][Aisle][Book].... Sometimes the only thing common is the Book. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in the sameserver - vw 7.7.1
|
|
Sames,
I have a sense that you could just assume dbo schema for your tables within a given SQL Server database. If someone wants to run 2 different Store databases, they can create a second SQL Server database. -Boris -----Original Message----- From: Samuel S. Shuster [mailto:[hidden email]] Sent: Thursday, July 28, 2011 2:46 PM To: Boris Popov, DeepCove Labs Cc: Adams, Charles; Steve Whitson; [hidden email] Subject: Re: [vwnc] store sql server - multiple databases in the sameserver - vw 7.7.1 Boris: > I'd love to see SQL Server support named databases for Store, the > current situation is just plain silly and can't be that hard to > address, can it? (I'm afraid of the answer a bit) Be afraid, be VERY afraid! As I stated/implied in another response to in this thread, there are many hurdles to overcome in the major database platforms by way of their different takes on various concepts. At this time we officially support DB2, PostGreSQL, SqlServer and Oracle for Store. We are working on SQLLite which may make it into the next release (it pretty much already works in 7.8, but not "officially supported" yet). A simple example of the issues at play are: Database Schemas, and their relationship to users, rolls/groups and Table Spaces. In PostGreSQL, they are explicitly named and exist in a "database". The relationship between user and schema doesn't exist except for the granting of rights to a roll (not users). TableSpaces to the extent that they exist, do not play in this at all. The INFORMATION_SCHEMA lives in a Catalog at the Database level. In Oracle, a "schema" is in effect associated with the user, TableSpaces exist but do not play in this at all. There is no INFORMATION_SCHMA in stead we have the global all_tables view. In SqlServer a "schema" is associated with a user and lives in a TableSpace. Unlike in Oracle (and PGSQL) you may not define a "relationship" between objects across TableSpaces. Each TableSpace has it's own local (user based) INFORMATION_SCHEMA I don't have my DB2 at hand to look it up, but it too is slightly different. Fortunately we now have Glorp which allows us to (mostly) have it deal with the differences. None the less, our Login system doesn't have per login TableSpaces, which is really kind of needed for SqlServer but none of the others (but would be useful but only in installing and removing a Store database in Oracle), and unfortunately even Glorp doesn't currently have all the mechanisms to deal with this added dimension. This is sort of like dealing with a four dimensional array Library[x][x][x][x]. In one system it is Library[Floor][Room][Shelf][Book] and in the next it is Library[Room][Floor][Shelf][Book] and in the next its Library[Building][Room][Aisle][Book].... Sometimes the only thing common is the Book. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in thesameserver - vw 7.7.1
|
|
In reply to this post by Samuel S. Shuster-4
I've never heard of SQL Server having TableSpaces... Is that just me, or do you mean some other SQL Server concept, e.g. filegroups or databases?
I've always wondered why Store (on SQL Server) splits itself into two databases, NEWBERN1 and NEWBERN2, despite the obvious links between them. Putting the (B)LOBs in their own table is common practice, but I don't know the motivation for putting them in their own database. Is it an attempt to mirror something that makes sense on Oracle (or whichever other database Store started on)?
Steve From: [hidden email] on behalf of Samuel S. Shuster Sent: Thu 28/07/2011 21:45 To: Boris Popov, DeepCove Labs Cc: [hidden email] Subject: Re: [vwnc] store sql server - multiple databases in thesameserver - vw 7.7.1 Boris: _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: store sql server - multiple databases in thesameserver - vw 7.7.1
|
|
Steven: I've always wondered why Store (on SQL Server) splits itself into two databases, NEWBERN1 and NEWBERN2, despite the obvious links between them. Putting the (B)LOBs in their own table is common practice, but I don't know the motivation for putting them in their own database. Is it an attempt to mirror something that makes sense on Oracle (or whichever other database Store started on)? In their own Table Space, not Database, although I admit that in SqlServer the line is very blurred. In Oracle for instance, this is not so, a Database can have multiple Table Spaces and each of these can be on the same "device" or multiple "devices." Sooo.... If you have them on separate devices and you have a smart server and so on and so on. Again, the trouble is that these concepts don't translate perfectly from one Database platform to the next, and the devil is in the details. This is related to why we have slowly gotten rid of ALL of the Database based permission and user/group/roll management code in Store... Now totally gone in 7.8. Either you're the only person using the DB, which makes everything E-Z, or you're local and have a local <air-quote>DBA<air-quote> or a real DBA for a specific platform is best suited. Trying to deal with the differences, even in Glorp is a huge undertaking. And So It Goes Sames ______________________________________________________________________ Samuel S. Shuster [|] VisualWorks Engineering, Store Project Smalltalk Enables Success -- What Are YOU Using? _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
«
Return to VisualWorks
|
1 view|%1 views
| Free forum by Nabble | Edit this page |