Replication sample program

Hello i am new to SQL Replication.

I have a application in which Publisher node insert/update/delete in a SQL server data base on it's own node.

I have 3-4 different nodes which has SQL servers.

My reqirement is that when ever i insert/update/delete on the sql server in the publisher node, all the subscriber should insert/update/delete the same data in their respective data base.

so in short, all the publishers and subscribers should have same set of data. note that the subscribers are not doing any additional changes in their databases.

Also , at some point of time (when there is a fail over in the subscriber database), while restarting the subscriber database, i need to first synchronize all the data which were lost during down time with the publisher database and then onwards continue replication.
I will term this as startup synchronization step.

Can i use the SQL Transactional replication for this purpose? DO i need some other mechanism for startup synchronization step.

For example in the publisher i run this query,
Insert into customer(customerid,name,itemname) Values(20,'faith','speaker')

Once this query is executed on the publisher, i want to see that all the customer tables on the various subscribers node should have this record (20,'faith','speaker').

Is Replication is good approach or any thing else is suggested.

Can any body point to some sample tutorial or sample script to perform this kind of operation using Replication

Thanks a lot in adavance

Yes. Transactional replication is good enough to implement your scenario.

Whenever your data change happened on the publisher, subscriber will get the same change immediately (just default setting , continuous mode for both log reader and distribution agents). As for your "startup synchronization step", you can just resynchronize subscription, publisher present data will wrap up and load to subscriber through snapshot and distribution agent.

Here is transactional replication introduction in SQL Books On Line. It will also point you to implementation sample through UI and/or scripts.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/3ca82fb9-81e6-4c3c-94b3-b15f852b18bd.htm

Thanks

|||

Hello, Thanks for the info, it was really useful.

I have an extra complexity involved in my application. That is I have more than one set of publishers which are redundant to overcome the fail over situation.

When a particular publisher goes down , all the subscribers which were connected to the this failed publisher node, should now switch to a new publisher(before starting the replication , this subscriber should synchronise it self with the new publisher).

all the publishers independently updating there databases from a redundant lagacy application. that means i don't need to implement redundancy of publishers using the Replication.

I just should be able to synchronise the subscriber with the new publisher, assuming that the new publisher has the latest data (It is same as the .

There on, i should replicate the data from the new publisher to the subscriber.

Please proveide some details on how to perform this mixed approach.

Thanks in advance for your valuable feed back.

Replication performance question

Hello to all,

I have a performance question: we have a cluster with 2 SQL instances on 1 node (another instance is on another node, but no link with my current problem!). Let's call them C1SQL1 and C1SQL2.

This node is a Hyperthreaded Xeon 2.8Ghz with 1 gig of memory.

These 2 instances are using transactionnal replication and are configured as the distributor and publisher. C1SQL1 is not using much power, it's a small replication with around 10 agents. C1SQL2 is a bit heavier, with around 100 distribution agents. C1SQL2 has around 50 subscribers in 12 publications, but not all subscribers are used in each publications.

Once in a while, this cluster node impacts our production environment (since it's also a production server) and we're wondering if performance wise, it's really not powerfull enough to be the distributor?

I've isolated C1SQL2 on it's own logical CPU, and in idle mode, the replication workload (history, checking if new transactions are made) peaks at around 15-50% each 4-10 seconds.

Can I have any input on this?

Thanks!

Can you give some more detail, I'm not clear on what the problem/symptoms are. Regardless, have you been monitoring disk, memory and CPU to rule out hardware bottleneck? The replication workload you mention in the last sentence, are you talking about logreader agent or distribution agent? Is this SQL 2000 or 2005? Are you running Repl MOnitor in the background?

Replication monitor error but no error?!

Hello again,
The root node of my SQL Server replication monitor is showing a red X but
all the actual replications listed underneath are OK. What might be causing
this and how can I get rid of it! My DBA is getting twitchy...
Thanks
Will
If you run profiler and navigate to the replication monitor, you should see
why this is happening. The replication monitor gets its info from
tempdb.dbo.MSreplication_agent_status and running
sp_MSload_replication_status refreshes this table. As restarting the SQL
Server Service causes the tempdb to be recreated from the model database,
this also removes the problem.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .