Help on transactional replication

Hi,
I am a newbie dba and relatively new to replication area.
I am in a process of setting up a transactional replication on sql 2000
for a reporting purpose to offload this activity on the production
server.
My basic goal is to enhance the performance on the production server. I
thought replicating to a reporting server would be an ideal choice
using transactional relication scheduled on an hourly basis.
following is the setup i am trying to achieve...
I am using Production server which is more like an OLTP server as a
publisher, Distributor is going to be on the same server as of
subscriber because of less resources. Moerever I was suggested that
running the Distributor & Pulling the subscription would result in
better performance for both the servers.
Now my questions are...
1. Am i going in a write direction to achieve my task?
2. what are the precautions one should take before making the
"production server" as publisher is transactional relication?
3. Do i need to increase the size of db's and logs on production
server?
4. How should we know that replication is causing stress on production
server?
5. what is the ideal size of database and the transaction log that is
going to hold the replicated data?
6. How to remove replcation from a database completely in case if it
fails and causing issues on productions?
Thanks very much all. I will really appretiate your tips and
suggestions.
AK
Firstly, the method you are following is fairly commonly used. The size of
the logs and databases depends entirely on your initial data size and
transaction volume, so as long as you have the logs set to a large size or
autogrow, you'll just have to monitor them for your circumstances. The
impact of replication on your system can be monitored using the normal
counters in windows system monitor (processor usage, ram usage and disk
usage - if you are not familiar with these I can dig out some links). To
prevent the use of the replication overhead in the event of a slowdown, you
could initially stop the log-reader agent then remove the publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply paul,
I have left the database/log size to autogrow and will keep an eye on
their size.
also, you haven't answered clearly whether am i doing a right thing by
keeping the distributor on a subscriber server instead of keeping it
with publisher in production server?
Could you also please indicate what sort of implication the production
server will have with this kind of setup? I have to make it clear to my
manager before i setup this thing on live environment.
And the basic question, I would think the initial snapshot should be
done out of business hours? If yes, how often i need to take the
snapshot or just reading the transaction logs would be enough as long
as there are no schema changes on the published tables?
Will the snapshot agent gets the entire shcema and data from the
published tables every time it runs or only at once in the begining in
transactional replication?
and the last thing, If you please don't mind can you show me some links
on how to read and understand processor and memory usage things.
Thanks very much
AK.
Paul Ibison wrote:
> Firstly, the method you are following is fairly commonly used. The size of
> the logs and databases depends entirely on your initial data size and
> transaction volume, so as long as you have the logs set to a large size or
> autogrow, you'll just have to monitor them for your circumstances. The
> impact of replication on your system can be monitored using the normal
> counters in windows system monitor (processor usage, ram usage and disk
> usage - if you are not familiar with these I can dig out some links). To
> prevent the use of the replication overhead in the event of a slowdown, you
> could initially stop the log-reader agent then remove the publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||For a 1:1 system, putting the distributor on the subscriber box is a valid
choice. I prefer to have the distributor on the production box so i have
control over the backups centrally, and will therefore take the extra hit
there. Pull distribution agents or push will run on the subscriber in your
case. You could have the distributor on the production box and use a pull
subscriber to have the agent still run on the subscriber's box. However
these are not equivalent, and there will be a lot of disk read/write access
to the distribution database which generally contends with the production
database as it resides on the same disk array - hence this is why your
choice makes sense.
For the implications of the log-reader reading the transaction log and
writing the transactions to the distribution database - only you can
determine this for your setup. Best practices are to set up this in a lab
environment mimicing the production site.
Only run the snapshot agent ONCE and then disable it. You'll need to run it
manually for adding new articles and in the case of reinitialization
(hopefully never ).
For links, I'll dig some out for you...
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
Thanks very much for your valuable tips. I really appreciate your time
on this.
Now one last thing i want to ask, once i have disabled the snapshot
agents, then how the log reader agent will read whenever there are any
transactions happend on publisher?
I want toschedule the job to read the transactions every hour. Shall i
schedule the log reader agent or the distribution agent to achieve this
task?
Many Thanks.
AK
Paul Ibison wrote:
> For a 1:1 system, putting the distributor on the subscriber box is a valid
> choice. I prefer to have the distributor on the production box so i have
> control over the backups centrally, and will therefore take the extra hit
> there. Pull distribution agents or push will run on the subscriber in your
> case. You could have the distributor on the production box and use a pull
> subscriber to have the agent still run on the subscriber's box. However
> these are not equivalent, and there will be a lot of disk read/write access
> to the distribution database which generally contends with the production
> database as it resides on the same disk array - hence this is why your
> choice makes sense.
> For the implications of the log-reader reading the transaction log and
> writing the transactions to the distribution database - only you can
> determine this for your setup. Best practices are to set up this in a lab
> environment mimicing the production site.
> Only run the snapshot agent ONCE and then disable it. You'll need to run it
> manually for adding new articles and in the case of reinitialization
> (hopefully never ).
> For links, I'll dig some out for you...
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log reader should run continuously and the distribution agent can be set
to run hourly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hello Paul,
Thanks very much for your help. I have successfully tested this setup
in test environment.It was spot on. I am hoping to implement this on
live very soon.
Thanks,
AK
Paul Ibison wrote:

> The log reader should run continuously and the distribution agent can be set
> to run hourly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .

Help on replication from SQL 2005 to DB2/AS400

I was trying to set up a replication process to move data from SQL 2005 to DB2/AS400.

To get it started, I have create a simplest table (say, PERSON) on SQL 2005 server, and the same table (PERSON) on DB2/AS400. I have only one column SSN (of type int). I have journaled the table on DB2 end. I was able to have successfully configured publisher (on SQL 2005) and subscriber (i.e. a pushed subscription). I used transactional replication. And I also installed HIS 2004 to have DB2OLEDB ready. I was able to use query/insert/delete against table on DB2 based the linked server (that I created for testing purpose).

I did a lot of reading on this and my impression is that replicating to DB2/AS400 is supported from SQL 2005. But I always have this error from replication monitor. Can anyone enlighten me what was wrong.

Command attempted:

CREATE TABLE MY_DB.PERSON(
SSN
INT NOT NULL)

(Transaction sequence number: 0x00000020000000F9000100000004, Command ID: 4)

Error messages:

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42710, SQLCODE: -601 (Source: MSSQL_REPL_DB2, Error number: -601)
Get help: http://help/-601

I assume you've read Books Online topic IBM DB2 Subscribers already? If so, try this KB article: http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;Q239458

|||

Hi Steve,

Couple questions?

Did you setup the replication through user interface or replication stored procedures?

It seems like that you are trying to publisher data from SQL server to an "existing" DB2 table, is this a safe assumption to make? If this is what you are attempting, UI does not support this scenario, you will have to set it up through the stored procedures.

Let me know if you need more help.

Gary

|||

Hi Gary and Greg:

1). To answer Gary's questions -

Yes. i setup replication through user interface (i.e. SQL 2005 management studio). Your assumption is correct. I was trying to replicate data from SQL 2005 to DB2/AS400.

I have always used UI to set up the replication process (like SQL to SQL). Unfortunately I have rarely used stored procedure to setup replication although i know it is doable. In another word, i do not have any hands-on experience yet to set up replication from stored procedures. Can you post some samples here or can you enlighten me in a different way please?

2) Greg - i have installed SQL 2005 SP1 on my machine. I wonder if i need to download the special fix that the article refers to.

I have read all of Books Online topics of IBM DB2 Subscribers. It is a kind of generic guidance but not specific enough to help solve my issue. Plus, some of description is a bit vague.

Thank you very much for your attention anyway. Please help me more if you can. I do appreciate it.

Steve

Thanks in advance.

|||

Hi Steve,

Here ia a sample script for setuping replication for DB2 subscriber without dropping and re-creating the table on DB2 subscriber side.

Please note:

For sp_addpublication, the following have to be set
@.sync_method=N'character',
@.enabled_for_het_sub='true'

For sp_addarticle, the following have to be set
@.creation_script=null,
@.pre_creation_cmd=N'none',
@.schema_option=0x00,
@.ins_cmd=N'SQL',
@.del_cmd=N'SQL',
@.upd_cmd=N'SQL',

For sp_addsubscription , the following have to be set
@.update_mode=N'read only',
@.subscriber_type=3

The above list is the ones I think are the more critical ones to get them right (hopefully I didn't miss anything). The sample will require some modification to your environment such as server name, user account, password and DB2 connection info.

After running the following script, if you chose to run the snapshot, logread, and distribution agents manually, I have included the command line statement at the bottom of the script.

And don't forget to create a table schema on the DB2 side first.

Let me know if this solve your problem.

Cheers,

Gary

/* This posting is provided as is. */

Sample Script

USE master
EXEC sp_adddistributor 'myservername'
go

EXEC sp_adddistributiondb 'distribution', @.data_folder='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA', @.data_file='ddata.dat', @.data_file_size=32, @.log_folder='c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA', @.log_file='dlog.dat', @.log_file_size=16, @.security_mode=0, @.login='sa', @.password='******'
go

EXEC sp_adddistpublisher @.publisher='myservername', @.distribution_db='distribution', @.security_mode=0, @.login='sa', @.password='******', @.working_directory='\\myservername\MSSQL\REPLDATA'
go

create database [pubdb]
go

pubdb..sp_replicationdboption 'pubdb', 'publish', 'true'
go

-- This table is used to store the DB2 connection string, it can save you to time to copy/paste the string.
create table [_provider] ([string] varchar(1000))
go
-- Please replace with your DB2 connection string
insert into [_provider] ([string]) values ('Provider=DB2OLEDB;........')
go

use pubdb
go
-- Please replace with a valid user account
exec sp_addlogreader_agent @.job_login='MYDOMAIN\USERID' , @.job_password='******' , @.publisher_security_mode=1
go
-- Create a sample table on SQL server, assuming a same table called [MYTEST] already exist on DB2
create table [MYTEST] ([PKCOL] tinyint primary key)
insert into [MYTEST] ([PKCOL]) values (77)
go

exec sp_addpublication
@.publication=N'DTYPETEST',
@.sync_method=N'character',
@.retention=0,
@.allow_push=N'true',
@.allow_anonymous=N'false',
@.autogen_sync_procs='false',
@.enabled_for_internet=N'false',
@.snapshot_in_defaultfolder=N'true',
@.compress_snapshot=N'false',
@.allow_subscription_copy=N'false',
@.add_to_active_directory=N'false',
@.repl_freq=N'continuous',
@.status=N'active',
@.independent_agent=N'true',
@.immediate_sync=N'true',
@.allow_sync_tran=N'false',
@.allow_queued_tran=N'false',
@.allow_dts=N'false',
@.replicate_ddl=0,
@.allow_initialize_from_backup=N'false',
@.enabled_for_het_sub='true'
go

exec sp_addarticle
@.publication=N'DTYPETEST',
@.article=N'MYTEST',
@.source_owner=N'dbo',
@.source_object=N'MYTEST',
@.type=N'logbased',
@.description=null,
@.creation_script=null,
@.pre_creation_cmd=N'none',
@.schema_option=0x00,
@.identityrangemanagementoption=N'none',
@.destination_table=N'MYTEST',
@.destination_owner=null,
@.vertical_partition=N'false',
@.ins_cmd=N'SQL',
@.del_cmd=N'SQL',
@.upd_cmd=N'SQL',
@.force_invalidate_snapshot=1,
@.fire_triggers_on_snapshot='false'
go

exec sp_addsubscription
@.publication=N'DTYPETEST',
@.subscriber=N'DB2NTOLEDB',
@.destination_db=N'(default destination)',
@.subscription_type=N'Push',
@.frequency_type=1,
@.frequency_interval=0,
@.frequency_relative_interval=0,
@.frequency_recurrence_factor=0,
@.frequency_subday=0,
@.frequency_subday_interval=0,
@.active_start_time_of_day=0,
@.active_end_time_of_day=0,
@.active_start_date=0,
@.active_end_date=20070101,
@.enabled_for_syncmgr=N'False',
@.offloadagent=0,
@.sync_type=N'Automatic',
@.article=N'all',
@.update_mode=N'read only',
@.dts_package_location=N'Distributor',
@.subscriber_type=3
go

declare @.provstr varchar(1000)
select @.provstr=[string] from [master]..[_provider]
exec sp_addpushsubscription_agent
@.publication='DTYPETEST',
@.subscriber='DB2NTOLEDB',
@.subscriber_db='(default destination)',
@.subscriber_security_mode=0,
@.subscriber_login='Your DB2 User ID',
@.subscriber_password='Your DB2 Password',
@.job_login='Windows account or SQL server account',
@.job_password='******' ,
@.subscriber_provider_string=@.provstr,
@.subscriber_provider='DB2OLEDB',
@.subscriber_catalog='DB2 catalog'

--"c:\Program Files\Microsoft SQL Server\90\COM\SNAPSHOT.EXE" -Publisher MYSERVER -PublisherDB pubdb -Publication DTYPETEST -Distributor MYSERVER -OutputVerboseLevel 1 -DistributorLogin sa -DistributorPassword password

--"c:\Program Files\Microsoft SQL Server\90\COM\LOGREAD.EXE" -Publisher MYSERVER -PublisherDB pubdb -Distributor MYSERVER -OutputVerboseLevel 1 -DistributorLogin sa -DistributorPassword password

--"c:\Program Files\Microsoft SQL Server\90\COM\DISTRIB.EXE" -Subscriber DB2NTOLEDB -Publisher MYSERVER -PublisherDB pubdb -Distributor MYSERVER -Publication DTYPETEST -SubscriberType 3 -OutputVerboseLevel 2 -DistributorLogin sa -DistributorPassword password
THE END -

|||

Wow, a super nice reply and posting. Thanks much Gary. Seems you are quite knowledgeable in using replication to DB2.

I will give it a shot today to see how it works.

- Steve

Incidently I wonder why Microsoft does not elaborate this on Books Online in detailed.

|||

Hi Gary -

Thank you for the very detailed info and sample scripts. That is very helpful in getting me started to use script to create replication/subscription.

I have tried over and over, and unfortunately i was not able to get it to work. Shawn told me that he talked to you and you two are going to help me out of this trouble.

Thanks a lot.

Steve

|||

What is the problem you are encountering? Can you provide more detail? At what point does it fail? Maybe the schema of your table may help.

Thanks,

Gary

|||

Hi Gary -

I worked on this with a couple of your Microsoft engineers last Friday. Finally I got it to work using scripts. But there are many limitations for replication from SQL server 2005 to DB2 as compared with the replication from SQL 2005 to SQL 2005. The following are the main limitations i found:

1) The replication can NOT replicate schema change from SQL server to DB2. It makes replication to fail.

