Multiple replicated masters

Question

Multiple replicated masters

carl.marcinik

Newbie

Points: 2
More actions
March 21, 2009 at 9:27 am

#97435

I am currently evaluating a variety of DB architectures and a few products (primarily MS SQL Server and MySQL) in order to determine a workable solution for scaling writes. High availability is also an issue but scaling is the driving force.
In that regard, I have been exploring sharding (horizontal partitioning) architectures and other options as well. In the course of this investigation, I was told from an old SQL Server hand, that in SQL Server 2000 it is possible to support multiple replicated masters. I have been looking for information on this topic, but have not found anything definitive yet. Can anyone tell me if this is possible?
To be clear, what is being claimed is that you can define the same schema on multiple SQL Server masters and essentially load balance writes across the masters and they will synchronize with one another through some kind of replication scheme. I am not certain how well this scales for writes, but can this kind of scheme be implemented in SQL Server 2000? Any definitive links along these lines would be greatly appreciated.
Thanks,
Carl

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 1

Not sure what the 'old hand' is specifically referring to, but SQL Server does not support "shared nothing" type architectures. (I say this, but there is an initiative that is under wraps going on now in Redmond to investigate an architecture similar to Oracle Real-Application Clusters.). There are a lot of homespun architectures for scaling-out SQL Server; he might be thinking of one similar to what we used in 1996-1997 at match.com where you designate servers that are write/update, and servers that are read only. Replication takes care of pushing the write/update data to the read servers, and the application has separate or multiple connections obviously for reading and writing. Merge replication does come to mind as well, with full copies of the database on each server, but the gotcha here is heavily on the load balancing piece of the equation.

Try to get some more information if possible and I can tell you what he's thinking. But as far as setting up something like this through a wizard or something baked-in, we don't have this just yet.

carl.marcinik Newbie Points: 2 More actions · Answer 2

Hi Lee,

Thanks for the response. So, what you seemed to indicate is that on SQL Server 2000 it is possible to replicate between a (write) master and a (read) slave(s?), but not between masters? In the example you gave from match.com, how did you synchronize between the datbases on the write machines? Or did you? What is considered to be the "best practices" for scaling writes using SQL Server 2000 (I am mainly thinking in terms of scale out architectures). I will try to get more details from the "old hand" and will pass them on.

Thanks,

Carl

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 3

So, what you seemed to indicate is that on SQL Server 2000 it is possible to replicate between a (write) master and a (read) slave(s?), but not between masters?

First, this isn't germane to SQL 2000 ("the GD Germans got nothin' to do with it!". Ahem. Sorry. Flashback to a movie). Anyway, this approach using replication can be used for any version of SQL Server. What you are calling "masters", there is no designation for the term master, unless you are speaking about the master database. "Master" as he is calling it, is probably being used as a metaphor while discussing architecture, and is not a keyword, term, or commonly known or accepted phrase in this DBMS. Having said this, no, I know of no way of doing this. That's why I likened the process that you are imagining to Merge Replication; there, a full and complete database can be stored on each server instance. Changes to one propagate to the others.

In the example you gave from match.com, how did you synchronize between the datbases on the write machines? Or did you?

We didn't. We did not have to because there were no multiple instances of a given table across machines. So, if we had 200 tables, we might have 100 of them on one server, and the others on another. No shared copies of anything. Again, follows where I mentioned that SQL Server is a "shared nothing" DBMS.

What is considered to be the "best practices" for scaling writes using SQL Server 2000 (I am mainly thinking in terms of scale out architectures).

I have no idea. You'd probably have to go to the Microsoft site or Google! and search for scaling out SQL Server. I have seen some of their papers, and papers from the field, but I can't point you to them directly. Many, however, do incorporate SQL Server replication for scale-out; it's very reliable and has a solid history now of running with minimal errors and great uptime.