Questions about setting up transactional replication between SQL 2000 and 2005...

Hi,

I'm planning to setup transactional replication from a remote SQL 2000 server (Standard Edition) (publisher) and a local SQL 2005 (Standard Edition) server (subscriber).

The replication only needs to be one-way (from publisher to subscriber). The link between the two is a shared 2Mb SDSL line, which has heavy use so can be fairly slow.

My questions are:

1. Is transactional replication possible from 2000 to 2005, in the STANDARD editions?

2. We want the subsriber database to be updated fairly regularly throughout the day. Which replication method would you recommend given my requirements? Snapshot, transactional or merge?

3. I understand that when you setup transactional replication, it creates another database (pubs). Is that correct? I'm sure I read somewhere that it will change the structure of the database being subscribed to - is that true? I don't want my source database to change!

4. Finally we want to be able to run reports etc (read only) on the subscriber database (thus taking the load off the remote database). Will this be possible? Or will the subscriber database be in a constant 'updating' state?
LVL 1
grjitdeptAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SQL_SERVER_DBACommented:
Transactional replication is fully supported in the Standard Edition. Your Standard Edition instance can be a Publisher, Distributor, and Subscriber. Transactional replication works by sending changes to the subscriber as they happen so this probably would be best for your scenario. Your source will not change but there are some exceptions with schemas...
(1) Changing the width of a column: Not allowed - needs reinitialisation or the combination of sp_repladdcolumnsp_repldropcolumn mentioned in the general section.
(2) Dropping a constraint on the published table - allowed for a check constraint this can be dropped at the publisher but is not replicated.
(3) creating a new constraint on the published table - allowed but not replicated
(4) dropping an index on the published table, would it be replicated to the subscriber - Allowed but not replicated.
(5) creating a new index on a published table, will this be replicated or an index would need to be created on the Subscriber database - allowed but not replicated.
You okay to run reports on either

For those actions allowed on the publisher but not replicated, you could use On-Demand Script Execution (or linked servers etc).

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrandonGalderisiCommented:
My questions are:

1. Is transactional replication possible from 2000 to 2005, in the STANDARD editions?
Absolutely yes

2. We want the subsriber database to be updated fairly regularly throughout the day. Which replication method would you recommend given my requirements? Snapshot, transactional or merge?
Transactional:  Merge replication will require your data model to change in order to add a timestamp column to track the state of a record for the merge reconciliation.  Transactional replication will only send the changes to the tables on which you have created publications.

3. I understand that when you setup transactional replication, it creates another database (pubs). Is that correct? I'm sure I read somewhere that it will change the structure of the database being subscribed to - is that true? I don't want my source database to change!
No.  It will create a distribution database, called Distribution by default, which will be a holding area for transactions to be replicated.  It will not require changes to the data model on your source.  

4. Finally we want to be able to run reports etc (read only) on the subscriber database (thus taking the load off the remote database). Will this be possible? Or will the subscriber database be in a constant 'updating' state?
You can run reports as much as you would like.  You can even do some minor logging to an additional table in the subscriber database so that you can see the current state of all replicated tables.  Basically, at the bottom of each replication procedure, put a small line like this below.
Update dbo.ReplicationLog
set LastReplicationDate=getdate()
where tableName = 'theNameOfTheTable'
if @@rowcount=0
insert into replicationLog(TableName,LastReplicationDate)
select 'theNameOfTheTable',getdate()

Open in new window

0
moonraker169Commented:
This is exactly the scenario i was wondering about.

Thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.