Replication fail(register fail)

Hi, there
I tried to replicate between the sql 2000 servers
As you know, In order to try to set up the replication, I need a sql server
name on the list box which is used to select the publisher and the
subscriber.
So I had tried to register SQL 2000 Server at the first time. but there is
now server name, so I had registered sql server using IP address.
and then setted up the replication. but one day sql server registered using
IP address removed and replication failed.
I think it's problem is because of the Network.
For the reference, two sql server don't locate under the same HUB and far
from each other. but they are on the same Subnet mask.
What should I check for fixing that problem?
Please, let me know.
Thanks in advance for your help,
Kelly Seo
can you ping the remote server? What does ping -a IpAddress return?
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
"kelly seo" <jyseo@.tricom.co.kr> wrote in message
news:eaRlSybhFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi, there
> I tried to replicate between the sql 2000 servers
> As you know, In order to try to set up the replication, I need a sql
server
> name on the list box which is used to select the publisher and the
> subscriber.
> So I had tried to register SQL 2000 Server at the first time. but there is
> now server name, so I had registered sql server using IP address.
> and then setted up the replication. but one day sql server registered
using
> IP address removed and replication failed.
> I think it's problem is because of the Network.
> For the reference, two sql server don't locate under the same HUB and far
> from each other. but they are on the same Subnet mask.
> What should I check for fixing that problem?
> Please, let me know.
>
> Thanks in advance for your help,
> Kelly Seo
>
|||sure, of course
when I ping to the remote server, return the remote ip address.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:enCfPxfhFHA.2484@.TK2MSFTNGP15.phx.gbl...
> can you ping the remote server? What does ping -a IpAddress return?
> --
> 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
> "kelly seo" <jyseo@.tricom.co.kr> wrote in message
> news:eaRlSybhFHA.1148@.TK2MSFTNGP12.phx.gbl...
> server
> using
>

Replication as backup

Hello All

We intend to replicate a database in order to have it as a near immediate
standby in case of the failure of the main server.

Is this the best solution for disaster recovery?

We are currently testing our replication plan. When we set up the
subscriber, a snapshot of the publisher is created and this takes hours. Is
there a better way?

My understanding is that a RAID array would help in the case of the failure
of a single disc in the server.

What about log shipping. How does log shipping work with identity columns?

Does anyone have any experience of this? Can you offer advice and guidance?

Regards

Ian"Ian Wyld" <ianwyld@.tiscali.co.uk> wrote in message
news:41507254_1@.mk-nntp-2.news.uk.tiscali.com...
> Hello All
> We intend to replicate a database in order to have it as a near immediate
> standby in case of the failure of the main server.
> Is this the best solution for disaster recovery?
> We are currently testing our replication plan. When we set up the
> subscriber, a snapshot of the publisher is created and this takes hours.
> Is
> there a better way?
> My understanding is that a RAID array would help in the case of the
> failure
> of a single disc in the server.
> What about log shipping. How does log shipping work with identity columns?
> Does anyone have any experience of this? Can you offer advice and
> guidance?
>
> Regards
> Ian

I don't really know from your description what you mean by "disaster
recovery" - this page covers some of the high-availability options you have
with MSSQL:

http://www.microsoft.com/sql/techin...vailability.asp

One issue with a replicated database as a standby is that if the primary
fails, then your applications need to be reconfigured with the new server
and database name; if you need failover which is transparent to your
clients, then clustering is the usual solution.

For information on optimizing the replication initial snapshot, see here:

http://www.microsoft.com/technet/pr...n/tranrepl.mspx

RAID will protect you against losing one or more disks, depending on the
configuration, as will a NAS or SAN, although MSSQL is only supported on
NAS/SAN solutions certified for it:

http://support.microsoft.com/defaul...1&Product=sql2k

Log shipping copies every transaction from one database to another by
copying transaction log backups and then restoring them. The secondary
database is always offline so the logs can be restored as they arrive from
the primary server - that means no changes can be made (it can't even be
read), so identity values aren't an issue. Log shipping also has the same
application reconfiguration issue as replication, of course.

Log shipping is a simple solution, but the secondary database is offline and
you would lose (at best) minutes of data if the primary goes down.
Replication is more complex, but the secondary database can be online, and
you can limit data loss to seconds rather than minutes. Clustering is
probably the most expensive solution, but you can lose a whole server and
still carry on with no interruption.

Simon|||Consider log shipping instead of replication if all you need it DR.

"Ian Wyld" <ianwyld@.tiscali.co.uk> wrote in message
news:41507254_1@.mk-nntp-2.news.uk.tiscali.com...
> Hello All
> We intend to replicate a database in order to have it as a near immediate
> standby in case of the failure of the main server.
> Is this the best solution for disaster recovery?
> We are currently testing our replication plan. When we set up the
> subscriber, a snapshot of the publisher is created and this takes hours.
> Is
> there a better way?
> My understanding is that a RAID array would help in the case of the
> failure
> of a single disc in the server.
> What about log shipping. How does log shipping work with identity columns?
> Does anyone have any experience of this? Can you offer advice and
> guidance?
>
> Regards
> Ian

Replication and DBCCs

I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
replication need stopped in order to facilitate the dbccs'? Should a new
snapshot be taken following the dbccs?
No need to reinitialize, pause etc - you can run these in place.
If yo are running DBCC DBREINDEX you might notice some delay before the
synchronization completes though because of the locking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul.
"Paul Ibison" wrote:

