Scaling out a database-Expert help appreciated
crb...@yahoo.com
Our current database houses tables from different functional areas.
This is very convenient when we need to perform joins and other
operations that involve tables from more than one area.
We are using SQL Server 2005 (and will use 2008 in the future) and
most of our access is performed via stored procedures.
Due to business reasons, we are moving to a service oriented
archicteture and we will probably need to change the architecture of
the database by partitioning it across functional boundaries. As a
result, we will end with a set of databases, instead of one
centralized database.
My questions:
1. Is there any technology in SQL Server that could help me in the
process? Linked servers, distributed transactions, etc. (I am not
familiar with any of them)
2. How could I reuse (if possible) the current stored procedures,
specially the ones that use data in more that one area?
3. What is the performance impact of this new architecture?
4. How are the relationships between entities in different areas
enforced? For instance, in the current model, one of the entities of
functional area "B" had a foreign key into an entity of area "A".
Your comments and advise are greatly appreciated.
Kind regards
CD
Uri Dimant
> 1. Is there any technology in SQL Server that could help me in the
> process? Linked servers, distributed transactions, etc. (I am not
> familiar with any of them)
Also Partitions in SS2005
http://www.sqlskills.com/resources/Whitepapers/Partitioning%20in%20SQL%20Server%202005%20Beta%20II.htm -
---Partitions
> 2. How could I reuse (if possible) the current stored procedures,
> specially the ones that use data in more that one area?
Specify a database name such as EXEC dbname.dbo.stored_procedure
> 3. What is the performance impact of this new architecture?
It depends on many factors. You will have to maintain more databases, it
takes more system resources(memory. etc.,.)
> 4. How are the relationships between entities in different areas
> enforced? For instance, in the current model, one of the entities of
> functional area "B" had a foreign key into an entity of area "A".
What does mean entity of area ? You mean table A in database A and table B
un database B?
<crb...@yahoo.com> wrote in message
news:9a266464-7c1a-4a1c...@a1g2000hsb.googlegroups.com...
John Bell
If your new set of databases has the same schemas as your current database
then your procedures for a standalone area should work as they are. For your
procesdures looking at multiple areas try distributed partitioned views (see
books online for more) where you can query the tables in each database using
a single view. If set up correctly a partitioned view will have have little
if any impact and depending on your current system, could potentially improve
it.
You can't have cross database foreign keys, but you can replicate some of
the functionality with check constraints although these may potentially
impact performance.
If you only need segregation of the data you could achieve this by
non-distributed partitioned views and have the tables in the same database.
If that was the case you could have foreign keys.
John
Tibor Karaszi
mind that there are many ways to skin a cat...
> 1. Is there any technology in SQL Server that could help me in the
> process? Linked servers, distributed transactions, etc. (I am not
> familiar with any of them)
Linked Servers, yes. Like SELECT ... FROM srv.db.schema.object. You can even create a synonym
against an object in the linked server - so you get some level of location transparency.
Distributed transactions. Well, if you need to make sure that a modification is performed as a unit
of work and the tables involved are on several SQL Server instances, then you need some type of
distributed transaction. This involves a transaction coordinator which in the MS world is the MSDTC
service. Distributed transactions are much more complex than local transactions so you should design
to keep these at a minimum.
> 2. How could I reuse (if possible) the current stored procedures,
> specially the ones that use data in more that one area?
Possibly using a combination of linked servers and synonyms.
> 3. What is the performance impact of this new architecture?
SQL Server do not have a distributed optimizer. And even if it did, you would need to be careful.
Imagind a join of two large tables that resied on different SQL Servers. You need to be very careful
watching the execution plan etc.
> 4. How are the relationships between entities in different areas
> enforced? For instance, in the current model, one of the entities of
> functional area "B" had a foreign key into an entity of area "A".
You would have to use triggers. I would be very very cautios if having a trigger that modifies
against a linked server, though. Again, design should be done to keep this to a minimum.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<crb...@yahoo.com> wrote in message
news:9a266464-7c1a-4a1c...@a1g2000hsb.googlegroups.com...
TheSQLGuru
think a few newsgroup posts/answers are going to help this project be
successful. You should truly consider bringing in some expert help for a
short term to help you with architecture and initial design/dev. SOA is
extremely hard to do well as it is and if you don't get a good database
underpinning you are sunk before you even start.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
<crb...@yahoo.com> wrote in message
news:9a266464-7c1a-4a1c...@a1g2000hsb.googlegroups.com...