March 2, 2007 at 2:48 am
Hi,
I need to synchronize the data between two SQL Server 2005 Express.
The database is really simple, just few tables (but data are inserted very often) without any triggers.
Since Express edition could not behave as a Publisher I was thinking about implementing manually a sort of Replication mechanism... It should be a sort of Transactional Replication with queueud updating.
Does it sound a good idea?
Has anyone done this before o there are better solution?
Thanks in advance,
Stefano
March 2, 2007 at 3:06 am
March 2, 2007 at 3:58 am
Hi James,
The data may arrive at both sides but not at the same time.
It's a sort of master-slave relation:
The master receives the data from the field and these data should be copied to the slave.
It may happen that the Master-Slave roles are inverted and the data start arriving at the second Server and have to be copied to the first.
Thanks,
Stefano
March 2, 2007 at 4:28 am
Stefano,
There are several options - the first would be some sort of log shipping mechanism - however this is generally only a unidirectional process and you would have to reseed and reverse the direction if you changed over the direction - this is probably the best option if it is viable - you will have to use windows scheduler and some vbs scripts to run it but it shouldnt be too hard to set up and will work reliably but you wont get merge replication. If you want to go with this option then let me know and I will give you some tips on how to set it up.
You might be able to set up a DTS task to do the replication (but I am not a DTS guru I would have a google and see if there are any articles/code examples for this).
You might also be able to setup an update trigger which updates the remote database but this will suck performance wise.
A tool such as Red Gate's SQL Data Compare has both an API and a command line version which will sync data which you can schedule every few hours/minutes.
Beyond that I cant think of anything at this second - if you want true merge replication rather than a hack you will have to move up a version of SQL 2005.
Someone else might have a better suggestion.
- James
--
James Moore
Red Gate Software Ltd
March 2, 2007 at 5:15 am
Hi James,
Thanks again for your kind suggestions!
I will make some investigation about the log shipping and DTS techniques that you suggest which I don't know very well... in case I'll ask you some link about the log shipping.
But isn't log shipping not available with the Express edition?
I could not consider the Red Gate tool because our customer what us to suggest a free solution... their system is based on MSDE and now they want to port it to 2005 Express and add to it this sort of Replication mechanism.
You're right, triggers may slow everything down so I will consider it as a last chance.
Best regards!
Stefano
March 2, 2007 at 6:24 am
Stefano,
The principle of log shipping is fairly simple, microsoft dont give you all the niceties in SQL Server Express but you can still get pretty much the same functionality.
The first step is on the master server.
1. Perform a full backup the master database to disk IE: BACKUP DATABASE masterDatabase TO DISK = '\\Server\Common\master.bak' WITH INIT
2. On the slave server you then want to restore this database and put it into standby mode (it will be read only) IE RESTORE DATABASE slaveDatabase FROM DISK = '\\SERVER\Common\master.bak' WITH ...., STANDBY='standbyfilename.dat'
3. You then want to setup a windows scheduled job which takes a log backup of your masterDatabase and puts it on \\Server\Common\ (with the datetime in the name to avoid overwriting previous log backups).
4. You then set up another windows scheduled job which checks for log backups on \\Server\Common\ from the slave server and restores each one in the right order and moves each successfully moved log backup out of \\Server\Common\ to some other directory (say c:\logshipped\).
Its going to be quite a lot of work for you to get it working with sql server express but should be doable.
When you want to swap things over what you have to do take a last transaction log backup, applying any outstanding transaction log backups, run RESTORE slaveDatabase WITH RECOVERY to bring it into an editable mode, take a full backup of slaveDatabase, restore it with STANDBY to masterDatabase and reverse the jobs round.
- James
--
James Moore
Red Gate Software Ltd
March 2, 2007 at 6:47 am
Wow!
Thank you very very much!
That sound good, not very simple but at least I will have the opportunity to improve my knowledge about SQL Server!
Best regards,
Stefano
April 19, 2012 at 1:50 am
I think DTS is not an option here as we need to have sql server agent to schedule the DTS packages.
Possibly we can develop the package on the standard version and then can deploy it on SQL Express.
Which should be MS's SQL Express with advanced option and Express Edition toolkit should be installed on the box.
And the DML Triggers with Linked servers approach isn't really advisable in the performance standpoint.
It would be better if we can manage to have modified Datetime on every table along with a windows service which runs periodically to compare the data on each database
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply