locked
Replication and mirroring options RRS feed

  • Question

  • Hello.

     

    I am exploring different replication solutions and would appreciate any feedback that anyone can give.

     

    We are providing a hosted database solution to our customers.  Our application runs at their branches and accesses that data that we host.  In case of disconnection from us, the branches revert to a read-only copy of their data that we replicate out to them.  We are using merge pull replication.  The branches are running MSDE.  We are running SQL Server 2005 Enterprise SP2, upgraded just under a year ago from SQL Server 2000.  It is a business requirement to upgrade the branches to SQL Server Express 2005 in the near future.

     

    We are looking at various ways to overcome the problem of Express being unable to schedule replication jobs and synchronise with the publisher.  The synchronisation itself can be handled by RMO, so we do not have any concerns there.  The scheduling itself is more of a problem.

     

    My questions are:

     

    1.  The branch servers are not on our domain.  Is it true that only merge replication will work between different domains?

     

    2.  We are currently using pull replication because we want to avoid overworking our host database (which is both the publisher and the distributor).  How might I best estimate the impact on the server of switching to push replication?

     

    3,  If we stay with pull replication, we need to be able to set up the scheduling at the branch servers through an automated process.  Manual setup or operation is not acceptable.  What solutions are any of you using to the scheduling of pull replication with SQL Server Express?

     

    4.  We will be introducing high availability database mirroring.  We will therefore require a witness server.  What is the feasibility of using this witness or the mirror server as the distributor?  Would this make push replication less intensive for our host publisher?

     

    Any feedback on these questions is very much appreciated.

     

    Friday, January 25, 2008 11:04 AM

Answers

  • Inline:-

     

     

    1.  The branch servers are not on our domain.  Is it true that only merge replication will work between different domains?

     

     

    -- Any type of Replication can work across the Domains using different ways like "Pass-through Authentication" etc.

     

    HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet

    http://support.microsoft.com/kb/321822

     

     

     

     

    2.  We are currently using pull replication because we want to avoid overworking our host database (which is both the publisher and the distributor).  How might I best estimate the impact on the server of switching to push replication?

     

    You have to try this in a test environment first to have a look n feel of the overhead.

    The more the number of Subscribers the more agents will run on the Distributor and thus more overhead.

     

    I would recommend pull subscriptions if the Subscribers are not connected to Publisher on regular basis (frequent disconnected Subscribers)

     

     

     

    3,  If we stay with pull replication, we need to be able to set up the scheduling at the branch servers through an automated process.  Manual setup or operation is not acceptable.  What solutions are any of you using to the scheduling of pull replication with SQL Server Express?

     

    Use the “replmerg.exe” on the Express Subscribers with required parameters and then wrap it at OS level as a job and schedule the job as per your needs.

     

     

     

     

    4.  We will be introducing high availability database mirroring.  We will therefore require a witness server.  What is the feasibility of using this witness or the mirror server as the distributor?  Would this make push replication less intensive for our host publisher?

     

     

    Are you talking about Mirroring the Replicated Databases ?

     

    Please check the below link for Replication with Mirroring for more in-depth details:-

     

    Replication and Database Mirroring

    http://msdn2.microsoft.com/en-us/library/ms151799.aspx

     

     

    Regards

    Rishi Maini

     

    Friday, January 25, 2008 1:38 PM

All replies

  • Inline:-

     

     

    1.  The branch servers are not on our domain.  Is it true that only merge replication will work between different domains?

     

     

    -- Any type of Replication can work across the Domains using different ways like "Pass-through Authentication" etc.

     

    HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted Domains or Across the Internet

    http://support.microsoft.com/kb/321822

     

     

     

     

    2.  We are currently using pull replication because we want to avoid overworking our host database (which is both the publisher and the distributor).  How might I best estimate the impact on the server of switching to push replication?

     

    You have to try this in a test environment first to have a look n feel of the overhead.

    The more the number of Subscribers the more agents will run on the Distributor and thus more overhead.

     

    I would recommend pull subscriptions if the Subscribers are not connected to Publisher on regular basis (frequent disconnected Subscribers)

     

     

     

    3,  If we stay with pull replication, we need to be able to set up the scheduling at the branch servers through an automated process.  Manual setup or operation is not acceptable.  What solutions are any of you using to the scheduling of pull replication with SQL Server Express?

     

    Use the “replmerg.exe” on the Express Subscribers with required parameters and then wrap it at OS level as a job and schedule the job as per your needs.

     

     

     

     

    4.  We will be introducing high availability database mirroring.  We will therefore require a witness server.  What is the feasibility of using this witness or the mirror server as the distributor?  Would this make push replication less intensive for our host publisher?

     

     

    Are you talking about Mirroring the Replicated Databases ?

     

    Please check the below link for Replication with Mirroring for more in-depth details:-

     

    Replication and Database Mirroring

    http://msdn2.microsoft.com/en-us/library/ms151799.aspx

     

     

    Regards

    Rishi Maini

     

    Friday, January 25, 2008 1:38 PM
  • Thank you very much for your reply, Rishi.

     

     Rishi Maini SQL DBA wrote:

     

    3,  If we stay with pull replication, we need to be able to set up the scheduling at the branch servers through an automated process.  {snip}

     

    Use the “replmerg.exe” on the Express Subscribers with required parameters and then wrap it at OS level as a job and schedule the job as per your needs.

     

     

    This would certainly be attractive.  Can anyone point me at any good resources for writing code to set up and modify  OS-level jobs for Windows XP SP2 and Windows Vista?

     

     Rishi Maini SQL DBA wrote:

     

    Are you talking about Mirroring the Replicated Databases ?

     

     

    No, we only want to mirror the host databases.  Since I posted my question, it has become less likely that we would use the witness server and more likely that we would use the mirror server as the replication distributor, if possible.  Is it feasible to do this?

     

    Once again, very grateful for any help.

    Friday, January 25, 2008 2:20 PM
  • HI,

     

     

    http://msdn2.microsoft.com/en-us/library/ms151799.aspx

     

    This link is not working now

     

    give me teh correct link

     

     

    Thanks,

    Ramaprasad.

    Thursday, June 26, 2008 6:04 PM