Replacing a publisher with subscriber

SQL 2000
Currently, I have push transact replication with
immediate updates going out to several subscribers.
I want to take the publisher off the network and have a
subscriber become the publisher...
I will remove replication, and then delete the
field "msrepl_tran_version" from all of the published
tables.
Then I can install replication and push it out to the
subscribers.
I'm not using DRI.
Does this sound like the right plan?
Thanks,
Don
Yes, but what do you mean by "I'm not using DRI?"
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:038601c48edb$fd525570$a401280a@.phx.gbl...
> SQL 2000
> Currently, I have push transact replication with
> immediate updates going out to several subscribers.
> I want to take the publisher off the network and have a
> subscriber become the publisher...
> I will remove replication, and then delete the
> field "msrepl_tran_version" from all of the published
> tables.
> Then I can install replication and push it out to the
> subscribers.
> I'm not using DRI.
> Does this sound like the right plan?
> Thanks,
> Don
>
|||You'd mentioned in an earlier post of mine that if I was
using DRI than certain other steps would have to be done
regarding removing and setting up transact SQL.
Does that sound right?
Don
>--Original Message--
>Yes, but what do you mean by "I'm not using DRI?"
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:038601c48edb$fd525570$a401280a@.phx.gbl...
>
>.
>

Repl works on VPN, not via internet

I am trying to setup a process in a VB.Net app that allows the user to manually invoke a transactional replication process. This works fine on my development machine within our network/VPN. However, when I disconnect my machine from the VPN, the process
keeps failing with a "Process could not connect to Distributor" error. Unfortunately, our customers/users will not be in our domain...they need to be able to connect & replicate over the internet.
Here is the current setup:
Server
Windows 2003 Server
Sql Server 2000 (sp3)
Client
Windows 2000 Pro or Windows XP Pro
MSDE
Also...from within the network I have to use only the server's name "MyServer" to get this to work. If I use a fully qualified name, like "MyServer.Domain.Com" it fails with the same error.
The server acts as both the publisher and the distributor. The publication is set up to allow anonymous pull subscriptions via FTP. I have a sql login that I have setup and made sure that it is in the Publications PAL.
I have used this example: http://support.microsoft.com/default...;en-us;Q319648 to create the .net process.
From a client machine...I can access the ftp site on the server via:
ftp Server.Domain.Com.
I cannot get to the server with Ping or Tracert. I was told by our network admin that the ability to Ping our server has been disabled.
I think that this must be a server configuration problem...since I can get the whole thing to work when I am on the network. I checked the log files (based on a suggestion from another post) on the server...and it doesn't even appear that a login is attem
pted.
Does anyone have any other suggestions?
thanks
when you are using a vpn you are able to authenticate using NT
authentication. This won't work over the internet. You will have to change
how you are authenticating for this to work.
If you can ftp you have connectivity.
Make the following changes
oSnap.PublisherSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION
to
oSnap.PublisherSecurityMode = SQLINITXLib.SECURITY_TYPE.DB_AUTHENTICATION
oSnap.PublisherPassword="password"
oSnap.PublisherLogin="account"
change
oSnap.DistributorSecurityMode =
SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION
to
oSnap.DistributorSecurityMode =
SQLINITXLib.SECURITY_TYPE.DB_AUTHENTICATION
oSnap.DistributorPassword="password"
oSnap.DistributorLogin="account"
Repeat these changes everywhere you see NT_AUTHENTICATION.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"dd" <dd@.discussions.microsoft.com> wrote in message
news:BBA7CF14-42B4-417C-8C4D-A422D520EA0B@.microsoft.com...
> I am trying to setup a process in a VB.Net app that allows the user to
manually invoke a transactional replication process. This works fine on my
development machine within our network/VPN. However, when I disconnect my
machine from the VPN, the process keeps failing with a "Process could not
connect to Distributor" error. Unfortunately, our customers/users will not
be in our domain...they need to be able to connect & replicate over the
internet.
> Here is the current setup:
> Server
> Windows 2003 Server
> Sql Server 2000 (sp3)
> Client
> Windows 2000 Pro or Windows XP Pro
> MSDE
> Also...from within the network I have to use only the server's name
"MyServer" to get this to work. If I use a fully qualified name, like
"MyServer.Domain.Com" it fails with the same error.
> The server acts as both the publisher and the distributor. The
publication is set up to allow anonymous pull subscriptions via FTP. I have
a sql login that I have setup and made sure that it is in the Publications
PAL.
> I have used this example:
http://support.microsoft.com/default...;en-us;Q319648 to create
the .net process.
> From a client machine...I can access the ftp site on the server via:
> ftp Server.Domain.Com.
> I cannot get to the server with Ping or Tracert. I was told by our
network admin that the ability to Ping our server has been disabled.
> I think that this must be a server configuration problem...since I can get
the whole thing to work when I am on the network. I checked the log files
(based on a suggestion from another post) on the server...and it doesn't
even appear that a login is attempted.
> Does anyone have any other suggestions?
> thanks
|||Hilary...
Thanks for the reply. It is good to know that if I can connect via ftp I am doing something right.
Actually, I already made those changes to the example code...changing from NT to DB authentication in each place it was occuring. Though...even on our VPN, I had to change the login and password to the "sa" account so it would work.
We do have a firewall...not sure if that is the problem. And...another note...I have another application running on a PocketPC (with the .Net Compact Framework) that is successful in a merge replication over the internet without using "sa" account...and
it is on the same database I am using for my transactional one that is failing.
"Hilary Cotter" wrote:

> when you are using a vpn you are able to authenticate using NT
> authentication. This won't work over the internet. You will have to change
> how you are authenticating for this to work.
> If you can ftp you have connectivity.
> Make the following changes
> oSnap.PublisherSecurityMode = SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION
> to
> oSnap.PublisherSecurityMode = SQLINITXLib.SECURITY_TYPE.DB_AUTHENTICATION
> oSnap.PublisherPassword="password"
> oSnap.PublisherLogin="account"
> change
> oSnap.DistributorSecurityMode =
> SQLINITXLib.SECURITY_TYPE.NT_AUTHENTICATION
> to
> oSnap.DistributorSecurityMode =
> SQLINITXLib.SECURITY_TYPE.DB_AUTHENTICATION
> oSnap.DistributorPassword="password"
> oSnap.DistributorLogin="account"
> Repeat these changes everywhere you see NT_AUTHENTICATION.
>
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "dd" <dd@.discussions.microsoft.com> wrote in message
> news:BBA7CF14-42B4-417C-8C4D-A422D520EA0B@.microsoft.com...
> manually invoke a transactional replication process. This works fine on my
> development machine within our network/VPN. However, when I disconnect my
> machine from the VPN, the process keeps failing with a "Process could not
> connect to Distributor" error. Unfortunately, our customers/users will not
> be in our domain...they need to be able to connect & replicate over the
> internet.
> "MyServer" to get this to work. If I use a fully qualified name, like
> "MyServer.Domain.Com" it fails with the same error.
> publication is set up to allow anonymous pull subscriptions via FTP. I have
> a sql login that I have setup and made sure that it is in the Publications
> PAL.
> http://support.microsoft.com/default...;en-us;Q319648 to create
> the .net process.
> network admin that the ability to Ping our server has been disabled.
> the whole thing to work when I am on the network. I checked the log files
> (based on a suggestion from another post) on the server...and it doesn't
> even appear that a login is attempted.
>
>

Repl problem with Identity Ranges?

I've given the automatic range on one of the tables for replication and I
entered
1000000 for publisher and 1000000 for subscriber.
When the table is replicated, the id column starts with 278418. Why does it
do this? does SQL know not to count? or am i missing something in
understanding?
I'm sorry. After some research, I found out that i missed something. Sorry
for the post.

Repl Mgr reporting error

Win 2k & SQL 2k
I have trans & merge replication between several remote
sites and my main office. On a couple of the Replication
Managers, an error is being reported on a publication.
When I expand the dist agent & the publication folder to
the actual publication, there is no error. I have
restarted the remote server and still the error exists,
even after I refresh the Replication Manager in EM.
Any one have an idea?
Larry,
when I have seen these rogue errors reported before, it was because of
incorrect data in tempdb so restarting the sql server service on the
distributor removed both the erroneous table and the red icon. hilary
mentions that running sp_MSload_replication_status normally clears this
error as well.
HTH,
Paul Ibison
|||I have restarted the server where the publications exist
and they are their own distributors. I ran
sp_MSload_replication_status on both the publisher and
the subscriber and the errors were not cleared. Any
ideas?
|||Larry,
can you try restarting the sql server service on the server which has
replication manager.
If this still doesn't fix it, try using profiler after you refresh the
replication manager and see if you can track it down.
HTH,
Paul Ibison

Repl failure on <> in data field

We added data to a table with ‘<’ and ‘>’ symbols. The replication failed on
SQLServer with the error “Syntax error or access violation”. We replaced the
'<' and '>' with '(' and ')', which fixed the replication error. Can you not
use brackets in a table that is replicated?
This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
" and '
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Is this a known bug?
Is there any fix available?
"Paul Ibison" wrote:

> This applies to the following characters: * ,< , > , ? , / , \ , % , | , : ,
> " and '
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||You could describe it as a limitation, a feature or a bug
There's no fix - apart from removing the relevant characters (see
http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry I did not expained well.
The table name doesn't contain special character but a space. (Space is not
in the list of special characters). For example [Title Types]
Replication works fine. Adding new data, updating data gets replicated
successfully. Now, when you add data with any special characters in one of
the nvarchar data fields of replicated table ( i.e. [Title Types]).
Replication fails.
The microsoft article addressing the issue of article name with special
characters but I have issue with data when table name contains space(which is
not special character listed).
I can not stop users to not enter special characters in data. So I think I
have only 2 options:
1. Rename table to TitleTypes (i.e remove space), All special characters
will work in data. (Lot of work as table is getting referenced from many
places)
2. add instead of trigger on table to get rid of special characters. (Extra
load on DB for insert and update due to trigger).
Further suggestions will be helpful.
Thanks
"Paul Ibison" wrote:

> You could describe it as a limitation, a feature or a bug
> There's no fix - apart from removing the relevant characters (see
> http://support.microsoft.com/default.aspx?scid=kb;[LN];829746)
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

repairing replicated tables

Hi all,
I have a sql2005 merge replication running nightly between SQl 2005 standard
and SQLexpress. (The subscription is on the SQLexpress) last night it began
failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
it and the lowest level of repair is repair with loss. This box is on its
way out and i need to band-aide it until the new one arrives and is setup.
My question is, do I need to drop the replication before putting the DB in
single user mode? Is there a way to repair the table without running DBCC
checktable with the repair option?
TIA,
Joe
Is the problem related to some indexes and do you get a RID ID error?
If so this is related to some indexes (on system tables IIRC) and you
can drop them and recreate them.
Run CHECKDB again note the object ID which is experiencing the error
and evaluate whether it is an index or not. Script out the index, drop
it and recreate it. There will be no data loss associated with this.
If it is table related you will have to bcp the data out noting where
failure occurs and then work around that/those rows using the firstrow
and lastrow options. You can use DBCC Page to look at the problem
pages if you need to be so granular in your data retrieval.
On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
> Hi all,
> I have a sql2005 merge replication running nightly between SQl 2005 standard
> and SQLexpress. (The subscription is on the SQLexpress) last night it began
> failing. I ran a DBCC checkDB and found one table has 3 inconsistancies in
> it and the lowest level of repair is repair with loss. This box is on its
> way out and i need to band-aide it until the new one arrives and is setup.
> My question is, do I need to drop the replication before putting the DB in
> single user mode? Is there a way to repair the table without running DBCC
> checktable with the repair option?
> TIA,
> Joe
|||Thank you, it was an index issue and droping and recreating the Indexes did
the trick.
Thanks again.
"Hilary Cotter" wrote:

> Is the problem related to some indexes and do you get a RID ID error?
> If so this is related to some indexes (on system tables IIRC) and you
> can drop them and recreate them.
> Run CHECKDB again note the object ID which is experiencing the error
> and evaluate whether it is an index or not. Script out the index, drop
> it and recreate it. There will be no data loss associated with this.
> If it is table related you will have to bcp the data out noting where
> failure occurs and then work around that/those rows using the firstrow
> and lastrow options. You can use DBCC Page to look at the problem
> pages if you need to be so granular in your data retrieval.
> On Jan 15, 2:16 pm, jaylou <jay...@.discussions.microsoft.com> wrote:
>