Merge rows

I've been tearing my hair out for the past few days trying to get a merge across rows in SQL SERVER 200. Here is an example result set that I have so far:

Code Snippet

zzzz_X@.X_orange.net 0 0 1
zzzz_X@.X_orange.net 0 1 0
zzzz_X@.X_orange.net 1 0 0
zztoproadking_X@.X_yahoo.ca 0 0 1
zztoproadking_X@.X_yahoo.ca 0 1 0
zztoproadking_X@.X_yahoo.ca 1 0 0
zztoponly_X@.X_yahoo.com 0 0 1
zztoponly_X@.X_yahoo.com 0 1 0
zztoponly_X@.X_yahoo.com 1 0 0
zzjozz_X@.X_aol.com 0 0 1

What I want to do ultimately is merge those repeated email addresses into a single row, such as:

Code Snippet

zzzz_X@.X_orange.net 1 1 1
zztoproadking_X@.X_yahoo.ca 1 1 1
zztoponly_X@.X_yahoo.com 1 1 1
zzjozz_X@.X_aol.com 0 0 1

I realize this is possible using cursors, but is there a simple SQL way to achieve the same results?

Here's my SQL for the first result set:

Code Snippet

SELECT DISTINCT TOP 10 C.EmailAddress,
CASE C.CampaignID WHEN '1' THEN '1' ELSE '0' END AS travel_deals,
CASE C.CampaignID WHEN '2' THEN '1' ELSE '0' END AS contests,
CASE C.CampaignID WHEN '3' THEN '1' ELSE '0' END AS picks_of_the_week
FROM dbo.MarketingEmailCampaignAddresses C
LEFT OUTER JOIN Members M
ON C.EmailAddress = M.email
GROUP BY C.EmailAddress, C.campaignID, M.memberID
ORDER BY C.EmailAddress DESC

You can group them using "group by" clause.

SELECT TOP 10

C.EmailAddress,
max(CASE WHEN C.CampaignID = '1' THEN '1' ELSE '0' END) AS travel_deals,
max(CASE WHEN C.CampaignID = '2' THEN '1' ELSE '0' END) AS contests,
max(CASE WHEN C.CampaignID = '3' THEN '1' ELSE '0' END) AS picks_of_the_week

FROM

dbo.MarketingEmailCampaignAddresses C
LEFT OUTER JOIN Members M
ON C.EmailAddress = M.email

GROUP BY

C.EmailAddress

ORDER BY C.EmailAddress DESC

go

AMB

|||What is the MAX() doing in this case? The columns after the email address are booleans (or bit in SQL SERVER dialect).

While your way does seem to do an email address merge, I'm getting 1,1,1 for every email address.
|||

You are in the rite track, but missed the group by function which group all your row wise data into single row. You can use max/sum as per your requirement.

This is the legacy approach to get the Pivot data(swaping row based values into Columns),

Code Snippet

SELECT DISTINCT TOP 10

C.EmailAddress,

C.campaignID,

M.memberID,

Isnull(Max(CASE C.CampaignID WHEN '1' THEN '1' END),0) AS travel_deals,

Isnull(Max(CASE C.CampaignID WHEN '2' THEN '1' END),0) AS contests,

Isnull(Max(CASE C.CampaignID WHEN '3' THEN '1' END),0) AS picks_of_the_week

FROM

dbo.MarketingEmailCampaignAddresses C

LEFT OUTER JOIN Members M

ON C.EmailAddress = M.email

GROUP BY

C.EmailAddress,

C.campaignID,

M.memberID

ORDER BY

C.EmailAddress DESC

|||

I really don’t know how you got all 1.

Here the sample,

Create Table #marketingemailcampaignaddresses (

[EmailAddress] Varchar(30) ,

[CampaignID] int

);

Insert Into #marketingemailcampaignaddresses Values('zzzz_X@.X_orange.net','1');

Insert Into #marketingemailcampaignaddresses Values('zzzz_X@.X_orange.net','2');

Insert Into #marketingemailcampaignaddresses Values('zztoproadking_X@.X_yahoo.ca','3');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','1');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','2');

