Running A Perfect Transactional Replication

I am just wondering what would the best computer specifications for running a perfect transactional replication. The database would have around 50 transactions per second.
Pentium 4 2.4GHz Dual Processor
2 GB RAM ECC
33.6 GB HDD X 5 @. 10,000 RPM
Windows Server 2003
Anyone with any suggestions or maybe I left out some settings to configure?
Thanking you people in advance.
In article <ECB8346D-41BB-422D-BB04-0FD41E3F12A1@.microsoft.com>, "=?Utf-8?B?Q1c=?=" <anonymous@.discussions.microsoft.com> wrote:
>I am just wondering what would the best computer specifications for running a
> perfect transactional replication. The database would have around 50
> transactions per second.
>Pentium 4 2.4GHz Dual Processor
>2 GB RAM ECC
>33.6 GB HDD X 5 @. 10,000 RPM
>Windows Server 2003
>Anyone with any suggestions or maybe I left out some settings to configure?
>Thanking you people in advance.
Across what connection?
WAN? 10Mbps? 1000Mbps?
How large are the transactions?
How many table do they affect?
How well normalised is the data?
Are these inserts, updates, or deletes?
|||In a LAN environment, across 100Mbps.
It has about 50 transactions per second.
It affects about 15 tables, about 5 are heavy ones. These five are constantly updated.
It has reached third-normalised form.
It has all three types of inserts, updates and deletes operations.
Thank you.

Run SSIS Package from SQL 2000

I am looking for a way to execute an SSIS package as the next step in the SQL 2000 Agent, with the first step being replication. Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?

Thanks

JWeil wrote:

... Can I reference dtexec.exe on the SSIS box to execute the package located in the file system?

Yes, should work. Agent in SQL 2000 has a limitation of just one proxy account, but if you are OK with this and the package can be run under this account, it should be fine.

|||

Thanks.

Would I need to install anything on the SQL 2000 server or just reference the executable on the 2005 box?

|||SSIS should be installed on the machine where the package is run, you can't run it by pointing to dtexec on remote share.

If you want to run the package on the same machine, where SQL 2000 is installed - install SSIS on this box.

If you want to run the package on another box where SQL 2005 is installed, you need some other way - e.g. create Agent job on SQL 2005 machine, invoke it remotely using Agent store procedures.

Run sp_resyncmergesubscription to solve un-sync data problem between subscribers and Publisher

Hello,

I have set up a merge replication with 4 subscribers and a publisher on SS05 sp2 on 5 window servers. After running my application on the replication with big data for a while, the data in some of tables of subscriber are out of sync from the publisher. I tried to run sp_resyncmergesubscription to fix the problem according to http://doc.ddart.net/mssql/sql2000/html/replsql/replmon_9pwh.htm. The procedure run successfully. However, the problem is still exist.

Does anyone else have this problem. Please help!

Did you try @.resync_type = 0

When you ran sp_resyncmergesubscription procedure and @.resync_type = 0 is intense operation...

|||

I tried with @.resync_type = 2.

Here is the script I have run at the Publisher:

exec sp_resyncmergesubscription NULL, NULL, 'ds3263a', 'red-dalmssql103', 'ds3263c',2, '2007-04-06 10:00:00.000'

|||

What tables are out of sync?

Do you have filtering? Is the data same for all subscribers?

Do you have any user triggers on tables?

|||

It might have more than one table out of sync. However, I know for sure one user table is out of sync between subscribers. The data in the table, rn_schedule_task, is in sync status between publisher and owning subscriber. For example,

Table – rn_schedule_task:

TaskID

Status

1

succeeded

2

running

3

failed

The status of task 1 showing as “succeeded” on publisher and owning subscriber, but showing as “running” on other subscriber(s).

We do not have any user triggers on the table that I know.

|||

I am not sure how you have gotten into this situation.

But for the rows that are different, you could dummy update these rows.

You could do: update m_schedule_task set Status=Status where TaskID=<task ID of the row that is out of sync>

Then run the merge agent and hopefully it should converge this time.

Run sp_resyncmergesubscription to solve un-sync data problem between subscribers and Publisher

Hello,

I have set up a merge replication with 4 subscribers and a publisher on SS05 sp2 on 5 window servers. After running my application on the replication with big data for a while, the data in some of tables of subscriber are out of sync from the publisher. I tried to run sp_resyncmergesubscription to fix the problem according to http://doc.ddart.net/mssql/sql2000/html/replsql/replmon_9pwh.htm. The procedure run successfully. However, the problem is still exist.

Does anyone else have this problem. Please help!

Did you try @.resync_type = 0

When you ran sp_resyncmergesubscription procedure and @.resync_type = 0 is intense operation...

|||

I tried with @.resync_type = 2.

Here is the script I have run at the Publisher:

exec sp_resyncmergesubscription NULL, NULL, 'ds3263a', 'red-dalmssql103', 'ds3263c',2, '2007-04-06 10:00:00.000'

|||

What tables are out of sync?

Do you have filtering? Is the data same for all subscribers?

Do you have any user triggers on tables?

|||

It might have more than one table out of sync. However, I know for sure one user table is out of sync between subscribers. The data in the table, rn_schedule_task, is in sync status between publisher and owning subscriber. For example,

Table – rn_schedule_task:

TaskID

Status

1

succeeded

2

running

3

failed

The status of task 1 showing as “succeeded” on publisher and owning subscriber, but showing as “running” on other subscriber(s).

We do not have any user triggers on the table that I know.

|||

I am not sure how you have gotten into this situation.

But for the rows that are different, you could dummy update these rows.

You could do: update m_schedule_task set Status=Status where TaskID=<task ID of the row that is out of sync>

Then run the merge agent and hopefully it should converge this time.

Run a Snapshot agent from a script or cmdline

I would like to be able to run a snapshot agent from a TSQL script or
from a cmd line. I have merge replication setup to several different
publications and would like to script all of the snapshot agents to run
on cmd rather on a schedule. I am using SQL Server 2000 with SP3.
Thank You in advance.
You could use sp_start_job from TSQL, or use the activeX control if you want
the command-line control.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .