Dec 19, 2011
tom

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

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.

Related posts:

  1. Are there ramifications of running SQL Server 2008 Developer and SQL Server 2008 Express side-by-side?
  2. Which SQL Server edition?
  3. Can you upgrade SQL Server 2008 SP2 Standard Edition to 2008 R2 Developer Edition?
  4. Migrate database from SQL Server Standard to SQL Server Express for DotNetNuke
  5. Installing MS SQL Server 2008 R2 side by side with MS SQL 2008 R2 Express edition

Leave a comment