Replication to the Non Default File Group

Hi,

I have a snapshot replication set up on a SQL 2K Server publishing to other SQL 2K servers. The publisher database has mutliple file groups - which is structured the same as the subscriber databases. The snapshot has been set to delete and recreate the tables on the subscribers - this is a performance decision as the tables are large and this is the better approach. The tables reside in their own file groups (but not the default). I want the replication agents to recreate these tables in the original file groups and not the default.

Is there a method within Replication to specify which file group to recreate the table in so it doesn't fill up the default file group?

Thanks in advance,

Pete

Hi Pete,

The tables on the subscriber will only be created on the default filegroup. The options are either to not drop tables and use delete instead or modify the default filegroup of the destination databases.

You can create a job that does the following:

1. sets the default filegroup of the subscriber database to secondary filegroup.

2. Agent starts and does the job.

3. Once agent job has finished and call a new job that will set the filegroup backup to primary.

In SQL 2005 you have the option to specify the scriptts that you want to execute before and after the snapshot is applied.

Jag

|||

There is a third option. After the snapshot has been created, edit the script files on the distributor which hold the CREATE TABLE commands and correct them to use the correct file groups.

You can also setup a T/SQL script which can be run after the snapshot has been delivered. Write a script which rebuilds the clustered indexes of the tables on the correct file group. By rebuilding the clustered index and moving it to another file group you will move the tables to that file group as well.

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?sql

Replication question

Hi,
How do I know if any database is a publishing database or not?
Same way, how do I know if a particular server is a subscriber or not?
I want to know, the quickest and fastest way to get the publication name
and/or subscription name from the server.
regards
KP
Hi,
U can select for the Sysdatabases table in master database.
In that with the Category column value we can identified whether its
published or subscribe.
Values are
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
Herbert
"Krishnaprasad Paralikar" wrote:

> Hi,
> How do I know if any database is a publishing database or not?
> Same way, how do I know if a particular server is a subscriber or not?
> I want to know, the quickest and fastest way to get the publication name
> and/or subscription name from the server.
> regards
> KP
|||Check out DATABASEPROPERTYEX() in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:2C60C225-68EF-4670-99E4-8817CD2A80B4@.microsoft.com...
Hi,
U can select for the Sysdatabases table in master database.
In that with the Category column value we can identified whether its
published or subscribe.
Values are
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
Herbert
"Krishnaprasad Paralikar" wrote:

> Hi,
> How do I know if any database is a publishing database or not?
> Same way, how do I know if a particular server is a subscriber or not?
> I want to know, the quickest and fastest way to get the publication name
> and/or subscription name from the server.
> regards
> KP
sql

Replication question

Hi,
How do I know if any database is a publishing database or not?
Same way, how do I know if a particular server is a subscriber or not?
I want to know, the quickest and fastest way to get the publication name
and/or subscription name from the server.
regards
KPHi,
U can select for the Sysdatabases table in master database.
In that with the Category column value we can identified whether its
published or subscribe.
Values are
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
Herbert
"Krishnaprasad Paralikar" wrote:

> Hi,
> How do I know if any database is a publishing database or not?
> Same way, how do I know if a particular server is a subscriber or not?
> I want to know, the quickest and fastest way to get the publication name
> and/or subscription name from the server.
> regards
> KP|||Check out DATABASEPROPERTYEX() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:2C60C225-68EF-4670-99E4-8817CD2A80B4@.microsoft.com...
Hi,
U can select for the Sysdatabases table in master database.
In that with the Category column value we can identified whether its
published or subscribe.
Values are
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
Herbert
"Krishnaprasad Paralikar" wrote:

> Hi,
> How do I know if any database is a publishing database or not?
> Same way, how do I know if a particular server is a subscriber or not?
> I want to know, the quickest and fastest way to get the publication name
> and/or subscription name from the server.
> regards
> KP

Replication question

Hi,
How do I know if any database is a publishing database or not?
Same way, how do I know if a particular server is a subscriber or not?
I want to know, the quickest and fastest way to get the publication name
and/or subscription name from the server.
regards
KPHi,
U can select for the Sysdatabases table in master database.
In that with the Category column value we can identified whether its
published or subscribe.
Values are
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
--
Herbert
"Krishnaprasad Paralikar" wrote:
> Hi,
> How do I know if any database is a publishing database or not?
> Same way, how do I know if a particular server is a subscriber or not?
> I want to know, the quickest and fastest way to get the publication name
> and/or subscription name from the server.
> regards
> KP|||Check out DATABASEPROPERTYEX() in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:2C60C225-68EF-4670-99E4-8817CD2A80B4@.microsoft.com...
Hi,
U can select for the Sysdatabases table in master database.
In that with the Category column value we can identified whether its
published or subscribe.
Values are
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
--
Herbert
"Krishnaprasad Paralikar" wrote:
> Hi,
> How do I know if any database is a publishing database or not?
> Same way, how do I know if a particular server is a subscriber or not?
> I want to know, the quickest and fastest way to get the publication name
> and/or subscription name from the server.
> regards
> KP

replication monitor error

When Replication monitor shows an error icon, I removed all the publication jobs completedly. After I reconfigured publishing, subscriber,Distribution, why the replication monitor still shows an error icon even though there is no replication job scheduled?

Thank you!

Thanks for using replmonitor.

Would you like provide more detailed repro steps? like how do you remove publicaiton jobs, delete jobs under sqlserveragent, or remove subscriptions? How did you reconfigure everything, did you disable distribution and publication, then recreate them?

Thanks

Yunjing

|||My problem has been resolved by restart sql server agent. Thank you!

replication monitor error

When Replication monitor shows an error icon, I removed all the publication jobs completedly. After I reconfigured publishing, subscriber,Distribution, why the replication monitor still shows an error icon even though there is no replication job scheduled?

Thank you!

Thanks for using replmonitor.

Would you like provide more detailed repro steps? like how do you remove publicaiton jobs, delete jobs under sqlserveragent, or remove subscriptions? How did you reconfigure everything, did you disable distribution and publication, then recreate them?

Thanks

Yunjing

|||My problem has been resolved by restart sql server agent. Thank you!

Replication in SQL Server 2000

Hi,
I have Server A(Publishing Server) and Server B(Subscribed Server)
which are in replication. The replication type used is "Transactional
Push Model".
My requirement is to
1. Drop Table1 which is available in both Server A and Server B
2. Drop Table2 which is available in Server A and not in Server B
I have to do this without stopping replication across servers.
Kindly share any information that could be useful.
Thank you,
Praveen
Praveen,
in your post you don't mention which table(s) is involved in replication. If
I assume that tables 1 and 2 are both replicated, then this is strange as it
means server B hasn't initialized yet. Anyway, in general you need to run
sp_droparticle. However this only is acceptable if there aren't any
subscribers to an article, so you run sp_dropsubscription to remove the
subscriptions to a particular article, then sp_droparticle. After that you
delete the table as per usual (drop table table1 etc).
HTH,
Paul Ibison