Writing to two MS SQL databases simultaneously via an ASP page

Question

Writing to two MS SQL databases simultaneously via an ASP page

Dave plant

SSC Journeyman

Points: 78
More actions
July 12, 2004 at 3:19 am

#108558

Hi
I have an asp based site and to ensure 100% uptime I plan to host this site with two different ISP's. I will have my primary domain which my extranet users will use but as a back up I also want to host the site under a different domain name with a different ISP so that if http://www.mysite1.com is down they can go to http://www.mysite2.com and access the same info.
Can it be set up so that the asp pages write the same information to database 1 and database 2 held on the different servers with the different hosting companies?
I am looking for a cheap alternative to mirroring
Many thanks
Dave

Viewing 14 posts - 1 through 13 (of 13 total)

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

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 1

so what you're trying to do is replication between two ISPs

do you have any ideas about conflict management? what if the same record is changed at the same time on both systems? (effectively)

is one purely used a hot standby? or can it be used as live while the other is also live?

what level of error trapping wold you plan to use? if the site writes the data to both servers then if a server goes down how ill you play catch up?

what volume of transactions would there be per hour/day?

MVDBA

Dave plant SSC Journeyman Points: 78 More actions · Answer 2

Hi

i think I am trying to replicate between two ISP's I would like all information to be written to both databases immediately so that the viewer of the database information can always see it. It is not as important for people to be able to write to the database 100% of the time but the viewing of the information in the database is mission critical, as the information is for staff who are on the telephone giving this information to customers.

I would be happy if only one domain can write to the database (to avoid conflicts) but both domains can read access the database(s) so that even though the writing to the database may be less than 100% uptime, the reading of the database is 100%.

It would be great if I was able to write and read to both databases though if that was easy enough to do

I will need people to be able to read from both database simultaneously but 1 domain would be designed as the primary domain whilst the second domain should only be used if the primary one is down. There will only be about 10 people reading from the database so I will be able to personally explain which database to use when to them, but with the nature of people I could not guarantee that people will always use domain 1 (even if it is up), some may log into domain 2 by accident

I am not sure how to ensure commonality of information between the databases but possibly an hourly exchange of updated information

It will not be a high transaction database. There are only 200 companies who will write to the database about once or twice a day each (at most) and a staff who will be reading from the database about 1000 times a day (at most)

I hope I have provided enough info.

Thank you for your time

Dave

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

ok - just a few more questions, and hopefully we should be able to come up with some good ideas

what sort of link do you have between the 2 database servers? how much bandwdth is there?

do you have any restrictions (e.g. performance is an issue, can't use triggers etc etc....)

and finally , what version and edition of sql server are you using ? 2000 enterprise? standard?

Cheers

MVDBA

Dave plant SSC Journeyman Points: 78 More actions · Answer 4

Hi Mike

I have written to the ISP’s to find out the exact version of SQL I am using. I will also find out if they both support ODBC

I am unsure of the bandwidth but it is quite high e.g. unlimited on one and 70gb on the other a month

I don’t believe there is any link between the databases at the moment as they are two completely separate ISP’s, I know one of them supports ODBC

The restrictions could be quite high; they are both on virtual servers, one with ourinternet.us and the other with ecui.com but I am not sure. Which functions would be important?

Ourinternet.us uses SQL 2000 Enterprise edition

I hope to be back with the edition of SQL ecui uses.

Also if we limit the writing to the database to a single domain/database would that solve the problem of replication?

Thanks

Dave

Dave plant SSC Journeyman Points: 78 More actions · Answer 5

Hi Ecui uses

SQL Server 2000 SP3a for client usage

ta

d

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

looks like your only hope is to create your own form of replication,

use update insert and delete triggers to drip feed data between sites (use linked servers to keep 4 part naming conventions.)

i wouldn't at all advise you to attempt to do this using the ASP/HTML code in your web pages. the possibility of your data goig out of sync is all too high (and difficult to get back from)

the other option is a middle tier data syncronisation manager although this would involve a lot of effort (and cost) to develop.

MVDBA

Dave plant SSC Journeyman Points: 78 More actions · Answer 7

Hi Mike

Would you agree it sounds like it would be better not to do it at all as the data could get all mesed up.

thanks

Dave

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 8

proboably, but it can be done as long as you are carefull.

if you can establish one site first and then try some test connectivity directly between the SQL servers (create a linked server and read data) that way you can PUSH data to a hot standby server on the other site.

if your second site only allowed reading of data then you've solved your syncronisation problems.

MVDBA

Dave plant SSC Journeyman Points: 78 More actions · Answer 9

Hi Mike

thanks for all your help. I will probably give it a miss till I get my own dedicated server.

Dave

rbrooks7424 SSC Journeyman Points: 88 More actions · Answer 10

I know this is not what your plan is, but this is what I would do.

IMHO,

Get an ISP that ensures 99.999% uptime. They would have built-in mirroring, hot-swappable Raid X blah blah blah. It must be around the same price of firing up 2 accounts with different ISPs or WPPs.

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 11

hell, host the site yourself.

if you only have that number of customers a normal ADSL line should do the business. just make sure you geta static IP

then you can invest in Failover Clusters and the like for your availability.

It also means you have COMPLETE control.

MVDBA

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 12

I really can't see how your can depend on any type of mission critical performance out of an ISP. Have you looked into using MSDE 2000 and distributing it to your users along with your application. That way if your remote SQL server goes down the users can continue to work and all will be well when the ISP comes back up. Of course the users OS would require some version of IIS to run the ASP pages.

See http://support.microsoft.com/default.aspx?scid=kb;en-us;324992

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 13

i've tried that one before.

version control becomes an absolute nightmare. and you then take on all the problems of having enough connectivity to replicate to multiple sites as a background operation.

you aren't actually going to use the replicated database unless the main one fails.

i'ev seen 3 companies with a similar approach and they all abandonded it within a year.

MVDBA