I read Paul Ibison's article about "Replication Across Non-Trusted Domains
or Using the Internet".
Me (and my company) need to take a very tough decision whether using SQL
server 2005 merge replication or building our own synchronization engine.
Our big problems are:
1. Everything has to work over the internet (over port 443 only), even port
21 for ftp is not an issue…
2. Out product is for customers overseas without the ability to go through
an integration process at the client site. It means we must use some
automatic process configuring the publisher or subscriber and users running
processes, etc.
3. The installation must work from an installation cd (both client and
server), without the need to manually configure domain users to run certain
replication agents – it must also be automatically.
Which one (or all) of the demands above is possible?
We are willing to make small changes –only after we definitely know that we
must open port 21 for example.
I understood that the implementation of offline clients (doesn't matter if
it's PDA with SQL CE/Mobile or windows XP with MSDE/Express) and Merge
replication in the SQL Server is good in theory but difficult to manage and
requires a lot of extra handling issues like opening ports and adding users
granted to run the processes for the replication process.
Are there any improvements in 2005 in these issues (I know about the option
to replicate through iis so the port problem is now solved?!).
My most important request is that I'll be glad if anybody knows and can
write about products in the open market which used this architecture.
Thanks,
Gil.
I'm in a crucial
Is it true? Are there any improvements in 2005 in these issues (I know about
the option to replicate through iis so the port problem is now solved?!).
I'll be glad if anybody knows and can write about products in the open
market which used this architecture.
Thanks,
Gil.
1) no problem, you can configure sql server to run on any port - have a
look at http://support.microsoft.com/default...b;en-us;823938 in
the section marked. However, port 443 is for https, did you want your SQL
Server traffic encrypted?
Configuring an instance of SQL Server to use a static port
2) you can script out replication jobs by right clicking on a publication
and selecting - generate sql script. You can generate the scripts necessary
to enable replication by right clicking on the replication folder and
selecting Generate SQL Scripts, and then selecting Distributor properties.
3) you can configure autorun.inf to run an executable which will run the
scripts generated above
Everything you list is possible - you suddenly start talking about SQL CE -
if your clients are running SQL CE, you will need a web server to
synchronize with. You could run over port 443 for this. I would not
characterize "Merge replication in the SQL Server is good in theory but
difficult to manage and requires a lot of extra handling issues like opening
ports and adding users granted to run the processes for the replication
process."
This is simply not true. Merge replication or any form of replication only
needs port 1433 (or whatever port you run SQL Server on) open. If you are
deploying your snapshot over the internet you will probably want port 21
open as well. Regarding the addition of users, this could be valid. You can
run pull agents on the subscribers which will connect to the publisher under
different accounts if you are doing filtering by suser_name(). But by
default you probably would not have to do this. I normally use pass through
authentication which uses the same account names.
There are lots of improvements, fixes, and features in SQL 2005. Web
synchronization is one of them. Although the documentation talks about
running over port 1433 (https) in addition to 80(http), I believe this is a
mistake.
I worked on a replication topology with over 60 merge clients. We had our
share of problems, but in general it was highly stable.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gil" <Gil@.discussions.microsoft.com> wrote in message
news:123D2E11-371E-419E-8B5F-D4710F95A008@.microsoft.com...
> Hi,
> I read Paul Ibison's article about "Replication Across Non-Trusted Domains
> or Using the Internet".
> Me (and my company) need to take a very tough decision whether using SQL
> server 2005 merge replication or building our own synchronization engine.
> Our big problems are:
> 1. Everything has to work over the internet (over port 443 only), even
port
> 21 for ftp is not an issue.
> 2. Out product is for customers overseas without the ability to go through
> an integration process at the client site. It means we must use some
> automatic process configuring the publisher or subscriber and users
running
> processes, etc.
> 3. The installation must work from an installation cd (both client and
> server), without the need to manually configure domain users to run
certain
> replication agents - it must also be automatically.
> Which one (or all) of the demands above is possible?
> We are willing to make small changes -only after we definitely know that
we
> must open port 21 for example.
> I understood that the implementation of offline clients (doesn't matter if
> it's PDA with SQL CE/Mobile or windows XP with MSDE/Express) and Merge
> replication in the SQL Server is good in theory but difficult to manage
and
> requires a lot of extra handling issues like opening ports and adding
users
> granted to run the processes for the replication process.
> Are there any improvements in 2005 in these issues (I know about the
option
> to replicate through iis so the port problem is now solved?!).
> My most important request is that I'll be glad if anybody knows and can
> write about products in the open market which used this architecture.
> Thanks,
> Gil.
>
> I'm in a crucial
> Is it true? Are there any improvements in 2005 in these issues (I know
about
> the option to replicate through iis so the port problem is now solved?!).
> I'll be glad if anybody knows and can write about products in the open
> market which used this architecture.
> Thanks,
> Gil.
>
>
|||Thank you for your answer...
But I guess I wasn't completely clear.
We plan to use merge replication with new web replication feature available
in "SQL server 2005", and I wanted to know whether there is an option to work
with SSL over http without the need for opening the ftp port?
We definitely cannot open port 1433 because the replication will go over the
internet – and Windows authentication is not possible too.
There is also a need to use SUSER_SNAME() function in order filter by the
connected user. Is there a workaround of doing it without giving special
permissions to the user? I noticed there are security problems impersonating
the users just like that, these are the most annoying parts
Thanks,
Gil.
"Hilary Cotter" wrote:
> 1) no problem, you can configure sql server to run on any port - have a
> look at http://support.microsoft.com/default...b;en-us;823938 in
> the section marked. However, port 443 is for https, did you want your SQL
> Server traffic encrypted?
> Configuring an instance of SQL Server to use a static port
> 2) you can script out replication jobs by right clicking on a publication
> and selecting - generate sql script. You can generate the scripts necessary
> to enable replication by right clicking on the replication folder and
> selecting Generate SQL Scripts, and then selecting Distributor properties.
> 3) you can configure autorun.inf to run an executable which will run the
> scripts generated above
> Everything you list is possible - you suddenly start talking about SQL CE -
> if your clients are running SQL CE, you will need a web server to
> synchronize with. You could run over port 443 for this. I would not
> characterize "Merge replication in the SQL Server is good in theory but
> difficult to manage and requires a lot of extra handling issues like opening
> ports and adding users granted to run the processes for the replication
> process."
> This is simply not true. Merge replication or any form of replication only
> needs port 1433 (or whatever port you run SQL Server on) open. If you are
> deploying your snapshot over the internet you will probably want port 21
> open as well. Regarding the addition of users, this could be valid. You can
> run pull agents on the subscribers which will connect to the publisher under
> different accounts if you are doing filtering by suser_name(). But by
> default you probably would not have to do this. I normally use pass through
> authentication which uses the same account names.
> There are lots of improvements, fixes, and features in SQL 2005. Web
> synchronization is one of them. Although the documentation talks about
> running over port 1433 (https) in addition to 80(http), I believe this is a
> mistake.
> I worked on a replication topology with over 60 merge clients. We had our
> share of problems, but in general it was highly stable.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Gil" <Gil@.discussions.microsoft.com> wrote in message
> news:123D2E11-371E-419E-8B5F-D4710F95A008@.microsoft.com...
> port
> running
> certain
> we
> and
> users
> option
> about
>
>
|||Re the port issue - I asked this question on the Beta newsgroup - for Merge
over the internet in SQL 2005, is HTTP (80) supported or must it be HTTPS
(443) and the reply was HTTPS only. (which makes it a pain fully testing the
whole process on XP which doesn't support certificate server). At the time I
noticed that the advice in BOL for the initial Beta2 version had some typos
and mixed the 2 - HTTP and HTTPS, or was at best misleading. I haven't
checked the new BOL yet on this issue.
Cheers,
Paul Ibison
|||Hi,
Can someone give me some real-world example of a product (not an IT project
- where a DBA be in the integration process, a product for customers
overseas) which is using web merge replication (over http only) ?
Doesn't matter if the Client is windows XP or mobile.
Thanks,
Gil.
"Paul Ibison" wrote:
> Re the port issue - I asked this question on the Beta newsgroup - for Merge
> over the internet in SQL 2005, is HTTP (80) supported or must it be HTTPS
> (443) and the reply was HTTPS only. (which makes it a pain fully testing the
> whole process on XP which doesn't support certificate server). At the time I
> noticed that the advice in BOL for the initial Beta2 version had some typos
> and mixed the 2 - HTTP and HTTPS, or was at best misleading. I haven't
> checked the new BOL yet on this issue.
> Cheers,
> Paul Ibison
>
>
|||Gil,
I'd also be interested to hear of any such products but I think we'll be
lucky - SQL Server's only in Beta2 at this stage
HTH
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
No comments:
Post a Comment