What are my options for achieving a warm backup server for a SQL Server Express instance running a single database?
Sitting beside my production SQL Server 2008 Express box I have a second physical box currently doing nothing. I want to use this second box as a warm backup server by somehow replicating my production database in near real time (a little bit of data loss is acceptable). The database is very small and resources are utilized very lightly.
In the case that the production server dies, I would manually reconfigure my application to point to the backup server instead.
Although Express doesn't support log shipping natively, I am thinking that I could manually script a poor man's version of it, where I use batch files to take the logs and copy them across the network and apply them to the second server at 5 minute intervals.
Does anyone have any advice on whether this is technically achievable, or if there is a better way to do what I am trying to do?
Note that I want to avoid having to pay for the full version of SQL Server and configure mirroring as I think it is an overkill for this application. I understand that other DB platforms may present suitable options (eg. a MySQL Cluster), but for the purposes of this discussion, let's assume we have to stick to SQL Server.
I've seen others try the poor man's version of log shipping with little luck. That doesn't mean it won't work. I've been looking for a similar solution for a while. Mirroring is great but the apps that connect need to support it for the failover to be elegant (or at least connect properly such that they will get redirected). We use DPM 2007 for backups and Microsoft had touted how you could easily script auto-restores with it so I've done that but the solution isn't all that clean or pretty so I wouldn't recommend it. Perhaps it will be better with DPM 2010. None of this amounts to an answer but a bit of insight into what doesn't work. Good luck.
very possible to do, but not for the faint of heart.
There are some good scripts written by DBA's out there, but you'll probably need to be competent enough to modify it for your own needs.
I've done it in cases where there's been no official support from MS. i.e. logshipping btwn sql server 2000 primary & sql server 2005 stand-by.
This script may be a good starting point. Even though its for 2000, the method is till valid.