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 N - Tier architecture ?

Hi
1. I am in process of designing N-Tier Application using ASP.NET. Can
anyone guide me the right material or microsoft guidelines document
which I can used in designing the N-Tier application.
2. I would also like to know whether to use Web Services or .Net
Remoting in designing N-Tier application
3. General 3 Tier architecture has 3 Tier : Presentation Layer ,
Business Layer and Database Layer
How this 3 layers are seperated out in N-Tier architecture.
Help would be very much appreciated.
Thanks
Silent Ocean
This is a little off-topic for this newsgroup.
However, http://msdn.microsoft.com/practices/ is a pretty good starting
poitn for architectural/design guidance for MS technologies.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Silent Ocean" <silentocean555@.yahoo.com> wrote in message
news:%23e4PMIhnFHA.2852@.TK2MSFTNGP15.phx.gbl...
Hi
1. I am in process of designing N-Tier Application using ASP.NET. Can
anyone guide me the right material or microsoft guidelines document
which I can used in designing the N-Tier application.
2. I would also like to know whether to use Web Services or .Net
Remoting in designing N-Tier application
3. General 3 Tier architecture has 3 Tier : Presentation Layer ,
Business Layer and Database Layer
How this 3 layers are seperated out in N-Tier architecture.
Help would be very much appreciated.
Thanks
Silent Ocean
sql

Help on N - Tier architecture ?

Hi
1. I am in process of designing N-Tier Application using ASP.NET. Can
anyone guide me the right material or microsoft guidelines document
which I can used in designing the N-Tier application.
2. I would also like to know whether to use Web Services or .Net
Remoting in designing N-Tier application
3. General 3 Tier architecture has 3 Tier : Presentation Layer ,
Business Layer and Database Layer
How this 3 layers are seperated out in N-Tier architecture.
Help would be very much appreciated.
Thanks
Silent OceanThis is a little off-topic for this newsgroup.
However, http://msdn.microsoft.com/practices/ is a pretty good starting
poitn for architectural/design guidance for MS technologies.
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"Silent Ocean" <silentocean555@.yahoo.com> wrote in message
news:%23e4PMIhnFHA.2852@.TK2MSFTNGP15.phx.gbl...
Hi
1. I am in process of designing N-Tier Application using ASP.NET. Can
anyone guide me the right material or microsoft guidelines document
which I can used in designing the N-Tier application.
2. I would also like to know whether to use Web Services or .Net
Remoting in designing N-Tier application
3. General 3 Tier architecture has 3 Tier : Presentation Layer ,
Business Layer and Database Layer
How this 3 layers are seperated out in N-Tier architecture.
Help would be very much appreciated.
Thanks
Silent Ocean

Help me with this problem, please!

I have a question..
I had to put times in this process but I don't have idea
how to do this.
The enterprise use SQL Server 2000 and pages ASP, and
their server is in Corea.
Could be by DTS in SQL server, but I'm not sure it it
possible with DTS..
Section, send automatically post card
This process send post card in birthdays of employees.
This will run dialy for to send automatically post card to
employes who is him birthday that day..
Somebody knows how to do that and could be recommend
another way if it's not possible with DTS..
Please, send me the answer to my mail
bmartinez@.cosapisoft.com.pe
Thanks anyway
Grettings
BrunoDepending on the setup you have to accomplish this task,
you can use a DTS package scheduled every day to execute
the send postcard process.
Please let me know if you have any questions.
Edgardo Valdez
MCSD, MCDBA, MCSE, MCP+I
http://www.edgardovaldez.us/
>--Original Message--
>I have a question..
>I had to put times in this process but I don't have idea
>how to do this.
>The enterprise use SQL Server 2000 and pages ASP, and
>their server is in Corea.
>Could be by DTS in SQL server, but I'm not sure it it
>possible with DTS..
>Section, send automatically post card
>This process send post card in birthdays of employees.
>This will run dialy for to send automatically post card
to
>employes who is him birthday that day..
>Somebody knows how to do that and could be recommend
>another way if it's not possible with DTS..
>Please, send me the answer to my mail
>bmartinez@.cosapisoft.com.pe
>Thanks anyway
>Grettings
>Bruno
>
>.
>