glennsantacruz wrote:1) Research/implement transactional replication for SQL Express ( so as not to introduce additional licensing burdens on our Veeam environment ). However, SQL Express has limitations with respect to transactional replication.
2) Use "full blown" SQL installations in both sites, with "full blown" transactional replication ( and "full blown" Microsoft licensing costs... )
3) "Cross-point" the Veeam servers. What I mean is to point the "prod" datacenter Veeam to a SQLExpress instance in the DR datacenter, and vice-versa. This would (naturally) introduce latency on all the Veeam jobs, with respect to database updates, but we'd be facing that very same latency with real-time replication (wouldn't we?). At face value, this seems just crazy, but the more I consider it, the more I'm liking it. I'm especially considering Tom's case here - this could give the "best case" of push replication without the nasty problem of site failure (or other mid-replication issues...)
So I have thoughts, just been too busy to get them out lately.
We were serious leaning toward using option "2" because we already have dedicated MSSQL servers, although we don't currently use them for Veeam, but we could. Unfortunately, those SQL servers are currently being replicated with Veeam, so it adds an interesting twist. Would I simply start replicating all of our SQL servers via transactional replication and quit using Veeam for those? Maybe. It does add a layer of complication we're not to sure about, it's yet another service which we'd need to be alerted when it's broken and another service to check on daily. It also gets complicated as you scale. We have four sites replicating (remote sites replicating to main datacenter, main datacenter replicating to secondary datacenter) and may soon add sites 4-6. Now I need 6 SQL servers, each transactionally replicating to it's partner site. That's 6 places for things to go wrong. Very iffy.
So, after thinking about all of those things, we had also dreamed up a few other options:
1. Basically, our first idea was exactly what you're thinking for "Option 3". Put the SQL server on the "target" side of the Veeam "push" server. This seems reasonable, our remote sites could all use a single SQL server in the main datacenter, while the Veeam server in the main datacenter would use a SQL server in the failover datacenter. It would be easy enough to proceduralize the failover to the secondary Veeam servers in the even of a disaster.
2. Our second thought was to keep doing basically what you're currently doing, sending a copy of the database after each replication job but, for added protection, take a storage system or file system level snapshot as well. That way, if a new Veeam replication cycle starts, and a crash happens during the cycle, you can just revert your replication targets to the point of the last replication cycle. This is also pretty easy to proceduralize if your environment can easy support snapshots.
3. Third option...just say the heck with it and try to wait a few months for Veeam 5 with SureBackup. Why does this help? Well, Veeam VBK files are significantly more robust than replica's. They can be imported into another Veeam server, and an "incomplete" VBK file (one that was interrupted in progress) can still be recovered. Actually, if you have a large VBK file with 10 servers, and 5 were backed up before the crash, those servers will be available to their latest recovery point, while the others can be rolled to their previous point. No need for the "original" SQL database. Since you can simply run your VM straight from the VBK files, there's very little need for replication anyway.