2) type problems – when replicating data from a type of either nchar(10) or nvarchar(10) to BD2's GRAPHIC(10). The replication ALWAYS FAILED.

QUESTIONS:

Currently I tested DB2OLEDB using connection string like “……Units of Work=RUW; … “, I wonder how replication supports data consistence and reliability. In case of replication failure, how does the rollback work? Without using distributed transcation enabled (like above), how the replication support guaranteed data delivery ?

- Steve

|||

My SQL side table scema:

CREATE TABLE [dbo].[MYTEST](

[PKCOL] [tinyint] NOT NULL,

[NEW_COL1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_MYTEST_NEW_COL] DEFAULT (N'nchar'),

[NEW_COL2] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_MYTEST_NEW_COL2] DEFAULT (N'nvarchar'),

CONSTRAINT [PK__MYTEST__7C8480AE] PRIMARY KEY CLUSTERED

(

[PKCOL] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

The DB2's table scema is:

CREATE TABLE PUBDB.MYTEST (

PKCOL INTEGER DEFAULT NULL ,

NEW_COL1 GRAPHIC(10) CCSID 835 DEFAULT NULL ,

NEW_COL2 GRAPHIC(10) CCSID 835 DEFAULT NULL ,

CONSTRAINT PUBDB.Q_PUBDB_MYTEST_PKCOL_00001 PRIMARY KEY( PKCOL ) ) ;

|||

1) SQL server does not support DDL (schema change) for DB2 subscriber.

2) Replication failed from nchar(10) or nvarchar(10) to DB2's GRAPHIC(10) is because of the default datatype mapping from SQL to DB2. By default, both nchar(10) and nvarchar(10) are mapped to VARCHAR on DB2 side. I believe this can be changed. Please check out the following article,

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/repref9/html/bfd2e111-cada-4304-80a8-c1e1061573b5.htm

When you said rollback, do you mean rollback on SQL publisher? Or DB2 subscriber?

Cheers,

Gary

The posting is provided as is

|||

1) SQL server does not support DDL (schema change) for DB2 subscriber.

IT IS REALLY TRUE. I HAVE TRIED THIS AND IT DID EXACTLY THE SAME AS WHAT YOU SAID HERE. ONE OF YOUR MICROSOFT ENGINEER ALSO PROVED THIS AS WELL.

2) As of datatype mapping, i have tried to modify the following file:

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\SqlClientToDB2.xml

and

C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles\SqlClientToIBMDB2.xml

Can you confirm if these files are the ones that need to customize for datatype mapping?

3) Regarding rollback-- while in the process of replication of a record or a batch of several records, network fails (i.,e. broken), does publisher roll back those records that have been propergated to the subscriber? How does publisher know which record fails so that it knows where to start next time when network is restored.

Thanks.

Help on Agent Login Change

Hello,
I need to change the SQLAgent start up login in roder to begin a
replication scenario.
I have been using "system account" byt now wish to change it to
another one I set up - Publish. Publish is an administartor account,
is in the Logins section, etc.
When I changed the SQLAgent and tried to restart it I received an
error as follow -
Could not start the SQLServerAgent service on Local Computer.
The service did not return an error. This could be an internal Windows
error or and internal service error.
If the problem persists, contact your system administrator.
When I tried to go back to the system account I got the same error and
now cannot start the SQLAgent.
Anyone have any suggestions on how to resolve this ?
THANK YOU in advance !
Jon SpartanWhat do you have in SQLAgent.out file?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jon Spartan" <jonu@.ixtech.net> wrote in message
news:23ltqvsp52v2gm1n22nflf100g41o6ddrv@.4ax.com...
> Hello,
> I need to change the SQLAgent start up login in roder to begin a
> replication scenario.
> I have been using "system account" byt now wish to change it to
> another one I set up - Publish. Publish is an administartor account,
> is in the Logins section, etc.
> When I changed the SQLAgent and tried to restart it I received an
> error as follow -
> Could not start the SQLServerAgent service on Local Computer.
> The service did not return an error. This could be an internal Windows
> error or and internal service error.
> If the problem persists, contact your system administrator.
> When I tried to go back to the system account I got the same error and
> now cannot start the SQLAgent.
> Anyone have any suggestions on how to resolve this ?
> THANK YOU in advance !
> Jon Spartan|||Contents of SQLAgent.Out are as follows -
11/10/2003 8:56:32 AM - ? [129] SQLServerAgent starting under Windows
NT service control
11/10/2003 8:56:37 AM - ! [298] SQLServer Error: 18456, Login failed
for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
11/10/2003 8:56:37 AM - ! [000] Unable to connect to server;
SQLServerAgent cannot start
11/10/2003 8:57:01 AM - ? [098] SQLServerAgent terminated (normally)
On Mon, 10 Nov 2003 00:00:06 GMT, Jon Spartan <jonu@.ixtech.net> wrote:
>Hello,
>I need to change the SQLAgent start up login in roder to begin a
>replication scenario.
>I have been using "system account" byt now wish to change it to
>another one I set up - Publish. Publish is an administartor account,
>is in the Logins section, etc.
>When I changed the SQLAgent and tried to restart it I received an
>error as follow -
>Could not start the SQLServerAgent service on Local Computer.
>The service did not return an error. This could be an internal Windows
>error or and internal service error.
>If the problem persists, contact your system administrator.
>When I tried to go back to the system account I got the same error and
>now cannot start the SQLAgent.
>Anyone have any suggestions on how to resolve this ?
>THANK YOU in advance !
>Jon Spartan|||That is strange. The error message suggests that Agent tries to login as
LocalSystem, and it cannot do that because that isn't added as a Windows
Login. LocaSystem should be there, and also you mention that you aren't
using LocalSystem. Sorry, beats me...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jon Spartan" <jonu@.ixtech.net> wrote in message
news:jhbvqv08sbaehnl7lsrvk1ooa59090mpjf@.4ax.com...
> Contents of SQLAgent.Out are as follows -
> 11/10/2003 8:56:32 AM - ? [129] SQLServerAgent starting under Windows
> NT service control
> 11/10/2003 8:56:37 AM - ! [298] SQLServer Error: 18456, Login failed
> for user 'NT AUTHORITY\SYSTEM'. [SQLSTATE 28000]
> 11/10/2003 8:56:37 AM - ! [000] Unable to connect to server;
> SQLServerAgent cannot start
> 11/10/2003 8:57:01 AM - ? [098] SQLServerAgent terminated (normally)
>
> On Mon, 10 Nov 2003 00:00:06 GMT, Jon Spartan <jonu@.ixtech.net> wrote:
> >Hello,
> >
> >I need to change the SQLAgent start up login in roder to begin a
> >replication scenario.
> >
> >I have been using "system account" byt now wish to change it to
> >another one I set up - Publish. Publish is an administartor account,
> >is in the Logins section, etc.
> >
> >When I changed the SQLAgent and tried to restart it I received an
> >error as follow -
> >
> >Could not start the SQLServerAgent service on Local Computer.
> >The service did not return an error. This could be an internal Windows
> >error or and internal service error.
> >If the problem persists, contact your system administrator.
> >
> >When I tried to go back to the system account I got the same error and
> >now cannot start the SQLAgent.
> >
> >Anyone have any suggestions on how to resolve this ?
> >
> >THANK YOU in advance !
> >
> >Jon Spartan
>

Help needed to setup replication from SCRATCH

Hi,
I have tried and tried to solve my problems without any success.
Here I go...
I have 2 SBS 2000 Servers (Server A and Server B) in different offices 20
miles from each other.
SQL SP4 on each with ISA Ports open to 14446 (dont like 1433).
ODBC connects from my workstation to each server on port 14446 so
communication to database is ok.
I have only one database (800mb) in size that I need MERGE replication.
I have setup the client utility (in think) in server A (subscriber) with an
alias name of server B. Server B (distributor) simply wont talk to server
A? even though I know the connection is valid.
What I need is from scratch a walkthough on how to setup replication on both
servers. I have searched the net for weeks now, posted questions and read MS
articles but I'm not getting anywhere...
Please help.
TIM
Hopefully this article will help clarify things a bit:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi,
Have read that article and undertand most of it but there are 2 things
missing.
1. Client Network setup (alias setup on the subscriber). Do you have to do
this on the publisher as well?.
2. FTP...Seen a lot of this. Do I need to setup FTP on both servers as
well!...
Sorry but its not sinking in yet...
"Paul Ibison" wrote:

