Replicate SPs and UDFs - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

Replicate SPs and UDFs

Author

Topic

thombeaux
Starting Member

18 Posts

Posted - 2007-08-02 : 13:25:23

Can changes made to replicated stored procedures (and User-defined Functions) on the Publisher update the Subscriber database?

If I ALTER a SP (or UDF) on Database1 with peer-to-peer Transaction Replication to Database2, will the alter replicate to the Subscriber(s)?

Thomas

ThomBeaux

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-02 : 13:31:58

Yes if you setup replication to do this. You just need to make sure to select these options when you setup replication.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 13:41:23

If you have already set up the replication, you can add the articles later by using sp_Addarticle. Check out books on line for more info on that.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

thombeaux
Starting Member

18 Posts

Posted - 2007-08-02 : 14:52:59

dinakar and tkizer,

Have either one of you run Transaction Replication in a Production environment?

Thomas

ThomBeaux

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 14:55:47

quote:
Originally posted by thombeaux

dinakar and tkizer,

Have either one of you run Transaction Replication in a Production environment?

Thomas

ThomBeaux

Yes I have setup and support Transactional Replication on a production environment.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-02 : 15:07:10

quote:
Originally posted by thombeaux

dinakar and tkizer,

Have either one of you run Transaction Replication in a Production environment?

Yes. In fact, it's a mission critical process for us. We've got it setup on 6 different databases. It's been in production for around 4 years. We've used it on SQL Server 2000 and 2005.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

thombeaux
Starting Member

18 Posts

Posted - 2007-08-02 : 15:32:20

The reason I ask this, is because we have a Production system, ~400 tables, ~100 UDFs, and 1000s of SPs. We have 5 different Publications for the tables and a seperate publication for SPs and a seperate one for UDFs. The database is ~200 GBs in an Active/Active cluster 64-bit SQL Server 2005.

We used the ALTER statement to change a UDF, and now it has become deselected from the Publication, thus did not replicate. Strange!!!

What could have happened? Do I need to re-create the publication/subscription for the UDFs? Can I add the UDF back to the Publication-will it replicate? Do I have to restart the snapshot?

Thanks,
Thomas

ThomBeaux

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 16:19:11

No you dont have to reinitialize or restart the snapshot. You can just add the UDF to the publication. Which is what sp_Addarticle does.

Here's a sample: This is for adding a table. you might need to add/modify parameters for a UDF.


  exec sp_addarticle  @publication =  'Pub_Name' 
     ,  @article =  'Article_Name' 
     ,  @source_table =  'Article_Name' 
  

  exec sp_addsubscription  @publication =  'Pub_Name'
     ,  @article =  'Article_Name'
     ,  @subscriber =  'subscriber_server' 
     ,  @destination_db =  'subscriber_db'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

thombeaux
Starting Member

18 Posts

Posted - 2007-08-02 : 17:23:57

That does not answer my question about why the UDF was unselected after running the ALTER on the UDF...

Thomas

ThomBeaux

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-02 : 17:44:19

Was it actually being published? or you thought it was?
In 2000, we cannot alter any table that is being replicated. We have to use special replication related procs to change the schema. I know thats not needed anymore in 2005 and you can directly run ALTER statements. I am not sure if it was the ALTER that caused the issue. AFAIK, replication drops/recreates the object. So you can perhaps look at the create_date/modify_date of the function in sys.objects and see when was it last modified.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

thombeaux
Starting Member

18 Posts

Posted - 2007-08-02 : 18:00:06

All UDFs were in this Publication. It is the only one not selected now, after we ran the ALTER statement.

Thomas

ThomBeaux

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources