[SQL2000] Transactional replication and database filegroups

Hello everybory
Some general question about Transactionnal Replication features on SQL 2000
In case of using Trans. Repl. to duplicate a whole database to another SQL
2000 server, does the Transactionnal Replication preserve database's
filegroup (i.e. tables/indexes distributed accross different files on disk ) ?
No info found about it in the BOL or KB.
I have made some testings and even when the destination database is created
with multiple filegroups before transactionnal replicatiion activated, all
the tables fall in the PRIMARY filegroupn whatever their initial filegroup in
the source database.
Any option or hints to do this ?
Thank in advance
Jb,
you could script out the creation of filegroups and creation of objects onto
particular filegroups. On the publication properties, this script could be
made to run before the snapshot is applied. On the article properties,
naming conflicts section, you can select to leave the table intact if it
already exists. This bypasses the inbuilt snapshot schema generation.
HTH,
Paul Ibison
"Jb Vernejoux" <JbVernejoux@.discussions.microsoft.com> wrote in message
news:AB06D8F0-1CB8-4E44-AC66-FEBC5311CD8C@.microsoft.com...
> Hello everybory
> Some general question about Transactionnal Replication features on SQL
2000
> In case of using Trans. Repl. to duplicate a whole database to another SQL
> 2000 server, does the Transactionnal Replication preserve database's
> filegroup (i.e. tables/indexes distributed accross different files on
disk ) ?
> No info found about it in the BOL or KB.
> I have made some testings and even when the destination database is
created
> with multiple filegroups before transactionnal replicatiion activated, all
> the tables fall in the PRIMARY filegroupn whatever their initial filegroup
in
> the source database.
> Any option or hints to do this ?
> Thank in advance
|||use a pre snapshot command that adds the file groups. (Alter database test
add filegroup file_group).
and then add some files to this filegroup
ALTER DATABASE test
ADD FILE(NAME = 'test_FG', FILENAME = 'C:\temp\test_FG_Data.NDF', SIZE = 1,
FILEGROWTH = 10%) TO FILEGROUP [test]
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uuKyWTciEHA.2664@.TK2MSFTNGP11.phx.gbl...
> Jb,
> you could script out the creation of filegroups and creation of objects
onto[vbcol=seagreen]
> particular filegroups. On the publication properties, this script could be
> made to run before the snapshot is applied. On the article properties,
> naming conflicts section, you can select to leave the table intact if it
> already exists. This bypasses the inbuilt snapshot schema generation.
> HTH,
> Paul Ibison
> "Jb Vernejoux" <JbVernejoux@.discussions.microsoft.com> wrote in message
> news:AB06D8F0-1CB8-4E44-AC66-FEBC5311CD8C@.microsoft.com...
> 2000
SQL[vbcol=seagreen]
> disk ) ?
> created
all[vbcol=seagreen]
filegroup
> in
>
|||ok,
thanks all for your info
Jb VERNEJOUX
"Hilary Cotter" wrote:

> use a pre snapshot command that adds the file groups. (Alter database test
> add filegroup file_group).
> and then add some files to this filegroup
> ALTER DATABASE test
> ADD FILE(NAME = 'test_FG', FILENAME = 'C:\temp\test_FG_Data.NDF', SIZE = 1,
> FILEGROWTH = 10%) TO FILEGROUP [test]
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uuKyWTciEHA.2664@.TK2MSFTNGP11.phx.gbl...
> onto
> SQL
> all
> filegroup
>
>

[REPL] server config for distributor server?

We are going to have two SQL 2000 Servers with a SQL 2005 distributor server that will handle 1 way transactional replication. What kind of server should I ask IT to spec out that would satisfy an installation such as this?

Our production DB is 30GB and we are replication about 60% of the data to another server.rack mountable
dual processor with hyperthreading
100 GB RAID (disk space is not that important, but might as well make it RAID)
16 GB Memory (from what I understand memory is the most important attribute)

any other suggestions...?

@update_mode for Bidirectional Transactional Replication

Hi
I'm in the process of working out how we are going to set up
replication for a client. Quite a daunting task.
I'm think we are going to use Bidirectional Transactional Replication,
as described here:
http://support.microsoft.com/default.aspx?scid=820675
If you look at the sample you will see that they use @.update_mode =
N'sync tran' when creating the subscription. Is this necessary? I set
it up using @.update_mode = N'read only', which seemed to work fine, but
I am worried that I might be missing something.
Any comments welcome...
Thanks
Gary
that's a doc bug - it should read readonly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Gary van der Merwe" <garyvdm@.gmail.com> wrote in message
news:1130327855.663530.282590@.o13g2000cwo.googlegr oups.com...
> Hi
> I'm in the process of working out how we are going to set up
> replication for a client. Quite a daunting task.
> I'm think we are going to use Bidirectional Transactional Replication,
> as described here:
> http://support.microsoft.com/default.aspx?scid=820675
> If you look at the sample you will see that they use @.update_mode =
> N'sync tran' when creating the subscription. Is this necessary? I set
> it up using @.update_mode = N'read only', which seemed to work fine, but
> I am worried that I might be missing something.
> Any comments welcome...
> Thanks
> Gary
>

@bitmap was not supplied to upd procedure in transactional

Initially when I had setup my transactional replication I had created it with
out taking a snapshot and by synching the databases.
Now I am trying to add new table to one of my existing publication in
production by creating scripts for ins, upd and del in test server and
deploying it to subscriber but it throws an error saying
"sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
Inserts just work fine.
This is what i used to add-
exec sp_addarticle @.publication = 'Repl_Source'
, @.article = TableName
, @.source_table = TableName
, @.sync_object = null
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
how many columns are in your table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
> Initially when I had setup my transactional replication I had created it
> with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>
|||18 columns.
If I rememebr correctly I have tested same before a 6 months agao on
different table and i rememeber it working. Not sure what changed.
"Hilary Cotter" wrote:

> how many columns are in your table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
> message news:AF5F5FE4-E43C-48C6-8FF9-9739C7C3DE62@.microsoft.com...
>
>
|||Hi Hilary,
I think this is what thats happenening
- As I have scripted ins, upd and del scripts in dev environment by
generating them by running snapshot in transactional replication, those are
scripted with @.bitmap varaiable.
- Looks like the code I have used to add subscription
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
is trying for ins, upd and del procs with no @.bitmap.
Instead of above code I have used
exec sp_addsubscription
@.publication = Repl_Source,
@.article = TableName,
@.subscriber = SERVERNAME,
@.destination_db = 'Repl_Destination',
@.sync_type = N'automatic',
@.subscription_type = N'pull',
@.update_mode = N'read only'
then when i made data changes it asked for snapshot to be execute dfirst and
it created sp's at destination with out bitmap
As all my other sp's were created during initial setup with bitmap i would
like them to be in sync . rather than some with bitmap variable and some with
out..
Any advise....
"SQL Replication Guy" wrote:
[vbcol=seagreen]
> 18 columns.
>
> If I rememebr correctly I have tested same before a 6 months agao on
> different table and i rememeber it working. Not sure what changed.
> "Hilary Cotter" wrote:
|||return to your publisher and in qa change to the publication database, then
recreate your replication procs by issuing a sp_scriptpublicationcustomprocs
'publicationName'
then copy what you find in the results pane, go to the subscriber, change to
the subscription database, and paste these results into qa and run them.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SQL Replication Guy" <SQLReplicationGuy@.discussions.microsoft.com> wrote in
message news:C261558E-58A6-4810-A5E1-AB24439B9A9E@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> I think this is what thats happenening
> - As I have scripted ins, upd and del scripts in dev environment by
> generating them by running snapshot in transactional replication, those
> are
> scripted with @.bitmap varaiable.
> - Looks like the code I have used to add subscription
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
> is trying for ins, upd and del procs with no @.bitmap.
>
> Instead of above code I have used
> exec sp_addsubscription
> @.publication = Repl_Source,
> @.article = TableName,
> @.subscriber = SERVERNAME,
> @.destination_db = 'Repl_Destination',
> @.sync_type = N'automatic',
> @.subscription_type = N'pull',
> @.update_mode = N'read only'
> then when i made data changes it asked for snapshot to be execute dfirst
> and
> it created sp's at destination with out bitmap
>
> As all my other sp's were created during initial setup with bitmap i would
> like them to be in sync . rather than some with bitmap variable and some
> with
> out..
> Any advise....
>
> "SQL Replication Guy" wrote:
|||Hi Hilary,
I was trying several options soory could get to try your option but this
worked--
sp_addarticle
@.publication = 'Repl_Source',
@.article = 'TableName',
@.source_object = 'TableName',
@.destination_table = 'TableName',
@.ins_cmd = 'call sp_MSins_TableName,
@.del_cmd = 'call sp_MSdel_TableName',
@.upd_cmd = 'mcall sp_MSupd_TableName', -- MCALL CREATED bitmap in UPD sp
@.type = 'logbased',
@.pre_creation_cmd = 'drop',
@.status = 16,
@.source_owner = 'dbo',
@.vertical_partition = 'false',
@.filter = Null,
@.auto_identity_range = 'false'
and then
exec sp_addsubscription @.publication = 'Repl_Source'
, @.article = TableName
, @.subscriber = SERVERNAME
, @.destination_db = 'Repl_Destination'
"SQL Replication Guy" wrote:

> Initially when I had setup my transactional replication I had created it with
> out taking a snapshot and by synching the databases.
> Now I am trying to add new table to one of my existing publication in
> production by creating scripts for ins, upd and del in test server and
> deploying it to subscriber but it throws an error saying
> "sp_MSupd_tablenameexpects parameter '@.bitmap', which was not supplied"
> Inserts just work fine.
> This is what i used to add-
> exec sp_addarticle @.publication = 'Repl_Source'
> , @.article = TableName
> , @.source_table = TableName
> , @.sync_object = null
> exec sp_addsubscription @.publication = 'Repl_Source'
> , @.article = TableName
> , @.subscriber = SERVERNAME
> , @.destination_db = 'Repl_Destination'
>
>