SQL server backups - backwards situation

Question

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.

Answer 1

We can see from Features Supported by the Editions of SQL Server 2008 R2 that the Express Edition supports Subscriber Only.

However, one option would be to use 1 of your Standard Edition licenses and utilize that instance to act as the Publisher/Distributor for your Express Edition Subscribers. This configuration would require a bi-directional solution such as Merge Replication or Bi-directional Transactional Replication.

Answer 2

I believe express can only be a subscriber not a publisher for replication. I would probably script copying the backups off instead or migrate to a single standalone server.

EDIT: I would not worry about using replication simply to consolidate backups. You haven't mentioned what is missing from your current backup solution other than trying to find a way to get them to another server. I haven't seena reason to introduce the complexity of replication for a problem that seems to be solvable with a copy job.