> Hopefully this article will help clarify things a bit:
> http://www.replicationanswers.com/InternetArticle.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I think these questions really relate to push/pull. The article refers to a
pull subscription. For push, the merge agent needs to see the subscriber so
set up the alias on the publisher. I'm reasoning this out as I don't have a
test environment here at present. For the FTP, the ports need to be opened
in your firewalls, but the FTP snapshot files reside on the publisher ie the
FTP ServerName referred to is the publisher's server name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||OK thanks.
Whats the correct way to complete the client network utility (IP/Computer
Name/Server Name etc).
Regards
"Paul Ibison" wrote:

> I think these questions really relate to push/pull. The article refers to a
> pull subscription. For push, the merge agent needs to see the subscriber so
> set up the alias on the publisher. I'm reasoning this out as I don't have a
> test environment here at present. For the FTP, the ports need to be opened
> in your firewalls, but the FTP snapshot files reside on the publisher ie the
> FTP ServerName referred to is the publisher's server name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||There's an example on my article - the only thing to be careful of is to set
it up for TCP/IP and use the actual server name as the alias name.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply but I've tried everything now and i'm still not any
connection from the subscriber to the publisher!!!!!.
I'm gonna spend a few more days on this and then give up...........
Thanks
TIM
"Paul Ibison" wrote:

> There's an example on my article - the only thing to be careful of is to set
> it up for TCP/IP and use the actual server name as the alias name.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||It sounds like firewall issues ie ports not opened, or opened in one
direction only, or your IP address not being in the allowed list etc.
Can you get a connection using SSMS/EM using the alias?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
Thanks for the reply.
On the ISA firewall i have set port 14446 as my default sql server port and
disabled 1433. I then published the server using this rule to forward to the
external interface (2 nics). I then set a new protocol def (14446) and bound
the rule to this.
Now I can access sql database using ODBC from my XP using this
port/password/username so I can only assume that the correct ports and comms
are getting through.
Now my SBS server computer name is CLIFT.Local and my alias for SQL is
CLIFTSERVER.
I have changed the service startup accounts to a user i created in AD giving
FULL admin rights. All services including the agent is running fine.
I have created a distributor/publisher on CLIFT server.
I have created the alias to CLIFTSERVER on my BROADSERVER (other SBS Server)
and in the client network utility it asks for computer name and server name
but I'v every possible combination and it still wont connect.
My routers are set to not respond to PING but when I disable this function
PING works so there is a connection there.
Is there anything else I need to do before I setup the subscriber?.
Please help...
"Paul Ibison" wrote:

> It sounds like firewall issues ie ports not opened, or opened in one
> direction only, or your IP address not being in the allowed list etc.
> Can you get a connection using SSMS/EM using the alias?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
>
|||OK - for the replication setup, the alias needs to be CLIFT. The server name
needs to be the IP address and you also specify the port, the network lib is
tcp/ip. Once set up like that, just try to register in SSMS using CLIFT as
the server name to test.
For the initialization you'll need to set up FTP or do a nosync
initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

help needed on merge replication

i have a merge replication. it runs well, the subscriber disconnect from the
publisher about 5 days, then i reconnect it to publisher. theoretically
speaking, the changes made during these 5 days made on both publisher and
subscriber will merge into one copy. but customer complain about data missing
on the subscriber. I run Red Gate data compare on publisher and subscriber
database, and find there are different records, missing records and
additional records. Any one know why this happened, and what is the way to
synchronize?
Thanks a lot.
Has the merge agent been run after the 5 days of disconnect?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Mahesh.
The problem is the merge agent was restarted after the 5 days of
discounnect. And has been alway connected till now.
"Mahesh [MSFT]" wrote:

> Has the merge agent been run after the 5 days of disconnect?
> Hope that helps
> --Mahesh
> [ This posting is provided "as is" with no warranties and confers no
> rights. ]
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||Is the merge agent failing or succeeding?
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A0E71256-C581-45A3-A888-8E181D4D6A7E@.microsoft.com...[vbcol=seagreen]
> Thanks Mahesh.
> The problem is the merge agent was restarted after the 5 days of
> discounnect. And has been alway connected till now.
> "Mahesh [MSFT]" wrote:
|||Check to see if there are conflicts in the conflict viewer. Also check to
see if you have compenstate_for_errors set to false. This setting will not
resolve some conflicts, but rather leave dml which originates on the
conflict loser in place. For example a pk violation will not be resolved
with the winning publisher's pk value, but the subscriber's one.
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
"green hand" <green hand@.discussions.microsoft.com> wrote in message
news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>i have a merge replication. it runs well, the subscriber disconnect from
>the
> publisher about 5 days, then i reconnect it to publisher. theoretically
> speaking, the changes made during these 5 days made on both publisher and
> subscriber will merge into one copy. but customer complain about data
> missing
> on the subscriber. I run Red Gate data compare on publisher and subscriber
> database, and find there are different records, missing records and
> additional records. Any one know why this happened, and what is the way to
> synchronize?
> Thanks a lot.
|||Thanks Hilary,
I checked the conflict viewer, there is a conflict and the publish win. Does
these explain the data inconsistence between publisher and discriber? I
failed to find where compenstate_for_errors is, for I am an new DBA, could u
give me some hint? should I set it to false or true?
"Hilary Cotter" wrote:

> Check to see if there are conflicts in the conflict viewer. Also check to
> see if you have compenstate_for_errors set to false. This setting will not
> resolve some conflicts, but rather leave dml which originates on the
> conflict loser in place. For example a pk violation will not be resolved
> with the winning publisher's pk value, but the subscriber's one.
> --
> 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
>
> "green hand" <green hand@.discussions.microsoft.com> wrote in message
> news:017EBCEF-416D-4300-A792-843BCC26C4A0@.microsoft.com...
>
>
|||It might. If compenstate for errors is set to true all databases will be
consistent. If not they won't. The compenstate for errors parameter is part
of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
true or false.
Also consult http://support.microsoft.com/kb/828637 for more info.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary,
> I checked the conflict viewer, there is a conflict and the publish win.
> Does
> these explain the data inconsistence between publisher and discriber? I
> failed to find where compenstate_for_errors is, for I am an new DBA, could
> u
> give me some hint? should I set it to false or true?
> "Hilary Cotter" wrote:
|||Hilary,
Thanks very much for help!
I found compensate_for_errors in a property of sp4. currently the publisher
and two subscribes are all under sp3. It seams the only way is to apply sp4
on all the 3 box. and then set it to ture. Am I right?
Thanks
"Hilary Cotter" wrote:

> It might. If compenstate for errors is set to true all databases will be
> consistent. If not they won't. The compenstate for errors parameter is part
> of sp_addmergearticle. Use sp_helpmergearticle to verify if this is set to
> true or false.
> Also consult http://support.microsoft.com/kb/828637 for more info.
> --
> 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
>
> "green hand" <greenhand@.discussions.microsoft.com> wrote in message
> news:8CB5F530-B61D-404B-AC8B-3888509B13CE@.microsoft.com...
>
>
|||That is correct.
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
"green hand" <greenhand@.discussions.microsoft.com> wrote in message
news:A5E1B1DF-36A1-43FD-8F7A-87B46CB2E66A@.microsoft.com...[vbcol=seagreen]
> Hilary,
> Thanks very much for help!
> I found compensate_for_errors in a property of sp4. currently the
> publisher
> and two subscribes are all under sp3. It seams the only way is to apply
> sp4
> on all the 3 box. and then set it to ture. Am I right?
> Thanks
> "Hilary Cotter" wrote:

help needed in replication..

Hi All,

I am new to Sql Server.I have to create snapshot replication between SQL 2000 and 2005.The Publisher should be sql 2000 and the subscriber is sql 2005.I need ur valuable inputs as how to start.

Hi,

I would start with the replication section of SQL BOL (http://msdn2.microsoft.com/en-us/library/ms151198.aspx) for general information on snapshot replication. To use multiple version of SQL in a replication topology, you can refer to this page (http://msdn2.microsoft.com/en-us/library/ms143241.aspx) for more information.

Thanks,

Peng

|||

Hi Peng,

Thanks for you reply..

I have a few more queries on snapshot replication

Steps followed:

1)created distributor and publisher at the SQL Server 2000 end.

3)Create a Publication Database at SQL Server 2000 end.

2)Created subscribtion for the Publication at the SQL Server 2005 end.

Will these steps do for a Snap-shot replication?

Do i need to configure a distributor at the SQL Server 2005 end also?

How do i check whether the replication is success?

If i try to synchronize from the SQL server 2000 side,Its asking for a username,password...and its not accepting what ever login,passwd i give..

