Re: Distributed transactions, replication, views
- From: "Hilary Cotter" <hilary.cotter@xxxxxxxxx>
- Date: Tue, 20 Jun 2006 10:11:14 -0400
Tables are normally logically partitioned by function. With proper indexing
you can grow your tables extremely large with little performance
degradation.
If the majority of your queries align themselves with certain columns, i.e.
date you can take advantage of distributed partition views and have a date
range table on another server. When you query through the DPV the other
server will process the query there and send the results back locally.
If you have significant processors on your server you can partition the
table using a table partition function. This appears to the dba as a single
table, but in actuality the table can be distributed over different file
groups which could be on different disks/controllers, etc.
So if your queries align themselves with the partitions you have advantages
to doing this. Otherwise consider a monolithic table.
To answer you questions
1) I think you should look at join filters for this.
2) The way replication works is that if a transaction is committed on the
source (publisher), it will be replicated in its entirety to the destination
(subscriber), and either applied their in a transactional context, or rolled
back in a transactional context.
3) triggers are a solution but they add latency and are not a good solution
when the source and destination are on different servers.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<deja@xxxxxxxxxxxx> wrote in message
news:1150809668.187716.145400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
hi,
I have been tasked with restructuring a database. The database is
currently recreated for different periods so for 2005/2006 we have one
database and for 2006/2007 we have another. But clearly some tables do
not change much and so they want these tables hived off to a separately
maintainable database but the data could still change, so for example a
location might have capacity of 400 people in 2005 but because of
building work, might have twice that in 2006 but generally it is
unlikely to change much.
How best to do this?!
a) I am looking at just having the "period databases" converting their
tables to "period specific" views of the reference database. The
trouble is that every attribute might need to be period specific i.e if
capacity changed for 2006 there would be a 2006 location record but
lets then say that we changed "Number of Windows" in 2005 - In reality
I would expect the 2006 record to change because it effectively only
"inherited" its Number of Windows from 2005 but how do I "know" this
unless I make a different record/table for each attribute? It's object
orientation represented in RDBMS tables with one location object
inheriting everything from another but overriding one or more
properties.
b) would replication be a better solution? I am no expert in
replication - but if transactions are enforced i.e if the update cannot
be replicated to all databases then it should be rolled back, I can
imagine some pretty problematic transactions. Transactional replication
just seems to apply the transaction as a whole to a subscriber but does
not fail the publishers transaction if the subscriber replication fails
c) update triggers? - presumably use some form of distributed
transaction but this seems even more complex and likely to result in
lengthy transaction times if not deadlocks etc.
the other complication is that there has to be a solution for SQL
Server and Oracle. Though not necessarily the same solution it would
obviously be easier to implement.
Has anyone else had a similar requirement and how did you implement it?
Big thanks in advance if you have any suggestions.
cheers,
Phil
.
- References:
- Distributed transactions, replication, views
- From: deja
- Distributed transactions, replication, views
- Prev by Date: Distributed transactions, replication, views
- Next by Date: Re: MSmerge_conflict_publication_article table
- Previous by thread: Distributed transactions, replication, views
- Next by thread: Re: MSmerge_conflict_publication_article table
- Index(es):
Relevant Pages
|
Loading
