SQL Server Replication only for Enterprise Edition? Does it require Enterprise on both sides? Other solution?

Question

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

Answer 1

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.