The Error Message i get is :

A Connection could not be established to <server 2005>

Reason:Login failed for the user “<user name>”.The user is not associated with a trusted SQL Server connection.

…………….

pls help me with this.

thankx

Help Merge Replication

Hi to all,

I'm writing an application with Comact Framework 2.0 and I'd like to use merge replication to synchronize data among the sever and 25 Pocket PC.

The DataBase contains static tables (for configuration) and dynamic tables that I want to publish.

Configuration tables are small so no problem to publish them. The problem is with dynamic table and that's my first question:

One of these dynamic tables contains 150,000 records and data inside it (I can't use filter because all data must be "seen" to all POcket PC) should be published on each Pocket PC so: Is it possible to use merge replication with such big table?

Or is there another method to treat this kind of problem?

Should I use merge replication only for configuration tables?

If it's possible to use merge replication, what's the best hardware configuration for my server?

For example:

1) One PC for IIS and one for SQL Server with 2 CPU

2) or two PC in NLB so that request from Pocket PC's to IIS are balanced?

I'musing SQL Server 2005 SP1 and Windows CE 5.0

TIA.

Flavio

there's no table size limit for a table to be published by merge replication, 150k rows should not be a problem. Regarding your hardware configuration, it's up to you as it will depend if you're getting bottlenecks or not. Test with one configuration to see if there are any bottlenecks that can be relieved by the other configuration.

Help me: About Sql server Internet merge Replication

i am working on merge replication over internet
i configure each and everything as Paul told in artical
i configure TCP\IP port at client network utility as 1433, also server at
server network utility as 1433
also FTP port at publisher properties is 21
but it is not working
it show error as "sql server not exist or access denied"
i configure client computer using client network utility,
server computer by server network utility
also proper publication and subcriber and ftp root
please solve my problem
need u'r kind help
thanking you
amy
I'm still pretty new at some of this replication stuff. But I went and
pinged the subscriber from the publisher and vice versa. Then I set up
Aliases through the configuration tool using TCP\IP and setting the alias
name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
know if this is helpful, but it sounds like to me that your servers just
can't see one another and an alias, using TCP/IP or even a linkedserver may
help.
Good Luck!!
Scott E. Hunley (MCAD)
Measure Twice, Cut Once!!!
"amy" wrote:

> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy
|||thanks for reply
please tell me how to set up
Aliases through the configuration tool using TCP\IP
i done pinging from subsciber from publisher and also publisher to subsciber
thanking you
amy
"Scott Hunley" wrote:
[vbcol=seagreen]
> I'm still pretty new at some of this replication stuff. But I went and
> pinged the subscriber from the publisher and vice versa. Then I set up
> Aliases through the configuration tool using TCP\IP and setting the alias
> name equal to that of the Server (i.e. My local server: S-HUNLEY). I don't
> know if this is helpful, but it sounds like to me that your servers just
> can't see one another and an alias, using TCP/IP or even a linkedserver may
> help.
> Good Luck!!
> --
> Scott E. Hunley (MCAD)
> Measure Twice, Cut Once!!!
>
> "amy" wrote:
|||If you were able to ping the machines successfully from one another then
that's a good sign. Now, in the Configuration Tool:
1. Right-Click the Alias node on the tree.
2. Enter the Name of the server you are communicating with (make sure these
are exactly the same.
3. Enter a port number, by default SQL uses 1433
4. choose TCP/IP
5. Enter the IP address of the server your communication with.
That should give you a way of communicating to the server.
Try that and let me know.
Scott E. Hunley (MCAD)
Measure Twice, Cut Once...
"amy" wrote:
[vbcol=seagreen]
> thanks for reply
> please tell me how to set up
> Aliases through the configuration tool using TCP\IP
> i done pinging from subsciber from publisher and also publisher to subsciber
> --
> thanking you
> amy
>
> "Scott Hunley" wrote:
|||I got the same error all morning while working with my Replication problems.
I got past this error by going into the properties for the publication and
adding my IUSR_MACHINENAME account to the publication access list. It started
working like a champ. This is with SQL 2005, I'm not sure of how to do it
with SQL 2000.
Hope this helps.
"amy" wrote:

> i am working on merge replication over internet
> i configure each and everything as Paul told in artical
> i configure TCP\IP port at client network utility as 1433, also server at
> server network utility as 1433
> also FTP port at publisher properties is 21
> but it is not working
> it show error as "sql server not exist or access denied"
> i configure client computer using client network utility,
> server computer by server network utility
> also proper publication and subcriber and ftp root
> please solve my problem
> need u'r kind help
> --
> thanking you
> amy