Insert Into #marketingemailcampaignaddresses Values('zztoponly_X@.X_yahoo.com','3');

Insert Into #marketingemailcampaignaddresses Values('zzjozz_X@.X_aol.com','2');

Select

EmailAddress,

Case When CampaignID = '1' Then 1 Else 0 End AS travel_deals,

CASE WHEN CampaignID = '2' THEN 1 ELSE 0 ENDAS contests,

CASE WHEN CampaignID = '3' THEN 1 ELSE 0 ENDAS picks_of_the_week

From

#marketingemailcampaignaddresses

/*

EmailAddresstravel_deals contestspicks_of_the_week

-- --

zzzz_X@.X_orange.net100

zzzz_X@.X_orange.net010

zztoproadking_X@.X_yahoo.ca001

zztoponly_X@.X_yahoo.com100

zztoponly_X@.X_yahoo.com010

zztoponly_X@.X_yahoo.com001

zzjozz_X@.X_aol.com010

*/

Select

EmailAddress,

Isnull(Max(Case When CampaignID = '1' Then 1 End),0) AS travel_deals,

Isnull(Max(CASE WHEN CampaignID = '2' THEN 1 END),0)AS contests,

Isnull(Max(CASE WHEN CampaignID = '3' THEN 1 END),0)AS picks_of_the_week

From

#marketingemailcampaignaddresses

Group By

EmailAddress

/*

EmailAddresstravel_deals contestspicks_of_the_week

-- --

zzjozz_X@.X_aol.com010

zztoponly_X@.X_yahoo.com111

zztoproadking_X@.X_yahoo.ca001

zzzz_X@.X_orange.net110

*/

|||You are absolutely correct. I was getting all 1's when trying to run the pivot without creating a temp table with the original results.

I had to do an SELECT INTO and create a temp table, then run a SELECT from that table to achieve the correct results.

Thanks for the help (both of you)!

Merge Replication w/ Web Synchronization Across Non-Trusted Domain

I have a requirement to replicate a portion of a 2005 database using merge
replication where the database server is in a workgroup at location A and the
web server is in an AD domain at location B. Both locations are connected
via a VPN.
Becuase of the disparate domains we are unable to push snapshots to a share
on the Web Server w/o using FTP. After specifying the FTP information in the
FTP Snapshot and Internet dialog, the following message is returned when
attempting to start the Snapshot Agent:
Message: The replication agent failed to create the directory
'\\172.27.1.187\unc\ftp\DAYMONJPSV02$TEST_CORE_APP RISCORE1\20071218021362\'.
Stack: at
Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String directory)
at
Microsoft.SqlServer.Replication.Snapshot.SnapshotP rovider.CreateSnapshotFolders()
at
Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.CreateSnapshotFolders()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL,
Error number: MSSQL_REPL52026)
Get help: http://help/MSSQL_REPL52026
Source: mscorlib
Target Site: Void WinIOError(Int32, System.String)
Message: Message: Logon failure: unknown user name or bad password.
Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String
maybeFullPath)
at System.IO.Directory.InternalCreateDirectory(String fullPath, String
path, DirectorySecurity dirSecurity)
at System.IO.Directory.CreateDirectory(String path, DirectorySecurity
directorySecurity)
at
Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String directory) (Source: mscorlib, Error number: 0)
The user id and password are those of a domain user for the FTP server at
location B. Do I have to use a non-AD account?
You need to use a snapshot account which has rights to modify to
\\172.27.1.187\unc. You specify this account in sp_addpublication_snapshot
using the @.job_login and @.job_password parameters.
This account should exist on \\172.27.1.187 and your publisher.
http://www.zetainteractive.com - Shift Happens!
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
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:7575E064-365A-49B4-B479-AD3C7D4E14C8@.microsoft.com...
>I have a requirement to replicate a portion of a 2005 database using merge
> replication where the database server is in a workgroup at location A and
> the
> web server is in an AD domain at location B. Both locations are connected
> via a VPN.
> Becuase of the disparate domains we are unable to push snapshots to a
> share
> on the Web Server w/o using FTP. After specifying the FTP information in
> the
> FTP Snapshot and Internet dialog, the following message is returned when
> attempting to start the Snapshot Agent:
> Message: The replication agent failed to create the directory
> '\\172.27.1.187\unc\ftp\DAYMONJPSV02$TEST_CORE_APP RISCORE1\20071218021362\'.
> Stack: at
> Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String
> directory)
> at
> Microsoft.SqlServer.Replication.Snapshot.SnapshotP rovider.CreateSnapshotFolders()
> at
> Microsoft.SqlServer.Replication.Snapshot.MergeSnap shotProvider.CreateSnapshotFolders()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer SnapshotProvider.GenerateSnapshot()
> at Microsoft.SqlServer.Replication.SnapshotGeneration Agent.InternalRun()
> at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQL_REPL,
> Error number: MSSQL_REPL52026)
> Get help: http://help/MSSQL_REPL52026
> Source: mscorlib
> Target Site: Void WinIOError(Int32, System.String)
> Message: Message: Logon failure: unknown user name or bad password.
> Stack: at System.IO.__Error.WinIOError(Int32 errorCode, String
> maybeFullPath)
> at System.IO.Directory.InternalCreateDirectory(String fullPath, String
> path, DirectorySecurity dirSecurity)
> at System.IO.Directory.CreateDirectory(String path, DirectorySecurity
> directorySecurity)
> at
> Microsoft.SqlServer.Replication.Utilities.CreateDi rectoryWithExtendedErrorInformation(String
> directory) (Source: mscorlib, Error number: 0)
> The user id and password are those of a domain user for the FTP server at
> location B. Do I have to use a non-AD account?
|||Thanks Hillary. I am assuming that becasue the servers are in two different
security domains that the account should be local on both servers? Also, if
the publication has already been created, can it be modified to modify the
job_login and job_password parameters? Thanks in advance.
"Hilary Cotter" wrote:

> You need to use a snapshot account which has rights to modify to
> \\172.27.1.187\unc. You specify this account in sp_addpublication_snapshot
> using the @.job_login and @.job_password parameters.
> This account should exist on \\172.27.1.187 and your publisher.
> --
> http://www.zetainteractive.com - Shift Happens!
> 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
> "parchk" <parchk@.discussions.microsoft.com> wrote in message
> news:7575E064-365A-49B4-B479-AD3C7D4E14C8@.microsoft.com...
>
>
|||Exactly, it should be a local account on both servers.
You can modify the snapshot account by right clicking on the publication in
SSMS, selecting properties and clicking on the agent security tab.
http://www.zetainteractive.com - Shift Happens!
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
"parchk" <parchk@.discussions.microsoft.com> wrote in message
news:915C3515-3E0A-4755-88B7-DE72D927A0E2@.microsoft.com...[vbcol=seagreen]
> Thanks Hillary. I am assuming that becasue the servers are in two
> different
> security domains that the account should be local on both servers? Also,
> if
> the publication has already been created, can it be modified to modify the
> job_login and job_password parameters? Thanks in advance.
> "Hilary Cotter" wrote:

Merge Replication of large database

Hi,
I am setting up a merge replication of a large database across internet
between two servers which are located in two geographically different
location and can connect to each other with a fast T1 Connection.
The purpose of it is to have a hot backup site available for the DB.
The problem i am facing is that the DB size before setting up replication is
2GB.
When I set up merge replication and selected snapshot to create , it grow
the DB size to 6GB. Most of the increase in Trasaction log.
Why?
I will apply the backup of these Snapshot generated DB to the subscriber
manually first and then set up the pull subscription on the subscriber.
Is this the right way to handle this situation?
How can I reduce the size of db after activating the replication?
I tried it with shrinkdatabase command but it does not shing the log file
much. What transactions are open that it does not shrink?
Please advice any better solution.
Sam
Backup the transaction log and then issue a shrink. This should correct the
problem if you are using the full recover model.
Also issue a dbcc opentran to see if there are any open transactions. Use
sp_who2 to evaluate whether the spid is orphaned and if you can kill it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"sam" <sam@.discussions.microsoft.com> wrote in message
news:8D35DB00-6FF6-4B75-AE4B-3A6E4EF8322B@.microsoft.com...
> Hi,
> I am setting up a merge replication of a large database across internet
> between two servers which are located in two geographically different
> location and can connect to each other with a fast T1 Connection.
> The purpose of it is to have a hot backup site available for the DB.
> The problem i am facing is that the DB size before setting up replication
is
> 2GB.
> When I set up merge replication and selected snapshot to create , it grow
> the DB size to 6GB. Most of the increase in Trasaction log.
> Why?
> I will apply the backup of these Snapshot generated DB to the subscriber
> manually first and then set up the pull subscription on the subscriber.
> Is this the right way to handle this situation?
> How can I reduce the size of db after activating the replication?
> I tried it with shrinkdatabase command but it does not shing the log file
> much. What transactions are open that it does not shrink?
> Please advice any better solution.
> Sam
>

