[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Advice on replication

Posted on 2005-05-13
7
Medium Priority
?
203 Views
Last Modified: 2010-03-19
Ok

We have one server (server1) that hosts a website that backs on to a sql server database sitting on the same machine.

In the event of a machine failure, the website will failover to another server (server2).  There is a sql server database that sits on this server as well.

We upload data to the sql server on server1 several times a week.  At the moment if server1 is down (which it has been intermittently over the past 6 months) we stop uploading data untill server1 is back up again so that the databases dont get out of sync. Ideally, whichever server is up, we want to be able to continue uploading data and be sure that when the other server comes back, it will sync the dbs so that all data will be current.

We have used snapshot replication so far to keep the dbs on each server in sync but there seems to be an issue with snapshot rep when the db is being served off server2.  So we plan to stop using it.

Does merge replication fit this scenario? Will merge rep automatically sync the dbs when a server comes back on line.  If not is there a better suggestion?

Wing
0
Comment
Question by:WingYip
7 Comments
 
LVL 27

Expert Comment

by:Dabas
ID: 13995042
Hi WingYip:
I am quite happy with snapshot rep, and do not understand the problem you are having. Sync does stop if the subscriber is off line, but it quickly reestablished.
Merge rep will not solve your problem. It is used if you want to have a two way street.

Dabas
0
 
LVL 1

Author Comment

by:WingYip
ID: 13995094
Well we do need a 2 way street.  But if 1 server is down we want to continue uploading to whichever server is working and then have it automatically sync when the other server comes back online.

Will merge work in that respect?

Wing
0
 
LVL 27

Expert Comment

by:Dabas
ID: 13995148
Wing

My experience for the time being has been only one way.
When I say one way, I mean the main server holds the data, and it updates the other server as data changes
With a merge sync, any data that changes on either server will cause the other one to perform the change too.


0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 21

Accepted Solution

by:
Kevin3NF earned 500 total points
ID: 13996079
You can have a two way steet with any of the replication options:

From Books Online:
With snapshot replication or transactional replication, replicated data is by default read only; however, you have the ability to modify replicated data at the Subscriber by using updatable subscriptions. If you need to modify data at the Subscriber using snapshot or transactional replication, you can choose one of the following options depending on your requirements.

Looks like snapshot with queued updating my be the way to go...you just have to point your "upload" application to the subscriber in teh event the publisher goes down and re-point when it is back up.  Or, get the issue resolved that causes the server to go down and don't worry about it.
0
 
LVL 11

Expert Comment

by:andrewbleakley
ID: 13999849
I am a HUGE fan of merge replication for similar senario's. It is admittedly an extra burdon on machines and networks (but I develop - the rest is for DBA's and SysAdmin's to worry about). To answer you questions specifically

>>"Does merge replication fit this scenario? "
Yes it does

>>"Will merge rep automatically sync the dbs when a server comes back on line. "
Yes it will (if you schedule it to do so)

>>"If not is there a better suggestion?"
I like merge replication, the other responder's like snapshot replication. Clearly you have trouble with snapshot replication, so why not try out merge, be careful and plan ahead, read everything you can get your hands on, ask every question that comes to mind and have fun
0
 
LVL 27

Expert Comment

by:Dabas
ID: 13999882
andrewbleakley:
> other responder's like snapshot replication
Just a short clarification.
In my situation I do not NEED merge. (I only need to update remote servers with info on the HO server) It sounds like a great idea though!
My only doubt is that if you are having problems with snapshot replication, I do not think that just by changing to merge the problems will go away. It is not clear from your question what exactly the "issue with snapshot rep" is.



Dabas
0
 
LVL 1

Author Comment

by:WingYip
ID: 14178065
Thanks to all.  Kevin3nf pointed me in the right direction however.

Cheers

Wing
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question