Hello Everyone and I hope you are all well

I have been browsing your forums to try and find a solution to a HA problem I am now facing but think I am in a somewhat unique position so thought I'd post about it. Basically I am trying to find a new HA solution due to an increased quantity of databases - nothing strange about that but read on!

We use:

1* Server 2008 Standard (Backup) with SQLServer 2008 Standard R2

1* Server 2003 Standard (Primary) with SQLServer 2008 Standard R2

Our current connectivity a 2mb SDSL connection at each end

We are a company with about 100 Staff so don't have a lot of IT resource. Just 2 of us!

The Problem:

At the moment we test our DR facility once every 2 weeks to make sure things are working. Each time we go down I have to go through the process of moving all our databases down there and back again (we have about 40) and I do this with some logshipping scripts I wrote (They basically enable and disable the built in logshipping and export / import final logs put dbs into restore mode etc). Now these 'mostly' work although due to the nature of our internet connection some times I will get login failures and have to manually intervene. Now I am faced with a further 60 databases coming onto my servers and will need to do the same to these. As these are messaging system databases I cant afford data loss in a DR test. I am basically thinking that logshipping is possibly no longer a good solution as there are too many things that can go wrong and when they do take too long to recover from. We need to keep everything in house and cannot afford a consultant.

Some Good news:

We will be beefing up our internet connection but this is unlikely to be much more than 10mb EFM due to cost pressures. It will however have a decent SLA so should be better than the SDSL.

There is a budget available for this of a few £1000

With that in mind:

What should I use for HA?!!

Any ideas would be fantastic and appreciated.

Cheers

Chris

Quite Useful Information !
Thanks for the information !

Chris,
From the information you gave us, most specifically "I can't afford data loss" I would suggest the following solutions:

DR Solution: Implement database mirroring (you might have to upgrade to enterprise edition to set the mirroring to asynchronous due to your unreliable internet connection)

HA Solution: Buy a new server and cluster them

I know these solutions are not within budget but these are the best solutions to fulfill the business requirements you gave us.

I hope this helps
Reghardt

As Reghardt said, unfortunately the budget makes it difficult. SQL Server is one of those things that, when you get to HA and clustering, etc, it can scale pretty quickly and it isn't ever cheap.

That being said, your connection is getting dramatically better, so that should help things. Mirroring like he said is a good option. Is logshipping proving inefficient, or just too much work?

Hi Guys and thanks very much for the responses which I really appretiate. The good news is I have managed to get budget for 2 new servers (hardware only at this point).

To answer richardgilmore about logshipping my problem is that occasionally the scripts fail because the server temporarily loses connection to the DR site. I believe this is a problem with the internet connection but cant prove it (Ive spent days trying to fix this with no luck!). I could put in error checking and deal with it on a per database basis but the test is time sensitive (we have hourly message that must be sent and recieved) and I would have to then sort each DB out individually which I don't want to do, in a real disaster this doesn't matter as Id just bring them out of standby (and accept some data loss). My theory was if somehow I could just deal with one large problem rather than lots of little problems my life might be easier! I discounted mirroring before because of the large quantity of Databases people around various forums said it was a bad idea (and capped at 20 as our DB server has only 2 CPUs?).

I have looked at clustering and it looks expensive but Id have to see what the prices are on our Licensing agreement with Microsoft (Open value subscription). How complicated is it to set up?

My ultimate goal is to not have to do anything when I go to DR for the databases, in an even more ideal world we would be able to use our applications at our DR site at the same time.

How does this all sound? Thinking about it Mirroring and Clustering could provide my ultimate goal? Obviously this depends on a new Internet connection.

Thanks

Chris

Update:

We are now going to get a 100mb point to point line put in place between these two locations.