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 tables cleanup

Hi group,
I want to know if the system tables added by replication (MS_%) are
automatically purged in any way?
Or is my task to clean them regularly? If the former is true, what is the
recommended cleanup procedure?
Thanks,
Renato
Renato,
if you're referring to merge metadata, what you needed to do is actually
quiesce your entire enterprise, make sure everybody synchronizes all their
changes, and then pick a quiet time for a maintenance period and run the
stored procedure sp_cleanupmergemetadata. This changed in SP1 as now merge
is enabled to do automatic trimming of metadata based on the publication
retention period, which you can define (there is a profile parameter called
MetadataRetentionCleanup. It's just a Boolean, so you can set it to true or
false, 1 or 0, and turn retention-based cleanup on or off.).
If you're referring to system tables retained after you have removed a
subscriber, as long as the database is no longer involved in publication or
subscription, you can run sp_removedbreplication to reset the database.
HTH,
Paul Ibison
|||Fine! That explain all... Thank you!
Renato
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uiqOTuvIEHA.580@.TK2MSFTNGP12.phx.gbl...
> Renato,
> if you're referring to merge metadata, what you needed to do is actually
> quiesce your entire enterprise, make sure everybody synchronizes all their
> changes, and then pick a quiet time for a maintenance period and run the
> stored procedure sp_cleanupmergemetadata. This changed in SP1 as now merge
> is enabled to do automatic trimming of metadata based on the publication
> retention period, which you can define (there is a profile parameter
called
> MetadataRetentionCleanup. It's just a Boolean, so you can set it to true
or
> false, 1 or 0, and turn retention-based cleanup on or off.).
> If you're referring to system tables retained after you have removed a
> subscriber, as long as the database is no longer involved in publication
or
> subscription, you can run sp_removedbreplication to reset the database.
> HTH,
> Paul Ibison
>
|||replication maintains the replication tables automatically.
With merge replication you might want to run sp_mergecleanupmetadata every
once in a while paying attention to the caveats in
http://msdn.microsoft.com/library/de...repl3_7k89.asp
"Renato Aranghelovici" <renatoa@.rdslink.ro> wrote in message
news:OSs2TRvIEHA.3840@.TK2MSFTNGP11.phx.gbl...
> Hi group,
> I want to know if the system tables added by replication (MS_%) are
> automatically purged in any way?
> Or is my task to clean them regularly? If the former is true, what is the
> recommended cleanup procedure?
> Thanks,
> Renato
>
sql

Replication Problem!

Hello Group,
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.

For now i'm quite sure that i'll have to use replication to resolve
this issue.

But which type of replication should i use? Transactional, Merge or
Snapshot?

Please note that the client is running his website from the main
server, which is using the source SQL Server database - the one i'll
have to replicate.

Last time when i tried to register the destination server, at my
source server Enterprise Manager, it gave me an error failing to
register the destination server. When i asked the client, all he could
tell me was that both the servers are firewalled and that might have
been the problem.

So just tell me how should i go for it?

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***If you go the replicated route then your choice (of merge, snapshot or
transactional) is probably straight forward:

- Will they ever want to make changes on the remote server? If yes then you
must use merge replication.
- Otherwise got for Transactional.

Unless the database is small don't go for snapshot. Snapshot is fine for
just that, taking the odd snapshot, but isn't really suited if you want to
regularly propagate changes.

Regarding your problem connecting to the remove server. It is quite likely
that firewalls could be causing you a problem. I've not tried it, but
presumably the most secure way forward would be to create a vpn connection
between the two servers (firewalls may still be an issue) and then replicate
across the vpn link.

Hope this helps,

Brian.

www.cryer.co.uk/brian

"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:d5Sue.8$_r5.2081@.news.uswest.net...
> Hello Group,
> I'm working on two SQL server databases on two different remote
> servers. What client needs is that his database on one remote server
> should be mirrored to the other one. The changed should be propogated at
> regular intervals at the other server.
> For now i'm quite sure that i'll have to use replication to resolve
> this issue.
> But which type of replication should i use? Transactional, Merge or
> Snapshot?
> Please note that the client is running his website from the main
> server, which is using the source SQL Server database - the one i'll
> have to replicate.
> Last time when i tried to register the destination server, at my
> source server Enterprise Manager, it gave me an error failing to
> register the destination server. When i asked the client, all he could
> tell me was that both the servers are firewalled and that might have
> been the problem.
> So just tell me how should i go for it?
> Thanks in advance
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi again!

The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!

Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!

Shall i go for Merge Rep? I heard that there are conflicts in that?

So then what is the way out?

Thanks Brian, for replying!

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> The problem is that all the source tables dont have a pri key... and
> i have learnt that Trans Rep doesnt support replicating tables without
> Pri key. Besides there's no question of adding Pri Keys to the database
> which is already live!

Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.

> Second thing is that, the database size is in between 1 and 2GBs. That
> means even Snapshot Rep is not suitable!
> Shall i go for Merge Rep? I heard that there are conflicts in that?

I doubt that merge replication is possible without PKs. If rows can't
be identified, it's getting darn difficult to do replication.

Seems to me you have three options:

1) Add PKs to the database, and do transactional replication.
2) Regularly backup the database and restore on the other end.
3) Log shipping.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns968035B96389Yazorman@.127.0.0.1...
> debian mojo (debian_mojo@.yahoo.com) writes:
>>
>> The problem is that all the source tables dont have a pri key... and
>> i have learnt that Trans Rep doesnt support replicating tables without
>> Pri key. Besides there's no question of adding Pri Keys to the database
>> which is already live!
> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.
>> Second thing is that, the database size is in between 1 and 2GBs. That
>> means even Snapshot Rep is not suitable!
>>
>> Shall i go for Merge Rep? I heard that there are conflicts in that?
> I doubt that merge replication is possible without PKs. If rows can't
> be identified, it's getting darn difficult to do replication.
> Seems to me you have three options:
> 1) Add PKs to the database, and do transactional replication.
> 2) Regularly backup the database and restore on the other end.
> 3) Log shipping.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

I fully agree with everything Erland has said.

You may not currently have primary keys, but surely there are fields that
you are treating as unique? How do you currently uniquely identify a record?
A primary key can be defined using more than one field.

Treat Erland's comment as a dire warning:

> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.

Its put very well, and spot on.

Brian.

www.cryer.co.uk/brian|||Hello Erland and Brian,
Thanks for the reply!

The problem is that there are around 36 user tables in the source
database and only 5 of them have pri keys, the rest are either using
foriegn keys or dont have any kinda keys at all!

Is it good to have pri keys on all tables in the database.

Please do note that the database is for a website and it is estimated
that it will hit 10GB in the first one month!

Is there any chance of replication without compromising the database
integrity or shall i look for any other options such as log shipping?

Please advise!

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Please do note that the database is for a website and it is estimated
> that it will hit 10GB in the first one month!

Of course with no Pkeys, the chances for duplicates increase, and so
will the database size.

> Is there any chance of replication without compromising the database
> integrity or shall i look for any other options such as log shipping?

If you don't want to add primary keys (and save the database from a
disaster furtther afield) log shipping or backup/restore is what you
have to look into.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks again, Erland,

Since the database has problems with Pri keys, i'm thinking about Log
Shipping as an alternative.

But as it is mentioned in BOL that for LS to work, it's necessary to
configure three servers:
1. Source Server
2. Monitor Server
3. Destination Server.

Now the destination and the source databases can be on the same
server, but it is mandatory to keep the monitor server separate.

The problem with this approach is that i've access to only two servers
- one source and one destination. I wont have the privilege of having
another one just for setting up the monitor server!

What should i do?

Thanks in advance!
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Since the database has problems with Pri keys, i'm thinking about Log
> Shipping as an alternative.
> But as it is mentioned in BOL that for LS to work, it's necessary to
> configure three servers:
> 1. Source Server
> 2. Monitor Server
> 3. Destination Server.
> Now the destination and the source databases can be on the same
> server, but it is mandatory to keep the monitor server separate.
> The problem with this approach is that i've access to only two servers
> - one source and one destination. I wont have the privilege of having
> another one just for setting up the monitor server!

Looks like you are starting to get some good arguments: "Either we
fix primary keys to the database, or we go and buy some more hardware,
else we can't run this show".

I will have to admit that the need for third machine was news to me,
but I have never set up log shipping myself.

Actually, add PKeys to those tables does not have to be a killer
work. If there are unique indexes, just drop these and recreate
them as primary keys. If there are not any primary keys, just add
an uniqueidentifier with the default of NEWID() to the tables, and
make that the primary key (nonclustered!). That does not really help
to make the data model any better, but at least you can set up
replication over this dying grace.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
That's true that moving away from the problem is not a solution. But
you see that the database i'm talking about is a 24 x 7 database, and
i'm not pretty sure how apps that are using this db.

If i do pri keys to the tables, is there any possibility that the apps
may suffer is some way.

I do agree that in most of the cases there's no harm... but you see
the database is a production one and i'm really paranoid about it's
safety!

I hope you understand!

BTW, your advice was an eye-opener for me.

So what do you suggest? what are the other things i should take care
of before adding the pri keys?

Debian

