Replication deployment model

How to package the correct set of necessary assemblies along with the client
to ensure that the software can function correctly while deploying
replication implementation along with software.
Is MS planning to provide a merge module or similar for the replication
featureset?
what is the recommended deployment model for stand-alone clients which are
replicating(Merge Replication) between a local and remote server where the
local doesn't include an install of the SQL management tools? (It will have
express.)
Abhi,
you can use RMO to create a pull subscription. To synchronize, you can also
use RMO or Windows Synchronization Manager or invoking the agent executable
files from the command prompt with a supplied set of parameters.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

replication and schema change

Hello everyone!
We are software developement company. We are planning to setup replication
for few our customers. But our software is still under developement and
database schema is changing quite often.
What is the best, fastest and easiest way to implement this database chnages
at our "replication" customers?
Uros
Uros,
sp_repladdcolumn and sp_repldropcolumn cater for a lot of schema changes. To
alter a column take a look at this article:
http://www.replicationanswers.com/AddColumn.asp. On SQL Server 2005 things
are easier: http://www.replicationanswers.com/AlterSchema2005.asp.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||An alternative method which allows for more than just adding or removing 1
column at a time...
--Drop Subscription & Article
exec sp_dropsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
exec sp_droparticle @.publication = 'fxDB6_Pub1'
, @.article = 'MyTable'
-- Make DDL changes
ALTER TABLE MyTable
ALTER COLUMN...
-- Add article
exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
@.source_owner = N'dbo'
, @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
N'logbased', @.creation_script = null
, @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x00000000000000F3, @.status = 16
, @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
@.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
@.filter = null
, @.sync_object = null, @.auto_identity_range = N'false'
-- Add Subscription(s)
exec sp_addsubscription @.publication = 'Pub1'
, @.article = 'MyTable'
, @.subscriber = 'MySubscrServer'
, @.destination_db = 'SubscrDBName'
, @.sync_type = 'automatic'
-- Start Snapshot agent - creates snapshot only for MyTable article
-- Distribution agents will ship to subscribers
Another solution for changes to many tables:
- drop all subscriptions
- drop publication
- make changes
- re-create publication, with script of course (ui takes too long w/ many
tables)
- add subscriptions
- fire up agents...
Regards,
ChrisB
www.MyDatabaseAdmin.com
"uros" wrote:

> Hello everyone!
> We are software developement company. We are planning to setup replication
> for few our customers. But our software is still under developement and
> database schema is changing quite often.
> What is the best, fastest and easiest way to implement this database chnages
> at our "replication" customers?
> Uros
|||Chris - Good suggestion. However, "@.schema_option =
0x00000000000000F3" can be a different value depending on the options one
may have chosen through the interface during the first attempt for the setup.
-A
"Chris" wrote:
[vbcol=seagreen]
> An alternative method which allows for more than just adding or removing 1
> column at a time...
> --Drop Subscription & Article
> exec sp_dropsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> exec sp_droparticle @.publication = 'fxDB6_Pub1'
> , @.article = 'MyTable'
> -- Make DDL changes
> ALTER TABLE MyTable
> ALTER COLUMN...
> -- Add article
> exec sp_addarticle @.publication = N'Pub1', @.article = N'MyTable',
> @.source_owner = N'dbo'
> , @.source_object = N'MyTable', @.destination_table = N'MyTable', @.type =
> N'logbased', @.creation_script = null
> , @.description = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x00000000000000F3, @.status = 16
> , @.vertical_partition = N'false', @.ins_cmd = N'CALL sp_MSins_MyTable',
> @.del_cmd = N'CALL sp_MSdel_MyTable', @.upd_cmd = N'MCALL sp_MSupd_MyTable',
> @.filter = null
> , @.sync_object = null, @.auto_identity_range = N'false'
> -- Add Subscription(s)
> exec sp_addsubscription @.publication = 'Pub1'
> , @.article = 'MyTable'
> , @.subscriber = 'MySubscrServer'
> , @.destination_db = 'SubscrDBName'
> , @.sync_type = 'automatic'
> -- Start Snapshot agent - creates snapshot only for MyTable article
> -- Distribution agents will ship to subscribers
> Another solution for changes to many tables:
> - drop all subscriptions
> - drop publication
> - make changes
> - re-create publication, with script of course (ui takes too long w/ many
> tables)
> - add subscriptions
> - fire up agents...
> Regards,
> ChrisB
> www.MyDatabaseAdmin.com
> "uros" wrote:

Replication Advice needed

Hello,

We are developing a software for a logistic company, which will have around 1000 branches, and we need to synchronize the database(SQL Server) between all the branches using the database at web server, i.e the branches can get the new data from the web server,as well as push the data at the web server, please tell us how can we accomplish this task,

ThanksThis wont work if I'm reading this correctly. You want to have a server both receiving and pushing data. Besides using it as the web server and replication on one server to feed 1000 sites. I say get 4 deal Racs Use
1. database server
2. replication server
3. webserver
4. backupserver.
at sites, have 2 server interfaces and one for live and the other for replication. Users should never get the live data. They only see the replication and thats transparent to them. Hope this helps. I'd also checkout lazydba.com real good info

Replication Active Connection

Using SQL Server 2000 transactional replication which is running continously
to populate our reporting server. We have a thrid party software which we
need to use a interface to take a cvs file and import it into their database.
The interface requires that it has exclusive access to the database. Since we
are running replication there is always a constant connection to the
published database. Is there away to stop that connection. I have tried
stopping the synchronization but it seems to me the only way to remove that
connection is to delete the publiction. Is there an easier way.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200610/1
If the third party software needs access to the subscriber database you
could stop the distribution agent, or have the third party application put
the database in read only or single user mode before it starts to do its
work.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"masmith via droptable.com" <u27472@.uwe> wrote in message
news:680d19bf42976@.uwe...
> Using SQL Server 2000 transactional replication which is running
> continously
> to populate our reporting server. We have a thrid party software which we
> need to use a interface to take a cvs file and import it into their
> database.
> The interface requires that it has exclusive access to the database. Since
> we
> are running replication there is always a constant connection to the
> published database. Is there away to stop that connection. I have tried
> stopping the synchronization but it seems to me the only way to remove
> that
> connection is to delete the publiction. Is there an easier way.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200610/1
>

Replicating the schema while preserving existing data

Hello All,
Here is my situation:
I have a core database that is used to define the schema
of a client-owned database for use with some software. As
the software evolves and changes so too will the schema of
the core database. Ideally these changes should be
propogated down to the client-owned database;altering the
schema to match the new core version without destroying
the data that may already exists.
My attempts to use replication for this have failed when I
perform the following:
-The core database has a single table with three fields.
-The client database has nothing.
*I configure and activate replication
- Now the client database has an identical copy of the
table from the core database.
*I make changes to the core database schema by adding
three more fields to the table.
- Now the client database and core database have
differeing schemas.
*I add data to the client database table.
*I initiate replication from the core to the client
database expecting my three new fields to be appended to
the table without destroying my existing data.
-I don't get what I expect, instead I get a the original
three field table that was first replicated into the
client database and the data is missing.
How can I replicate a schema change from one database into
another without destroying any of the data that may
already exist on the target database?
Thanks,
Marc Deskins
marcdeskins@.hotmail.com
Marc,
you'll need to use sp_repladdcolumn, and after that there's no need to
reinitialize or do anything else, provided you haven't done a nosync
initialization. Just synchronizing will be enough.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for the quick reply. I'll look into using
sp_repladdcolumn.
Marc Deskins
|||Paul,
Using sp_repladdcolumn was only a partial success. While
the newly added column is propogated down to the client
database from the core database the data that was added to
the client database before synchronizing is deleted.
What I am trying to do is have the schema changes to the
tables in the core database propogate down to the client
database without deleting data that was entered before
synchronization.
I understand this is sometimes unavoidable (dropped
columns etc.) so what I am concentrating on right now is
synchronizing the changes to the schema due to column
additions.
So if I have a table that looks like this in the client
database:
Field1|Field2|Field3
-Data-|-Data-|-Data-
-Data-|-Data-|-Data-
-Data-|-Data-|-Data-
And I make a change in the core database to the table so
it now looks like this:
Field1|Field2|Field3|Field4
Then I a synchronize the client and core databases so the
tables have matching schemas.
I want my newly synchronized table in the client database
to look like this:
Field1|Field2|Field3|Field4
-Data-|-Data-|-Data-|
-Data-|-Data-|-Data-|
-Data-|-Data-|-Data-|
Not like this (which is what it does now):
Field1|Field2|Field3|Field4
Is there something I am missing or can this not be done
using SQL Server 2000 replication?
Thanks,
Marc Deskins

>--Original Message--
>Marc,
>you'll need to use sp_repladdcolumn, and after that
there's no need to
>reinitialize or do anything else, provided you haven't
done a nosync
>initialization. Just synchronizing will be enough.
>HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>.
>
|||Marc,
something is definitely wrong here. Can you post up the table schema
(Field1|Field2|Field3) and script out the publication and post it up. Also,
please confirm that you are on sp3. I'll try to reproduce, because what
you're seeing shouldn't happen.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)