> No need to reinitialize, pause etc - you can run these in place.
> If yo are running DBCC DBREINDEX you might notice some delay before the
> synchronization completes though because of the locking issues.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||There have been some reports that some DBCC commands make the log reader
stop reading transactions. This was in SQL 2000.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>I want to run dbccs against replicated databases, dbcc_checkdb etc. Does
> replication need stopped in order to facilitate the dbccs'? Should a new
> snapshot be taken following the dbccs?
|||We experience similar things during our weekly DBCC REINDEXing on the
replicated database. It stops the distributor from applying replicated
transactions and causes the distribution agent to timeout and fail eventually
after the retry count is exceeded.
Is there a way to automate the checking of distribution agent if it is
running at the end of REINDEXing and to restart distribution agent if not.
Thanks
PVG
"Hilary Cotter" wrote:

> There have been some reports that some DBCC commands make the log reader
> stop reading transactions. This was in SQL 2000.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "DC_SQL_GUY" <DC_SQL_GUY@.discussions.microsoft.com> wrote in message
> news:8947CD19-95C7-45F8-9403-92DF293F731E@.microsoft.com...
>
>
|||You could modify the reindex job to have an additional step before :
exec msdb..sp_stop_job 'distribution agent job name'
and an additional step at the end:
exec msdb..sp_start_job 'distribution agent job name'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||How about scheduling the distribution agent not to run in the interval you
are doing your re-indexing.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"PVG" <PVG@.discussions.microsoft.com> wrote in message
news:199ED0FD-FEE5-47AA-BE0D-CD08BB4C5B92@.microsoft.com...[vbcol=seagreen]
> We experience similar things during our weekly DBCC REINDEXing on the
> replicated database. It stops the distributor from applying replicated
> transactions and causes the distribution agent to timeout and fail
> eventually
> after the retry count is exceeded.
> Is there a way to automate the checking of distribution agent if it is
> running at the end of REINDEXing and to restart distribution agent if not.
> Thanks
> PVG
> "Hilary Cotter" wrote:

Replication - PK & SP Question

If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
JLS,
you need to add PKs to tables only in the case of transactional replication. Adding an extra column to a table may cause issues with existing TSQL if it refers to the table without listing the columns - you have to look at individual cases, but insert into xxx select * from yyy where yyy is a table having had an extra column added will fail in this case. For this type of TSQL you'll need to change the syntax to use explicit column names.
Regards,
Paul Ibison
|||the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||"You only need the not for replication option when a replication process is going to be inserting an identity value"
I want to make sure I understand this, because I set all the tables in my publishing database which have identity columns to "Yes - Not for Replication".
I am doing one way transactional replication, so after reading the White Paper, I changed all my identity columns, and changed all my triggers to "Not for Replication" as well.
Did I not need to do the identity column bit? Is it correct that the rows from the publishing table will be inserted/updated to the subscriber table with no effect on the identity column, it will merely be updated or written?
As far as SP's, I don't have any inserts with
insert into table a
select * from table b
So this means I should not have any problems with SP's just because I added identity columns to be used as PK's, right?
I apologize if I seem to be asking the same questions of you, I just want to make sure I have it straight in my head before going to production.
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:uiOJncIOEHA.2560@.TK2MSFTNGP11.phx.gbl...
the stored procedures making the inserts have to be aware of the PK's if you are going to be supplying its value. If you are using the indentity column on the tables in the publisher you only need the identity property - not the identity property with the not for replication option.
You only need the not for replication option when a replication process is going to be inserting an identity value - normally the only time you need this is when you are doing some form of bi-directional replication, ie merge, queued updating, or bi-directional tranactional replication.
You don't need it for immediate updating subscribers.
You only have to worry about the added column when you are doing an unqualified insert, ie
insert into table1
select * from table2
It table2 now has an added column (an identity or GUID column) this class of inserts will fail.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23qMkojFOEHA.2560@.TK2MSFTNGP11.phx.gbl...
If I add Primary keys to tables in order to replicate them, is it also necessary to change any stored procedures doing inserts?
I thought I read a post that stated as long as the insert was not doing a "Select *" on the insert, the sp should be ok. I am, however, receiving an error when a stored procedure executes over a table I just added a primary key to. I just added a column "pk_col" it's an identity column set to "Not for Replication"
Any suggestions would be appreciated!
Thanx!
|||That is correct. You only need to put the not for replication switch on the publisher when a replication process is going to update the PUBLISHER and you are not using Immediate Updating susbcribers.
These cases are 1) republishing, 2) merge, 3) bi-directional transactional, 4) queued.
You will need them on the subscriber if you want to have the replication process maintain the identity ranges it is publishing to the subscriber, but the subscriber will maintain identity ranges when a user process (Ie anything but a replication process)
does the updates.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Replication

I was working on a project that use local and remote SQL server. In order to keep the database up-to-date I wanted to implement replication on the SQL servers. But unfortunately thetransaction replication which meet my requirement best is disabled on the replication configuration module (snapshoot and merge replications are active). Is there any way I can make the transaction replication enabled. I know it was supposed to be enabled by default. I’m using Windows 2003 server and SQL server 2000.

Sincerely

Do you mean you can't select the Transactional Replication as Replication Type when configure Publication?|||Yes, that is what i mean.|||Sorry I haven't heard of such issue. If you have no better choice, you may reinstall the client tools to see whether it helps.