Replication Advice Needed

Environment:
SQL 2000 SP4 on Windows 2003
Replication Type = Transactional Replication
Servers involved = server 1, server 2
Activity = server 1 has lots of activity for inserts,updates but no deletes
for 7:30 AM - 6 PM NY TIME then it becomes stable and becomes active again
from 9 PM - 4 AM NY TIME for offshore processing.
Situation:
I need to set up an environment to support minimum latency as low as few
seconds or so. I need to make sure that two systems stay in sync with Data
and schema though there are very less chances of making schema changes. I
thought of considering replication but there are few concerns.
1. Data can be replicated very quickly but then schema changes are a big
concern as replication blocks most of the schema changes.
2. When a new table is added on server A, I want it to be added to a server
B as well which can not happen without adding this table to a new or existing
publication and run snapshot\distribution agent etc..
3. On existing stored procedure, if I replicate the schema and executions,
do the subsequent alter procedure commands also get replicated? OR if the
proc is altered on Server 1, I need to manually run ALTER Table on server 2.
Then I thought of using Log Shipping and ran into following concerns.
1. If I set up transaction log backup very frequently on server 1 say every
minute, performance becomes a nightmare which is not acceptable at all in
this situation.
One more question on replication:
If I take the publisher server backup and restore on subscriber, how would
it affect replication? Would the replication be still funtional after restore
is done on subscriber?
Please advice if replication is a right approach in this scenario and if I
do implement replication, what additional steps would I need to take should
new schema changes appear. If Replication is not a good approach and Log
Shipping is a performance bottleneck, what other directions can I look at,.
Thanks for reading my request and I appreciate your suggestions.
Thanks in advance.
Nitin
Nitin,
to clarify a bit, I'd like to know what the second server is to be used
for - is it a HA box, or is it to be used for reporting?
Also, how definite are you on the idea of a few seconds latency?
My first reflex is database mirroring, with database snapshots if you
require a reporting solution, but let's find out more first.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
Thanks fror your response. Second server is used only in read only mode.
There wont be new transactions other than what are happening on Server 1.
Also, Database mirroring is not supported on SQL 2005 yet and is turned off.
I dont know if we can mirror DB in SQL 2000. Few seconds latecy is ok. I have
spoken to people and they have said latency shouldnt go in minutes, seconds
are OK as nothing can be instantaneous in real world.
What are your thoughts on it.
-Nitin
"Paul Ibison" wrote:

> Nitin,
> to clarify a bit, I'd like to know what the second server is to be used
> for - is it a HA box, or is it to be used for reporting?
> Also, how definite are you on the idea of a few seconds latency?
> My first reflex is database mirroring, with database snapshots if you
> require a reporting solution, but let's find out more first.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Nitin,
database mirroring is not yet supported, but it is available. Some companies
are using it but admittedly it is a little worrying in a production
environment if it is not yet supported. Anyway, log shipping would have too
long a latency and mirroring doesn't exist in SQL Server 2000 so
transactional replication would be the obvious choice. Whether you'll
achieve the latency you require is difficult to say - I guess you'll need to
do a POC.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
Since this is production environment, I really cant use DB Mirroring since
it is not supported if somethig goes wrong tomorrow and I cant get a support
on it, I will be in trouble. What do you recommend for moving the schema
changes to server 2 when changes were made on server 1 without replication
being stopped. For example: a dba can create index on an article without
affecting replication but now two servers are out of sync with schema. Do you
think there is a way to script out the schema differences and some automated
job can run those schema changes on server 2.
Nitin
"Paul Ibison" wrote:

> Nitin,
> database mirroring is not yet supported, but it is available. Some companies
> are using it but admittedly it is a little worrying in a production
> environment if it is not yet supported. Anyway, log shipping would have too
> long a latency and mirroring doesn't exist in SQL Server 2000 so
> transactional replication would be the obvious choice. Whether you'll
> achieve the latency you require is difficult to say - I guess you'll need to
> do a POC.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Nitin,
as you're on SQL 2000, the process is more manual but you can use
sp_repladdcolumn and sp_repldropcolumn for most things (including many
schema changes http://www.replicationanswers.com/AddColumn.asp). As I say,
this is manual, and you'll need to ensure this is done on the publications.
Index changes aren't propagated in SQL 2005 either so I tend to do these
manually although if I had a lot of subscribers or disconnected pull
subscribers I'd use sp_addscriptexec. Keeping the databases in sync will
require a strict way of working, and sometimes you might have to
reinitialize a table (yes, it's possible in transactional replication using
SQL 2000).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)