Merge replication in 'SQL Server'

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.
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)

Merge Replication across the internet

Hi,
I require to replicate data 2 ways across the internet.
I think merge replication would be the best way to do this.
How secure is it across the internet ?
Is there anyway to create a file out of differences to be applied to
another database.
I am using SQL Server 2000
Srini,
yes this is possible with a few treaks.
To make it completely secure, most people use a VPN.
File-based replication is not possible.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

merge Replication across Internet

I'm want to setup merge replication ( sql 2000 ) where one sql server would
be installed with a hosting provider & other would be in our office. I'm
able to setup a test setup in my office between 2 servers which are in same
NT domain.
I have queries about how to setting up merge replication over internet. I
request all sql replication expertes to please answer my query.
1. While setting up merge replication is it a must to have point to point
connect i.e do we need to have a leased line connectivity between hosting
provider where we would be hosting sql server & our office ?
2. The server hosted in hosting provider should be made to log in to our NT
office domain or can i keep this server in standalone mode & achieve merge
replication ?
2.My office has internet connectivity via some isp. If leased line
connectivity to hosting provider is not possible then how can i setup merge
replication on internet ? At my server hosted in hosting provider do i need
to setup vpn ( l2tp ) connectivity to my server in office ? Please note that
in my test setup in office both servers were in same NT domain & connected in
a LAN.Idea of vpn connectivity is to extend my office network over internet
to the server hosted in hosting provider. is my direction of thinking right ?
Please share your experiences if some one in this forumn has implmented a
similar setup.
tanks.....
1) You need some form of connectivity - whether it is across the internet or
through a dedicated lease line doesn't matter. You might want to use a VPN
for security requirements.
2) You don't need to create a trust relationship between your domain and the
hosting company server/domain. You would use SQL authentication and FTP for
this. Consult this kb for more information.
http://support.microsoft.com/default...b;en-us;321822
2) As long as you can connect to both servers using Enterprise Manager you
will be ok. Note that you will probably want to register both servers using
the NetBIOS names (what is returned via select @.@.servername and which should
match xp_cmdshell 'hostname'). Use Client Network Utility to build an alias
which will match the Fully Qualified DomainName of the SQL Server i.e.
(SQLServer1.ResourceDomain.Microsoft.com).
Using a VPN is not essential. Microsoft does recommend it for security
reasons. If you are concerned about security (i.e. are a bank or have
sensitive data) use a VPN, if you are not concerned you don't have to use a
VPN. Using a VPN slows down communication, because of this many companies
choose not to use it.
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
"PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
news:470A0759-D7D8-4275-BDB1-58F4B4BCC3B2@.microsoft.com...
> I'm want to setup merge replication ( sql 2000 ) where one sql server
would
> be installed with a hosting provider & other would be in our office. I'm
> able to setup a test setup in my office between 2 servers which are in
same
> NT domain.
> I have queries about how to setting up merge replication over internet. I
> request all sql replication expertes to please answer my query.
> 1. While setting up merge replication is it a must to have point to point
> connect i.e do we need to have a leased line connectivity between hosting

