Automating the replication process

Question

Hi Everyone,

I am wondering if there is a way of automating the whole replication process where a publication and a subcription can be created automatically through a program (with specific parameteres) ?

Any ideas or hints will be appreciated,
Thanks!

Answer 1

Hi,

 

    This is interesting. There are some sps (stored procs) on SQL Server, that can be used to setup merge replication.

 

Enabling merge replication on a db: sp_replicationdboption  'db name', 'merge publish', true

Creating a merge publication: exec sp_addmergepublication

Adding snapshot agent job: [exec sp_addpublication_snapshot ....

Adding each article: exec sp_addmergearticle

Configuring added/removed columns: exec sp_mergearticlecolumn (if needed only, by default, all columns are part of publication)

 

    However, I am not sure of Transactional and other types of replication. Please let me know, if this is not what you intended.

 

Thanks

Udaya

Answer 2

Hi,

It might work, but here is a bit more detail of what i am trying to accomplish. Maybe this will give a better idea.
I am trying to install SQL express through a script, so there will be no installation wizard and the client does not have to go through it. Once that done, i want to set up merge replication the same way. through scripts. So that the whole thing works at the back without the need of calling the Management Studio Express.

I might be wrong, but for running those SPs one would need to call the Management Studio then connect to it and then run it from there?

Thanks,

Answer 3

Hi,

 

    SQL Server Deveoper Edition (Which I used), allows you to run these through osql.exe. I suppose it is the same case for Express edition too.

 

Thanks

Udaya

 

Answer 4

SQL Server 2000 MSDE engine and SQL Server 2005 Express can participate in all types of replication as long as they are the subscriber. They are not supported as publishers for replication.

Answer 5

MSDE can be a publisher for merge replication.

Consult: http://msdn.microsoft.com/en-us/library/ms143470.aspx

Publishing from SQL Server Express

SQL Server MSDE could serve as a Publisher for merge publications. SQL Server Express, the replacement for MSDE, cannot server as a Publisher. It can subscribe to merge, transactional, and snapshot publications. Merge replication and transactional replication with updating subscriptions both allow changes to be propagated from Subscribers back to the Publisher. For more information about replicating to SQL Server Express, see Replicating Data to SQL Server Express.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

if you dont want to use scripts, you can also set up replication from a .net program using rmo managed code assembly. more details about it are here http://technet.microsoft.com/en-us/library/ms146869.aspx

Answer 7

Thanks for the info Hilary. Subsequently, will it be possible to publish transactional replication via 'push' subscriptions, making Sql Server 2005 Express a publisher in essense? If so, are there any repercussions of doing so?

Answer 8

No, SQL Server Express can't be a publisher for any type of replication. The fact that you want it to push to subscribers or have the subscribers pull from them has no bearing on the matter.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941