SQL 2008 update

Question

Post reply

SQL 2008 update

ionas

Mr or Mrs. 500

Points: 521
More actions
July 18, 2016 at 1:29 am

#311835

We use SQL 2008 Express to customers sites. We need to maintain a backup to our portal. We first take a full backup from each customer site and then restore it to our portal. Then, on a regular basis, we need to restore to our portal only the changes made to customers dbs. So, we need to take the changes made after full backup or after the last restore and 'play' them to our portal db. The customers do not have internet connections, they run on Vessels, so the backup file should be as small as possible. Differential file is not small.
Are there any other options ?

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Have you tried looking into Merge Replication? Looks like a perfect fit for your problem.

-- Gianluca Sartori

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 2

I am not aware of this. Can you please give me some details regarding "merge replication" ?

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Merge replication is a data distribution technology that is used to keep multiple subscribers in sync with a publisher.

Each row is marked with a unique GUID that is used to identify changed rows. Some change tracking tables are populated by triggers when changes are applied to the tables, so that subscribers can receive changed rows when they connect to the publisher. A program called merge agent takes care of sending changes to the subscribers and receive changes in case of bidirectional articles (tables).

Here is an overview of merge replication: https://msdn.microsoft.com/en-us/library/ms152746.aspx

-- Gianluca Sartori

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 4

Dear Santoni,

for our case, the problem is we don't have a continuous connection between subscriber and publisher. Can we accomplish this offline ? Also, our source db is on SQL 2008 EXPRESS.

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Merge Replication is designed for subscribers staying offline for long periods. Merge Replication is available in all versions of SQL Server since 2000 if I recall correctly. It definitely is part of SQL Server 2008.

In Express Edition you will have to create some scheduled tasks to run the merge agent, as SQL Server Agent will not be available.

-- Gianluca Sartori

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 6

Thanks a lot for given info. We try it and let you know !

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 7

You said "Merge Replication is designed for subscribers staying offline for long periods". YES, ok. When it comes online, as I understand, must be able to connect to the publisher, right ?

To our case, the publisher cannot reach the subscriber, because subscriber is not connected to the Internet. We must create a script file with the changes and run the script to the subscriber.

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

There is a way to allow connection of Merge Replication subscribers through a web application in IIS. It's called web synchronization: https://msdn.microsoft.com/en-us/library/ms151763.aspx

-- Gianluca Sartori

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 9

The problem is, we don't have internet connection to publisher !. The installation is "isolated" on a Vessel ! We can only exchange files.

spaghettidba SSC Guru Points: 105732 More actions · Answer 10

How do they provide the backup file to you? I suppose there is some connectivity involved at some point

-- Gianluca Sartori

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 11

ionas

Mr or Mrs. 500

Points: 521

July 18, 2016 at 7:51 am

#1890595

They send it via email

spaghettidba SSC Guru Points: 105732 More actions · Answer 12

OK, sorry if I insist...

1) if they send via email, at some point the machines are connected to a network that could allow the sync

2) sending via email means that the databases are so small that they fit in the 1-10 MB range: how are things supposed to work when the databases grow?

3) inventing your own sync mechanisms is a terrible idea: avoid that if possible

-- Gianluca Sartori

ionas Mr or Mrs. 500 Points: 521 More actions · Answer 13

Dear Santoni,

I definitely agree with you. We do not want to write a code to sync both databases. That's why are trying to find a ready tool. The vessels (customers installations) do not have Internet connection, instead, they have only (for few minutes) the capability to send some emails every day. On top of that, the bigger the email the more money the spend ! So, we are looking to send, via email, only the changes to the data. Generally speaking, the databases are small, the daily transactions are few.

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

A few minutes are ok to sync via merge replication. It only sends the delta, so it looks like a perfect fit for your case.

-- Gianluca Sartori