> provider where we would be hosting sql server & our office ?
> 2. The server hosted in hosting provider should be made to log in to our
NT
> office domain or can i keep this server in standalone mode & achieve
merge
> replication ?
> 2.My office has internet connectivity via some isp. If leased line
> connectivity to hosting provider is not possible then how can i setup
merge
> replication on internet ? At my server hosted in hosting provider do i
need
> to setup vpn ( l2tp ) connectivity to my server in office ? Please note
that
> in my test setup in office both servers were in same NT domain & connected
in
> a LAN.Idea of vpn connectivity is to extend my office network over
internet
> to the server hosted in hosting provider. is my direction of thinking
right ?
> Please share your experiences if some one in this forumn has implmented a
> similar setup.
> tanks.....
>
|||Many thanks for reply.I suppose my office network has firewall & suppose
hosting provider also has firewall, then for merge replication which ports
would be involved?Based on your inputs will talk to my network team in office
regarding the same.
"Hilary Cotter" wrote:

> 1) You need some form of connectivity - whether it is across the internet or
> through a dedicated lease line doesn't matter. You might want to use a VPN
> for security requirements.
> 2) You don't need to create a trust relationship between your domain and the
> hosting company server/domain. You would use SQL authentication and FTP for
> this. Consult this kb for more information.
> http://support.microsoft.com/default...b;en-us;321822
> 2) As long as you can connect to both servers using Enterprise Manager you
> will be ok. Note that you will probably want to register both servers using
> the NetBIOS names (what is returned via select @.@.servername and which should
> match xp_cmdshell 'hostname'). Use Client Network Utility to build an alias
> which will match the Fully Qualified DomainName of the SQL Server i.e.
> (SQLServer1.ResourceDomain.Microsoft.com).
> Using a VPN is not essential. Microsoft does recommend it for security
> reasons. If you are concerned about security (i.e. are a bank or have
> sensitive data) use a VPN, if you are not concerned you don't have to use a
> VPN. Using a VPN slows down communication, because of this many companies
> choose not to use it.
> --
> 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
> "PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
> news:470A0759-D7D8-4275-BDB1-58F4B4BCC3B2@.microsoft.com...
> would
> same
> NT
> merge
> merge
> need
> that
> in
> internet
> right ?
>
>
|||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
"PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
news:1A422957-611D-45FC-8D3E-A734340ECC27@.microsoft.com...
> Many thanks for reply.I suppose my office network has firewall & suppose
> hosting provider also has firewall, then for merge replication which ports
> would be involved?Based on your inputs will talk to my network team in
office[vbcol=seagreen]
> regarding the same.
> "Hilary Cotter" wrote:
internet or[vbcol=seagreen]
VPN[vbcol=seagreen]
the[vbcol=seagreen]
for[vbcol=seagreen]
you[vbcol=seagreen]
using[vbcol=seagreen]
should[vbcol=seagreen]
alias[vbcol=seagreen]
use a[vbcol=seagreen]
companies[vbcol=seagreen]
I'm[vbcol=seagreen]
internet. I[vbcol=seagreen]
point[vbcol=seagreen]
hosting[vbcol=seagreen]
our[vbcol=seagreen]
note[vbcol=seagreen]
connected[vbcol=seagreen]
implmented a[vbcol=seagreen]
|||ports 1433 and 21 (for ftp).
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
"PELEAANIL" <PELEAANIL@.discussions.microsoft.com> wrote in message
news:1A422957-611D-45FC-8D3E-A734340ECC27@.microsoft.com...
> Many thanks for reply.I suppose my office network has firewall & suppose
> hosting provider also has firewall, then for merge replication which ports
> would be involved?Based on your inputs will talk to my network team in
office[vbcol=seagreen]
> regarding the same.
> "Hilary Cotter" wrote:
internet or[vbcol=seagreen]
VPN[vbcol=seagreen]
the[vbcol=seagreen]
for[vbcol=seagreen]
you[vbcol=seagreen]
using[vbcol=seagreen]
should[vbcol=seagreen]
alias[vbcol=seagreen]
use a[vbcol=seagreen]
companies[vbcol=seagreen]
I'm[vbcol=seagreen]
internet. I[vbcol=seagreen]
point[vbcol=seagreen]
hosting[vbcol=seagreen]
our[vbcol=seagreen]
note[vbcol=seagreen]
connected[vbcol=seagreen]
implmented a[vbcol=seagreen]