I've tried this on 2 other forums, does anyone know the answer?

Question

I've tried this on 2 other forums, does anyone know the answer?

Viewing 7 posts - 46 through 51 (of 51 total)

You must be logged in to reply to this topic. Login to reply

EdVassie SSC Guru Points: 60481 More actions · Answer 1

Wow what a lot of posts!

Being a former mainframe DB2 DBA and IDUG speaker, and now having some knowledge of SQL Server, I thought I would add my 2p...

There factors surrounding the decision to use a single database or multiple databases are much the same in DB2 as in SQL Server. If you know what your business requirements are, and know putting all the data into a single database on DB2 seems a bad idea, it will be just as bad in SQL Server.

When you are doing a migration of this sort, you need to make a fundamental decision:

* Do I try to optimise the design as part of the migration

* Do I keep the design as similar as possible and look at optimising as a separate project after the migration.

There is a lot of prior experience that shows trying to load too much into a migration project adds risks. If the technical and business people are reasonably happy with the current database design, you may be adding unnecessary risk by trying to merge databases as part of migrating to SQL Server.

On the other hand, if the technical people think the current design sucks and is an impediment to the business, then making design changes could help the project.

The only definite answer to this problem is 'it depends'. You need to look at the business requirements, assess the risks of alternative approaches, and make your decision. However, feel confident that just about any technical decision on database design normally has the same answer in DB2 and SQL Server, even if the syntax may be different.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Paul White SSC Guru Points: 150468 More actions · Answer 2

EdVassie (5/5/2009)
Wow what a lot of posts!

Yep, not bad. But nothing like the discussion that ensued over the string-splitting routines... 🙂

EdVassie (5/5/2009)
The only definite answer to this problem is 'it depends'.

Some people here will be very glad to hear that regular use of the 'it depends' maxim has finally led to it becoming a definite answer :laugh:

Cheers,

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 3

Paul White (5/5/2009)
EdVassie (5/5/2009)
Wow what a lot of posts!
Yep, not bad. But nothing like the discussion that ensued over the string-splitting routines... 🙂
EdVassie (5/5/2009)
The only definite answer to this problem is 'it depends'.
Some people here will be very glad to hear that regular use of the 'it depends' maxim has finally led to it becoming a definite answer :laugh:
Cheers,
Paul

Heh... what definite answer? Use a CLR? Well, that depends... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 4

Jeff:

This was what I was referring to: (with emphasis this time!)

EdVassie (5/5/2009)
The only definite answer to this problem is 'it depends'.

So 'it depends' is a definite answer... 😀

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Ross McMicken SSCarpal Tunnel Points: 4440 More actions · Answer 5

I would go with the separate databases myself. It's much easier to me to manage access by database thant to control access to a single database by 13 different locations that probably have no need to see another unit's data. This is similar to what I support now, where we have 15 instances of an accounting system running, with separate databases for each instance. We use NT groups to control access, and no one sees any data they don't need. Backups aren't really an issue - the DBA"s create a basic plan and clone it each time we add an instance.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

Paul White (5/17/2009)
Jeff:
This was what I was referring to: (with emphasis this time!)
EdVassie (5/5/2009)
The only definite answer to this problem is 'it depends'.
So 'it depends' is a definite answer... 😀

BWAA-HAAA!!! Man, you made my day! I was hoping that was what you were getting at. Well done. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

waxingsatirical SSC Eights! Points: 934 More actions · Answer 7

This is a very old post. But not one that I think should be allowed to die.

There has been a lot of discussion of 13 vs 1. Personally my uninformed preference would be for the 13.

Unfortunately, In my case 13 is not an option, as 1 database is a requirement from our client. That database has to contain separated data (different companies).

We've done this before in another system, by peppering all the tables with a company id, but it has led to performance issues which admittedly were partly down to poor indexing/querying.

Is there a performance benefit to going down the multiple schemas route? Any pitfalls that come to mind?

Any advice greatly appreciated.