September 15, 2008 at 8:04 am
I tend to agree with the two posts above. I separate things out for two reasons:
- security, you can secure things, but the more co-located things are, the more you're asking for holes. The same could be said of a separate server, larger attack surface, but I think that it's better to have separate databases if you have data that requires it.
- scale, meaning that I might need to move this app/client/set of data to a new server. If so, then separate it out now.
September 16, 2008 at 9:31 am
Steve Jones - Editor (9/15/2008)
I tend to agree with the two posts above. I separate things out for two reasons:- security, you can secure things, but the more co-located things are, the more you're asking for holes. The same could be said of a separate server, larger attack surface, but I think that it's better to have separate databases if you have data that requires it.
- scale, meaning that I might need to move this app/client/set of data to a new server. If so, then separate it out now.
I agree Steve. I have a rather unique perspective here in that I have a client were I manage 6700+ databases on a single server for them. It is a payroll processing entity, and clearly the data disparate and NEEDs to be separate for both security and recoverability issues. If something gets FUBARd in one client, only that client is affected and only that client's database would have to be restored.
Steve's last point about scale-out applies too. We have the ability to drop in a second server and migrate databases should performance needs or new-client additions warrant it. With a single database all you can do is buy a bigger server, which has diminishing ROI.
NOTE that managing extremely large numbers of databases has significant issues. Essentially nothing will work with that (log shipping, mirroring, replication, third-party applications, etc, etc). SSMS, thank goodness, does perform quite well believe it or not!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 16, 2008 at 2:04 pm
Hey, an agreement with me! Usually I'm the one agreeing.
September 17, 2008 at 4:46 am
I have to wonder why anyone would ever consider keeping unrelated data in the same database. Separate databases provide the security and segregation that good internal controls require. I can't imagine trying to maintain security on a single database where some people need access to 100 tables, another set of folks need access to another 50 tables, etc. I'll probably have nightmares tonight just thinking about it.
September 17, 2008 at 6:49 am
Unrelated, like for another application? I wouldn't do it, but I think some people see SQL Server as a database and a single one at that. They don't get the login/user separation for separate databases.
Most people are 1 instance = 1 database and they can't understand any different. Vendors don't want anything else on the instance because it increases support costs.
September 17, 2008 at 7:29 am
Ross McMicken (9/17/2008)
I have to wonder why anyone would ever consider keeping unrelated data in the same database. Separate databases provide the security and segregation that good internal controls require. I can't imagine trying to maintain security on a single database where some people need access to 100 tables, another set of folks need access to another 50 tables, etc. I'll probably have nightmares tonight just thinking about it.
You can even have that situation in a large database where it is perfectly appropriate to have all the data in the one database - which is where schemas come into play. Quick example might be an ERP system where production, HR and finance all require some information from - say- the employee table, Finance and Production from BOMs, stock etc and you can streamline the process of filtering the data appropriately using the appropriate schema for each area linked to the user logins
September 17, 2008 at 10:49 pm
I can understand using schemas on ERP databases where it's essentially an integrated application. And, in my experience, most ERP systems have tools within the app to allow for flexible data extraction, with an abstraction layer that insulates users from having to know which tables to query - an important consideration given the cryptic names that ERP providers like to use for tables and columns. In that case, application security is usually enough to ensure users don't gain access to data they don't need to see. I will say that my company does not keep HR data in anything other than a dedicated HR system. The SAP systems used by HR run on their own servers, and access is very restricted.
In the situation described by the OP, where all of the apps uised the same database, I think that is the epitome of a really bad idea. Especially if users need to query the data directly for analysis, as is often the case with company developed apps.
September 19, 2008 at 6:31 am
I'd always go for separate databases, for a number of reasons, but starting with a drawback, you can't use DRI across databases, so you have to enforce Referential Integrity some other way (or not at all!)
As for the pros,
I think it's nice when you open a database to see just the objects etc. relating to the application you're working on, (and not to have to rely on naming conventions to identify your objects.)
If you do ever have to restore from backup, it's just your application that is affected - you don't bring everything down with it.
It gives you much more flexibility for doing promotions (you're less likely to be stepping on toes.)
Someone mentioned that you can't do indexed views across databases - true but why would you want to do an indexed view across applications? If the data are so closely linked that you want to do an indexed view across them, then nominate an 'owner' application, and put all these tables in the same db.
I have tried both approaches but am now a firm believer in 1 app 1 database. We also use dbchaining whereby if the dbowners and object owners are the same, you can query across dbs without any explicit permissions. (There are security considerations with db chaining - but if you know what these are and are happy with them, then it makes life much easier.)
Another 'standard' I follow is to ALWAYS use a SYNONYM for any access to another database. That way I know where I leave my database, and if another database moves or changes name etc. I can just update my Synonyms.
I think the pain only comes when you're talking about cross server / instance queries with linked servers in play. What I'm for is multiple databases on the same sql instance.
Regards,
David.
September 19, 2008 at 12:20 pm
I can think of another "pain point" to SYNONYMS - someone (as in me, a consultant) having to step in and debug/tune the queries that use synonyms. I absolutely despise the amount of time I waste doing the 'dereference dance'!! :crazy:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 22, 2008 at 2:24 am
Ross McMicken (9/17/2008)an important consideration given the cryptic names that ERP providers like to use for tables and columns.
Hey - what's cryptic about ttdsls045400? 😛
September 23, 2008 at 6:19 am
the lead programmer who designed and built the system here put one database per customer with one database for all customers - for those system tables and user functions that every customer uses
one application
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply