|
This is a bit of a different SQL question, but I thought I'd ask it here. Our database stores information about our customers. In general, there are very few times where we need to do queries across the entire customer base - what happens at one customer has absolutely no relation to the other ones, other than our people who are assigned to that customer. As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size. In general I do not feel that this is a good idea, if only because at some point you start to reach SQL's native limitations- number of databases, SP reuse in caching, and even just raw performance for backup/restore/indexing operations. Beyond that, when you do eventually have some level of cross-customer querying/reporting to do, it will not be very performant to query this, even if you go with a data waarehouse solution. Can anyone provide me with some more ammo as to why we would not want to go down this path?
|
#
¿
Nov 8, 2007 17:29
|
|
|
|
| # ¿ Apr 29, 2024 17:22 |
|
|
I have a sql 2005 object permissions issue that I can't quite figure out. One of our approaches to database security is to deny select/update/delete access to all tables, and then grant select/execute on views & stored procs. This has worked well for us to date, except for a new server installation we've just done. In that case, when we execute sp_foo, it tells us that select permission is denied on tbl_foo (referenced by sp_foo). The exact object path is: Windows Integrated login x assigned to user Y in the database. User Y is granted to role Z in the database. Role Z is granted select/execute on sprocs & views, and denied delete/insert/select/update/references on tables. We've compared a known good server with this bad server, and they're both the same (9.0.3054), both on 64-bit sql server 2005.
|
|
#
¿
Jan 25, 2008 23:42
|
|
|
yaoi prophet posted:Lots of great wisdom I cannot agree more with this. I get uncomfortable with ORM after a certain load. It really seems to me like there are two entirely different camps regarding databases: 1) Clean separation between the app tier and the database tiers. Enforced through by denying access to tables and driving access through stored procs, views, etc, allowing for a highly secure design and the ability to change the physical data model with no impact to the application tier. 2) The ORM camp. I hate to be short sighted, but it just seems like a pattern used by developers who see the database as nothing more than a file system. I know this is not correct, but there is some truth to it.
|
|
#
¿
Jan 30, 2008 04:21
|
|
|
I think the key item here is that if you're a DBA these days, you pretty much also need a copy of Visual Studio to truly round out your toolbox. Management Studio is pretty good, but not all the way there. I don't believe you can debug stored procs without it, and even the BI studio includes a stripped down version. Anything with SQLCLR definitely would benefit from it.
|
|
#
¿
Feb 13, 2008 18:57
|
|