replication to new server

Hi
I have replication set up and working correctly. However we now have a new
SQL Server and want to set up replication to there(instead of the existing
server). Do we have to go through setting up the whole replication process
again or is there an easier way?
Thanks
I would start from scratch... Server names, etc are stored in the
registry... It is often better to be clean...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"jonjo" <jonjo@.discussions.microsoft.com> wrote in message
news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
> Hi
> I have replication set up and working correctly. However we now have a new
> SQL Server and want to set up replication to there(instead of the existing
> server). Do we have to go through setting up the whole replication process
> again or is there an easier way?
> Thanks
|||Ok Thanks for the advice
"Wayne Snyder" wrote:

> I would start from scratch... Server names, etc are stored in the
> registry... It is often better to be clean...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "jonjo" <jonjo@.discussions.microsoft.com> wrote in message
> news:5BB5248E-F8E2-45A7-8F04-9383C423DD16@.microsoft.com...
>
>
|||Jonjo,
when you say 'set up replication to there' do you mean add a subscription?
If so then adding the subscription is much like any other subscription, and
EM can generate the scripts of an existing subscription to be used as a
template if you like - just change the servername using find and replace and
then run the script on the subscriber. If you mean you want to set up the
second server as a publisher/distributor and have the same publications,
then it depends on the complexity. If it's a few simple publications then
I'd start from scratch. If it's more complex, and you haven't documented
changes to custom profiles, post-snapshot filepaths etc then I'd script out
and edit the script before running it on the new publisher to be sure of
having a replica, and it'll also be much faster.
HTH,
Paul Ibison (SQL Server MVP)

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!
Can you post the schema of the problem table here? Also is this
transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegr oups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>
|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilary

Replication problem, update on table with varchar primary key

hi
i have a problem in the distribution of a replicated table.
The execution sequence is:
-i create a table (the primary key is varchar)
-i publish this table
-i insert a row in the table (no problem in the replication)
-i update the row (no the primary key)
-there is a problem in the replication
The problem is the varchar primary key... you know why?!?!
Tnx!Can you post the schema of the problem table here? Also is this
transactional replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
<paolofresujazz@.libero.it> wrote in message
news:1161350093.758198.225150@.f16g2000cwb.googlegroups.com...
> hi
> i have a problem in the distribution of a replicated table.
> The execution sequence is:
> -i create a table (the primary key is varchar)
> -i publish this table
> -i insert a row in the table (no problem in the replication)
> -i update the row (no the primary key)
> -there is a problem in the replication
> The problem is the varchar primary key... you know why?!?!
> Tnx!
>|||Hilary Cotter ha scritto:

> Can you post the schema of the problem table here? Also is this
> transactional replication.
Yes is transaction replication.
If i create a simple table with 2 field:
id: varchar(50) (primary key)
name: varchar(50)
and update the name field of a row...
Tnx Hilarysql

replication over internet

Hi
I want to set up a replication between servers over the internet. I know
there are 3 ways to do this, using VPN,proxy server or TCP/IP (FTP). but I
don't know the advantages of each one. could you tell me what are the
differences between these ways in performance and security?
and could you tell me what will happen if the servers disconnect from each
other during the process of replication?
Thanks
Tajik,
connectivity is really a choice between VPN or non-trusted connectivity. The
way I see these breaking down are...
using a VPN is secure and you'll be able to use fileshares ie there's no
need to enable FTP. So, although you're operating over the internet things
are much the same as per usual.
The proxy server is an element of the connectivity to a network by acting as
a firewall and blocking traffic on non-essential ports, and possibly
allowing traffic from only assigned IP addresses.
TCP/IP & FTP is the method used if you are replicating across non-trusted
domains - ie in the absence of a VPN. You can have it operate securely by
using a certificate and encrypting the TCP/IP traffic.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, I am getting the following error when I attempt to download the
snapshot files from the Replication FTP Site:
Date11/30/2005 1:20:22 PM
LogJob History
(28053-DB-inventory_master-inventory_master_a-S-HUNLEY-inventory_master-5007D5E3-0C82-47A1-A26A-5E8F52E68F83)
Step ID1
ServerS-HUNLEY
Job
Name28053-DB-inventory_master-inventory_master_a-S-HUNLEY-inventory_master-5007D5E3-0C82-47A1-A26A-5E8F52E68F83
Step NameRun agent.
Duration00:20:24
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
-XSERVER S-HUNLEY
-XCMDLINE 0
-XCancelEventHandle 00000828
2005-11-30 18:40:37.661 Startup Delay: 4093 (msecs)
2005-11-30 18:40:41.757 Connecting to Subscriber 'S-HUNLEY'
2005-11-30 18:40:42.008 Connecting to Distributor '28053-DB'
2005-11-30 18:40:42.729 Initializing
2005-11-30 18:40:42.919 Parameter values obtained from agent profile:
-bcpbatchsize 100000
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 10000
-querytimeout 300
-skiperrors
-transactionsperhistory 100
2005-11-30 18:40:44.121 Snapshot files will be downloaded via ftp
2005-11-30 18:40:44.301 Connecting to ftp site '28053-DB'
2005-11-30 18:40:46.334 Agent message code 20033. The process could not
retrieve file
'28053-DB_inventory_master_inventory_master_articles\2005 1130120007\Purchase_Order_Line_Items_1.sch' from the FTP site '28053-DB'.
The Publisher is a SQL Server 2000 Database (SP3A) running on Windows 2000
Advanced Server and the Subscriber is a SQL Server 2005 database running on
Windows XP Pro SP2. These databases on in diferent domains. I can ping the
machines from one another and I can see the FTP folder from the subscriber as
well as on the publisher.
Any ideas?
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:

> Tajik,
> connectivity is really a choice between VPN or non-trusted connectivity. The
> way I see these breaking down are...
> using a VPN is secure and you'll be able to use fileshares ie there's no
> need to enable FTP. So, although you're operating over the internet things
> are much the same as per usual.
> The proxy server is an element of the connectivity to a network by acting as
> a firewall and blocking traffic on non-essential ports, and possibly
> allowing traffic from only assigned IP addresses.
> TCP/IP & FTP is the method used if you are replicating across non-trusted
> domains - ie in the absence of a VPN. You can have it operate securely by
> using a certificate and encrypting the TCP/IP traffic.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Scott,
perhaps it is the path you are using to the ftp folder.
When I set this up I used the path
'c:\inetpub\ftproot\trsnapshot'
as the alternative snapshot location on the publisher (snapshot location
tab) - and
\trsnapshot\ftp
as the 'client path to this folder'.
Pls can you tell us what are the corresponding settings on your publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I have the following settings in my Publisher:
Alternate Path (Default Path unchecked) -
'\\28053-DB\D$\inetpub\ftproot\replication\inventory_master \'
Ftp Client path -
'\replication\inventory_master\'
The Server that I remote into, automatically assigns the \\28053-DB\D$\
qualifier. I didn't include the ftp subdirectory that you did, it was
created by the process however.
Is this explanation concise enough? If more details are needed please let
me know and I will provide them ASAP.
Thanks in advance for any help or advice you might have.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:

> Scott,
> perhaps it is the path you are using to the ftp folder.
> When I set this up I used the path
> 'c:\inetpub\ftproot\trsnapshot'
> as the alternative snapshot location on the publisher (snapshot location
> tab) - and
> \trsnapshot\ftp
> as the 'client path to this folder'.
> Pls can you tell us what are the corresponding settings on your publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>
|||Paul, by the time I was finished writing the first post/response.. I realized
what i had done wrong. I had forgot to put the subdirectory /ftp/ in my
Client Ftp path. Once i did, I reran the process and it worked. Now, I'm
not sure how well replication of anything other than the tables will work,
going from 2000 to 2005, but at least the Data Warehouse is populating
correctly, and transactionally.
I greatly appreciate your time and look forward to discussing other items in
the future.
Respectfully,
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"Paul Ibison" wrote:

> Scott,
> perhaps it is the path you are using to the ftp folder.
> When I set this up I used the path
> 'c:\inetpub\ftproot\trsnapshot'
> as the alternative snapshot location on the publisher (snapshot location
> tab) - and
> \trsnapshot\ftp
> as the 'client path to this folder'.
> Pls can you tell us what are the corresponding settings on your publisher.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Replication Issue

Hi:
I need to detach and detach the publisher database in transactional
replication. To do that, I need to disable publication from this database
which will also remove all the publications for this database. I will script
all the publications before I disable.
After attaching the database and enabling publication, I run the script to
create the publication again. After I do this, this does not start the
replication. Snapshot agent and Logreader agent are sitting idle. The
snapshot agents only run on the weekend. Do I have to run snapshot agents
again to start replication which I want to avoid during business hours? How
about Logreader agent, is it waiting for snapshot agent to execute first?
Please let me know.
Thanks a lot
If the agents are idle it should mean replication is working. Run profiler
and then do some dummy updates like this
update TableName set col1=col1
Making sure col1 is not a pk column.
You should see stuff flow.
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
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2B465355-B58F-4457-80D0-4457D5772C9F@.microsoft.com...
> Hi:
> I need to detach and detach the publisher database in transactional
> replication. To do that, I need to disable publication from this database
> which will also remove all the publications for this database. I will
> script
> all the publications before I disable.
> After attaching the database and enabling publication, I run the script to
> create the publication again. After I do this, this does not start the
> replication. Snapshot agent and Logreader agent are sitting idle. The
> snapshot agents only run on the weekend. Do I have to run snapshot agents
> again to start replication which I want to avoid during business hours?
> How
> about Logreader agent, is it waiting for snapshot agent to execute first?
> Please let me know.
> Thanks a lot
>
|||No. Replication is not working at this point becuase if you look at
subcription under publication folder it shows pending status. If I run
snapshot agent, it will then show active status and any transaction will
start applying. Is there anyway to activate the subscription without running
snapshot agents because I can't really do this during business hours?
"Hilary Cotter" wrote:

> If the agents are idle it should mean replication is working. Run profiler
> and then do some dummy updates like this
> update TableName set col1=col1
> Making sure col1 is not a pk column.
> You should see stuff flow.
> --
> 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
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2B465355-B58F-4457-80D0-4457D5772C9F@.microsoft.com...
>
>