Hi I'd like to know if it seems feasible to do things in this manner.
We have several application servers (a web server for intranet applications, a couple of different app servers). Each server is backed by it's own locally installed SQL Server Express 2008 or 2008 R2 instance. For the most part each instance handles it's own backups locally using inbuilt mechanisms (and the good solution from ola.hallengren.com).
What we want to do is get all these backups off onto another server. We bought a 5 connection licence of SQL Server 2008 R2 Standard. This might have been useable for some of our applications but almost certainly not for the web server which opens many connections to the database engine depending on site usage.
I'm happy for these servers to continue to use their Express instances, but what I want to do is replicate the databases to this new Standard service (which could be anywhere..).
I thought possibly I could use transactional replication effectively enabling the Express instances as Subscribers to push changes back to the Standard instance, then enable a single robust backups solution on the new Standard instance.
I've had a fair bit of trouble getting the replication working though (using an Express instance on my laptop and the Northwind database), and before I invest any more time into this I'm looking for some opinion on this solution.
Thanks in advance.