August 23, 2007 at 3:54 am
Hi, I need to replicate data between 2 SQL Server Express Server.
Is there a three party tool for this?
Thank you.
August 23, 2007 at 9:58 am
What kind of replication? Do you need to be able to make edits to both servers, or can one of them be a read only version? How quickly do you need the changes to appear on the other side?
I don't know of any 3rd party tools regardless, but if one of them can be read only that greatly simplifies your problem space. You can use a roll-your-own log shipping configuration. You will need to write or buy code but it is not super complicated. Each time the log is applied to the slave server you will need to disconnect clients for it to be applied.
If you need updates quickly but can still have one read only server, you may want to invest in one server being a non-free Sku and use transactional replication to push to your Express sku.
August 23, 2007 at 12:37 pm
Hi, thanks for your answers
I need to send the data of two servers (in diferent cities) to the central server. The three servers recieve input information from my Application. I need to update the information in the central server at least 1 time at week.
Should I use replication?
Does Sql Server Express allow replications between servers?
Should I use a third party program?
Are there other solutions?
Actually my application generate the data and send it via mail. But the process is manual, performed by the user. I would like this process be automatic.
Thanks.
August 27, 2007 at 9:20 am
Hi Juan,
SQL Express only supports being a subscriber to replication topologies, not a publisher. What this means is it can retrieve data from some non-Express SKU (and non-free), but it cannot send data to any server by traditional replication technologies.
I don't know if there are any 3rd party solutions, I haven't looked into this for my needs so I'm unfamiliar with it.
If you only need weekly updates, replication may be overkill for you. Is it a large or small quantity of data? If you simply need to move some reporting data to the central server you may be able to get by with just using a linked server. The downside is there is no built-in retry logic or error reporting to an email account or anything. You would have to handle this in code somewhere or have someone be able to notice manually and take corrective action.
The general approach for this would be:
-drill down in management studio to Server Objects => Linked Servers, then right-click and say New Linked Server. Enter the data here and set the security context you want to use whenever querying against the remote server. Let's say for example that you setup a linked server called mydb1.myapp.com
-then do reporting using 4 part names, as a simple contrived example:
--make note of what we're going to send up
DECLARE @batchToSend (Id INT);
INSERT @batchToSend (Id) SELECT Id FROM MyDatabase.dbo.MyTable WHERE Uploaded = 0;
--send up the data
INSERT [mydb1.myapp.com].MyDatabase.dbo.MyTable (CoolData)
SELECT CoolData FROM MyDatabase.dbo.MyTable WHERE Id IN (SELECT Id FROM @batchToSend);
--mark the local data as sent
UPDATE MyDatabase.dbo.MyTable SET Uploaded = 1 WHERE Id IN (SELECT Id FROM @batchToSend);
Hope that helps!
-Adrian
August 28, 2007 at 4:27 am
Thanks Adrian for your interest.
I'll try your recomendation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply