Could replication be the answer?

We have a US online business that is growing
internationally. We have a data model that allows us
to easily split countries to their own instance or own
physical server. A high volume of reads are expected
from our web servers so we feel that by spliting reads
and writes to separate servers, we could ease the
pressure on one server (i.e. have 1 SQL write server
where everything is written to; 1 or more SQL read
servers with a denormalized copy of the SQL write
data, populated via transactional replication). Our
database is quite large and could not afford the time
it takes to re-initialize the subscriber. Is there a
way to just replay specific transactions to the read
server? Also, we are thinking of maintaining a global
copy of the data (write data from all countries SQL
write servers). These servers would use
bi-directional queued updating transactional
replication. Not all data (vertically filtered) from
the individual country write servers will be
replicated to the global copy. If the copies get out
of sync, what is the best way to re-sync them? Again,
our databases are large enough that it would take
hours to reinitialize the data. Is there anyway to
just replay specific transactions to the subscribers?
Also, do you have any figures on the speed of
transactional replication in a high volume
environment, i.e. millions of transactions a day?
Is there a way to just replay specific transactions to the read server?
Once the publication has been created and subscribed to there's no official
way to filter them retrospectively. An unofficial way I have used is if I
already have a filter. The filter is implemented as a stored procedure and I
have edited the text of the stored procedure. This is not ideal as it isn't
reflected in the publication details, but as a temp fix it works ok.
If the copies get out of sync, what is the best way to re-sync them?
Firstly, I'm not sure I'd use bidirectional queued transactional
replication - I'd prefer to use merge in this situation. If there is
non-convergence the standard method is to reinitialize. Sometimes you can
manually converge the data then do a noinit initialization.
As for high-volume data, I can vouch for the use of TR to maintain a standby
server when there was 1000 transactions/minute (>1million per day) - we had
the latency down to 6 seconds. The only data I've seen on this is
http://www.microsoft.com/technet/pro.../tranrepl.mspx
HTH,
Paul Ibison
|||Define replay specific transactions to the read server.
There is a way to do this, but it depends on what you are trying to do. A
media company contacted me about something like this. Basically by adjusting
the a value in one of the replication metadata tabels using pure
transactional replication you can get all the transactions stored in the
distributor replays.
You can use sp_browsereplcmds to display a range or a transaction ID and
then replay this proc displayed by this commands on the subscriber.
When your data gets out of sync, you can run a validation to determine
exactly which tables are out of sync, and then decide whether to bcp/dts the
tables from the publisher to the subcriber to sync them up.
Or if your tables are too large you can do some comparison using a checksum
to determine exactly which rows are not in sync and then make a decision on
how to proceed.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Ozzie" <Ozzie@.discussions.microsoft.com> wrote in message
news:BDC6BBB1-74DD-4010-9F25-F36C612C343A@.microsoft.com...
> We have a US online business that is growing
> internationally. We have a data model that allows us
> to easily split countries to their own instance or own
> physical server. A high volume of reads are expected
> from our web servers so we feel that by spliting reads
> and writes to separate servers, we could ease the
> pressure on one server (i.e. have 1 SQL write server
> where everything is written to; 1 or more SQL read
> servers with a denormalized copy of the SQL write
> data, populated via transactional replication). Our
> database is quite large and could not afford the time
> it takes to re-initialize the subscriber. Is there a
> way to just replay specific transactions to the read
> server? Also, we are thinking of maintaining a global
> copy of the data (write data from all countries SQL
> write servers). These servers would use
> bi-directional queued updating transactional
> replication. Not all data (vertically filtered) from
> the individual country write servers will be
> replicated to the global copy. If the copies get out
> of sync, what is the best way to re-sync them? Again,
> our databases are large enough that it would take
> hours to reinitialize the data. Is there anyway to
> just replay specific transactions to the subscribers?
> Also, do you have any figures on the speed of
> transactional replication in a high volume
> environment, i.e. millions of transactions a day?