SQL Server replication

[ddrueding]

Never done this before, but it souded pretty straightforward.

I'm about to build 3 SQL Servers and set them up accross the US (Monterey, Miami, and Detroit). I need an entire database to replicate continuously between them. They are all connected to at least a 3Mbps SDSL line and will be linked via VPN. I have complete control over all three machines. I can't seem to find a simple MSDN article describing this process. Please tell me it's possible.

TIA

David

 

[Mercutio]

Do you just need replication or are you needing a shared data store?

 

[Mercutio]

... and before you get your hopes up that I might know what I'm talking about, I've only worked with MSSQL 6.5 and 7.

 

[ddrueding]

I need a database to be kept identical on three identical servers regardless of where a change is made. I need the updates to be as instintaneous as possible.

There won't be that much volume in changes, primarily adds. And the total bandwidth of adds, changes, and removes won't exceed 2kb/s; so we don't need to worry about outpacing the bandwidth.

I know this situation would be better served by a single server accessed via VPN, but as usual it's political :roll:

 

[Bozo]

If you are running Windows Server, you can use the 'File Replication Service' to keep the database current on each machine. It's instantaneous (allowing for network load).
I just set up two servers using this and connected them via a crossover cable. Working fine so far.

Bozo :mrgrn:

 

[Mercutio]

Won't work. DFS replicates at the file level. You'd be overwriting transactions left and right. Also, it's not instantaneous. Inter-site DFS replication occurs at the same time as inter-site directory replication (every half-hour plus or minus 15 minutes by default), and that's the kind of replication traffic that would bring a VPN'd WAN connection to its knees anyway.

Realistically, you need to tell the bastards trying to play politics to STFU. How?

Quote them the price for a WAN-based SAN. Fibre Channel is sorta possible over national distances, and what you're really talking about is a shared-store of dynamic data. A SAN+Cluster would be the right way to solve this problem, since all three servers need to constantly be in the same state.

I don't even want to THINK about how much that would cost.

Failing that, you're going to have to do transactional replication and shoot for as close to instantenous as you can get (and I don't know how different that is for MSSQL 2000/2003 from what I worked on). That might work just fine on a low-use DB, but if you've got something serious enough to need three copies of the DB, sooner or later you'll probably introduce errors stemming from two near-simultaneous transactions on different servers.

David, what you really need to do in this case is subcontract a DBA. I'm sure you could pull one off craigslist in a heartbeat.

 

[Bozo]

That's odd. Our file replication is instantaneous. And network traffic is negligible.
I have been informed the MSSQL has a replication service built in.

Bozo :mrgrn:

 

[Mercutio]

But are your machines on a LAN or is your DFS distributed over WAN links?

The only near-instant changes I'm aware of relating to Windows directory replication are password changes; at BP I saw the result of password changes from admins in London about a minute later, in Chicago.

Yes, MSSQL has its own replication manager. You configure it with Publishers and Distribution agents based on whatever conditions you set. Subscribers pick up the changes based on their own needs (pull) or based on the demands of the Publisher (push). The interesting thing in this case is that David will end up having three publishers/distributors and six subscribers in this case, and he's going to have to have them running constantly to keep things in synch... which is why he'd be better off with a single data store and a cluster of DB servers with awareness of each other's state.

 

[Handruin]

Quote them the price for a WAN-based SAN. Fibre Channel is sorta possible over national distances, and what you're really talking about is a shared-store of dynamic data. A SAN+Cluster would be the right way to solve this problem, since all three servers need to constantly be in the same state.

I don't even want to THINK about how much that would cost.

Fibre Channel is capable of crossing half the distance of the planet, but it requires the aid of a long distance device which works using a T1/T3 or higher carrier line. (I thought it was called DWDM (dense wavelength division multiplexing)) This hardware converts the campus solution Fibre channel into a longwave, long distance solution. The delay time increase for obvious reasons as distance increases.

I know it says 200KM, but I remember reading something about going much further...not 100% positive on this.

Irregardless, a cluster solution will leave you no better in the distributed part of SQL server. I've setup a few dozen 2/4 node SQL Server 2000 + MSCS in a SAN environment and SQL server only runs on one node of the cluster at any given time. You could move the storage groups to different hosts, but SQL server processing still occurs on only one host. Since SQL server 2000 is "MSCS aware", you can't even fool it into installing separate instances on separate nodes. (at least not that I've ever seen)

At work, I'd estimate our MSCS + SQL server SAN setup (with storage) costs i the area of $2.5 million. 2 storage arrays, 4 hosts, 2 Fibre switches, 4 HBA's. The biggest cost being the storage arrays. Definitely not an economical solution for anything less than a person looking for the highest possible uptime availability. (I won't personally use MSCS for such a critical task, but some banks do)

 

[ddrueding]

Yes, MSSQL has its own replication manager. You configure it with Publishers and Distribution agents based on whatever conditions you set. Subscribers pick up the changes based on their own needs (pull) or based on the demands of the Publisher (push). The interesting thing in this case is that David will end up having three publishers/distributors and six subscribers in this case, and he's going to have to have them running constantly to keep things in synch... which is why he'd be better off with a single data store and a cluster of DB servers with awareness of each other's state.

This is pretty much what I'm looking at. Becuase of the nature of the data flow, I'm not particularly worried about data corruption. And if the systems are slightly off, that won't kill us.

What is currently running:

The 3 sites belong to 3 different companies. Each have their own system (UNIX, Linux, and Windows) running their own custom software and outputting changes to a text file every 15 minutes. This text file is published to an FTP and pulled by the other 2 systems, where the information is integrated and files of their own are generated. I hate this "solution".

 

[Handruin]

What about using one database for writting and multiple database for reading? You can do this with MySQL replication.

http://www.mysql.com/doc/en/Replication_Intro.html

 

[ddrueding]

Part of the political process is that everyone works off their own database, and that information is then sent to the other users. Although having one run as the "master" and having the others sync their changes with it might be better.

Here's how the data will flow:

Location "A" will be the origin of all new records
Either location "B" or "C" will add fields to those records

-rarely-

Either location "B" or "C" will change a record and the other will add to it.

Following this, "A" will need to be a Publisher and a Subscriber.
"B" and "C" will need to be Subscribers (Pull) and Publishers (Push)

Ugh....do you have a link to a basic MS doc describing this process?

 

[ddrueding]

And on top of that I'm in the middle of writing a paper documenting the increased security of a VPN link over SFTP....blah.

 

[Jake the Dog]

is using Oracle instead of MSSQL an option? if so, checkout Quest Sofware's SharePlex for Oracle. not cheap, but good.

 

[ddrueding]

I'd really rather stick with MSSQL. If there's going to be a cost associated with the syncronisation, I'll just have a custom .NET web server app written to do the job. I was just under the impression that MSSQL 2000 enterprise was capable of doing this automatically.