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.
| Author |
Topic |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-12-11 : 10:46:17
|
| I have replication configured between SQL Server 2005 and Oracle 10g. We use this to replicate data from SQL Server to Oracle. The configuration is:publisher - SQL Serverdistributor - SQL Servertype - snapshot (on an 8 hour schedule)Now, configuring this through the wizards in SSMS was pretty easy. I now have a need to have these steps implemented via scripts. I need to create scripts to (a) completely remove all articles, distributor and publisher from the SQL Server and (b) recreate the publisher, distributor and articles from scratch. Now, not being too familiar with the internals of replication, are there any basic steps I should follow?Also, let me outline what I have done. I am stepping through each wizard and generating the scripts to tailor for our needs and converting these scripts to SQLCMD scripts. We have several identical environments so I'm using this type of script so I do not have to embed variable or sensitive information within the scripts themselves. The only problem I'm having is understanding the logical scripts to break the tasks out into (I wish to model the appropriate steps).Some of the goals I would like to accomplish are (a) model the scripts in some logical order and (b) understand which script calls need passwords and which can be left empty (the wizards either put NULL or empty strings in the script for passwords). |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2008-12-11 : 10:57:33
|
| you can script out replication setup procs from your replication folder in object explorer. Logically, you will need (1) configure distribution (2) add publication (3) add subscriber. so you will end up with sp_addpublication, sp_adddistpublisher (not exactly sure about the name) and sp_Addsubscription procs and their parameters when you script out replication.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-12-11 : 14:30:23
|
| dinakar - thanks for the response.I do have another question regarding the script that creates the actual articles. The version I scripted out (from a publication created via the SSMS wizards), for each table, it executes these procs:- sp_addarticle (once)- sp_articlecolumn (once for each column included)- sp_articlefilter (once)- sp_articleview (once)I am using the @filter_clause parameter of sp_addarticle. Consequently the @filter_clause parameter for both sp_articlefilter and sp_articleview is identical to what is specified for sp_addarticle. If I read the documentation correctly, calling sp_articlefilter & sp_articleview is optional. Am I correct in assuming I do not need to call these and my filter will still be applied to the article (since I supplied the @filter_clause parameter in sp_addarticle)? If so, what's the pro/con of calling/omitting these? |
|
|
|
|
|
|
|
|
Understanding replication