I am currently trying to setup Transformation on a Transactional Publication.
When When I finish setting up all the transformation for the package and
click finish to save the DTS package I get the Message "SQL Server Enterprise
manager could not complete this operation." this message comes up when it is
in the "Adding task 1 of 1" phase in the Creating the DTS package for
replication wizard.
Any help would be greatly appriciated.
Thanks in advance.
Jeff Stokes
Hi Jeff:
Right click on Server name and click Properties. In the properties window,
click on Connections tab and check if "Enforce Distributed Transactions"
Checkbox is checked. If so, uncheck it and then recreate your publication.
HTH
"Jeff Stokes" wrote:
> I am currently trying to setup Transformation on a Transactional Publication.
> When When I finish setting up all the transformation for the package and
> click finish to save the DTS package I get the Message "SQL Server Enterprise
> manager could not complete this operation." this message comes up when it is
> in the "Adding task 1 of 1" phase in the Creating the DTS package for
> replication wizard.
> Any help would be greatly appriciated.
> Thanks in advance.
> Jeff Stokes
|||Mark,
Thanks for your response. I checked the server properties and "Enforce
Distributed Transactions" is unchecked. Any otherthoughts?
Jeff
Jeff Stokes
"Mark" wrote:
[vbcol=seagreen]
> Hi Jeff:
> Right click on Server name and click Properties. In the properties window,
> click on Connections tab and check if "Enforce Distributed Transactions"
> Checkbox is checked. If so, uncheck it and then recreate your publication.
> HTH
>
> "Jeff Stokes" wrote:
sql
Showing posts with label setup. Show all posts
Showing posts with label setup. Show all posts
Friday, March 30, 2012
replication to sybase 12.5
Where can I find a good document detailing how to setup replication from ms
sql 2005 to Sybase?
Great...
I need a live feed of inserts updates and deletes from a MS SQL 2005 table
to a Sybase table, do you have a general idea how I can accomplish this with
SSIS?
Thx!
"Paul Ibison" wrote:
> As far as I know, only Oracle and DB2 are supported
> (http://technet.microsoft.com/en-us/library/ms151864(SQL.90).aspx). Could
> use SSIS and factor your own solution instead.
> HTH,
> Paul Ibison
>
>
|||I would not use ssis for this. You can create a subscription in SQL Server,
and then use sp_browsereplcmds to get the stored procedure calls and then
run them on the sybase server. You can pipe the results of sp_browsereplcmds
to a results table and select the tranaction IDs you want to stop and start
at.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...[vbcol=seagreen]
> Great...
> I need a live feed of inserts updates and deletes from a MS SQL 2005 table
> to a Sybase table, do you have a general idea how I can accomplish this
> with
> SSIS?
> Thx!
> "Paul Ibison" wrote:
|||So this will give allow me to perform transactional replication to a Sybase
server table?
"Hilary Cotter" wrote:
> I would not use ssis for this. You can create a subscription in SQL Server,
> and then use sp_browsereplcmds to get the stored procedure calls and then
> run them on the sybase server. You can pipe the results of sp_browsereplcmds
> to a results table and select the tranaction IDs you want to stop and start
> at.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...
>
>
|||Sort of. If you capture these statements they can be run on a sybase server.
It is possible to programmatically do this.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...[vbcol=seagreen]
> So this will give allow me to perform transactional replication to a
> Sybase
> server table?
>
> "Hilary Cotter" wrote:
|||Can you provide a bit more detail of how I could accomplish this
programmatically.
"Hilary Cotter" wrote:
> Sort of. If you capture these statements they can be run on a sybase server.
> It is possible to programmatically do this.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...
>
>
|||here is an example:
--create the table status
--create table status(pk int not null identity primary key, minxact_seqno
varbinary(16), maxxact_seqno varbinary(16))
declare @.maxxact_seqno varbinary(16)
declare @.minxact_seqno varbinary(16)
select @.minxact_seqno =maxxact_seqno from status
if @.@.rowcount=0
select @.minxact_seqno =xact_seqno From msrepl_transactions order by
entry_time
select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
entry_time desc
print @.minxact_seqno
print @.maxxact_seqno
exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
='MyComamnds'
insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
@.minxact_seqno )
go
--now what you do is read through the table MyCommands and apply these
commands on Sybase, you can use ado or a linked server for this
--when you have read everything drop the table MyCommands and repeat this
job.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...[vbcol=seagreen]
> Can you provide a bit more detail of how I could accomplish this
> programmatically.
> "Hilary Cotter" wrote:
|||I'm back...with more questions
1st
I'm thinking that:
@.minxact_seqno ,@.maxxact_seqno
should read:
@.xact_seqno_start, @.xact_seqno_end
yes/no?
2nd
so it won't really be transactional replication, it'll be a scheduled job?
3rd
how will the distribution database get cleared of it's transactions, once
they've been applied to the sybase server. what do i use as a subscriber?
4th
I'm familiar with setting up linked servers,
what's the best way to read through the table and apply the transactions?
BTW, Thx a mill for your time and help!
"Hilary Cotter" wrote:
> here is an example:
> --create the table status
> --create table status(pk int not null identity primary key, minxact_seqno
> varbinary(16), maxxact_seqno varbinary(16))
> declare @.maxxact_seqno varbinary(16)
> declare @.minxact_seqno varbinary(16)
> select @.minxact_seqno =maxxact_seqno from status
> if @.@.rowcount=0
> select @.minxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time
> select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time desc
> print @.minxact_seqno
> print @.maxxact_seqno
> exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
> ='MyComamnds'
> insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
> @.minxact_seqno )
> go
> --now what you do is read through the table MyCommands and apply these
> commands on Sybase, you can use ado or a linked server for this
> --when you have read everything drop the table MyCommands and repeat this
> job.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...
>
>
|||Answers inline.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> I'm back...with more questions
> 1st
> I'm thinking that:
> @.minxact_seqno ,@.maxxact_seqno
> should read:
> @.xact_seqno_start, @.xact_seqno_end
> yes/no?
>
OK, but the naming convention I use implies the maxiumn one last collected
and the current maxiumn. Its all a naming convention - whatever works for
you
> 2nd
> so it won't really be transactional replication, it'll be a scheduled job?
It will be transactional replication to a dummy subscriber. You will need to
create some scheduled jobs to read the commands and then replay them on
Sybase.
> 3rd
> how will the distribution database get cleared of it's transactions, once
> they've been applied to the sybase server. what do i use as a subscriber?
Create a dummy database - a plain vanilla one on the publisher and
replicated there. Delete rows from it on a schedule to keep it small and
then set your min retention to 6 hours or so, a time interval which will
allow you to collect all the commands. The distribution clean up job will
remove them from the distribution database. But it doesn't know if they have
been applied on Sybase yet, so you will have to make sure you collect them
before they are deleted.
> 4th
> I'm familiar with setting up linked servers,
> what's the best way to read through the table and apply the transactions?
You will have to implement some sort of tracking column. Perhaps an tinyint
or int col with a default of 0, as you apply the command delete it from the
table. You can also create tables for each day or hour and then read them
until all the commands are applied and then drop them.
> BTW, Thx a mill for your time and help!
Your welcome[vbcol=seagreen]
> "Hilary Cotter" wrote:
|||Thanks for the comments, the picture is much clearer now.
I've made some progress.
1. I've installed the Sybase drivers
2. I've setup the oledb connection and successfully retrieved data from the
Sybase tables using the linked server.
I've now hit a snag and need your expertise.
The parameters for sp_browsereplcmds are no longer the same.
The @.results_table is no longer a parameter.
What's the alternative in 2005 to accomplish the same thing?
"Hilary Cotter" wrote:
> Answers inline.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> OK, but the naming convention I use implies the maxiumn one last collected
> and the current maxiumn. Its all a naming convention - whatever works for
> you
> It will be transactional replication to a dummy subscriber. You will need to
> create some scheduled jobs to read the commands and then replay them on
> Sybase.
> Create a dummy database - a plain vanilla one on the publisher and
> replicated there. Delete rows from it on a schedule to keep it small and
> then set your min retention to 6 hours or so, a time interval which will
> allow you to collect all the commands. The distribution clean up job will
> remove them from the distribution database. But it doesn't know if they have
> been applied on Sybase yet, so you will have to make sure you collect them
> before they are deleted.
> You will have to implement some sort of tracking column. Perhaps an tinyint
> or int col with a default of 0, as you apply the command delete it from the
> table. You can also create tables for each day or hour and then read them
> until all the commands are applied and then drop them.
> Your welcome
>
>
sql 2005 to Sybase?
Great...
I need a live feed of inserts updates and deletes from a MS SQL 2005 table
to a Sybase table, do you have a general idea how I can accomplish this with
SSIS?
Thx!
"Paul Ibison" wrote:
> As far as I know, only Oracle and DB2 are supported
> (http://technet.microsoft.com/en-us/library/ms151864(SQL.90).aspx). Could
> use SSIS and factor your own solution instead.
> HTH,
> Paul Ibison
>
>
|||I would not use ssis for this. You can create a subscription in SQL Server,
and then use sp_browsereplcmds to get the stored procedure calls and then
run them on the sybase server. You can pipe the results of sp_browsereplcmds
to a results table and select the tranaction IDs you want to stop and start
at.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...[vbcol=seagreen]
> Great...
> I need a live feed of inserts updates and deletes from a MS SQL 2005 table
> to a Sybase table, do you have a general idea how I can accomplish this
> with
> SSIS?
> Thx!
> "Paul Ibison" wrote:
|||So this will give allow me to perform transactional replication to a Sybase
server table?
"Hilary Cotter" wrote:
> I would not use ssis for this. You can create a subscription in SQL Server,
> and then use sp_browsereplcmds to get the stored procedure calls and then
> run them on the sybase server. You can pipe the results of sp_browsereplcmds
> to a results table and select the tranaction IDs you want to stop and start
> at.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:44200793-FAA0-4F75-93F5-AD3671FBFD13@.microsoft.com...
>
>
|||Sort of. If you capture these statements they can be run on a sybase server.
It is possible to programmatically do this.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...[vbcol=seagreen]
> So this will give allow me to perform transactional replication to a
> Sybase
> server table?
>
> "Hilary Cotter" wrote:
|||Can you provide a bit more detail of how I could accomplish this
programmatically.
"Hilary Cotter" wrote:
> Sort of. If you capture these statements they can be run on a sybase server.
> It is possible to programmatically do this.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:B54DE9D7-9E2A-48D1-A843-492AF75A40C9@.microsoft.com...
>
>
|||here is an example:
--create the table status
--create table status(pk int not null identity primary key, minxact_seqno
varbinary(16), maxxact_seqno varbinary(16))
declare @.maxxact_seqno varbinary(16)
declare @.minxact_seqno varbinary(16)
select @.minxact_seqno =maxxact_seqno from status
if @.@.rowcount=0
select @.minxact_seqno =xact_seqno From msrepl_transactions order by
entry_time
select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
entry_time desc
print @.minxact_seqno
print @.maxxact_seqno
exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
='MyComamnds'
insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
@.minxact_seqno )
go
--now what you do is read through the table MyCommands and apply these
commands on Sybase, you can use ado or a linked server for this
--when you have read everything drop the table MyCommands and repeat this
job.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...[vbcol=seagreen]
> Can you provide a bit more detail of how I could accomplish this
> programmatically.
> "Hilary Cotter" wrote:
|||I'm back...with more questions
1st
I'm thinking that:
@.minxact_seqno ,@.maxxact_seqno
should read:
@.xact_seqno_start, @.xact_seqno_end
yes/no?
2nd
so it won't really be transactional replication, it'll be a scheduled job?
3rd
how will the distribution database get cleared of it's transactions, once
they've been applied to the sybase server. what do i use as a subscriber?
4th
I'm familiar with setting up linked servers,
what's the best way to read through the table and apply the transactions?
BTW, Thx a mill for your time and help!
"Hilary Cotter" wrote:
> here is an example:
> --create the table status
> --create table status(pk int not null identity primary key, minxact_seqno
> varbinary(16), maxxact_seqno varbinary(16))
> declare @.maxxact_seqno varbinary(16)
> declare @.minxact_seqno varbinary(16)
> select @.minxact_seqno =maxxact_seqno from status
> if @.@.rowcount=0
> select @.minxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time
> select @.maxxact_seqno =xact_seqno From msrepl_transactions order by
> entry_time desc
> print @.minxact_seqno
> print @.maxxact_seqno
> exec sp_browsereplcmds @.minxact_seqno ,@.maxxact_seqno , @.results_table
> ='MyComamnds'
> insert into status (maxxact_seqno, minxact_seqno ) values(@.maxxact_seqno,
> @.minxact_seqno )
> go
> --now what you do is read through the table MyCommands and apply these
> commands on Sybase, you can use ado or a linked server for this
> --when you have read everything drop the table MyCommands and repeat this
> job.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:1E733A3A-B91F-492D-920E-52D414A89E80@.microsoft.com...
>
>
|||Answers inline.
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
"gracie" <gracie@.discussions.microsoft.com> wrote in message
news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> I'm back...with more questions
> 1st
> I'm thinking that:
> @.minxact_seqno ,@.maxxact_seqno
> should read:
> @.xact_seqno_start, @.xact_seqno_end
> yes/no?
>
OK, but the naming convention I use implies the maxiumn one last collected
and the current maxiumn. Its all a naming convention - whatever works for
you
> 2nd
> so it won't really be transactional replication, it'll be a scheduled job?
It will be transactional replication to a dummy subscriber. You will need to
create some scheduled jobs to read the commands and then replay them on
Sybase.
> 3rd
> how will the distribution database get cleared of it's transactions, once
> they've been applied to the sybase server. what do i use as a subscriber?
Create a dummy database - a plain vanilla one on the publisher and
replicated there. Delete rows from it on a schedule to keep it small and
then set your min retention to 6 hours or so, a time interval which will
allow you to collect all the commands. The distribution clean up job will
remove them from the distribution database. But it doesn't know if they have
been applied on Sybase yet, so you will have to make sure you collect them
before they are deleted.
> 4th
> I'm familiar with setting up linked servers,
> what's the best way to read through the table and apply the transactions?
You will have to implement some sort of tracking column. Perhaps an tinyint
or int col with a default of 0, as you apply the command delete it from the
table. You can also create tables for each day or hour and then read them
until all the commands are applied and then drop them.
> BTW, Thx a mill for your time and help!
Your welcome
[vbcol=seagreen]> "Hilary Cotter" wrote:
|||Thanks for the comments, the picture is much clearer now.
I've made some progress.
1. I've installed the Sybase drivers
2. I've setup the oledb connection and successfully retrieved data from the
Sybase tables using the linked server.
I've now hit a snag and need your expertise.
The parameters for sp_browsereplcmds are no longer the same.
The @.results_table is no longer a parameter.
What's the alternative in 2005 to accomplish the same thing?
"Hilary Cotter" wrote:
> Answers inline.
> --
> 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
> "gracie" <gracie@.discussions.microsoft.com> wrote in message
> news:A3B3337C-DEDD-4811-97D9-DBC1A4051684@.microsoft.com...
> OK, but the naming convention I use implies the maxiumn one last collected
> and the current maxiumn. Its all a naming convention - whatever works for
> you
> It will be transactional replication to a dummy subscriber. You will need to
> create some scheduled jobs to read the commands and then replay them on
> Sybase.
> Create a dummy database - a plain vanilla one on the publisher and
> replicated there. Delete rows from it on a schedule to keep it small and
> then set your min retention to 6 hours or so, a time interval which will
> allow you to collect all the commands. The distribution clean up job will
> remove them from the distribution database. But it doesn't know if they have
> been applied on Sybase yet, so you will have to make sure you collect them
> before they are deleted.
> You will have to implement some sort of tracking column. Perhaps an tinyint
> or int col with a default of 0, as you apply the command delete it from the
> table. You can also create tables for each day or hour and then read them
> until all the commands are applied and then drop them.
> Your welcome
>
>
Replication to SQL 2005
Hello all,
We have merge replication setup between 3 servers. SQL1, SQL2 and SQL3.
We have installed SQL2005 on a ReportingServer and want to be able to
popluate the database on SQL2005 with data from SQL1. How can we set this up?
We want to avoide setting up the whole replication process again as the
database is 25 GB and SQL3 is updated via a telephone line.
Thanks
Regards
Ishan
Ishan,
if the ReportingServer is RO apart from the replication synchronization, I'd
use transactional replication. I'd also consider dong a nosync
initialization to set things up with a database of this size.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
We have merge replication setup between 3 servers. SQL1, SQL2 and SQL3.
We have installed SQL2005 on a ReportingServer and want to be able to
popluate the database on SQL2005 with data from SQL1. How can we set this up?
We want to avoide setting up the whole replication process again as the
database is 25 GB and SQL3 is updated via a telephone line.
Thanks
Regards
Ishan
Ishan,
if the ReportingServer is RO apart from the replication synchronization, I'd
use transactional replication. I'd also consider dong a nosync
initialization to set things up with a database of this size.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication to Mainframe Db2 OSZ
What would I need in order to setup replication between SQL2000 SP3 and MVS Subsystem running Db2 database . I mean, does it need any 3rd party tool or regular replication setup between SQL and Db2 is enoughAsk IBM for the DB2 Federated Server. If you have DB2 license, they should let you use that software free. It comes with a Replicator which allows you to replicate data from a different platform.
Replication to Mainframe Db2 OS390
Can I get drivers to setup Replication to Mainframe . I really don't want to install HIS
Thankscan someone please answer .
Thanks|||I don't think its possible without HIS.|||HIS what?
:D|||HIS stands for Host Integration Server, more from here (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) .
Thankscan someone please answer .
Thanks|||I don't think its possible without HIS.|||HIS what?
:D|||HIS stands for Host Integration Server, more from here (http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q269074) .
replication thru vpn
Hi,
Please help, I am new to replication.
I have setup a vpn connection from one computer to another.
e.g. Computer X and computer Y. Both are in different area of the city.
Both computers have mssql 2000. The source database is in computer X.
I like to replicate the data in computer X to computer Y. In computer Y I
can see the X's mssql db. It is shown us 192.168.0.2 in EM.
which is the ip address use in the VPN for computer X. I cannot create the
replication because the computer name is shown as an ip address.
Is there a way to change it to the real computer name?
Can someone please point me to any articles or tutorials that may help me to
set up replication thru VPN or thru the Internet?
Thank You.
Sam
You could try making use of the HOSTS file on the servers to map IP and the
server name. Then try to register SQL servers from enterprise manager
- Sid
"sam" wrote:
> Hi,
> Please help, I am new to replication.
> I have setup a vpn connection from one computer to another.
> e.g. Computer X and computer Y. Both are in different area of the city.
> Both computers have mssql 2000. The source database is in computer X.
> I like to replicate the data in computer X to computer Y. In computer Y I
> can see the X's mssql db. It is shown us 192.168.0.2 in EM.
> which is the ip address use in the VPN for computer X. I cannot create the
> replication because the computer name is shown as an ip address.
> Is there a way to change it to the real computer name?
> Can someone please point me to any articles or tutorials that may help me to
> set up replication thru VPN or thru the Internet?
> Thank You.
> Sam
>
>
>
|||sam wrote:
> Both computers have mssql 2000. The source database is in computer X.
> I like to replicate the data in computer X to computer Y. In computer Y I
> can see the X's mssql db. It is shown us 192.168.0.2 in EM.
> which is the ip address use in the VPN for computer X. I cannot create the
> replication because the computer name is shown as an ip address.
> Is there a way to change it to the real computer name?
Use the Client Network Utility to give that IP an alias.
|||Thanks.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:OyovrA9KFHA.2252@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> sam wrote:
I[vbcol=seagreen]
the
> Use the Client Network Utility to give that IP an alias.
Please help, I am new to replication.
I have setup a vpn connection from one computer to another.
e.g. Computer X and computer Y. Both are in different area of the city.
Both computers have mssql 2000. The source database is in computer X.
I like to replicate the data in computer X to computer Y. In computer Y I
can see the X's mssql db. It is shown us 192.168.0.2 in EM.
which is the ip address use in the VPN for computer X. I cannot create the
replication because the computer name is shown as an ip address.
Is there a way to change it to the real computer name?
Can someone please point me to any articles or tutorials that may help me to
set up replication thru VPN or thru the Internet?
Thank You.
Sam
You could try making use of the HOSTS file on the servers to map IP and the
server name. Then try to register SQL servers from enterprise manager
- Sid
"sam" wrote:
> Hi,
> Please help, I am new to replication.
> I have setup a vpn connection from one computer to another.
> e.g. Computer X and computer Y. Both are in different area of the city.
> Both computers have mssql 2000. The source database is in computer X.
> I like to replicate the data in computer X to computer Y. In computer Y I
> can see the X's mssql db. It is shown us 192.168.0.2 in EM.
> which is the ip address use in the VPN for computer X. I cannot create the
> replication because the computer name is shown as an ip address.
> Is there a way to change it to the real computer name?
> Can someone please point me to any articles or tutorials that may help me to
> set up replication thru VPN or thru the Internet?
> Thank You.
> Sam
>
>
>
|||sam wrote:
> Both computers have mssql 2000. The source database is in computer X.
> I like to replicate the data in computer X to computer Y. In computer Y I
> can see the X's mssql db. It is shown us 192.168.0.2 in EM.
> which is the ip address use in the VPN for computer X. I cannot create the
> replication because the computer name is shown as an ip address.
> Is there a way to change it to the real computer name?
Use the Client Network Utility to give that IP an alias.
|||Thanks.
"Ryan Walberg [MCSD]" <generalpf@.nospam.yahoo.reallynospam.com> wrote in
message news:OyovrA9KFHA.2252@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> sam wrote:
I[vbcol=seagreen]
the
> Use the Client Network Utility to give that IP an alias.
Replication through VPN successful??
In the office network domain I've already setup replication between two SQL
Servers( SQL11Server & SQL22Server). Now I am extending replication with SQL
servers in other non-trusted domains/workgroups (SQL33Server & SQL44Server).
SQL33Server & SQL44Server are in non-trusted domains connecting to the
office via VPN. SQL33Server can ping SQL11Server.mydomain.local fine,
but it cannot ping just SQL11Server. Is this going to be a problem if it
pings the full DNS address?
When I started MAKEPIPE on SQL11Server, from SQL33Server I have to type
READPIPE /Ssql11server.mydomain.local for it to work. If I type READPIPE
/Ssql11Server it doesn't work..
Do I have a NETBIOS resolution problem ' Or what is the problem and how can
I successful established the connections through VPN? What the steps I
must take to established a successful VPN replication?use a host file for this.
For instance your host file should contain this entry
123.345.567.678 SQL11Server
replacing the numbers with your IP address for SQL11Server
If a netbios name has a . in it, the resolution will be using DNS. It should
like your name resolution service (probably WINS) can figure out where
SQL111Server is.
"Joe Mine" <huytuanattpgdotcomdotau> wrote in message
news:utbkWlm%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> In the office network domain I've already setup replication between two
SQL
> Servers( SQL11Server & SQL22Server). Now I am extending replication with
SQL
> servers in other non-trusted domains/workgroups (SQL33Server &
SQL44Server).
> SQL33Server & SQL44Server are in non-trusted domains connecting to the
> office via VPN. SQL33Server can ping SQL11Server.mydomain.local fine,
> but it cannot ping just SQL11Server. Is this going to be a problem if it
> pings the full DNS address?
> When I started MAKEPIPE on SQL11Server, from SQL33Server I have to type
> READPIPE /Ssql11server.mydomain.local for it to work. If I type
READPIPE
> /Ssql11Server it doesn't work..
> Do I have a NETBIOS resolution problem ' Or what is the problem and how
can
> I successful established the connections through VPN? What the steps I
> must take to established a successful VPN replication?
>
>
Servers( SQL11Server & SQL22Server). Now I am extending replication with SQL
servers in other non-trusted domains/workgroups (SQL33Server & SQL44Server).
SQL33Server & SQL44Server are in non-trusted domains connecting to the
office via VPN. SQL33Server can ping SQL11Server.mydomain.local fine,
but it cannot ping just SQL11Server. Is this going to be a problem if it
pings the full DNS address?
When I started MAKEPIPE on SQL11Server, from SQL33Server I have to type
READPIPE /Ssql11server.mydomain.local for it to work. If I type READPIPE
/Ssql11Server it doesn't work..
Do I have a NETBIOS resolution problem ' Or what is the problem and how can
I successful established the connections through VPN? What the steps I
must take to established a successful VPN replication?use a host file for this.
For instance your host file should contain this entry
123.345.567.678 SQL11Server
replacing the numbers with your IP address for SQL11Server
If a netbios name has a . in it, the resolution will be using DNS. It should
like your name resolution service (probably WINS) can figure out where
SQL111Server is.
"Joe Mine" <huytuanattpgdotcomdotau> wrote in message
news:utbkWlm%23DHA.2664@.TK2MSFTNGP09.phx.gbl...
> In the office network domain I've already setup replication between two
SQL
> Servers( SQL11Server & SQL22Server). Now I am extending replication with
SQL
> servers in other non-trusted domains/workgroups (SQL33Server &
SQL44Server).
> SQL33Server & SQL44Server are in non-trusted domains connecting to the
> office via VPN. SQL33Server can ping SQL11Server.mydomain.local fine,
> but it cannot ping just SQL11Server. Is this going to be a problem if it
> pings the full DNS address?
> When I started MAKEPIPE on SQL11Server, from SQL33Server I have to type
> READPIPE /Ssql11server.mydomain.local for it to work. If I type
READPIPE
> /Ssql11Server it doesn't work..
> Do I have a NETBIOS resolution problem ' Or what is the problem and how
can
> I successful established the connections through VPN? What the steps I
> must take to established a successful VPN replication?
>
>
Wednesday, March 28, 2012
Replication setup via VB.NET
I have a VB.NET program that is executing the SQL commands to setup
replication. I am getting an error (A severe error has occured) on the
sp_addmergepubliction command.
I did setup a profiler and notice one area:
sp_adddistributor was found in the text of this event, the text has been
replaced with this comment for security reasons.
My entire text, before the sp_addmergepulication does:
use master
exec sp_replicationdboption @.dbname='InWare',etc
exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
if @.distributor is null
begin
declare @.xpath varchar (255)
select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
FROM master..sysdatabases where name='inware'
exec sp_adddistributor @.distributor=@.@.servername
exec sp_adddistributiondb @.database='inxsqlDIST',etc
exec sp_adddistpublisher @.publisher=@.@.servername, etc
end
exec sp_replicationdboption @.dbname='InWare',@.optname='merge
publish',@.value='true'
I guess, now that I see Profiler has the line sp_adddistributor
commented out, this could cause the problem. What is commenting it out -
SQL or VB.NET?
I want to use VB (my program) to have the user setup replication so they
don't have to use the enterprise manager, and it allows me to control
the setup.
Thanks.
Darin
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Darin wrote:
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
How about using SQL-DMO through COM Interop?
|||Profiler is removing it because the command contains the keyword "password".
Profiler won't display anything that includes that keyword to eliminate a
security hole. The command is still likely being run, just not displayed in
Profiler.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Darin" <darin_nospam@.nospamever> wrote in message
news:exGVx28KFHA.2596@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET program that is executing the SQL commands to setup
> replication. I am getting an error (A severe error has occured) on the
> sp_addmergepubliction command.
> I did setup a profiler and notice one area:
> sp_adddistributor was found in the text of this event, the text has been
> replaced with this comment for security reasons.
> My entire text, before the sp_addmergepulication does:
> use master
> exec sp_replicationdboption @.dbname='InWare',etc
> exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
> if @.distributor is null
> begin
> declare @.xpath varchar (255)
> select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
> FROM master..sysdatabases where name='inware'
> exec sp_adddistributor @.distributor=@.@.servername
> exec sp_adddistributiondb @.database='inxsqlDIST',etc
> exec sp_adddistpublisher @.publisher=@.@.servername, etc
> end
> exec sp_replicationdboption @.dbname='InWare',@.optname='merge
> publish',@.value='true'
> I guess, now that I see Profiler has the line sp_adddistributor
> commented out, this could cause the problem. What is commenting it out -
> SQL or VB.NET?
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
> Thanks.
> Darin
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Any idea what could be causing the error:
A severe error occured .... Any results should be discarded
on the exec sp_addreplication @.publication='InWareMerge'
when it works find on QA?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you script a publication in EM, it assumes you have the publisher,
distributor, and subscribers already set up for all servers involved in
replication. If that is not the case, then creating a publication will
fail.
GNH
"Darin" <darin_nospam@.nospamever> wrote in message
news:ukmK5P9KFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Any idea what could be causing the error:
> A severe error occured .... Any results should be discarded
> on the exec sp_addreplication @.publication='InWareMerge'
> when it works find on QA?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||My problem is all of my script run from the VB.NET program work fine
until that command.
Whereas if I take all of the script and paste it into a QA window it all
works perfectly.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
replication. I am getting an error (A severe error has occured) on the
sp_addmergepubliction command.
I did setup a profiler and notice one area:
sp_adddistributor was found in the text of this event, the text has been
replaced with this comment for security reasons.
My entire text, before the sp_addmergepulication does:
use master
exec sp_replicationdboption @.dbname='InWare',etc
exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
if @.distributor is null
begin
declare @.xpath varchar (255)
select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
FROM master..sysdatabases where name='inware'
exec sp_adddistributor @.distributor=@.@.servername
exec sp_adddistributiondb @.database='inxsqlDIST',etc
exec sp_adddistpublisher @.publisher=@.@.servername, etc
end
exec sp_replicationdboption @.dbname='InWare',@.optname='merge
publish',@.value='true'
I guess, now that I see Profiler has the line sp_adddistributor
commented out, this could cause the problem. What is commenting it out -
SQL or VB.NET?
I want to use VB (my program) to have the user setup replication so they
don't have to use the enterprise manager, and it allows me to control
the setup.
Thanks.
Darin
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Darin wrote:
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
How about using SQL-DMO through COM Interop?
|||Profiler is removing it because the command contains the keyword "password".
Profiler won't display anything that includes that keyword to eliminate a
security hole. The command is still likely being run, just not displayed in
Profiler.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
"Darin" <darin_nospam@.nospamever> wrote in message
news:exGVx28KFHA.2596@.TK2MSFTNGP10.phx.gbl...
>I have a VB.NET program that is executing the SQL commands to setup
> replication. I am getting an error (A severe error has occured) on the
> sp_addmergepubliction command.
> I did setup a profiler and notice one area:
> sp_adddistributor was found in the text of this event, the text has been
> replaced with this comment for security reasons.
> My entire text, before the sp_addmergepulication does:
> use master
> exec sp_replicationdboption @.dbname='InWare',etc
> exec dbo.sp_helpditributor @.rpcsrvname=@.distributor OUTPUT
> if @.distributor is null
> begin
> declare @.xpath varchar (255)
> select @.xpath=substring(filename,1,charindex('InWare_data ',filename)-1)
> FROM master..sysdatabases where name='inware'
> exec sp_adddistributor @.distributor=@.@.servername
> exec sp_adddistributiondb @.database='inxsqlDIST',etc
> exec sp_adddistpublisher @.publisher=@.@.servername, etc
> end
> exec sp_replicationdboption @.dbname='InWare',@.optname='merge
> publish',@.value='true'
> I guess, now that I see Profiler has the line sp_adddistributor
> commented out, this could cause the problem. What is commenting it out -
> SQL or VB.NET?
> I want to use VB (my program) to have the user setup replication so they
> don't have to use the enterprise manager, and it allows me to control
> the setup.
> Thanks.
> Darin
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Any idea what could be causing the error:
A severe error occured .... Any results should be discarded
on the exec sp_addreplication @.publication='InWareMerge'
when it works find on QA?
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you script a publication in EM, it assumes you have the publisher,
distributor, and subscribers already set up for all servers involved in
replication. If that is not the case, then creating a publication will
fail.
GNH
"Darin" <darin_nospam@.nospamever> wrote in message
news:ukmK5P9KFHA.3992@.TK2MSFTNGP15.phx.gbl...
> Any idea what could be causing the error:
> A severe error occured .... Any results should be discarded
> on the exec sp_addreplication @.publication='InWareMerge'
> when it works find on QA?
> Darin
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||My problem is all of my script run from the VB.NET program work fine
until that command.
Whereas if I take all of the script and paste it into a QA window it all
works perfectly.
Darin
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Monday, March 26, 2012
Replication Setup Over a Firewall
In trying to get replication working through a firewall, we followed the instructions f
ound in at: http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can anyon
e give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more informati
on about what we're seeing. Thanks for any help.Hi
Here is a new article that discusses this topic.
321822 HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
http://support.microsoft.com/?id=321822
HTH
Nick Schueler
--
| Thread-Topic: Replication Setup Over a Firewall
| thread-index: AcP6UkHmtqF4bCovR/yQr6n2IZhciA==
| X-Tomcat-NG: microsoft.public.sqlserver.connect
| From: "examnotes" <anonymous@.discussions.microsoft.com>
| Subject: Replication Setup Over a Firewall
| Date: Mon, 23 Feb 2004 13:16:09 -0800
| Lines: 7
| Message-ID: <154B63D7-63E9-40E3-AFC5-253EDEBD2650@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40244
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| In trying to get replication working through a firewall, we followed the
instructions found in at:
http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can
anyone give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more
information about what we're seeing. Thanks for any help.
|
ound in at: http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can anyon
e give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more informati
on about what we're seeing. Thanks for any help.Hi
Here is a new article that discusses this topic.
321822 HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
http://support.microsoft.com/?id=321822
HTH
Nick Schueler
--
| Thread-Topic: Replication Setup Over a Firewall
| thread-index: AcP6UkHmtqF4bCovR/yQr6n2IZhciA==
| X-Tomcat-NG: microsoft.public.sqlserver.connect
| From: "examnotes" <anonymous@.discussions.microsoft.com>
| Subject: Replication Setup Over a Firewall
| Date: Mon, 23 Feb 2004 13:16:09 -0800
| Lines: 7
| Message-ID: <154B63D7-63E9-40E3-AFC5-253EDEBD2650@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.connect
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.connect:40244
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| In trying to get replication working through a firewall, we followed the
instructions found in at:
http://support.microsoft.com/defaul...b;en-us;Q164667
However, this doesn't seem to work. Of course, this article applies to SQL
6.5, so not sure if there are updated instructions for SQL 2000. Can
anyone give me guidance on this?
If someone can tell me that this SHOULD work, then I can post more
information about what we're seeing. Thanks for any help.
|
Replication Setup Issues
While trying to configure replication from and to SQL Server 2000 boxes, I
encountered the following error, which I was not able to get past. The
error is encountered after completing the "Push Subscription Wizard" which
is set to create the database and initialize the schema and data. The
database gets created, but the schema does not.
Does anyone have advice on how to get past this error?
If not, I have questions further on regarding my test environment...
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
I am connected using windows authentication, and I am an administrator on
both boxes. I can connect to either database and perform any action I want,
but I still keep getting this error. I have not found any way to specify a
login for replication, I assume the job runs either under my login or as SA.
I changed the scheduled task to be owned by SA, and attempted to run it as
both "Self" and "DBO", with no luck. I kept getting the same error. Some
of the IDs that exist in our production database do not exist in the
database we are replicating to, although SA exists, as does my account.
Normally I do not use Enterprise manager for anything and I script
everything out, but the only instructions I had were for EM. Would I be
better off using a script in QA for setting up replication?
Regarding my test environment...
Now, after spending a couple of hours on this error, I decided to go back to
my test environment (where everything worked the first time) and make it
look more like my production environment so I could do another round of
testing. I restored the master database and my 3 database which will be
replicated, but the backups were form different dates and I ran into all
kinds of conflicts. Essentially, SQL Server said the replication
definitions were invalid, and would not let me remove them. Next week, when
time allows, I will be getting a cold backup of the entire database to
attach to, hopefully that will get my test database in a state where I can
do another round of testing.
Am I on the right track with my test environment? What is the proper way to
copy a replication database, and remove the replication?
Any advice will be greatly appreciated.
Are you using sql server logins for the distribution agent, and the
subscriber is set to only allow windows (trusted) authentication? This could
be one cause of the error.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both of the databases are set up to allow both SQL Server and trusted
authentication. I confirmed by logging into each server as SA, just to be
sure.
I am, however, performing all of the setup using trusted authentication,
under my own account, which has administrative priviledges.
I never specified a logon for the distribution agent, however. Maybe that
is the problem? I was never actually prompted to do so, and was unable to
find where to specify it. I am the owner of the distribution agent
process, and the first process in the scheduled job is set to run as (Self),
so I assume it is running under my account. I tried changing this to DBA,
and I tried changing the owner to SA, neither made a difference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:exM%23TihUHHA.3652@.TK2MSFTNGP04.phx.gbl...
> Are you using sql server logins for the distribution agent, and the
> subscriber is set to only allow windows (trusted) authentication? This
could
> be one cause of the error.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Jim,
just testing all posibilities here so pls can you check what the following
script returns at the subscriber.
Use Master
go
Select @.@.Servername
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Excellent catch. My test database comes back with the production database
name. The question is, how do I correct this, or how do I get the test
database to match my production database configuration?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Jim,
> just testing all posibilities here so pls can you check what the following
> script returns at the subscriber.
> Use Master
> go
> Select @.@.Servername
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||ok, I found this in BOL explaining how to change the server name. The
question is, will this be enough to get the restored database in a testable
state, or am I going about this the wrong way? Tonight I intend to take a
full cold backup from production and reattach to all the databases on test.
How to change to the current server name in the SQL Server 6.5 master
database (ISQL/w)
To change to the current server name in the SQL Server 6.5 master database
1.. Start Microsoft SQL ServerT in minimal configuration mode. In a
command prompt window, from the \Mssql\Binn directory, run:
sqlservr -f
2.. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then
click ISQL/w.
3.. Enter the sa password, and then click Connect.
4.. Execute SELECT @.@.SERVERNAME to retrieve the former server name.
5.. Execute sp_dropserver to drop the former server.
6.. Execute sp_addserver to add the current server.
7.. Stop SQL Server. In the command prompt window, press Ctrl+C.
8.. Restart SQL Server.
9.. Execute SELECT @.@.SERVERNAME to verify the current server name.
Examples
--Start SQL Server in minimal configuration mode.
--Retrieve the former server name.
SELECT @.@.SERVERNAME
--Drop the server returned from the previous select.
sp_dropserver 'SERVER6X'
--Add the current server.
sp_addserver 'SERVER70', local
--Stop SQL Server.
--Restart SQL Server in minimal configuration mode.
--Verify the current server name.
SELECT @.@.SERVERNAME
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uDG$BKDVHHA.2212@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Excellent catch. My test database comes back with the production database
> name. The question is, how do I correct this, or how do I get the test
> database to match my production database configuration?
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
following
>
|||Jim,
this should be enough to get the subscriber set up. For step 6 you just have
to remember the 'local' switch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, thank you for the assistance. I'll post back in a couple of days to
let everyone know how I made out.
I have added your site to my online SQL Server library. I have a feeling it
will come in useful.
Thanks again,
Jim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23Lh$XgDVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Jim,
> this should be enough to get the subscriber set up. For step 6 you just
have
> to remember the 'local' switch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I was finally able to get my test environment up and running, after renaming
my server, deleting the entries in the distribution database, and updating
the server name in the msdb.dbo.jobs table.
The test environment replicated without any issue, so I do not know why I am
having trouble in production.
Maybe I just need to remove the replication entirely and start from scratch?
Before I go that route, does anyone know what would be likely to cause this
error when trying to run the distribution agent?
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e5aCzMhUHHA.5012@.TK2MSFTNGP04.phx.gbl...
> While trying to configure replication from and to SQL Server 2000 boxes, I
> encountered the following error, which I was not able to get past. The
> error is encountered after completing the "Push Subscription Wizard" which
> is set to create the database and initialize the schema and data. The
> database gets created, but the schema does not.
> Does anyone have advice on how to get past this error?
> If not, I have questions further on regarding my test environment...
> ----
--
> --
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
> ----
--
> --
> I am connected using windows authentication, and I am an administrator on
> both boxes. I can connect to either database and perform any action I
want,
> but I still keep getting this error. I have not found any way to specify
a
> login for replication, I assume the job runs either under my login or as
SA.
> I changed the scheduled task to be owned by SA, and attempted to run it as
> both "Self" and "DBO", with no luck. I kept getting the same error.
Some
> of the IDs that exist in our production database do not exist in the
> database we are replicating to, although SA exists, as does my account.
> Normally I do not use Enterprise manager for anything and I script
> everything out, but the only instructions I had were for EM. Would I be
> better off using a script in QA for setting up replication?
> Regarding my test environment...
> Now, after spending a couple of hours on this error, I decided to go back
to
> my test environment (where everything worked the first time) and make it
> look more like my production environment so I could do another round of
> testing. I restored the master database and my 3 database which will be
> replicated, but the backups were form different dates and I ran into all
> kinds of conflicts. Essentially, SQL Server said the replication
> definitions were invalid, and would not let me remove them. Next week,
when
> time allows, I will be getting a cold backup of the entire database to
> attach to, hopefully that will get my test database in a state where I can
> do another round of testing.
> Am I on the right track with my test environment? What is the proper way
to
> copy a replication database, and remove the replication?
> Any advice will be greatly appreciated.
>
|||OK, I removed the replication entirely and started over, with the same
problems as before. It turned out to be an NT account/permissions issue.
The account that runs the SQL Server Agent on the distributor needs to have
access to the subscriber server as well.
Just to repeat the error:
> ----
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
I looked at the security event logs on the subscriber server and saw a login
failure when I was trying to push the subscription. The ID that was
erroring out was a local ID on the distributor. The SQL Server Agent was
being run under this local account. Creating the same local account on the
subscriber server and giving it the required permissions, corrected the
problem.
encountered the following error, which I was not able to get past. The
error is encountered after completing the "Push Subscription Wizard" which
is set to create the database and initialize the schema and data. The
database gets created, but the schema does not.
Does anyone have advice on how to get past this error?
If not, I have questions further on regarding my test environment...
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
I am connected using windows authentication, and I am an administrator on
both boxes. I can connect to either database and perform any action I want,
but I still keep getting this error. I have not found any way to specify a
login for replication, I assume the job runs either under my login or as SA.
I changed the scheduled task to be owned by SA, and attempted to run it as
both "Self" and "DBO", with no luck. I kept getting the same error. Some
of the IDs that exist in our production database do not exist in the
database we are replicating to, although SA exists, as does my account.
Normally I do not use Enterprise manager for anything and I script
everything out, but the only instructions I had were for EM. Would I be
better off using a script in QA for setting up replication?
Regarding my test environment...
Now, after spending a couple of hours on this error, I decided to go back to
my test environment (where everything worked the first time) and make it
look more like my production environment so I could do another round of
testing. I restored the master database and my 3 database which will be
replicated, but the backups were form different dates and I ran into all
kinds of conflicts. Essentially, SQL Server said the replication
definitions were invalid, and would not let me remove them. Next week, when
time allows, I will be getting a cold backup of the entire database to
attach to, hopefully that will get my test database in a state where I can
do another round of testing.
Am I on the right track with my test environment? What is the proper way to
copy a replication database, and remove the replication?
Any advice will be greatly appreciated.
Are you using sql server logins for the distribution agent, and the
subscriber is set to only allow windows (trusted) authentication? This could
be one cause of the error.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Both of the databases are set up to allow both SQL Server and trusted
authentication. I confirmed by logging into each server as SA, just to be
sure.
I am, however, performing all of the setup using trusted authentication,
under my own account, which has administrative priviledges.
I never specified a logon for the distribution agent, however. Maybe that
is the problem? I was never actually prompted to do so, and was unable to
find where to specify it. I am the owner of the distribution agent
process, and the first process in the scheduled job is set to run as (Self),
so I assume it is running under my account. I tried changing this to DBA,
and I tried changing the owner to SA, neither made a difference.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:exM%23TihUHHA.3652@.TK2MSFTNGP04.phx.gbl...
> Are you using sql server logins for the distribution agent, and the
> subscriber is set to only allow windows (trusted) authentication? This
could
> be one cause of the error.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||Jim,
just testing all posibilities here so pls can you check what the following
script returns at the subscriber.
Use Master
go
Select @.@.Servername
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Excellent catch. My test database comes back with the production database
name. The question is, how do I correct this, or how do I get the test
database to match my production database configuration?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
> Jim,
> just testing all posibilities here so pls can you check what the following
> script returns at the subscriber.
> Use Master
> go
> Select @.@.Servername
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
>
|||ok, I found this in BOL explaining how to change the server name. The
question is, will this be enough to get the restored database in a testable
state, or am I going about this the wrong way? Tonight I intend to take a
full cold backup from production and reattach to all the databases on test.
How to change to the current server name in the SQL Server 6.5 master
database (ISQL/w)
To change to the current server name in the SQL Server 6.5 master database
1.. Start Microsoft SQL ServerT in minimal configuration mode. In a
command prompt window, from the \Mssql\Binn directory, run:
sqlservr -f
2.. On the Start menu, point to Programs /Microsoft SQL Server 6.5, and then
click ISQL/w.
3.. Enter the sa password, and then click Connect.
4.. Execute SELECT @.@.SERVERNAME to retrieve the former server name.
5.. Execute sp_dropserver to drop the former server.
6.. Execute sp_addserver to add the current server.
7.. Stop SQL Server. In the command prompt window, press Ctrl+C.
8.. Restart SQL Server.
9.. Execute SELECT @.@.SERVERNAME to verify the current server name.
Examples
--Start SQL Server in minimal configuration mode.
--Retrieve the former server name.
SELECT @.@.SERVERNAME
--Drop the server returned from the previous select.
sp_dropserver 'SERVER6X'
--Add the current server.
sp_addserver 'SERVER70', local
--Stop SQL Server.
--Restart SQL Server in minimal configuration mode.
--Verify the current server name.
SELECT @.@.SERVERNAME
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:uDG$BKDVHHA.2212@.TK2MSFTNGP02.phx.gbl...[vbcol=seagreen]
> Excellent catch. My test database comes back with the production database
> name. The question is, how do I correct this, or how do I get the test
> database to match my production database configuration?
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ua103$CVHHA.4784@.TK2MSFTNGP03.phx.gbl...
following
>
|||Jim,
this should be enough to get the subscriber set up. For step 6 you just have
to remember the 'local' switch.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Paul, thank you for the assistance. I'll post back in a couple of days to
let everyone know how I made out.
I have added your site to my online SQL Server library. I have a feeling it
will come in useful.
Thanks again,
Jim
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23Lh$XgDVHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Jim,
> this should be enough to get the subscriber set up. For step 6 you just
have
> to remember the 'local' switch.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||I was finally able to get my test environment up and running, after renaming
my server, deleting the entries in the distribution database, and updating
the server name in the msdb.dbo.jobs table.
The test environment replicated without any issue, so I do not know why I am
having trouble in production.
Maybe I just need to remove the replication entirely and start from scratch?
Before I go that route, does anyone know what would be likely to cause this
error when trying to run the distribution agent?
The process could not connect to Subscriber '<SERVERNAME>'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
(Source: <SERVERNAME> (Data source); Error number: 18452)
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e5aCzMhUHHA.5012@.TK2MSFTNGP04.phx.gbl...
> While trying to configure replication from and to SQL Server 2000 boxes, I
> encountered the following error, which I was not able to get past. The
> error is encountered after completing the "Push Subscription Wizard" which
> is set to create the database and initialize the schema and data. The
> database gets created, but the schema does not.
> Does anyone have advice on how to get past this error?
> If not, I have questions further on regarding my test environment...
> ----
--
> --
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
> ----
--
> --
> I am connected using windows authentication, and I am an administrator on
> both boxes. I can connect to either database and perform any action I
want,
> but I still keep getting this error. I have not found any way to specify
a
> login for replication, I assume the job runs either under my login or as
SA.
> I changed the scheduled task to be owned by SA, and attempted to run it as
> both "Self" and "DBO", with no luck. I kept getting the same error.
Some
> of the IDs that exist in our production database do not exist in the
> database we are replicating to, although SA exists, as does my account.
> Normally I do not use Enterprise manager for anything and I script
> everything out, but the only instructions I had were for EM. Would I be
> better off using a script in QA for setting up replication?
> Regarding my test environment...
> Now, after spending a couple of hours on this error, I decided to go back
to
> my test environment (where everything worked the first time) and make it
> look more like my production environment so I could do another round of
> testing. I restored the master database and my 3 database which will be
> replicated, but the backups were form different dates and I ran into all
> kinds of conflicts. Essentially, SQL Server said the replication
> definitions were invalid, and would not let me remove them. Next week,
when
> time allows, I will be getting a cold backup of the entire database to
> attach to, hopefully that will get my test database in a state where I can
> do another round of testing.
> Am I on the right track with my test environment? What is the proper way
to
> copy a replication database, and remove the replication?
> Any advice will be greatly appreciated.
>
|||OK, I removed the replication entirely and started over, with the same
problems as before. It turned out to be an NT account/permissions issue.
The account that runs the SQL Server Agent on the distributor needs to have
access to the subscriber server as well.
Just to repeat the error:
> ----
> The process could not connect to Subscriber '<SERVERNAME>'.
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> (Source: <SERVERNAME> (Data source); Error number: 18452)
I looked at the security event logs on the subscriber server and saw a login
failure when I was trying to push the subscription. The ID that was
erroring out was a local ID on the distributor. The SQL Server Agent was
being run under this local account. Creating the same local account on the
subscriber server and giving it the required permissions, corrected the
problem.
replication setup help
Shailesh,
transactional replication over a VPN would be my choice,
with your home as a pull subscriber. If you haven't got
the VPN set up, then you can still use SQL Server logins
for synchronization and FTP for initialization.
This article should help in the configuration:
http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul Ibison wrote:
> Shailesh,
> transactional replication over a VPN would be my choice,
> with your home as a pull subscriber. If you haven't got
> the VPN set up, then you can still use SQL Server logins
> for synchronization and FTP for initialization.
> This article should help in the configuration:
> http://support.microsoft.com/?id=321822
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
There are so many options and issues. I don't think there's any way
to avoid spending a week just to read through everything and
understand it all...
sql
transactional replication over a VPN would be my choice,
with your home as a pull subscriber. If you haven't got
the VPN set up, then you can still use SQL Server logins
for synchronization and FTP for initialization.
This article should help in the configuration:
http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul Ibison wrote:
> Shailesh,
> transactional replication over a VPN would be my choice,
> with your home as a pull subscriber. If you haven't got
> the VPN set up, then you can still use SQL Server logins
> for synchronization and FTP for initialization.
> This article should help in the configuration:
> http://support.microsoft.com/?id=321822
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
There are so many options and issues. I don't think there's any way
to avoid spending a week just to read through everything and
understand it all...
sql
replication setup help
Can someone give me a pointer on how to set up a replication server?
I've browsed the replication documentation, but I just want to know
what the experts suggest for what I want to do.
I have two servers, Office and Home, with Internet access over 384k
DSL line. Office is the publisher, and Home is the subscriber. Home
should be synchronized to Office each night. It is a one-way
replication, i.e. mirroring. The database is many gigabytes, so
snapshot replication is not possible over DSL. Only additions and
changes must be transmitted. What is your suggestion? Should I look
into some third-party software?
Thanks,
Shailesh
Hi Shailesh,
From your descriptions, I understood that you would like to setup
Replication between your Office and Home via DSL. Home is supposed to be
synchronized every night. Have I understood you? Correct me if I was wrong.
Based on my scope, Transactional Replication might be the suitable one for
you. I am afraid you will have to following the document Paul Ibison
listed. Espacially Chapter "Set Up Replication Between Two Computers
Running SQL Server Across the Internet" show you how to configure SQL
Server Replication via Internet correctly.
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
Domains or Across the Internet
http://support.microsoft.com/default...b;EN-US;321822
Please understand that Replication Issues may be complicated so that you
will have to spend more time making it through. Here some more documents
that I think might be helpful to you.
HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or
Restore
http://support.microsoft.com/default...b;EN-US;320499
SQL Server Replication Setup
http://www.databasejournal.com/featu...le.php/1459011
Troubleshooting Replication Problems
http://www.databasejournal.com/featu...le.php/1468971
If you would like to show you step by step on how to setup transactional
replication between your HOME and OFFICE, I need more information about
scenario.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
|||Hi Shailesh,
I am just checking on your progress regarding the information that was sent
you! I wonder how the testing is going. If you encounter any difficulty,
please do not hesitate to let me know. Please post here and let me know the
status of your issue. Without your further information, it's very hard for
me to continue with the troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
I've browsed the replication documentation, but I just want to know
what the experts suggest for what I want to do.
I have two servers, Office and Home, with Internet access over 384k
DSL line. Office is the publisher, and Home is the subscriber. Home
should be synchronized to Office each night. It is a one-way
replication, i.e. mirroring. The database is many gigabytes, so
snapshot replication is not possible over DSL. Only additions and
changes must be transmitted. What is your suggestion? Should I look
into some third-party software?
Thanks,
Shailesh
Hi Shailesh,
From your descriptions, I understood that you would like to setup
Replication between your Office and Home via DSL. Home is supposed to be
synchronized every night. Have I understood you? Correct me if I was wrong.
Based on my scope, Transactional Replication might be the suitable one for
you. I am afraid you will have to following the document Paul Ibison
listed. Espacially Chapter "Set Up Replication Between Two Computers
Running SQL Server Across the Internet" show you how to configure SQL
Server Replication via Internet correctly.
HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
Domains or Across the Internet
http://support.microsoft.com/default...b;EN-US;321822
Please understand that Replication Issues may be complicated so that you
will have to spend more time making it through. Here some more documents
that I think might be helpful to you.
HOW TO: Manually Synchronize Replication Subscriptions by Using Backup or
Restore
http://support.microsoft.com/default...b;EN-US;320499
SQL Server Replication Setup
http://www.databasejournal.com/featu...le.php/1459011
Troubleshooting Replication Problems
http://www.databasejournal.com/featu...le.php/1468971
If you would like to show you step by step on how to setup transactional
replication between your HOME and OFFICE, I need more information about
scenario.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
|||Hi Shailesh,
I am just checking on your progress regarding the information that was sent
you! I wonder how the testing is going. If you encounter any difficulty,
please do not hesitate to let me know. Please post here and let me know the
status of your issue. Without your further information, it's very hard for
me to continue with the troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Replication setup Error..
When I tried to configure replication, the following error comes out.
What is that about and any idea?When I tried to configure replication, the following error comes out.
What is that about and any idea?
Try this Microsoft article:
http://support.microsoft.com/?id=321822
What is that about and any idea?When I tried to configure replication, the following error comes out.
What is that about and any idea?
Try this Microsoft article:
http://support.microsoft.com/?id=321822
replication setup
Hi,
I am using SQL 2005 server. If I want to replicate a table from A
server to B server, using transactional publication, do I setup a
publication in A server with article of the table and then setup a
subscription in B server? Am I over-simplifying or there is some thing I
need to set up? Cause now it is not changing the record in B server when I
change the record in A server.
Please help. Thanks.
Michael - have you set this up already? If the table is on Server B then I
assume that the initialization has worked and therefore the snapshot and
distribution agent have worked. Can you see if the distribution agent is on
a schedule and if it is, start the relevant job to send the recent
transactions over. Assuming this is the case, you might want to set the
agent to run continuously to reduce latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
I am using SQL 2005 server. If I want to replicate a table from A
server to B server, using transactional publication, do I setup a
publication in A server with article of the table and then setup a
subscription in B server? Am I over-simplifying or there is some thing I
need to set up? Cause now it is not changing the record in B server when I
change the record in A server.
Please help. Thanks.
Michael - have you set this up already? If the table is on Server B then I
assume that the initialization has worked and therefore the snapshot and
distribution agent have worked. Can you see if the distribution agent is on
a schedule and if it is, start the relevant job to send the recent
transactions over. Assuming this is the case, you might want to set the
agent to run continuously to reduce latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
Replication setup
How many articles can take part in merge replication?
I need to replicate about 200 tables. From them 25 are common for all my
subscribers and do not need to be filtered. Other I plan to filter with
static filters.
Is there benefit to allocate this 25 common tables in separate publication
or it will be better to include them in all publications where tables with
filters are placed?
Ideally you would have a single publication. There is no performance impact
by having multiple publications. If you are going to have multiple
publications (perhaps a publication for each article), you should group
articles which are related (PK-FK) relationships into the same publication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"massa" <massa@.discussions.microsoft.com> wrote in message
news:460AB955-4C93-4EE0-BC24-3B479E7807CF@.microsoft.com...
> How many articles can take part in merge replication?
> I need to replicate about 200 tables. From them 25 are common for all my
> subscribers and do not need to be filtered. Other I plan to filter with
> static filters.
> Is there benefit to allocate this 25 common tables in separate publication
> or it will be better to include them in all publications where tables with
> filters are placed?
>
I need to replicate about 200 tables. From them 25 are common for all my
subscribers and do not need to be filtered. Other I plan to filter with
static filters.
Is there benefit to allocate this 25 common tables in separate publication
or it will be better to include them in all publications where tables with
filters are placed?
Ideally you would have a single publication. There is no performance impact
by having multiple publications. If you are going to have multiple
publications (perhaps a publication for each article), you should group
articles which are related (PK-FK) relationships into the same publication.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"massa" <massa@.discussions.microsoft.com> wrote in message
news:460AB955-4C93-4EE0-BC24-3B479E7807CF@.microsoft.com...
> How many articles can take part in merge replication?
> I need to replicate about 200 tables. From them 25 are common for all my
> subscribers and do not need to be filtered. Other I plan to filter with
> static filters.
> Is there benefit to allocate this 25 common tables in separate publication
> or it will be better to include them in all publications where tables with
> filters are placed?
>
replication set up creates a linked server?
Hello - I recently setup replication here & I have a question. In doing an
audit of the SQL Servers the other DBA & I notice a linked server between a
dev box & a prod box with a read-only login called LinkedServer_RO to prod.
Neither one of us recognize this linked server & it's against the security
policies (understandably tight here at the CDC) to have linked servers unless
absolutely necessary. However when I go to remove the linked server it says
it can't be deleted because it is a subscriber in replication - did the
replication setup process create this? I had not remembered seeing linked
servers get automatically created before - any info is greatly appreciated -
thanks.
John F.
this is not created by replication.
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
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:0FDBFCE9-1F04-4BEE-A20A-AF6639200F91@.microsoft.com...
> Hello - I recently setup replication here & I have a question. In doing
an
> audit of the SQL Servers the other DBA & I notice a linked server between
a
> dev box & a prod box with a read-only login called LinkedServer_RO to
prod.
> Neither one of us recognize this linked server & it's against the security
> policies (understandably tight here at the CDC) to have linked servers
unless
> absolutely necessary. However when I go to remove the linked server it
says
> it can't be deleted because it is a subscriber in replication - did the
> replication setup process create this? I had not remembered seeing linked
> servers get automatically created before - any info is greatly
appreciated -
> thanks.
> --
> John F.
|||thanks so much for your quick reply - in that case I wonder how I can go
about removing it - it errors saying the linked server is a subscriber in
replication.
"Hilary Cotter" wrote:
> this is not created by replication.
> --
> 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
> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:0FDBFCE9-1F04-4BEE-A20A-AF6639200F91@.microsoft.com...
> an
> a
> prod.
> unless
> says
> appreciated -
>
>
|||oops - actually replication does not create this linked server but someone
may have created a linked server for a subscriber.
Go to tools, replication, configure distributor, publishers, and subscribers
and click on the subscribers tab. You should see it there.
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
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:4F7F8843-1205-4084-9DE0-997660584E04@.microsoft.com...[vbcol=seagreen]
> thanks so much for your quick reply - in that case I wonder how I can go
> about removing it - it errors saying the linked server is a subscriber in
> replication.
> "Hilary Cotter" wrote:
doing[vbcol=seagreen]
between[vbcol=seagreen]
security[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
linked[vbcol=seagreen]
|||John,
replication will create a remote server for subscribers needing distributed
transactions, but sometimes you also need a (more versatile) linked server
to the same server for some other purpose. As you can't have both entries in
the sysservers table, the solution is to convert the remote server to a
linked server which is my guess what has happened.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
audit of the SQL Servers the other DBA & I notice a linked server between a
dev box & a prod box with a read-only login called LinkedServer_RO to prod.
Neither one of us recognize this linked server & it's against the security
policies (understandably tight here at the CDC) to have linked servers unless
absolutely necessary. However when I go to remove the linked server it says
it can't be deleted because it is a subscriber in replication - did the
replication setup process create this? I had not remembered seeing linked
servers get automatically created before - any info is greatly appreciated -
thanks.
John F.
this is not created by replication.
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
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:0FDBFCE9-1F04-4BEE-A20A-AF6639200F91@.microsoft.com...
> Hello - I recently setup replication here & I have a question. In doing
an
> audit of the SQL Servers the other DBA & I notice a linked server between
a
> dev box & a prod box with a read-only login called LinkedServer_RO to
prod.
> Neither one of us recognize this linked server & it's against the security
> policies (understandably tight here at the CDC) to have linked servers
unless
> absolutely necessary. However when I go to remove the linked server it
says
> it can't be deleted because it is a subscriber in replication - did the
> replication setup process create this? I had not remembered seeing linked
> servers get automatically created before - any info is greatly
appreciated -
> thanks.
> --
> John F.
|||thanks so much for your quick reply - in that case I wonder how I can go
about removing it - it errors saying the linked server is a subscriber in
replication.
"Hilary Cotter" wrote:
> this is not created by replication.
> --
> 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
> "John F." <juantana@.newsgroups.microsoft.com> wrote in message
> news:0FDBFCE9-1F04-4BEE-A20A-AF6639200F91@.microsoft.com...
> an
> a
> prod.
> unless
> says
> appreciated -
>
>
|||oops - actually replication does not create this linked server but someone
may have created a linked server for a subscriber.
Go to tools, replication, configure distributor, publishers, and subscribers
and click on the subscribers tab. You should see it there.
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
"John F." <juantana@.newsgroups.microsoft.com> wrote in message
news:4F7F8843-1205-4084-9DE0-997660584E04@.microsoft.com...[vbcol=seagreen]
> thanks so much for your quick reply - in that case I wonder how I can go
> about removing it - it errors saying the linked server is a subscriber in
> replication.
> "Hilary Cotter" wrote:
doing[vbcol=seagreen]
between[vbcol=seagreen]
security[vbcol=seagreen]
it[vbcol=seagreen]
the[vbcol=seagreen]
linked[vbcol=seagreen]
|||John,
replication will create a remote server for subscribers needing distributed
transactions, but sometimes you also need a (more versatile) linked server
to the same server for some other purpose. As you can't have both entries in
the sysservers table, the solution is to convert the remote server to a
linked server which is my guess what has happened.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Replication schedule
Here's my problem
I have setup a transactional replication with a push
subscription, I schedule it to run every hour, every think
works good, however when the schedule start synchronizing
it never stops so its not stating at the next hour, I have
to manually click stop synchronizing and then it starts
again.
Please help!!!
Expand Replicaiton Monitor, expand the agents folder, expand the
distribution agents folder, right click on your distribution agent and
select agent properties. then select the steps tab. Click on Run Agent and
then click on the Edit button.
in the command section press the end key, remove the -Continuous statement,
click apply, ok, apply ok, right click on your distribution agent again and
select start agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"amw" <amw@.1800wheelchair.com> wrote in message
news:0b7b01c48b80$61dbdc50$a301280a@.phx.gbl...
> Here's my problem
> I have setup a transactional replication with a push
> subscription, I schedule it to run every hour, every think
> works good, however when the schedule start synchronizing
> it never stops so its not stating at the next hour, I have
> to manually click stop synchronizing and then it starts
> again.
> Please help!!!
|||Thanks for your reply
I follow your answer but I don't see the -Continuous
statement on the distribution agent schedule, but I see it
in the log reader agent schedule, should I remove it from
there? I think the log reader should run continuous.
>--Original Message--
>Expand Replicaiton Monitor, expand the agents folder,
expand the
>distribution agents folder, right click on your
distribution agent and
>select agent properties. then select the steps tab. Click
on Run Agent and
>then click on the Edit button.
>in the command section press the end key, remove the -
Continuous statement,
>click apply, ok, apply ok, right click on your
distribution agent again and[vbcol=seagreen]
>select start agent.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"amw" <amw@.1800wheelchair.com> wrote in message
>news:0b7b01c48b80$61dbdc50$a301280a@.phx.gbl...
think[vbcol=seagreen]
synchronizing[vbcol=seagreen]
have
>
>.
>
|||don't touch the log reader!
If it is not present in your distribution agent, you have a lot of data to
send to your subscriber. In time the job will finish.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"amw" <amw@.1800wheelchair.com> wrote in message
news:0c2801c48b8f$7ad28e20$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your reply
> I follow your answer but I don't see the -Continuous
> statement on the distribution agent schedule, but I see it
> in the log reader agent schedule, should I remove it from
> there? I think the log reader should run continuous.
> expand the
> distribution agent and
> on Run Agent and
> Continuous statement,
> distribution agent again and
> think
> synchronizing
> have
I have setup a transactional replication with a push
subscription, I schedule it to run every hour, every think
works good, however when the schedule start synchronizing
it never stops so its not stating at the next hour, I have
to manually click stop synchronizing and then it starts
again.
Please help!!!
Expand Replicaiton Monitor, expand the agents folder, expand the
distribution agents folder, right click on your distribution agent and
select agent properties. then select the steps tab. Click on Run Agent and
then click on the Edit button.
in the command section press the end key, remove the -Continuous statement,
click apply, ok, apply ok, right click on your distribution agent again and
select start agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"amw" <amw@.1800wheelchair.com> wrote in message
news:0b7b01c48b80$61dbdc50$a301280a@.phx.gbl...
> Here's my problem
> I have setup a transactional replication with a push
> subscription, I schedule it to run every hour, every think
> works good, however when the schedule start synchronizing
> it never stops so its not stating at the next hour, I have
> to manually click stop synchronizing and then it starts
> again.
> Please help!!!
|||Thanks for your reply
I follow your answer but I don't see the -Continuous
statement on the distribution agent schedule, but I see it
in the log reader agent schedule, should I remove it from
there? I think the log reader should run continuous.
>--Original Message--
>Expand Replicaiton Monitor, expand the agents folder,
expand the
>distribution agents folder, right click on your
distribution agent and
>select agent properties. then select the steps tab. Click
on Run Agent and
>then click on the Edit button.
>in the command section press the end key, remove the -
Continuous statement,
>click apply, ok, apply ok, right click on your
distribution agent again and[vbcol=seagreen]
>select start agent.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"amw" <amw@.1800wheelchair.com> wrote in message
>news:0b7b01c48b80$61dbdc50$a301280a@.phx.gbl...
think[vbcol=seagreen]
synchronizing[vbcol=seagreen]
have
>
>.
>
|||don't touch the log reader!
If it is not present in your distribution agent, you have a lot of data to
send to your subscriber. In time the job will finish.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"amw" <amw@.1800wheelchair.com> wrote in message
news:0c2801c48b8f$7ad28e20$a501280a@.phx.gbl...[vbcol=seagreen]
> Thanks for your reply
> I follow your answer but I don't see the -Continuous
> statement on the distribution agent schedule, but I see it
> in the log reader agent schedule, should I remove it from
> there? I think the log reader should run continuous.
> expand the
> distribution agent and
> on Run Agent and
> Continuous statement,
> distribution agent again and
> think
> synchronizing
> have
replication scenario
Hi there,
I have two servers setup at two different locations, one a publisher(server
A) the other subscriber(server B). I'm using Merge replication and SQL server
2000.
We have teams working in remote areas gathering data in laptops that have
been setup as subscribers to server A.
How can i get the laptops to synchronise with server B incase server A fails?
Samman,
have a look in BOL for "merge replication, alternate synchronization
partners".
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
I have two servers setup at two different locations, one a publisher(server
A) the other subscriber(server B). I'm using Merge replication and SQL server
2000.
We have teams working in remote areas gathering data in laptops that have
been setup as subscribers to server A.
How can i get the laptops to synchronise with server B incase server A fails?
Samman,
have a look in BOL for "merge replication, alternate synchronization
partners".
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
replication re-try setting
Hi. We have replication setup from a sql server 200 database to a
sybase database on Solaris. Every now and then, the sql server
replication process will run into a deadlock with other processes on
the sybase side and be chosen as the rollback victim. The rollback
causes the entire replication to go down, and we have to manually
bring it back up. Does anyone know how to set the re-try parameter for
the replication so that upon being rolled back, it will automatically
re-connect to sybase to continue with the replication ? Thanks.
Steve,
if you are using merge replication, restarting the merge agent should fix
the issue. If you want this to happen automatically, you can force the merge
agent's steps to run in a continuous loop (step 3 -> step 2).
HTH,
Paul Ibison
|||loop back to step 1. I believe that in order for for detection of non logged
agent shutdown you must loopback to step one.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$lpmAdZEHA.3092@.tk2msftngp13.phx.gbl...
> Steve,
> if you are using merge replication, restarting the merge agent should fix
> the issue. If you want this to happen automatically, you can force the
merge
> agent's steps to run in a continuous loop (step 3 -> step 2).
> HTH,
> Paul Ibison
>
|||Thanks Hilary,
step 3 -> step 1 is what I should have written.
Regards,
Paul
sybase database on Solaris. Every now and then, the sql server
replication process will run into a deadlock with other processes on
the sybase side and be chosen as the rollback victim. The rollback
causes the entire replication to go down, and we have to manually
bring it back up. Does anyone know how to set the re-try parameter for
the replication so that upon being rolled back, it will automatically
re-connect to sybase to continue with the replication ? Thanks.
Steve,
if you are using merge replication, restarting the merge agent should fix
the issue. If you want this to happen automatically, you can force the merge
agent's steps to run in a continuous loop (step 3 -> step 2).
HTH,
Paul Ibison
|||loop back to step 1. I believe that in order for for detection of non logged
agent shutdown you must loopback to step one.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23$lpmAdZEHA.3092@.tk2msftngp13.phx.gbl...
> Steve,
> if you are using merge replication, restarting the merge agent should fix
> the issue. If you want this to happen automatically, you can force the
merge
> agent's steps to run in a continuous loop (step 3 -> step 2).
> HTH,
> Paul Ibison
>
|||Thanks Hilary,
step 3 -> step 1 is what I should have written.
Regards,
Paul
Friday, March 23, 2012
replication question
Hi guys,
I have a question, not sure if it's do-able.
I have a transaction replication setup on Server A (Publisher) and Server B
is my Subscriber) I have over 100 tables in this publication.
I want to change order of columns to just one table in Publisher. is
there a way that I can do it without dropping publication?
let said my table have column A , column B, column C
I want to change order to Column B, Column C , Column A
nope. You would have to drop replication, script out your tables, export
your database, drop the tables, recreate them with the correct column order
and then reimport the data.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:O%23hiljsuFHA.3588@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have a question, not sure if it's do-able.
> I have a transaction replication setup on Server A (Publisher) and Server
B
> is my Subscriber) I have over 100 tables in this publication.
> I want to change order of columns to just one table in Publisher. is
> there a way that I can do it without dropping publication?
>
> let said my table have column A , column B, column C
> I want to change order to Column B, Column C , Column A
>
I have a question, not sure if it's do-able.
I have a transaction replication setup on Server A (Publisher) and Server B
is my Subscriber) I have over 100 tables in this publication.
I want to change order of columns to just one table in Publisher. is
there a way that I can do it without dropping publication?
let said my table have column A , column B, column C
I want to change order to Column B, Column C , Column A
nope. You would have to drop replication, script out your tables, export
your database, drop the tables, recreate them with the correct column order
and then reimport the data.
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
"Kevin" <pearl_77@.hotmail.com> wrote in message
news:O%23hiljsuFHA.3588@.tk2msftngp13.phx.gbl...
> Hi guys,
> I have a question, not sure if it's do-able.
> I have a transaction replication setup on Server A (Publisher) and Server
B
> is my Subscriber) I have over 100 tables in this publication.
> I want to change order of columns to just one table in Publisher. is
> there a way that I can do it without dropping publication?
>
> let said my table have column A , column B, column C
> I want to change order to Column B, Column C , Column A
>
Subscribe to:
Posts (Atom)