Replication over EXTREMELY slow links 2

[fs483]

Hello,

I have a unique situation. One of my customer has 3 sites (soon to be 4) spread out between North America and West Africa. The geology dept needs to use a special 3D application for site surveys and modeling in the mining field. The application uses a SQL Server DB. Now, we have Citrix implemented for an accounting software (which also uses SQL) at the HQ. This works fine. But for our Geology dept that can't work so we are thinking of putting SQL servers at the remote offices and one larger one at HQ. The Geology dept does many manipulations and simulations, that cannot work in Citrix. I need to have the SQL servers from the remote site replicate it's DB to the HQ SQL server. Each remote site will have their own data and the HQ must have data of all the sites (in separate DBs). There just one HUGE catch, the links between the remote sites and HQ are 128kbits (satellite link with over 800ms ping times). One other thing, the usable bandwidth is less than 5kbits because each Citrix session uses about 20kbits with half a dozen users + local browsing, there isn't much left for anything else. After speaking to the head of Geology dept, they said the files that are modified can vary betwen 500MB to 1.5GB. What possible solutions do I have for DB replication (not realtime) but as frequent as possible ? I don't know much about SQL and how it works...

Thanks
akwong

 

[fs483]

As for T-SQL training, I'm pretty sure at least some of us will follow a more advanced class. Everyone involved will also follow the basic training for DS.

HQ is indeed looking for the most recent data as soon as possible. We are currently having discs shipped from the remote sites but it's not a regular thing. Sometimes the people on the remote sites have other priorities and a request can take 4 weeks before it arrives. If the links are too slow, we will have to keep using this method until technology allows us to have faster lines at affordable prices. The other main issue is backups. There is none on the remote sites! We can't trust everyone to do all the backups. If we don't go with full DS at the remote sites, then we will not need a dedicated SQL server. We'll just put in a bigger fileserver and have the local IT take care of the backups. If we are going to put in this much money in a project, we are trying to get as ROI as possible.

I didn't ask in detail regarding the current 300GB of data, it might be saved models...

I think the Standard Edition will do and we'll probably buy the SQL2005 but install only SQL2000. I think if you buy the SQL2005, you also get the key for SQL2000. You just need to order the cds.

I doubt there will be a "dedicated DBA" on site. It will be probably a more senior geologist that will take care of that task.

Unfornately UPS/Fedex/Dicom don't reach the sites. Packages usually arrive at the main city office and then get trucked by our drivers onto the remote sites.

I have some sites (exploration only) that don't have any sort of data communication. The field guys have to travel to a little village that has a line, wait till daylight to power up the photocells and transmit their data on a 40k modem (in the best conditions) and retrieve their emails.

I learned this week that our third temporary remote office is looking into either cable service or RF (I think). Unfortunately this is only for the office in the city not at the actual mine site.

Already with VOIP, we save a lot of money. Sat-Phones used to cost 4 or 5$ a minute I reckon...

More to come...

Why can't they find gold in the middle of the city were you can have access to high speed lines, nice air conditionned 5 star hotels, fine cuisine and confortable temperatures...

 

[yetanotherjo]

Why can't they find gold in the middle of the city were you can have access to high speed lines, nice air conditionned 5 star hotels, fine cuisine and confortable temperatures..."

That's why us geologists are such special people!

I be thinking the "more senior geologist" doesn't know what he/she's in for, poor luv! But maybe get them to arrange that a weekly backup disk is carrier-pigeoned out come hell or high water as part of their database duties..."a management issue" in DS-speak.

Sounds like you have the SQL Server side of things under control now. Well done. If you have any DS specific queries or want an unbiased opinion on something, feel free to give me a yell.

 

[davebeel]

I hope you don't mind me piggy backing onto your conversation but I have a similar dilema.

I am setting up an SQL database with MS Access front end, which needs to run in 5-6 different locations which are only connected by a very slow connection (approx 128-256k). They are the same database for tracking of a large amount of personnel.

They will run stand alone, and I could if I wanted to download daily updates and email them across to the other servers for them to upload, however I would prefer it to be more real time realistic.

Without explaining the reasons why we have gone this way I am going to be running a replica database at each location and then want to set up SQL replication around the system. I am thinking the merge replication would be needed as they are the same database.

Three SQL locations are SQL 2000 Server run on Windows Server 2003, 3 are SQL Developer Edition run on Windows 2000 clients.

What is the best way to have these 6 machines replicating to each other. Is there any rules about it and do the machines all have to be on the same domain, or can they be totally separate domains.

Your assistance is appreciated.

 

[fs483]

I haven't done any testing yet. Budget is approved but haven't seen the signature on the dotted line yet so I don't want to take the risk of ordering 40K$ and not being paid !

Now if you want real time updates, I think it might be hard. Do you have any data running on the 128-256k line or is it dedicated just for your SQL replication ?

 

[davebeel]

Yes there are other things running on it, but not very bandwidth intensive.

 

[Catadmin]

Dave,

Merge Replication is not real time. There is some latency issues with it as it only periodically sends data back and forth between system.

Transaction Replication has little latency, but high overhead. Unfortunately, the catch is that you need a persistant connection between all machines for this to work. If you don't have one, or if it gets knocked off for other traffic, then replication will break. And I think this statement also holds true for Merge Replication (but I'm not positive).

You might want to check the Books Online and do some Googling for SQL Server Replication to get some good research done on this before you implement. Otherwise, you may be just shooting yourself in the foot before you know the gun is even loaded.

Hope that helps.



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[fs483]

Hardware has been ordered... Now we can soon start playing around with all the ideas that where mentionned in this thread !

 

[yetanotherjo]

Enjoy your new toys, Akwong. Let us know how you go.

 

[fs483]

Got every everything to support the server (rack, shelves, upses) so far but no SERVER YET !!!

 

[yetanotherjo]

...the fatal flaw in the grand plan...I guess you could keep yourself amused building the cabinet in the hope the server arrives with the next camel-train...

 

[fs483]

Got the hardware, minor setback. Powered the server on, boots up fine. Power up the SAN, BLACKOUT in the whole server room ! Too much juice drawn from the 14HDs... he he... Knocked out power to all 7 servers but luckly everything is UPS protected... Waiting for the electrician to wire up 3 or 4 more circuits in the server room... Stay tuned... Next worry is the noise in the server room and temperature...

 

[Catadmin]

WHOOOPS!!!

Bright Side?? You still get paid for sitting around twiddling your thumbs while the electrician comes out and fixes things, right?

@=)



Catadmin - MCDBA, MCSA
"The only stupid question is the one that *wasn't* asked.

 

[fs483]

Not really, I never get to sit around... We are an IT firm and have a lot of clients so it's always a shuffling game... We are on-call 24/7/365... No vacation and no honeymoon either, been like that for past 3 years ! Cell phones are never turned off. Anyways, electrician passed by, got ourselves a new 30amp circuit... Everything turned on and no blackouts... Trying to get the SAN working now... Never played with these toys... RTFM... Once that's done, we'll need a demo of datashed to test everything out.

 

[yetanotherjo]

Woo-hoo nearly there, mate! I know where you're coming from regards the juggling clients - fun, isn't it.