Replication does not start

We would like to make snapshot replicaiton. We use SQL Server 2000
Enterprise Edion with sp3 (over windows 2k advanced server).
We configured the server distributor and publisher correctly. After we
create publication and Publication properties- Run agent Now- we take the
following error message.
" Non-SysAdmins have been denied to permission to run replication jobsteps
without a proxy account.The step failed."
SQL Server and SQL Server agent services are running a domain administrator
account.
Both services use the same startup account and sql server agent's account
has sysadmin role.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4b4701c490c5$e6de1e50$a301280a@.phx.gbl...
> Can you make the sql server service and the sql server
> agent both use the same startup account. Alternatively
> you could add the sql server agent's service login
> account to the sysadmin role on sql server.
> HTH,
> Paul Ibison
|||Right-click the replication moniotor and select
distributor properties. On the publisher's tab click the
relevant elipsis and check that the agent uses
impersonation to connect to the publisher, then do the
same thing on the subscriber's tab.
HTH,
Paul Ibison
|||i checked it. it same as you said. but still it isnt working.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:4bc701c490cf$0a167350$a301280a@.phx.gbl...
> Right-click the replication moniotor and select
> distributor properties. On the publisher's tab click the
> relevant elipsis and check that the agent uses
> impersonation to connect to the publisher, then do the
> same thing on the subscriber's tab.
> HTH,
> Paul Ibison
>
|||Is the SQL account you are using part of the local admin group on the
Publisher machine?
Can you run this from Query Analyzer on the Subscriber?
master.dbo.xp_cmdshell 'dir \\publisherServerName\c$'
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Digikoray" <kkayisli@.hotmail.com> wrote in message
news:eLu2cJNkEHA.644@.tk2msftngp13.phx.gbl...
> i checked it. it same as you said. but still it isnt working.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:4bc701c490cf$0a167350$a301280a@.phx.gbl...
>
|||- The SQL account is part of the domain admins group( It's already part
of the Local administrator group.)
- I don't know how I can use Query Analyzer .
- What do you mean with master.dbo.xp_cmdshell.
-
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uxntDZOkEHA.592@.TK2MSFTNGP11.phx.gbl...
> Is the SQL account you are using part of the local admin group on the
> Publisher machine?
> Can you run this from Query Analyzer on the Subscriber?
> master.dbo.xp_cmdshell 'dir \\publisherServerName\c$'
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Digikoray" <kkayisli@.hotmail.com> wrote in message
> news:eLu2cJNkEHA.644@.tk2msftngp13.phx.gbl...
>
|||go to start, run, and type isqlw - that's query analyzer
the extended stored procedure xp_cmdshell will allow you to run command
shell (dos) arguments within query analyzer.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Digikoray" <kkayisli@.hotmail.com> wrote in message
news:%237f3b%23OkEHA.712@.TK2MSFTNGP09.phx.gbl...
> - The SQL account is part of the domain admins group( It's already part
> of the Local administrator group.)
> - I don't know how I can use Query Analyzer .
> - What do you mean with master.dbo.xp_cmdshell.
> -
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uxntDZOkEHA.592@.TK2MSFTNGP11.phx.gbl...
>

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...
> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluation/features/replication.asp
Steve

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evaluat...eplication.asp
Steve
sql

Replication between servers

Hi all
My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
its head office location. We have a second office, which is connected to
head office via a 2Mb fixed link - users of the databases in that second
office are connecting directly to the server in the head office. However,
we've realised that if our head office system fails, either through natural
disaster or sabotage, our second office will have no database functionality.
With that in mind, we are considering purchasing a second database server,
and putting it into the satellite office. However, users in both offices
would have to be able to use the same database, which handles about 20,000
record entries per day. My manager has asked me to find out how we would
set it up so that users in the head office can be connected to the head
office server, and users in the second office are connected to the second
server, and the data is then being effectively and efficiently merged.
To give a further understanding of the particular database that I'm thinking
of, the users are creating records in a total of four tables. Each of the
four tables has an identity field which is the primary key. However, three
of the four tables have a foreign key which links back to the fourth table.
Therefore, there can be no possibility of users at both sites generating the
same ID numbers.
TIA
Ian Henderson"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:d078kv$o50$1$8302bc10@.news.demon.co.uk...

> My company is using Microsoft SQL Server 2000 on a Windows 2000 server, in
> its head office location. We have a second office, which is connected to
> head office via a 2Mb fixed link - users of the databases in that second
> office are connecting directly to the server in the head office. However,
> we've realised that if our head office system fails, either through
natural
> disaster or sabotage, our second office will have no database
functionality.
> With that in mind, we are considering purchasing a second database server,
> and putting it into the satellite office. However, users in both offices
> would have to be able to use the same database, which handles about 20,000
> record entries per day. My manager has asked me to find out how we would
> set it up so that users in the head office can be connected to the head
> office server, and users in the second office are connected to the second
> server, and the data is then being effectively and efficiently merged.
> To give a further understanding of the particular database that I'm
thinking
> of, the users are creating records in a total of four tables. Each of the
> four tables has an identity field which is the primary key. However,
three
> of the four tables have a foreign key which links back to the fourth
table.
> Therefore, there can be no possibility of users at both sites generating
the
> same ID numbers.
Several solutions come to mind, from simple to more complex.
1) Providing your users are satisfied with the performance over the WAN
link --
Once you've got a second SQL Server running at the office, set up log
shipping so you have a fairly close copy of the primary SQL Server. In the
event of a failure of the primary location SQL Server or office, you could
point your users to the (now) local copy and continue working.
2) You could consider setting up merge replication, where updates take place
at each local office and the updates are replicated (merged) between SQL
Servers. Your last paragraph tells me you've thought about the implications
of the primary key creation. You do want to avoid replication conflicts, and
the design phase is the place to do that. See the following link on
replication to consider the other replication alternatives:
http://www.microsoft.com/sql/evalua...replication.asp
Steve

Replication between 2 SQL from 2 Windows SBS 2003?

Replication between 2 SQL from 2 Windows SBS 2003?
I have 2 SBS servers with SQL 2000. I would like to do a Merge
replication, I did it using wizard and everywhere I choose not trusted
connection but SQL login and password:
Configure '192.168.10.3' as a Publisher. Use '192.168.10.3' as the
Distributor.
Use '\\192.168.10.3\f$\Microsoft SQL
Server\MSSQL\ReplData' as the root snapshot folder for
Publishers using this Distributor.
Store the distribution database 'distribution' in 'f:\Microsoft
SQL Server\MSSQL\Data'.
Store the distribution database log file in 'f:\Microsoft SQL
Server\MSSQL\Data'.
Enable the following databases on '192.168.10.3' for merge
replication:
SampleDataBase
Enable the following servers as Subscribers to
publications on '192.168.10.3':
10.9.0.1
But I receive:
SQL Server Enterprise Manager could not configure '192.168.10.3' as the
Distributor for '192.168.10.3'.
Error 18483: Could not connect to server â'NSSâ' because
â'distributor_adminâ' is not defined as a remote login at the server.
But:
SELECT @.@.SERVERNAME, SERVERPROPERTY('ServerName') returns
NSS NSS
So it seems properly.
Ping nss working properly..
I need help!!! Any concepts? Maybe something connected with
distributor_admin and trusted connection â' I heard somewhere that it is
possible to disable trusted connection for distributor_admin but I donâ't
know how to do it?Hello,
I understand that when you try to create a merge replication, you receive
the error message "Error 18483: Could not connect to server "NSS" because
"distributor_admin" is not defined as a remote login at the server."
However, the servername is correct and it is the same as
SERVERPROPERTY('ServerName'). If I'm off-base, please let me know.
This issue could occur when the Machine name at windows level (Hostname) is
different from SQL Server Name (Select @.@.servername).
And this happens when the machine on which SQL Server is installed is
renamed after SQL Server installation. If it is so you will have to do the
following using Query Analyzer:
Sp_dropserver ?<server>?
(Replace <server> with the name returned by Select @.@.servername query)
Sp_addserver ?<server>?, ?local?
(Replace <server> with the machine name (Hostname) if it is default
instance otherwise <server>\instancename for named instance)
Also, did you try to use servername "NSS" when configure merge replication
and not using IP adress directly? I suspect this issue may also related to
network name resolution.
More related information:
818334 Replication setup is not successful when SQL Server is deployed by
using a disk image
http://support.microsoft.com/default.aspx?scid=kb;EN-US;818334
321822 HOW TO: Replicate Between Computers Running SQL Server in
Non-Trusted Domains or Across the Internet
http://support.microsoft.com/default.aspx?scid=kb;EN-US;321822
If you have any update, please let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] napisaÅ?(a):
> Hello,
> I understand that when you try to create a merge replication, you receive
> the error message "Error 18483: Could not connect to server "NSS" because
> "distributor_admin" is not defined as a remote login at the server."
> However, the servername is correct and it is the same as
> SERVERPROPERTY('ServerName'). If I'm off-base, please let me know.
yes its true!
> This issue could occur when the Machine name at windows level (Hostname) is
> different from SQL Server Name (Select @.@.servername).
> And this happens when the machine on which SQL Server is installed is
> renamed after SQL Server installation. If it is so you will have to do the
> following using Query Analyzer:
Select @.@.servername
returns
NSS
and full computer name is:
nss.domain.local
it is a domain server (SBS)
so computer name is nss and never been changed!
> Sp_dropserver �<server>�
> (Replace <server> with the name returned by Select @.@.servername query)
>
> Sp_addserver �<server>�, �local�
> (Replace <server> with the machine name (Hostname) if it is default
> instance otherwise <server>\instancename for named instance)
> Also, did you try to use servername "NSS" when configure merge replication
> and not using IP adress directly? I suspect this issue may also related to
> network name resolution.
> More related information:
>
> 818334 Replication setup is not successful when SQL Server is deployed by
> using a disk image
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;818334
>
> 321822 HOW TO: Replicate Between Computers Running SQL Server in
> Non-Trusted Domains or Across the Internet
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;321822
I will try it this...

Replication between 2 SQL from 2 Windows SBS 2003?

Replication between 2 SQL from 2 Windows SBS 2003?
I have 2 SBS servers with SQL 2000. I would like to do a Merge
replication, I did it using wizard and everywhere I choose not trusted
connection but SQL login and password:
Configure '192.168.10.3' as a Publisher. Use '192.168.10.3' as the
Distributor.
Use '\\192.168.10.3\f$\Microsoft SQL
Server\MSSQL\ReplData' as the root snapshot folder for
Publishers using this Distributor.
Store the distribution database 'distribution' in 'f:\Microsoft
SQL Server\MSSQL\Data'.
Store the distribution database log file in 'f:\Microsoft SQL
Server\MSSQL\Data'.
Enable the following databases on '192.168.10.3' for merge
replication:
SampleDataBase
Enable the following servers as Subscribers to
publications on '192.168.10.3':
10.9.0.1
But I receive:
SQL Server Enterprise Manager could not configure '192.168.10.3' as the
Distributor for '192.168.10.3'.
Error 18483: Could not connect to server ‘NSS’ because
‘distributor_admin’ is not defined as a remote login at the server.
But:
SELECT @.@.SERVERNAME, SERVERPROPERTY('ServerName') returns
NSS NSS
So it seems properly.
Ping nss working properly..
I need help!!! Any concepts? Maybe something connected with
distributor_admin and trusted connection – I heard somewhere that it is
possible to disable trusted connection for distributor_admin but I don’t
know how to do it?Hello,
I understand that when you try to create a merge replication, you receive
the error message "Error 18483: Could not connect to server "NSS" because
"distributor_admin" is not defined as a remote login at the server."
However, the servername is correct and it is the same as
SERVERPROPERTY('ServerName'). If I'm off-base, please let me know.
This issue could occur when the Machine name at windows level (Hostname) is
different from SQL Server Name (Select @.@.servername).
And this happens when the machine on which SQL Server is installed is
renamed after SQL Server installation. If it is so you will have to do the
following using Query Analyzer:
Sp_dropserver <server>
(Replace <server> with the name returned by Select @.@.servername query)
Sp_addserver <server>, local
(Replace <server> with the machine name (Hostname) if it is default
instance otherwise <server>\instancename for named instance)
Also, did you try to use servername "NSS" when configure merge replication
and not using IP adress directly? I suspect this issue may also related to
network name resolution.
More related information:
818334 Replication setup is not successful when SQL Server is deployed by
using a disk image
http://support.microsoft.com/defaul...kb;EN-US;818334
321822 HOW TO: Replicate Between Computers Running SQL Server in
Non-Trusted Domains or Across the Internet
http://support.microsoft.com/defaul...kb;EN-US;321822
If you have any update, please let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] napisa?(a):
> Hello,
> I understand that when you try to create a merge replication, you receive
> the error message "Error 18483: Could not connect to server "NSS" because
> "distributor_admin" is not defined as a remote login at the server."
> However, the servername is correct and it is the same as
> SERVERPROPERTY('ServerName'). If I'm off-base, please let me know.
yes its true!

> This issue could occur when the Machine name at windows level (Hostname) i
s
> different from SQL Server Name (Select @.@.servername).
> And this happens when the machine on which SQL Server is installed is
> renamed after SQL Server installation. If it is so you will have to do the
> following using Query Analyzer:
Select @.@.servername
returns
NSS
and full computer name is:
nss.domain.local
it is a domain server (SBS)
so computer name is nss and never been changed!

> Sp_dropserver ?<server>?
> (Replace <server> with the name returned by Select @.@.servername query)
>
> Sp_addserver ?<server>?, ?local?
> (Replace <server> with the machine name (Hostname) if it is default
> instance otherwise <server>\instancename for named instance)
> Also, did you try to use servername "NSS" when configure merge replication
> and not using IP adress directly? I suspect this issue may also related to
> network name resolution.
> More related information:
>
> 818334 Replication setup is not successful when SQL Server is deployed by
> using a disk image
> http://support.microsoft.com/defaul...kb;EN-US;818334
>
> 321822 HOW TO: Replicate Between Computers Running SQL Server in
> Non-Trusted Domains or Across the Internet
> http://support.microsoft.com/defaul...kb;EN-US;321822
I will try it this...

Replication based on subscribers informations

Hello all,
I want to use replication between one main (publisher/distributor) SQL server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over the internet.
1)
There is some tables on the main SQL server that need to be propagated to the other servers based on (Insert/Update/Delete) transactions. I called that kind static tables.
Example: there is a table called 'Events' wich have to propagate its data to ALL the subscribers.
2)
And there is another tables on the main SQL server that have data that must be propagated to clients (subscribers) based on their informations. I called that kind dynamic tables.
Example: there is table called 'Operations' wich have a column called 'Entreprise_ID', that have data about all operations concerning some entreprises. Those real entreprises have also SQL servers (wich shall be used as subscribers) to get data from the m
ain server, but Entreprise X must receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
I'm new into replication, i've searched over the web and understood it a little bit, so i thought that in my case i should use Transactional Replication if we suppose that data is a bit critical and must be refreshed on all the servers.
i configured the main SQL server with a subsriber to test transactional replication over the internet concerning what i called 'static tables' , it works very well.
But for applying filters on the 'dynamic tables' i don't know how to do it, i've seen that i can apply 'vertical filters' on those tables, but i don't know how to associate it with subscribers informations in order to send only appropriate data to each su
bscriber.
Thanx in advance for any comment.
a vertical filter is where you replicate a subset of the columns, a
horizontal filter is a where you replicate a subset of the rows in the
table.
I take it you want to do horizontal filtering or partitioning. To do this
right click on your publication and select publication properties, click on
the filter rows tab. locate the tables you wish to filter, and click on the
browse button to the right of the table/article. You will get the specify
filter dialog. Enter your filter there.
After your have made your changes, regenerate your snapshot, and distribute
it to your subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> Hello all,
> I want to use replication between one main (publisher/distributor) SQL
server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
the internet.
> 1)
> There is some tables on the main SQL server that need to be propagated to
the other servers based on (Insert/Update/Delete) transactions. I called
that kind static tables.
> Example: there is a table called 'Events' wich have to propagate its data
to ALL the subscribers.
> 2)
> And there is another tables on the main SQL server that have data that
must be propagated to clients (subscribers) based on their informations. I
called that kind dynamic tables.
> Example: there is table called 'Operations' wich have a column called
'Entreprise_ID', that have data about all operations concerning some
entreprises. Those real entreprises have also SQL servers (wich shall be
used as subscribers) to get data from the main server, but Entreprise X must
receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
>
> I'm new into replication, i've searched over the web and understood it a
little bit, so i thought that in my case i should use Transactional
Replication if we suppose that data is a bit critical and must be refreshed
on all the servers.
> i configured the main SQL server with a subsriber to test transactional
replication over the internet concerning what i called 'static tables' , it
works very well.
> But for applying filters on the 'dynamic tables' i don't know how to do
it, i've seen that i can apply 'vertical filters' on those tables, but i
don't know how to associate it with subscribers informations in order to
send only appropriate data to each subscriber.
> Thanx in advance for any comment.
|||Hello,
Yes Hilary, indeed, i made a mistake talking about 'vertical filtering', i must use 'horizontal filtering' but, the problem is not how to do it, by the wizard or something like that, i already know how to do it, my problem is a conception problem. How to
define an 'dynamic horizontal filter' for an article of a Transactional Replication, and this filter must propagate data dynamically based on subscribers informations.
For example:
Let's suppose that i have a table called 'Operations' that have 3 columns.
Op_ID--Date--Entreprise
1--01/05/2004--1
2--02/05/2004--2
3--15/05/2004--1
4--15/06/2004--3
5--19/06/2004--3
6--01/07/2004--2
so in this case let's suppose that we have 3 subscribers, hence 3 SQL servers each server in one Entreprise.
My need is, if a transaction happened in the table 'Operations' it have to be automatically propagated to the concerned entreprise. eg: there is one new row added '7--04/07/2004--2', this row have to be sent only to Subsriber number 2 because the o
ther subscribers don't need this data.
then, we'll have as a result, after synchronisation:
At Subscriber #1:
1--01/05/2004--1
3--15/05/2004--1
At Subscriber #2:
2--02/05/2004--2
6--01/07/2004--2
7--04/07/2004--2 (the new row)
And at Subscriber #3:
4--15/06/2004--3
5--19/06/2004--3
If anyone can tell me how to manage that problem, it will be a really Great help for me.
Sorry about my english level, it's my third language.
Thanks in advance for your comments.
"Hilary Cotter" wrote:

> a vertical filter is where you replicate a subset of the columns, a
> horizontal filter is a where you replicate a subset of the rows in the
> table.
> I take it you want to do horizontal filtering or partitioning. To do this
> right click on your publication and select publication properties, click on
> the filter rows tab. locate the tables you wish to filter, and click on the
> browse button to the right of the table/article. You will get the specify
> filter dialog. Enter your filter there.
> After your have made your changes, regenerate your snapshot, and distribute
> it to your subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
> news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
> the internet.
> the other servers based on (Insert/Update/Delete) transactions. I called
> that kind static tables.
> to ALL the subscribers.
> must be propagated to clients (subscribers) based on their informations. I
> called that kind dynamic tables.
> 'Entreprise_ID', that have data about all operations concerning some
> entreprises. Those real entreprises have also SQL servers (wich shall be
> used as subscribers) to get data from the main server, but Entreprise X must
> receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
> little bit, so i thought that in my case i should use Transactional
> Replication if we suppose that data is a bit critical and must be refreshed
> on all the servers.
> replication over the internet concerning what i called 'static tables' , it
> works very well.
> it, i've seen that i can apply 'vertical filters' on those tables, but i
> don't know how to associate it with subscribers informations in order to
> send only appropriate data to each subscriber.
>
>

Replication and Windows login

Does the administrator windows login name and password of all servers
participate in replication must be the same?, or may I use the SQL
authentication?IIRC, Replication using windows authentication requires a trusted connection
using the same account on both end. BOL also has this:
Connections to a server (Publisher, Distributor, or Subscribers) can use
Windows Authentication or SQL Server security. Windows Authentication is
generally preferred for greater security and ease of use; however,
connections to Windows 98 servers must use SQL Server security because
Windows Authentication is a feature only on Windows NT 4.0 and Windows 2000.
It is recommended that the Subscriber connection have dbo permissions in the
subscription database to make sure the proper permissions are granted, and
for overall simplification; however, dbo permissions are not
If you are connecting to an untrusted domain you may wish to use SQL
authentication although this will weaken security.
Neil MacMurchy
"Tal" <talg@.nice.com> wrote in message
news:OjhYTt2$DHA.2348@.TK2MSFTNGP09.phx.gbl...
> Does the administrator windows login name and password of all servers
> participate in replication must be the same?, or may I use the SQL
> authentication?
>

Replication and Windows login

Does the administrator windows login name and password of all servers
participate in replication must be the same?, or may I use the SQL
authentication?IIRC, Replication using windows authentication requires a trusted connection
using the same account on both end. BOL also has this:
Connections to a server (Publisher, Distributor, or Subscribers) can use
Windows Authentication or SQL Server security. Windows Authentication is
generally preferred for greater security and ease of use; however,
connections to Windows 98 servers must use SQL Server security because
Windows Authentication is a feature only on Windows NT 4.0 and Windows 2000.
It is recommended that the Subscriber connection have dbo permissions in the
subscription database to make sure the proper permissions are granted, and
for overall simplification; however, dbo permissions are not
If you are connecting to an untrusted domain you may wish to use SQL
authentication although this will weaken security.
Neil MacMurchy
"Tal" <talg@.nice.com> wrote in message
news:OjhYTt2$DHA.2348@.TK2MSFTNGP09.phx.gbl...
> Does the administrator windows login name and password of all servers
> participate in replication must be the same?, or may I use the SQL
> authentication?
>sql

Replication and Full Text Indexes.

SQL 2000 SP 3a running in an active passive cluster on Windows 2003.
This is the publisher and distributor of several publication in
transaction replication. Subscriptions are pulled anonymously. I plan
on setting up Full Text indexes/search on the cluster, on several of
the replicated tables, and was wondering if anyone has had any
experiences with this or can find any reason it would not work. Since
it's SQL 2000 and timestamp values can be replicated I can't see that
this would be an issue (as with SQL 7) as I will be using Change
Tracking to keep the indexes up to date.
I'm not necessarily concerned about replicating the full text indexes,
although it may be of use later. My major concern is can I replicate
the tables that have full text indexes with out issue? Also, since the
tables are already published can I just add the full text indexes
without worry?
Charlie
Hi Charlie,
Sorry for the late response.
[vbcol=seagreen]
Yes, you could make the replication for a Replicated database/table
successfully.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Replication Advice Needed

Environment:
SQL 2000 SP4 on Windows 2003
Replication Type = Transactional Replication
Servers involved = server 1, server 2
Activity = server 1 has lots of activity for inserts,updates but no deletes
for 7:30 AM - 6 PM NY TIME then it becomes stable and becomes active again
from 9 PM - 4 AM NY TIME for offshore processing.
Situation:
I need to set up an environment to support minimum latency as low as few
seconds or so. I need to make sure that two systems stay in sync with Data
and schema though there are very less chances of making schema changes. I
thought of considering replication but there are few concerns.
1. Data can be replicated very quickly but then schema changes are a big
concern as replication blocks most of the schema changes.
2. When a new table is added on server A, I want it to be added to a server
B as well which can not happen without adding this table to a new or existing
publication and run snapshot\distribution agent etc..
3. On existing stored procedure, if I replicate the schema and executions,
do the subsequent alter procedure commands also get replicated? OR if the
proc is altered on Server 1, I need to manually run ALTER Table on server 2.
Then I thought of using Log Shipping and ran into following concerns.
1. If I set up transaction log backup very frequently on server 1 say every
minute, performance becomes a nightmare which is not acceptable at all in
this situation.
One more question on replication:
If I take the publisher server backup and restore on subscriber, how would
it affect replication? Would the replication be still funtional after restore
is done on subscriber?
Please advice if replication is a right approach in this scenario and if I
do implement replication, what additional steps would I need to take should
new schema changes appear. If Replication is not a good approach and Log
Shipping is a performance bottleneck, what other directions can I look at,.
Thanks for reading my request and I appreciate your suggestions.
Thanks in advance.
Nitin
Nitin,
to clarify a bit, I'd like to know what the second server is to be used
for - is it a HA box, or is it to be used for reporting?
Also, how definite are you on the idea of a few seconds latency?
My first reflex is database mirroring, with database snapshots if you
require a reporting solution, but let's find out more first.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
Thanks fror your response. Second server is used only in read only mode.
There wont be new transactions other than what are happening on Server 1.
Also, Database mirroring is not supported on SQL 2005 yet and is turned off.
I dont know if we can mirror DB in SQL 2000. Few seconds latecy is ok. I have
spoken to people and they have said latency shouldnt go in minutes, seconds
are OK as nothing can be instantaneous in real world.
What are your thoughts on it.
-Nitin
"Paul Ibison" wrote:

> Nitin,
> to clarify a bit, I'd like to know what the second server is to be used
> for - is it a HA box, or is it to be used for reporting?
> Also, how definite are you on the idea of a few seconds latency?
> My first reflex is database mirroring, with database snapshots if you
> require a reporting solution, but let's find out more first.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Nitin,
database mirroring is not yet supported, but it is available. Some companies
are using it but admittedly it is a little worrying in a production
environment if it is not yet supported. Anyway, log shipping would have too
long a latency and mirroring doesn't exist in SQL Server 2000 so
transactional replication would be the obvious choice. Whether you'll
achieve the latency you require is difficult to say - I guess you'll need to
do a POC.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hi Paul:
Since this is production environment, I really cant use DB Mirroring since
it is not supported if somethig goes wrong tomorrow and I cant get a support
on it, I will be in trouble. What do you recommend for moving the schema
changes to server 2 when changes were made on server 1 without replication
being stopped. For example: a dba can create index on an article without
affecting replication but now two servers are out of sync with schema. Do you
think there is a way to script out the schema differences and some automated
job can run those schema changes on server 2.
Nitin
"Paul Ibison" wrote:

> Nitin,
> database mirroring is not yet supported, but it is available. Some companies
> are using it but admittedly it is a little worrying in a production
> environment if it is not yet supported. Anyway, log shipping would have too
> long a latency and mirroring doesn't exist in SQL Server 2000 so
> transactional replication would be the obvious choice. Whether you'll
> achieve the latency you require is difficult to say - I guess you'll need to
> do a POC.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Nitin,
as you're on SQL 2000, the process is more manual but you can use
sp_repladdcolumn and sp_repldropcolumn for most things (including many
schema changes http://www.replicationanswers.com/AddColumn.asp). As I say,
this is manual, and you'll need to ensure this is done on the publications.
Index changes aren't propagated in SQL 2005 either so I tend to do these
manually although if I had a lot of subscribers or disconnected pull
subscribers I'd use sp_addscriptexec. Keeping the databases in sync will
require a strict way of working, and sometimes you might have to
reinitialize a table (yes, it's possible in transactional replication using
SQL 2000).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication : SQL Server 2000 X MySQL 5.0.41 (Windows)

Good day!

I am carrying through a replication of the transacional and heterogeneous type between the SQL Server 2000 and MySQL 5.0.41, through driver of ODBC of the MySQL (version 3.51.15).

Unhappyly I am not having success!

I make all the necessary configurations, but in the hour to create schema in the MySQL, only the table msrepl7 is created (but nondata) and occurs the following error: “Date too long will be column “timecol” at row 1” and later “Invalid cursor state”.

The first error must to the size of the column timecol that it is created as char (16), but the value that goes to be inserted is to char (19) - > ({ts'2007-05-01 20:45: 21 '}) The table that is published has only two entire fields and the same one is not created in the MySQL, exactly with the option to initiate schema. I looked for in some sites and I found one that said to bind query Log of the MySQL and to catch the command of insert for the table msrelp7 and to insert in the hand, after making this to come back to the SQL Server and to ask for to synchronize again. Did not function, now only with the error of “Invalid cursor state”! To complete one another person in one another site it placed that was to exclude the table msrepl7 that everything would function, did not function!

Thanks!!

Cristiano Rodrigues

you'll have to search this forum and/or the web for help with MySQL subscribers, Microsoft currently provides supports for SQL Server, Oracle and DB2 subscribers.

Replication & Patch Installation

I have transactional replication running continously from one
publisher/distributor server to two subscriber servers. (Windows 2000
servers, fully patched and fully SP'd.)
I have experienced a problem with one of the subscriptions when taking the
publisher server off-line for patch installation, the subscriber reports a
failure to connect and then stops working.
I was wondering whether anybody has any recommendations or can point me in
the direction of best practices for taking the various parts of the
replication topology offline for maintenance
I take it you have a pull subscription?
The correct order is update the distributor, publisher and then the
subscriber. If your distributor/publisher is on the same machine, update the
publisher/distributor and then the subscriber.
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
"Nick Corlett" <Nick Corlett@.discussions.microsoft.com> wrote in message
news:09285AEC-E9B2-4CA8-8586-EEBFB408E8AB@.microsoft.com...
> I have transactional replication running continously from one
> publisher/distributor server to two subscriber servers. (Windows 2000
> servers, fully patched and fully SP'd.)
> I have experienced a problem with one of the subscriptions when taking the
> publisher server off-line for patch installation, the subscriber reports
a
> failure to connect and then stops working.
> I was wondering whether anybody has any recommendations or can point me in
> the direction of best practices for taking the various parts of the
> replication topology offline for maintenance
|||Thanks,
I do have pull subscriptions.
How about just general maintenance? Should I be worried about stopping
replication before taking servers offline?
"Hilary Cotter" wrote:

> I take it you have a pull subscription?
> The correct order is update the distributor, publisher and then the
> subscriber. If your distributor/publisher is on the same machine, update the
> publisher/distributor and then the subscriber.
> --
> 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
> "Nick Corlett" <Nick Corlett@.discussions.microsoft.com> wrote in message
> news:09285AEC-E9B2-4CA8-8586-EEBFB408E8AB@.microsoft.com...
> a
>
>
|||I think I may be missing the point here. The patches from windowsupdate
should not interfere with replication. If you get a hotfix from ms, you
should follow the ordering I outlined unless they tell you otherwise. It
would probably be a good idea to ask them.
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
"Nick Corlett" <NickCorlett@.discussions.microsoft.com> wrote in message
news:85AC3795-1CCF-4FDD-8E9F-A9358F34BB75@.microsoft.com...[vbcol=seagreen]
> Thanks,
> I do have pull subscriptions.
> How about just general maintenance? Should I be worried about stopping
> replication before taking servers offline?
> "Hilary Cotter" wrote:
the[vbcol=seagreen]
the[vbcol=seagreen]
reports[vbcol=seagreen]
me in[vbcol=seagreen]