June 26, 2014 at 3:34 pm
Hello all,
I support replication of 5 DB's replicating to 5 servers. (Transactional) For what ever reason the previous DBA does\did not replicate SP's, Indexes, Views or SP's via the MSSQL Replication options. However, he custom wrote a few jobs the does just that. I am not sure why and no one here seems to know.
The only thing I can think of is; the replicated db's may have different SP's, Indexes and Views to server a business need and maybe they would be deleted via replication.
My question(s) is: If I enable replications of Indexes, SP's and Views via the MSSQL replication options. Would it delete the one off Indexes, SP's and Views? Or will replication only add to what's on the replicated db's?
Thank you JZ
June 27, 2014 at 3:46 am
In 2005 I had many issues with replicating views and SP's. So I eventually stopped doing so. Even now I don't do it just because of the issues I had in 2005. I too wrote scripts to run on each server to upgrade views and SP's, and because these scripts always worked, and replication always had issues, I just continued using them - so that may be the reason why it was done.
Having said that - There is no real reason to continue to do so these days.
If you now decide to replicate the vies and SP's, then they will be overwritten with the source version, and any variation that may exist on the servers will be lost.
I would ensure that any indexing is always carried over with replication, with the exception of replication to a reporting server, which may actually require different indexes..
June 27, 2014 at 8:00 am
Thank you for the reply.. I do believe the current setup was written for SQL 2005. I am currently running SQL2008r2 with new SQL2012 server in the works.
June 27, 2014 at 8:54 am
Write for 2008. Things are much better. I am guilty of taking old baggage forwards. It is a flaw -
also in 2012 you will find it actually works out dependencies and such - a whole lot better- so hardly any failures.
June 27, 2014 at 12:43 pm
You can replicate stored procedures, and views if you dedicate yourself to only ever issuing ALTER statements on them to introduce new versions. A lot of places will generate drop/create scripts which will silently withdraw stored procs and views from the replication leaving you with old versions on the subscriber(s).
i don't replicate SPs or views either, because I do not have enough control over the scripts that get generated, or submitted for migration.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply