December 20, 2004 at 10:49 pm
Heya all,
2 questions from a replication newbie...havent implemented replication yet, just preparing for it...any comments greatly appreciated!
1) Is there any way to replicate structural changes to the DB? Our system is always changing (New columns, new tables, procedures, etc). If these cant be automated, what approach do other people use to solve this? Do you also have to create a new publication every time there is a structural change??
2) Is there a way to monitor replication automatically? When implemented, we will use one server for OLTP, and the 2nd for reporting. Say the OLTP DB stops replicating for some reason, is there a way to be notified that this has happened?? (so we can change the people using the reporting server to the OLTP server)
might throw in another question- anyone suggest a COMPREHENSIVE book/website for replication info and examples? all the info i have found so far just gives quite basic examples (ie using 1 server as publisher and distributor, 2nd as subscriber... we want to have 1 as pulisher and the 2nd as distributr and subscriber and there doesnt seem to be any info on this!
Cheers,
Ben
December 21, 2004 at 7:04 am
Replication can replicate structural changes, but it is not the best. In transactional replication, addin new columns is easy, you just execute the sp_repladdcolumn sp, and replication will take care to replicate the new column to all of your subscriber.
But adding a new table, is not that easy. You first need to add it to the publication, then add the subscriptions to this new table, generate a new snapshot, and sinc all your subscriber.
Before implementing replication, you should read all existing types in Books on line. There are many articles that can help you getting started.
Also, read about Snapshot replication, because it may the best for you depending the size of tables and network speed.
After you install replication... At the distributor (in EM) is will be visible the replication Monitor, to help you check the state of replication. And remember that replication runs with jobs, wich you can modify to notify you on errors.
December 21, 2004 at 3:22 pm
So, as far as #1 goes... I am currently over a merge replication environment. adding columns and tables is easy and doesn't require anything more than running a stored procedure. But, if you want to edit the default value for a column or change a datatype, add an index, etc., I've learned that dropping replication after business hours, recreating the publication, and repushing out the subscribers seems to work best. I think there may be a procedure for running queries via the replication agent, but I don't think I'd trust it for schema changes. Also, you can actually script out a publication setup, so you can just run it from query analyzer to recreate the publication as opposed to manually reconfiguring the publication.
Now, #2... you can set up alerts. Go into your management folder in enterprise manager, then go into SQL Agent, then go into your operators entry. Configure an operator for net messages, emails, and text messages (via cell phone, pager, whatever). Then you go into the jobs folder, find your replication jobs, and edit the job to send out alerts on completion. Be sure to use on completion instead of on success. Failed replication jobs sometimes report success... don't ask me why.
Besides that, good luck!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply