Advice for product manager
-
Would anyone happen to know of any public web pages or documents, by respected authorities in the field that would definitively convince a product owner that creating tens of thousands of databases in a single SQL Server is not a reasonable thing to do? In addition, having Joe-blow users creating and deleting databases is not a good idea, i.e. these are administrator only tasks. The product in question is an update of a previous product where there was a single ‘database’ per profile. Only in the old version, the ‘database’ was just a single file with no constraint checking.
I ran across this type of system in the 90s, app was build on system5 or some such crap. The owner wanted exactly the same thing written in Access or SQL server, same functionality and everything. When I flatly refused to even discuss the solution and offered to walk away from the contract he saw the light. Seriously if you cannot move him off this idiotic design, quit, move on your stress levels will appreciate it.
Never underestimate the power of human stupidity RAH
-
Would anyone happen to know of any public web pages or documents, by respected authorities in the field that would definitively convince a product owner that creating tens of thousands of databases in a single SQL Server is not a reasonable thing to do? In addition, having Joe-blow users creating and deleting databases is not a good idea, i.e. these are administrator only tasks. The product in question is an update of a previous product where there was a single ‘database’ per profile. Only in the old version, the ‘database’ was just a single file with no constraint checking.
Try this article from Microsoft. http://msdn.microsoft.com/en-us/library/aa479086.aspx[^]
-
Try this article from Microsoft. http://msdn.microsoft.com/en-us/library/aa479086.aspx[^]
Thanks for the link to the great article i.j.russell, I'm still reading and learning. Ah, I see, my description of the question wasn't very good. I should expand a bit on what I mean by profile. A better description would be an item where multiple, think thousands or tens of thousands, of these items are associated with a single tenant. The new software being developed copied a lot of code from the old version where an item, single file, was represented as a database using an API provided by an antiquated database product. The new software is trying to use this same scheme in SQL Server, one database per item. Since there can be tens of thousands of items, there will be tens of thousands of databases. I can hear the murderous laughter of database administrators everywhere. I think this scheme breaks a number of well-established guidelines but I’d like to give the product owner ammunition, concrete definitive documentation, to force the developers to change the product to something more reasonable. (Oh, and I’m one of the developers – just showing up late in the development cycle of the product.) The product owner is not very technical and so may have been convinced for some reason or other that this was an okay solution.
-
Thanks for the link to the great article i.j.russell, I'm still reading and learning. Ah, I see, my description of the question wasn't very good. I should expand a bit on what I mean by profile. A better description would be an item where multiple, think thousands or tens of thousands, of these items are associated with a single tenant. The new software being developed copied a lot of code from the old version where an item, single file, was represented as a database using an API provided by an antiquated database product. The new software is trying to use this same scheme in SQL Server, one database per item. Since there can be tens of thousands of items, there will be tens of thousands of databases. I can hear the murderous laughter of database administrators everywhere. I think this scheme breaks a number of well-established guidelines but I’d like to give the product owner ammunition, concrete definitive documentation, to force the developers to change the product to something more reasonable. (Oh, and I’m one of the developers – just showing up late in the development cycle of the product.) The product owner is not very technical and so may have been convinced for some reason or other that this was an okay solution.
The other option to consider is going the NoSql route and using something like MongoDb, CouchDb or even cloud-based storage like Microsoft Azure Table Storage or Amazon SimpleDb.
-
The other option to consider is going the NoSql route and using something like MongoDb, CouchDb or even cloud-based storage like Microsoft Azure Table Storage or Amazon SimpleDb.
Good options, though I'm pretty sure they are going to use SQL Server no matter what. The customers of the product are typically Microsoft shops so they really want SQL Server. I just can't imagine how the developers convinced the product owner that creating tens of thousands of databases would be okay.
-
Good options, though I'm pretty sure they are going to use SQL Server no matter what. The customers of the product are typically Microsoft shops so they really want SQL Server. I just can't imagine how the developers convinced the product owner that creating tens of thousands of databases would be okay.
Sql Server will actually do what they want as it supports up to 32,767 databases per server instance. http://msdn.microsoft.com/en-us/library/ms143432%28SQL.90%29.aspx[^] However, I would love to see the look on the first DBAs that are asked to support this application; I am fairly certain that it will not be pretty!
-
Sql Server will actually do what they want as it supports up to 32,767 databases per server instance. http://msdn.microsoft.com/en-us/library/ms143432%28SQL.90%29.aspx[^] However, I would love to see the look on the first DBAs that are asked to support this application; I am fairly certain that it will not be pretty!
-
Exactly, 'technically possible' but a horrendous idea. I'm guessing this is how development 'convinced' the product owner. Any good web pages to convince them that this is a ridiculous idea?
I can't find any sites that would help, but that in itself is helpfull because it means that the concept is either uber-cutting edge or plain stupid. Since Sql Server has been around for well over a decade, I am going for the latter. From a technical perspective, I can think of a few issues; 1. Your hardware requirements are going to be well in excess of what your application data should need. Your data should be easily handled by a single Sql Server Express database. 2. Connection pooling is going to be interesting as each database will have a different connection string. Performance will be adversely affected as new connections are created. 3. Whilst you can carry out DDL in a stored procedure to create a database, table(s) and indexes, this will take a few seconds each time.
-
Exactly, 'technically possible' but a horrendous idea. I'm guessing this is how development 'convinced' the product owner. Any good web pages to convince them that this is a ridiculous idea?
I'd go with i.j's plain stupid scenario, this is so far outside sensible that it would not be entertained by any reasonable designer. Consider a code base change to the database, maintenance will be an absolute nightmare and the cost of maintaining an app is much higher than developing the app.
Never underestimate the power of human stupidity RAH
-
Exactly, 'technically possible' but a horrendous idea. I'm guessing this is how development 'convinced' the product owner. Any good web pages to convince them that this is a ridiculous idea?
The key to your argument should be about cost; each cost can then have a technical answer to the question - for example, annual maintenance cost is $500k as we need to employ a team of DBA's to manage 1000's of databases Give your suggested solution with a cost, give his solution with it's associated costs. You can always ham it up a bit to ensure that your solution looks better. This will get his interest far more than technical discussions on the merits or otherwise of solutions.