Standby Server SQL\Web(Warm Site)

ChadPC

Active Member
Joined
Oct 24, 2012
Messages
46
Reaction score
0
Location
Cape Town
Hi

I was hoping this forum could help me. my colleagues and i are looking for a solution to setup a s warm site for a for our current SQL/web server. If i primary server goes down we want a instant switch to the standby server(warm site) with as little as possible data loss? If anyone knows of a solution to achieve this please let me know, it would be much appreciated.

Thanks
 
All enterprise grade server OS and RDBMS support some form of high availability/clustering configuration. If your servers run in a virtual environment, there are also other possibilities there.

On the database side, you might look at log shipping to a second warm db server. Also, frequent transaction log backups in conjunction with regular full database backups can give you a viable point in time backup.
 
Like was said, just setup either a cluster or availability group...

And setup a mirror with the DB stuff, should do the trick, even an availability group with the DB with SQL 2012 would work...

It all depends on the load, cost, etc etc etc you're looking at. Give us an idea and we'll give you some options to work with.
 
I assume you are concerned about hardware failure and not accidental data loss due to stupid user error... as replication would pull that error through to the replicated DB in any case.

If i'm correct, yeah mirroring and clustering would be a great option. The one i'm busy evaluating though is SQL Azure. To quote their high availability notes:

"The service replicates multiple redundant copies of your data to multiple physical servers to maintain data availability and business continuity. In the case of a hardware failure, Windows Azure SQL Database provides automatic failover to optimize availability for your application."

More here: http://msdn.microsoft.com/en-us/library/windowsazure/ee336230.aspx
and more importantly, here: http://blogs.msdn.com/b/jackgr/archive/2011/10/22/high-availability-on-the-azure-platform.aspx

Just something else to consider as it takes much of the pain of setting up replication away. Pretty damn impressive the way they do it.


Reading more into it now, this looks interesting:

"Replication

SQL Azure exposes logical rather than physical servers. A logical server is assigned to a single tenant, and may span multiple physical servers. Databases in the same logical server may therefore reside in different SQL Server instances.

Every database has three replicas: one primary and two secondaries. All reads and writes go to the primary, and all writes are replicated asynchronously to the secondaries. Also, every transaction commit requires a quorum, where the primary and at least one of the secondaries must confirm that the log records are written before the transaction can be considered committed. Most production data centers have hundreds of SQL Server instances, so it is unlikely that any two databases with primary replicas on the same machine will have secondary replicas that also share a machine."
 
Last edited:
Two questions you need to answer:
1. How critical is the information from a data loss point of view?
2. What is the maximum amount of time you can experience an outage before it becomes critical?

At the end of the day, unless you have synchronous replication or some form of high-availability/clustering set up, you will have data loss in the event of an outage, simply because you don't make backups every single second.

Geographically, how far is your warm site from your production environment? i.e. do you need to get in a car and drive there to commission the standby server?
Are the SQL backups small enough to transfer over the wire to your warm site, allowing you to restore to a standby box, perhaps only having to change an IP?
Is your connection between your production site and your warm site reliable enough to set up an active/passive cluster?

Lots of stuff to consider. I am busy working through a very similar exercise, exploring an active/active configuration for our primary data centre, and believe me, it is no small feat.
 
Are both the servers in the same datacentre?

I would suggest a SQL Mirror with Witness, you need three servers, two with SQL Standard +

pm me if you would like to go through with some instructions.
 
No need for a third server, you can have 2 SQL nodes and a file-share/disk quorum.
 
Top
Sign up to the MyBroadband newsletter
X