3

My situation: I've been looking for an option of data replication for my SQL Server (I'm not expert on it, I should say it - neither on English Language). I've seen a few options, like Log Shipping and SQL Replication. For Log Shipping, I've read that it only works if both servers are using SQL Enterprise Edition (I use Express). For SQL Replication, I saw that on the main server is necessary Enterprise, but I would like to know (although I'm almost certain) if Enterprise is necessary on both servers. If necessary, is there a option os data replication for Express? I would like something that doesn't make an entire database copy everytime, mor like a transactional replication. Thanks

  • 3
    Both log shipping and replication will work fine with SQL Server Standard edition. – JNK Sep 22 '15 at 13:39
  • Standard Edition will do replication, but debugging problems is a nightmare without SQL Server Enterprise Manager... – user316946 Oct 15 '15 at 22:45
6

I'm using log shipping extensively with SQL Server Standard edition. Replication also works fine with Standard edition. (Here are the SQL versions that support log shipping out of the box. Here is a less extensive chart with how the versions of SQL Server support replication.)

Since you're using Express, you don't have access to SQL Server Agent, which limits your possibilities. SQL Express can't be a publisher or a distributor because it needs the agent for that. And if you use a wizard to set up log shipping, it creates Agent jobs to run it.

You can, however, log ship to and from Express manually using Windows Task Scheduler. Basically, create a task scheduler job to use SQLCMD to back up the logs and move them to a share and/or restore the logs. (If you need to query the secondary database you'll need to restore it in Standby rather than Norecovery.)

You can also Replicate from SQL Standard edition to Express, as long as the server running Express is the subscriber rather than the publisher or distributor.

  • "Standby rather than Norecovery" link isn't working anymore. – Aleph0 May 22 '17 at 14:21
  • @Aleph0 Fixed now. – Katherine Villyard May 23 '17 at 18:50

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.