*** Sent via Developersdex http://www.developersdex.com ***|||what are the other things i should take care of on the app side, before
adding the pri keys?

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:aeawe.4$jU.2182@.news.uswest.net...
> Thanks Erland,
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.
> I do agree that in most of the cases there's no harm... but you see
> the database is a production one and i'm really paranoid about it's
> safety!
> I hope you understand!
> BTW, your advice was an eye-opener for me.
> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***

Don't make changes to a live production database unless you have tested it
first and are comfortable about the changes you are going to make. You NEED
primary keys, but don't make the assumption that adding a new primary key
won't break one of your applications. If all you are doing is redefining an
existing unique index as a primary key then that shouldn't hurt anything,
but if you are adding a new field as a primary (default NEWID as per
Erland's suggestion [a very good suggestion by the way]) then that does have
the potential to break something - I think its only likely to break badly
written code, but the potential is there, so test it first.

Take a copy, add your primary keys to that and then run through the whole
range of applications and ensure that everything works as you expect. Only
then, once you are entirely satisfied, introduce the changes to the live
database and even then be sure that you can roll your changes back if it all
goes horribly wrong.

Brian.

www.cryer.co.uk/brian|||debian mojo (debian_mojo@.yahoo.com) writes:
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.

Sure. Apps that do SELECT * on the tables and then spit out all columns
somewhere, or assume that it has a certain width will be confused by an
extra column if you add one. If you add the new column anywhere but last,
apps that refers to columns by column number will croak. All this is
bad practice, but since this site already has proven a fondness for
bad practice...

As Brian says, you need to test any changes in a safe environment. Which
includes finding out how long time it takes to add the indexes and the new
columns, so you can determine the downtime.

I should have added that beside looking for unique indexes, also look
for existing IDENTITY columns and existing guid columns, as they can
be used for the task.

> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?

Read Brian's article again. There was a lot of good advice there!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Replication ntext only by UPDATE

Dear group,
in the SQL-Server documentation it says:
"Merge replication supports the replication of text, ntext, and image
columns only if they have been updated explicitly by an UPDATE statement
because it causes a trigger to fire that updates meta data ensuring that the
transaction gets propagated to other Subscribers."
Is this also valid for INSERT-Statements?
In our case we have a table where only INSERTS of new rows occur (incl.1
ntext column). Can we replicate this by merge replication without problems?
Do you have a URL to 'prove' this? ;-)
thanks a lot,
regards,
ERic
I understand that you have to have a trigger that fires on insert, to update
the text column in the row again.
I haven't tried this myself.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Eric Voigt" <eric@.ericvoigt.de> wrote in message
news:2ubq8aF28taboU1@.uni-berlin.de...
> Dear group,
> in the SQL-Server documentation it says:
> "Merge replication supports the replication of text, ntext, and image
> columns only if they have been updated explicitly by an UPDATE statement
> because it causes a trigger to fire that updates meta data ensuring that
the
> transaction gets propagated to other Subscribers."
> Is this also valid for INSERT-Statements?
> In our case we have a table where only INSERTS of new rows occur (incl.1
> ntext column). Can we replicate this by merge replication without
problems?
> Do you have a URL to 'prove' this? ;-)
> thanks a lot,
> regards,
> ERic
>
>

replication monitor group

Hi,
I am part of the replmonitor role of a distribution
database. I am able to view the agents of the distribution
database but i am unable to start or stop any agent. the
books online says that i should be able to. Reference
Replication/administering and monitoring replication/Tools
for administering and monitoring replication/Replication
Monitor: "You can use Replication Monitor
to: ....Administer agents and subscriptions including
starting and stopping agents and reinitializing
subscriptions".
what did i miss?
Thankx for any help
do a search in BOL for replmonitor and look in Role Requirements. It states that this role can only view. It further states that you can't change anything unless you are also a member of the sysadmin role.
Likewise you can't start or stop agents unless you are a member of the sysadmin role, although this is left unstated in BOL.
In the quote you gave, it does say you can stop and start replication agents using replication monitor, but what is left unsaid is that you need the correct permissions to do so. If you do search on BOL for replmonitor and look in the Replication Monitor
section it does say however "however, the user will not ba ale to administer replication." Starting and stopping agents falls under the umbrella of administering replication.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"mona" wrote:

> Hi,
> I am part of the replmonitor role of a distribution
> database. I am able to view the agents of the distribution
> database but i am unable to start or stop any agent. the
> books online says that i should be able to. Reference
> Replication/administering and monitoring replication/Tools
> for administering and monitoring replication/Replication
> Monitor: "You can use Replication Monitor
> to: ....Administer agents and subscriptions including
> starting and stopping agents and reinitializing
> subscriptions".
> what did i miss?
> Thankx for any help
>