Merge Replication of Stored Procedures and its execution.

Hi,
Am planning Merge Replication for High Availability.
1. Keep an additional server with same configuations.
2. Create a Backup of the database and restore it to the second server.
3. Implement Merge Replicaiton between the two.
4. Replicate only stored procedures and its execution to improve performance.
My stored procedures have dyamic statements , Recursive Procedures , Nested
Proceudures.
Am not planning to replicate the tables but only the User defined functions
and User Stored procedures.
Does some one fore-see any issues with this architecuture. As i may have to
drop and recreate stored procedures during any application release for
enhancements.
I will be applying all my DDL and DML changes on both servers to keep them
in sync at any point of time.
High availability option is to change the DB Connection from 1st server to
2nd Server during Releases and make all the DDL Table changes on server 1
mean while Stop the Merge agent where in all the Stored procedure executions
will be qued from Server2 to Server1 . Once my changes are done enable the
Merge agent which will propogate all the Stored procedure exectutions to
server 1. After which change the DB Connection to Server1 then make all my
DDL Table changes on Sever2 to keep the schema in sync.
Any help would be appriciated.
Regards
Pradeep.
Pradeep,
replicating execution of stored procedures is a facet of transactional
replication rather than merge.
For the other point, to apply DDL changes you could use sp_addscriptexec or
use the Schema Replication option of SQL Server 2005.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||To piggy back on Paul's comments, merge replication is not recommended for
High Availability as it creates a greater latency for each transaction as
tracking triggers must be fired. Secondly the sync operations take longer
than a sync would take with transactional replication.
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
"PRADEEP M.M." <PRADEEPMM@.discussions.microsoft.com> wrote in message
news:601010A4-98E2-4A70-997A-68E2EEA44306@.microsoft.com...
> Hi,
> Am planning Merge Replication for High Availability.
> 1. Keep an additional server with same configuations.
> 2. Create a Backup of the database and restore it to the second server.
> 3. Implement Merge Replicaiton between the two.
> 4. Replicate only stored procedures and its execution to improve
> performance.
> My stored procedures have dyamic statements , Recursive Procedures ,
> Nested
> Proceudures.
> Am not planning to replicate the tables but only the User defined
> functions
> and User Stored procedures.
> Does some one fore-see any issues with this architecuture. As i may have
> to
> drop and recreate stored procedures during any application release for
> enhancements.
> I will be applying all my DDL and DML changes on both servers to keep them
> in sync at any point of time.
> High availability option is to change the DB Connection from 1st server to
> 2nd Server during Releases and make all the DDL Table changes on server 1
> mean while Stop the Merge agent where in all the Stored procedure
> executions
> will be qued from Server2 to Server1 . Once my changes are done enable the
> Merge agent which will propogate all the Stored procedure exectutions to
> server 1. After which change the DB Connection to Server1 then make all my
> DDL Table changes on Sever2 to keep the schema in sync.
> Any help would be appriciated.
> Regards
> Pradeep.
|||Hi Paul/Hilary,
Thanks a lot for your feedback.
I do understand your concerns of Performance as i had reviewed many reviews
whcih specify Merge Replication as an Overhead for the system.
My environment is SQL SERVER 2000. At present am working on a POC for this.
The basic feasability study has been done for all the functionality except
performance monitoring.
am merge replicating all my tables, i do understand that Merge replication
is not a high availability option but for the current appliction it suits.
Here at any point of time there will not be any inserts or updates or deletes
on a Same table in both nodes. Hence Conflict resolution will not be used and
i believe Conflict resolution consumes most of the resource in merge
replication.
If this could be implemented there are 2 big advantages if a SAN Frame or
the cluster fails for 1 cluster the other would take over this without any
down time. AT APPLICATION end there is a switch which changes the connection
to the second cluster.
I have tested all the Schema changes options which are feasable where in
Dropping a table , RE-Naming a table , Changing the Primary key would
re-generate the whole snapshot and apply it.
In your experience what are the options to make the database highly
available during Database changes , Patching servers and also during any
hardware failures.
Regards
Pradeep.
"Hilary Cotter" wrote:

> To piggy back on Paul's comments, merge replication is not recommended for
> High Availability as it creates a greater latency for each transaction as
> tracking triggers must be fired. Secondly the sync operations take longer
> than a sync would take with transactional replication.
> --
> 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
>
> "PRADEEP M.M." <PRADEEPMM@.discussions.microsoft.com> wrote in message
> news:601010A4-98E2-4A70-997A-68E2EEA44306@.microsoft.com...
>
>
|||Pradeep,
I'd urge you to investigate log-shipping and database mirroring and
transactional replication as potential alternatives before coming to a
choice - there's a lot of factors to take into account. Merge replication in
SQL Server 2005 has a nice benefit that it can now be set up as RO, but in
my experience it will always be a much slower means of synchronization than
transactional replication. Log-Shipping on the other hand will be able to
replicate all schema changes, unlike replication. As I say, there are many
things to consider before making a decision.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)