January 16, 2008 at 4:15 am
Background:
There is already an application(say X) developed, that has SQL Server 2005 as backend.
Now a new application has to be created (say Y) that will fetch some data from the existing DB of X.
Though the new application will fetch the data from X, it won’t control or maintain it. It’ll maintain data produced by their own application only.
The users of X and Y are different altogether.
Problem:
How to go about creating the DB for the new application?
Two scenarios:
1. Separate Databases – Having separate DB with Links. How does this work? What are the pros and cons?
2. Same DB with separate schemas – What are the pros and cons?
Is there any other (3rd) way it can be done?
January 16, 2008 at 6:24 am
Seperate the databases and provide a service (Service Oriented Architecture, SOA) that pulls the data from X in order to feed it to Y, through the applications, not through the databases. This avoids all the issues associted with linked servers and/or cross database queries. This allows you to place your database on servers as you see fit with only a change to connection strings. Also, because the service provides an interface between X & Y, you don't have to worry about internals changes to X & Y as long as they maintain the interface of the service.
You could go with two schemas, but then you need to ask yourself, do the two apps have different development, testing & release to production schedules? If no, the seperate schema's (and file groups, etc.) will work, if yes, then you're back to the service option.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 7:23 am
Thanks for the prompt reply.
The solution you have provided will be perfect. But what if we want to have some stored procedures that acesses the DB tables from both the sides? Allowing control through application is fine when you know you are not putting any business logic in the database.
But we already have been asked to put some procedures in place for some specific requirements that needs high performance.
January 16, 2008 at 7:51 am
Unless you want to tie the development schedules together (which is a perfectly viable option, but one that needs to be taken eyes open) you should seperate the databases. Once the databases are seperate, you should plan for the fact that they may be on different servers so that no data is shared between the two directly. You can write specific stored procedures to support the service, feeding the data in the particular manner needed by database Y from database X, but if you put in cross database queries, they're forever joined at the hip and you've sacrificed a lot of flexibility.
But hey, that's my 1.5 cents. We're currently developing most of our systems with this approach. But, we have a few older systems that make that cross-database hop. It really mucks with deployments, development, maintenance, storage, performance and sanity.
The thing is, when you're the developer on database X, you can test every single procedure on database X. You don't generally have a window into database Y, so you don't know how many and what kind of procedures are over there. You can break stuff without intending to. So, by tightly coupling these two databases by linking them, you may as well place the structures together because you won't be developing them independently.
We have one databse (all my fault by the way) that supports about 6 different applications. To this day we try to maintain six different development schedules and it always gets mucked up in some way or another. It's a lesson I learned the hard way. If it's two completely different apps with some bits of shared data, it's two seperate databases and an independent mechanism to get the shared data back & forth between the two.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 8:05 am
Hmmm.... you are right about the flexibility part. Two separate DBs gives you lot of flexibility and control too.
I have already started making up my mind to go for this solution.
But I still have some doubts.
What about the referential integrity part?
You cant enforce that if you have separate databses altogether. Right?
There is some data in my app X which is very much dependent on some data in Y.
If someone goes and delete some part of that data from Y, then my app will end up in a soup.
January 16, 2008 at 8:33 am
Yeah, that is one the BIG weakness to the approach. You can't count on referential integrity. Basically we work from the model that there is an authoritative source for data. So, while some data may be stored in two locations, the authoritative source of the data is X. You have to create mechanisms that allow for the update of data, if necessary, between the systems. It isn't easy. None of the solutions I've been through so far is. I just find that, in terms of development time, maintenance, flexibility, performance tuning, stuff like that, it's worth the price we have to pay in finding ways to maintain the data between the systems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 16, 2008 at 11:29 am
Thanks Grant for all your help.
Appreciated!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply