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.

  • As Jim pointed out, perhaps I was over-complicating the situation. Even having abandoned the idea of using SQL Server Standard as a way of getting data backups from multiple Express instances, I needed something. The scripts available from Ola Hallengren work really well and are not hard to set up. – Troy May 15 '14 at 4:57

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.

  • Yes I agree, this is what I was thinking myself. Obviously I have to move the databases first to the new server to be published. – Troy Dec 18 '11 at 10:26
  • I wonder obviously, if anybody has tried something like this with success though. I've had some difficulty trying to get it going.... – Troy Dec 18 '11 at 10:26

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.

  • I believe I said that already in my OP... thanks for your alternative input however. – Troy Dec 18 '11 at 10:28
  • ok I'll edit the answer to be more clear – Jim B Dec 19 '11 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.