Prev: Replicating from SQL 2000 to SQL 2008
Next: Moving the Distribution Database to a server with the same nam
From: Ben S Ben on 22 Mar 2010 08:26
Hi,

I work for a small business. We have SQL Server 2005 Standard running on our
office server. We also have a remote web server (hosted by Fasthosts) which
has SQL Server 2005 Express running on it.

We need to be able to synchronize data between the two servers over the
internet. It doesn't have to be real time, but it should at least be hourly.
The main problem is that data will be changed on BOTH servers (most changes
are made on the web server). Unfortunately we are a small business and cannot
afford to buy a SQL Server 2005 Standard processor license for the web
server, in order for us to configure that as the publisher.

Can anyone offer any advice on how to replicate these two servers, or any
alternative suggestions to replication?

Many thanks,
Ben S
From: Jay Konigsberg on 23 Mar 2010 09:03
How were you planning on resolving conflicts when the same records are
updated on both servers? The sort of things that Merge Replication deals
with?

--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Ben S" <Ben S(a)discussions.microsoft.com> wrote in message
news:84198A6B-11C6-4CB6-B4AA-B986DF24A33C(a)microsoft.com...
> Hi,
>
> I work for a small business. We have SQL Server 2005 Standard running on
> our
> office server. We also have a remote web server (hosted by Fasthosts)
> which
> has SQL Server 2005 Express running on it.
>
> We need to be able to synchronize data between the two servers over the
> internet. It doesn't have to be real time, but it should at least be
> hourly.
> The main problem is that data will be changed on BOTH servers (most
> changes
> are made on the web server). Unfortunately we are a small business and
> cannot
> afford to buy a SQL Server 2005 Standard processor license for the web
> server, in order for us to configure that as the publisher.
>
> Can anyone offer any advice on how to replicate these two servers, or any
> alternative suggestions to replication?
>
> Many thanks,
> Ben S


From: Ben S on 24 Mar 2010 08:17
Data updated on the web server will always take priority over data changed in
our office. If a conflict occurs, the data changed on the web server should
be used.

"Jay Konigsberg" wrote:

> How were you planning on resolving conflicts when the same records are
> updated on both servers? The sort of things that Merge Replication deals
> with?
>
> --
> Jay Konigsberg
> SQL Server DBA in Sacramento, CA
> http://www.linkedin.com/in/jaykonigsberg
>
> Live in Sacramento, CA?
> Join the Sacramento SQL Server User Group on LinkedIn
> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>
>
>
> "Ben S" <Ben S(a)discussions.microsoft.com> wrote in message
> news:84198A6B-11C6-4CB6-B4AA-B986DF24A33C(a)microsoft.com...
> > Hi,
> >
> > I work for a small business. We have SQL Server 2005 Standard running on
> > our
> > office server. We also have a remote web server (hosted by Fasthosts)
> > which
> > has SQL Server 2005 Express running on it.
> >
> > We need to be able to synchronize data between the two servers over the
> > internet. It doesn't have to be real time, but it should at least be
> > hourly.
> > The main problem is that data will be changed on BOTH servers (most
> > changes
> > are made on the web server). Unfortunately we are a small business and
> > cannot
> > afford to buy a SQL Server 2005 Standard processor license for the web
> > server, in order for us to configure that as the publisher.
> >
> > Can anyone offer any advice on how to replicate these two servers, or any
> > alternative suggestions to replication?
> >
> > Many thanks,
> > Ben S
>
>
> .
>
From: Jay Konigsberg on 24 Mar 2010 09:22
How many tables are we talking about that need to be replicated?

Basically, I'm thinking about changing this from an Admin task, to a
Development task. Which means that you're going to have to pay for
development and maintenance, which will probably end up being more than the
standard license - even if it is spread over time.

Some thought points:

- Express can be a Merge Replication Subscriber, however, because the
Express instance has precedence, this fact is somewhere between tricky and
useless.
- Triggers can be used on Express to record changes to a change
log/table.
- A job can be created on your Standard Edition that access the change
log and source tables via a Linked Server.
- A procedure can be written to apply those changes to the
office/standard instance.
- Using Merge replication to send the changes back to the Express is
possible, but tricky because of the precedence.

Another option would be to find a 3rd party package that will do the
replication and that you can afford. I Google'd "sql server express
replication" and got a lot of hits, though some refer to its ability to be a
Subscriber, others might do what you want. Just remember: Caveat Emptor!

Basically, you're asking for Enterprise class features, for free (or cheap)
and that just isn't likely.

Good luck though,
Jay


--
Jay Konigsberg
SQL Server DBA in Sacramento, CA
http://www.linkedin.com/in/jaykonigsberg

Live in Sacramento, CA?
Join the Sacramento SQL Server User Group on LinkedIn
http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg



"Ben S" <BenS(a)discussions.microsoft.com> wrote in message
news:0A500378-1952-4C2D-8679-73087DCF5565(a)microsoft.com...
> Data updated on the web server will always take priority over data changed
> in
> our office. If a conflict occurs, the data changed on the web server
> should
> be used.
>
> "Jay Konigsberg" wrote:
>
>> How were you planning on resolving conflicts when the same records are
>> updated on both servers? The sort of things that Merge Replication deals
>> with?
>>
>> --
>> Jay Konigsberg
>> SQL Server DBA in Sacramento, CA
>> http://www.linkedin.com/in/jaykonigsberg
>>
>> Live in Sacramento, CA?
>> Join the Sacramento SQL Server User Group on LinkedIn
>> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>>
>>
>>
>> "Ben S" <Ben S(a)discussions.microsoft.com> wrote in message
>> news:84198A6B-11C6-4CB6-B4AA-B986DF24A33C(a)microsoft.com...
>> > Hi,
>> >
>> > I work for a small business. We have SQL Server 2005 Standard running
>> > on
>> > our
>> > office server. We also have a remote web server (hosted by Fasthosts)
>> > which
>> > has SQL Server 2005 Express running on it.
>> >
>> > We need to be able to synchronize data between the two servers over the
>> > internet. It doesn't have to be real time, but it should at least be
>> > hourly.
>> > The main problem is that data will be changed on BOTH servers (most
>> > changes
>> > are made on the web server). Unfortunately we are a small business and
>> > cannot
>> > afford to buy a SQL Server 2005 Standard processor license for the web
>> > server, in order for us to configure that as the publisher.
>> >
>> > Can anyone offer any advice on how to replicate these two servers, or
>> > any
>> > alternative suggestions to replication?
>> >
>> > Many thanks,
>> > Ben S
>>
>>
>> .
>>


From: Ben S Ben on 24 Mar 2010 11:24
Many thanks for your advice Jay, much appreciated. You are probably right
about the cost of development being more than the cost of SQL Standard.
Perhaps it's time to spend some money...

"Jay Konigsberg" wrote:

> How many tables are we talking about that need to be replicated?
>
> Basically, I'm thinking about changing this from an Admin task, to a
> Development task. Which means that you're going to have to pay for
> development and maintenance, which will probably end up being more than the
> standard license - even if it is spread over time.
>
> Some thought points:
>
> - Express can be a Merge Replication Subscriber, however, because the
> Express instance has precedence, this fact is somewhere between tricky and
> useless.
> - Triggers can be used on Express to record changes to a change
> log/table.
> - A job can be created on your Standard Edition that access the change
> log and source tables via a Linked Server.
> - A procedure can be written to apply those changes to the
> office/standard instance.
> - Using Merge replication to send the changes back to the Express is
> possible, but tricky because of the precedence.
>
> Another option would be to find a 3rd party package that will do the
> replication and that you can afford. I Google'd "sql server express
> replication" and got a lot of hits, though some refer to its ability to be a
> Subscriber, others might do what you want. Just remember: Caveat Emptor!
>
> Basically, you're asking for Enterprise class features, for free (or cheap)
> and that just isn't likely.
>
> Good luck though,
> Jay
>
>
> --
> Jay Konigsberg
> SQL Server DBA in Sacramento, CA
> http://www.linkedin.com/in/jaykonigsberg
>
> Live in Sacramento, CA?
> Join the Sacramento SQL Server User Group on LinkedIn
> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
>
>
>
> "Ben S" <BenS(a)discussions.microsoft.com> wrote in message
> news:0A500378-1952-4C2D-8679-73087DCF5565(a)microsoft.com...
> > Data updated on the web server will always take priority over data changed
> > in
> > our office. If a conflict occurs, the data changed on the web server
> > should
> > be used.
> >
> > "Jay Konigsberg" wrote:
> >
> >> How were you planning on resolving conflicts when the same records are
> >> updated on both servers? The sort of things that Merge Replication deals
> >> with?
> >>
> >> --
> >> Jay Konigsberg
> >> SQL Server DBA in Sacramento, CA
> >> http://www.linkedin.com/in/jaykonigsberg
> >>
> >> Live in Sacramento, CA?
> >> Join the Sacramento SQL Server User Group on LinkedIn
> >> http://www.linkedin.com/groups?home=&gid=2825448&trk=anet_ug_hm&goback=%2Emyg
> >>
> >>
> >>
> >> "Ben S" <Ben S(a)discussions.microsoft.com> wrote in message
> >> news:84198A6B-11C6-4CB6-B4AA-B986DF24A33C(a)microsoft.com...
> >> > Hi,
> >> >
> >> > I work for a small business. We have SQL Server 2005 Standard running
> >> > on
> >> > our
> >> > office server. We also have a remote web server (hosted by Fasthosts)
> >> > which
> >> > has SQL Server 2005 Express running on it.
> >> >
> >> > We need to be able to synchronize data between the two servers over the
> >> > internet. It doesn't have to be real time, but it should at least be
> >> > hourly.
> >> > The main problem is that data will be changed on BOTH servers (most
> >> > changes
> >> > are made on the web server). Unfortunately we are a small business and
> >> > cannot
> >> > afford to buy a SQL Server 2005 Standard processor license for the web
> >> > server, in order for us to configure that as the publisher.
> >> >
> >> > Can anyone offer any advice on how to replicate these two servers, or
> >> > any
> >> > alternative suggestions to replication?
> >> >
> >> > Many thanks,
> >> > Ben S
> >>
> >>
> >> .
> >>
>
>
> .
>
 |  Next  |  Last
Pages: 1 2
Prev: Replicating from SQL 2000 to SQL 2008
Next: Moving the Distribution Database to a server with the same nam